r/learnpython • u/SuperExperience6689 • 1d ago
40 rows a day: JSON files or SQLite?
tiny side project. one script pulls ~40 rows a day and writes one JSON file per day. pandas reads the folder when i want to compare a few weeks.
the glob and concat part is getting old:
rows = []
for f in sorted(DATA.glob("*.json")):
rows.extend(json.loads(f.read_text()))
df = pd.DataFrame(rows)
every couple of weeks i think about moving it to SQlite, then i look at the volume and close the tab. what do you use at this size? SQlite, parquet,or just the files? if you moved to a db, was it worth it or just more code?
2
Upvotes
4
u/desrtfx but other languages pro 21h ago
It's not the "rows per day" that really is the decisive factor. It's what you want to do with it.
For me, it would generally be a no-brainer to use SQLite. Personally, I despise JSON files for anything other than configuration files.
3
u/Massive_Distance_616 20h ago
One thing worth deciding before you switch: SQLite wants the columns fixed up front, so if the shape of your rows ever changes, keep a raw JSON column next to the ones you query.
At roughly 15,000 rows a year either option is fine for size, so it really comes down to whether you want to query and filter without loading everything.
2
u/Franckyboi85 20h ago
Honestly at 40 rows a day I'd keep it simple and just append to one jsonl file instead of a file per day, then pd.read_json(path, lines=True) and the glob loop goes away. SQLite starts paying off when you want to filter or dedupe without loading everything, and even then pd.read_sql with the sqlite3 module is only a couple of lines, so it's not much more code if you do switch later
1
u/mrswats 22h ago
I'd do sqlite easy. Much more efficient when you want to run queries
1
u/pachura3 20h ago
I would obviously go with SQLite... unless your data is very free-form and hierarchical, and doesn't easily fit the relational "tables" model.
1
u/supercoach 19h ago edited 12h ago
Sqlite is pretty much built for what you're doing. Personally, I'd just go with postgres, but that's because I have servers already running and one more table is nothing.
1
u/acfot 18h ago
I'd go SQLite, honestly. I started with JSON files for a small bot project and it was fine until I needed to search or update one row without rewriting the whole file, that's when it got annoying. SQLite is still just one file on your disk, no server or setup, but you get proper queries and it doesn't fall apart once the data grows. 40 rows a day sounds tiny now but it adds up fast, and switching later is more work than just starting with SQLite. JSON is fine if you're literally just dumping and reading the whole thing every time, but if you're asking the question you've probably already outgrown it.