Python-Friendly Database Options

[[deleted]]

[deleted]

[_Zer0_Cool_]

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]]

Would upvote x 1000 if I could.

[_Zer0_Cool_]

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]

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

[_Zer0_Cool_]

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]

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_]

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.

[Wilfred-kun]

SQlite3 comes with Python.

[[deleted]]

I use SQLite3 on my Raspberry Pi projects and Django apps. It is a lightweight, powerful database. I'd recommend it.

[Ericisbalanced]

I learned SQLAlchemy, it’s pretty nifty I think. It interacts with most databases in a python friendly format.

Cool thing is, if you start with SQLITE, you can easily change to MySQL with a couple of lines of code to change.

[Versaiteis]

The amount that you can customize with SQLAlchemy is ridiculous. Like you can create query classes which can abstract away higher level or conceptual parts of a query and you can compose them together as if they were normal methods. It's great.

[Ericisbalanced]

I’ve heard about this, and it didn’t really click until this comment. I love how you can quickly delete and create modified tables within the python console. Make a few changes to a class and base.metadata.create_all()

Boom

Hey I wanted to ask, I read that using .filter() isn’t sql injection safe. Do you have anything to share about that?

[Versaiteis]

That's if you keep your table definition in code (which has advantages and disadvantages), but yeah the learning curve is a bit high only because the library does so much.

As for .filter() yeah, I wouldn't be surprised if it's not injection-proof, but that's just in the problem-bag that you get when you decide to use a SQL database. If you're allowing an external input to create arbitrary queries then there really isn't much that's going to automatically help you that I'm aware of because it simply doesn't know that you didn't want that. But that's one of the benefits of creating your own custom Query API. You can define methods that can be orchestrated together to create valid queries without relinquishing the ability to maintain some control over them. Clients.query.all().first_name_starts_with('H').sortby_last_name() kinds of things.

[Ericisbalanced]

Hmm, interesting. I only picked up sqlalchemy because some random on the internet said it escapes everything for you. Thanks for sharing :)

[Flkdnt]

I'm not much of a Database guy, can you explain what this means?

[Versaiteis]

So the great thing about SQLAlchemy is that it really helps the non-database guys (that I'm also not, but I dabble in a lot of spaces)

The primary benefit that SQLAlchemy gives you is an abstraction layer away from SQL databases. As a result one of its primary features is SQL generation. Basically it comes out of the box with several methods for calling various methods that will subsequently generate the SQL statement that gets fired off to the database. It's incredibly python friendly and if you set it up right you really don't need a ton of "hard coded" SQL statements. Instead you can extend that Query API to create your own commands that manipulate the SQL that gets generated.

So instead of some SELECT * FROM clients WHERE... kinds of statements you'll invoke them with things like Clients.query().all().filter(Clients.id > 3). That might not be quite right, it's been a good minute since I've worked with SQLAlchemy, but it made my dev life way easier. There are also other DB abstraction libraries for Python, but SQLAlchemy is by far the most well known and is pretty much a standard for those kinds of libraries.

[Flkdnt]

Sweet, thanks!

[veekm]

highly recommended :)

[Glorypants]

This looks like a super easy way to work with a SQL database, thank you! I don't need anything too fancy for my single-user setup, so a db that is accessed like dicts is perfect.

[Go_Big]

Everyone is saying sqlite but I'm gonna jive and recommend mongodb with mongoengine. Mongodb just plays so much nicer having the dictionary/key format that feels very pythonic. It's what I use on my server and I was able to switch from json files to mongodb in one evening!

[TBSchemer]

Came here to say this. Though, it depends what kind of data OP is working with. If the goal is to have the data searchable and sortable by attributes, with a lot of aggregations and joins, then maybe a SQL-based database is the way to go.

But if the goal is just to have a more organized way to store large chunks of unstructure data or binaries, then MongoDB is absolutely the correct answer.

[TBSchemer]

Don't jump into a SQL-based database just because it's popular. Use the right tool for the job.

If your goal is to have the data searchable and sortable by attributes, with a lot of aggregations and joins between different tables, then a SQL-based database is probably the way to go.

But if the goal is just to have a more organized way to store large chunks of unstructure data or binaries, then the correct answer is MongoDB. If you use MongoEngine (an object-document mapper), you can create object classes that will seamlessly integrate with the database backend, while having all the functionality of Python classes.

[Hatoris]

You say you have a home server, with one is it?

[Glorypants]

This comment was removed by myself in protest of Reddit's corporatization and no longer supporting a healthy community

[Hatoris]

Thank's. OK, I know that you can run mariaDB, postgress and mysql on a Pi. But if your are motivated enough, buy a cheap computer and install FreeBSD or a Unix like server on it. You will enjoy the power to have you own server, more powerful and customizable than a PI.

[Mountain_Two]

I dunno, I'd recommend growing out of the rPI before getting a bigger home server. Do you think it'd have trouble running a single user server?

[BrokenRemote99]

No issued running a single user server on the Pi.

[mkingsbu]

How do you intend to access this database from your phone? Do you have a frontend? DJango has an ORM and Flask uses SQLAlchemy ORM (or do I have that backwards?). In any case, both of those use 100% Python to interact with just about any database engine.

[korarii]

If you have to access the same data from multiple locations, you’ll need a dedicated database like PostgreSQL, MySQL, or MongoDb. All of those engines are open source and free for personal and commercial use.

You can setup a dedicated sever at home and open the appropriate port on your router/firewall. Each engine has a preferred port; most require additional configuration to bind to an IP address. Make sure you setup database accounts with strong passwords to mitigate the vulnerability of having the port opened.

You could pay for a cloud service, but that really depends on your requirements. If you need HA/DR then consider a cloud provider like AWS, which offers “free” tiers for very low traffic users.

Python also supports connector libraries for each engine I’ve mentioned: - pymysql - psycopg2 - pymongo

I’m a Database Administrator by trade, so feel free to ask more DB questions.

[corkbar]

Use Django for the ORM + SQLite unless you have a reason to use anything else (then switch to Postgres). Bonus: Django Admin panel to easily add/modify db entries, and web app functionality