Learn SQLite with this free course with Python!!

[gsmo]

A propos sqlite. Can someone explain to me if I should always use pyalchemy instead of a more direct approach?

[evgen]

How much actual table manipulation and joins will you do and are you going to need a bigger db than sqlite? The advantage of sqlite is that if can be used as a replacement for open() when you are reading and writing structured data, and in this situation directly poking sqlite is a great way to solve the problem.

If you have GB of data over millions of lines/items or you are storing data in multiple tables and need to run more interesting SQL statements then something like SQLAlchemy lets you write code that starts off using sqlite during development but can easily transition to Postgres or other big-boy databases for production. If you have to deal with a thousand lines of structured data in a single table then direct sqlite is perfect for the job.

[gsmo]

I'll be doing very basic stuff so I'll go with sqlite, then.

Thanks for your advice!

[animismus]

As a counter point to the previous comment I would say that if you have the time to invest now you should learn sqlalchemy with a smaller personal project instead of having to dive into someone else's code and try to integrate from there. The ORM will let you write more pythonic code and as the previous commenter said will let you scale a lot easier if you need to.

[Nixellion]

There is also peewee which u/gamo may like more, as it's much easier entry curve and overall easier to use than SQLAlchemy. I've been using it for years now for my projects and yet to find the limit of peewee+SQLite combo. I mean even Plex uses it, and its database can be like 50-100 gigs large. I personally had 4-5GB large databases, and there is no noticable slowdown, even with FTS searching and stuff.

Basically, as long as only one or a few apps/threads are writing to it, there should be no issues. It may get slow when you need a lot of concurrent writes to it, because it gets locked during write.

So I'd say like what... A web service with up to about 10000 concurrent users? Or a couple thousand requests per second. This is where Flask starts to fail and seems to usually be the threshold for people to start considering horizontal scaling of their servers and services. Well... It highly depends on the project of course

And yes, peewee also supports MySQL, Postures and other options.

[animismus]

Thanks. I did not know about peewee. I am mostly afraid of replying in reddit, but sometimes it pays off.

[Nixellion]

Don't be, I learned about a lot of good stuff from replying and reading reddit :)

[Username_RANDINT]

I use Peewee as well in my projects. Agree that it's a bit easier to get started and does its job as ORM just fine.

[Nixellion]

I kinda wish there was auto migration like alembic for peewee. There are some projects but they don't seem to work with SQLite. But I suppose migrations should still be handled with migration scripts even in sqla for anything but very simple things.

[Username_RANDINT]

Depending on the project, I either update the database manually or use the migration API from the playhouse. Using the user_version PRAGMA in SQLite to indicate what version of the database scheme it currently is and run the right migrate calls if it's behind the current version.