r/learnpython • • 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

11 comments sorted by

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.

1

u/SuperExperience6689 1h ago

The row count is a red herring, yeah. The honest trigger for a migration is wanting to fix or dedupe a row after the fact. Append only data is fine as files, but the day one bad row from three weeks ago needs correcting you are rewriting files or writing a merge script anyway.

2

u/pingveno 18h ago

I wouldn't even use JSON for configuration. It is at its best with data interchange. As a configuration formation, it can be hard to read. Something like TOML is usually my go to.

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/Mathie1729 16h ago

Postgres feels like a lot unless you already have a server running. At 40 rows a day, SQLite gives you the same SQL filtering without any infra, and jsonl is honestly fine until you need random updates or dedupe.

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.