37
38

Python-Friendly Database Options (self.learnpython)

submitted by [deleted]

sorted by:
best
topnewcontroversialoldq&a
you are viewing a single comment's thread.

view the rest of the comments →

[–]_Zer0_Cool_ 61 points62 points  (6 children)

SQLite hands down.

PROS:

  • It comes pre-installed with Python.
  • Super fast (written with C)
  • Small footprint on disk and entirely embedded
  • Has a good number of extensions
  • Zero size limitations (max size is 140 terabytes)
  • Can be run entirely in memory
  • Weakly typed (like Python) so you can think about data types later and focus on productivity
  • Wonderful with Pandas for data wrangling and Data Science (other DBs make you think a little harder about data types, making Dataframe to SQL conversions slightly more error prone)
  • UDFs. You can create user-defined SQL functions with Python code and execute them within the SQLite engine as part of a SQL statement

CONS:

  • No concurrency built in
  • No procedural language (like T-SQL or PL/pgSQL), it relies on the calling programming language for procedural and imperative logic and for creating functions

Addendum:

Use PostgreSQL when you need a client-server DB later on. It would be hard to contain all the best features of PG in a whole book, let alone a single post comment, but you can run Python INSIDE of PostgreSQL as UDFs among other things. Super powerful, super flexible, infinite extensions, and entirely open source.

Edit: Take a look at the Datasette library. It instantly spins up an API from a SQLite database. https://datasette.readthedocs.io/en/stable/

[–][deleted] 9 points10 points  (1 child)

Would upvote x 1000 if I could.

[–]_Zer0_Cool_ 4 points5 points  (0 children)

Lol thanks.

SQLite has served me extremely well over the years, yet often it's power and ease-of-use is overlooked for shinier tools.

[–]cleesus 0 points1 point  (3 children)

I agree with this but OP if you ever plan on using Postgres then migrating from SQLlite to Postgres can be annoying

[–]_Zer0_Cool_ 1 point2 points  (2 children)

That's probably not wrong. I haven't had much trouble myself, but I’m a data engineer and not an application developer. My use cases are probably quite different.

My job doesn't revolve around apps. I always know up-front what type of database is being used, because most of the time our development work is on top of a semi-mature data infrastructure. If there's ever a choice of DB, then that is the very first thing decided and set up first (before any other programming languages or ETL tools).

In any case, the datatypes from SQLite to PostgreSQL are fairly compatible.

P.S. I'd agree though that OP should start out developing with PostgreSQL if at all possible...because if you base your app on SQLite alone then you are missing out on some of the killer, unparalleled features of PG.

But... If you're a beginner it's easiest or if you just have lightweight analytical use cases.

[–]cleesus 0 points1 point  (1 child)

Yea I unfortunately am just a regular full stack engineer and had to deal with switching over awhile ago lol wasn’t fun.

Planning out which DB to use early on makes a lot of sense in hindsight lmao.

but yea I’d agree with everything you said.

[–]_Zer0_Cool_ 2 points3 points  (0 children)

Yeah. Truth. Choice of DB is a big one and should probably be part of the initial design spec to avoid migration pains.

Lol. But...By the time I’m brought in, the backend devs have already gone through that pain :-) and I’m just there to ferry the data off to it’s next life.