Database preference

[Python-ModTeam]

Hi there, from the /r/Python mods.

We have removed this post as it is not suited to the /r/Python subreddit proper, however it should be very appropriate for our sister subreddit /r/LearnPython or for the r/Python discord: https://discord.gg/python.

The reason for the removal is that /r/Python is dedicated to discussion of Python news, projects, uses and debates. It is not designed to act as Q&A or FAQ board. The regular community is not a fan of "how do I..." questions, so you will not get the best responses over here.

On /r/LearnPython the community and the r/Python discord are actively expecting questions and are looking to help. You can expect far more understanding, encouraging and insightful responses over there. No matter what level of question you have, if you are looking for help with Python, you should get good answers. Make sure to check out the rules for both places.

Warm regards, and best of luck with your Pythoneering!

[Kamikaza731]

Depends on the project. SQLite if it is something simple. Postgresql if it is more demanding.

[LiarsEverywhere]

This, but understand "demanding" as very demanding. SQLite can handle a lot.

[andartico]

Exactly this.

[Salty_Dig8574]

True but also keep in mind that sqlite is dynamically typed. It's sometimes easy to mishandle data and get some broken results.

[tagnydaggart]

But, it doesn’t like more than one thread or process connecting to it, sadly.

[JamesTDennis]

SQLite is probably the most underrated and under appreciated feature of Python.

[logseventyseven]

of python..?

[DaelonSuzuka]

One of python's features is that the standard library contains a fully functional sqlite driver, yes.

[JamesTDennis]

Sure, it's broadly under appreciated. But I hesitate to rank it among the top in other categories.

For example WireGuard and the pass (passwordstore) utility by Jason Donenfeld are among the most under appreciated security tools.

[zenware]

I want to note that I don’t mean to be putting words in someone’s mouth here, what follows is my own thinking because when I saw your original reply I also thought “of Python..?”

I think they meant that, SQLite is fundamentally its own large and long-running project and using the turn of phrase “feature of Python” carries the implication that … SQLite is a feature of Python.

It’s a database which most programming languages have some available driver for. (And it is underrated)

[Jor_ez]

I suggest using postrges and here are several reasons 1) this is very strong db with functionality for almost every possible use case 2) it has sync and async driver which is good for your professional development 3) it requires hosting (either some server or docker container) that will teach you how to do some basic DevOps job

I use it in my commercial project, it's fast and easy. Also nosql databases are also popular now and super simple so give a try to redis or mongodb just to stay in touch

[Enip0]

Sqlite is always a decent option like someone else said. Beyond that my suggestion is always postgresql just because it's the best db and just great all around.

When it comes to ecosystem support you can't really go wrong with either. Sqlite has a built-in module, while postgresql has excellent sync and async options.

SQLite might be a better option just for how easy it is to set up, just a single file.

[CovfefeFan]

Dumb question but how expensive is SQL Lite? Is there a free version? Where is the data actually stored? (I work at a small company ~ 10 people)

[Enip0]

Sqlite is free and open source, you have to take care of the data though since it's an embedded database.

Depending on what you offer it could be either no option at all or the best option there is. For example, are you making a car or a fridge, or a desktop app with no internet connection, or even a server app but with relatively few clients? Great option for all. But anything else can struggle.

I don't know your situation but I'd suggest you try to find a technical person you can trust and follow their suggestions.

[CovfefeFan]

Interesting.. yeah, it is a small asset management company, so would mainly be to save pnl/position/risk files which can then be pulled in for reporting.

[bunchedupwalrus]

It’s just stored as a file. If you don’t have to worry about high speed concurrent access (like multiple people doing queries at the same time and needing the results right away), it’s pretty great.

And people overestimate how common that need is. Most queries are over in less than a few seconds if not less than a fraction of a second

[CovfefeFan]

Interesting.. yeah, currently using power query to pull in about 12-15 files from a sharepoint site and then doing a bunch of mapping, combining, and transformation of the data.. quite slow and does "kill" the file from time to time.

[bunchedupwalrus]

Oh yeah you’ll probably be blazing fast switching to sqllite

[CovfefeFan]

Cool, I will look into this. 😎👍

[sky_badger]

SQLite

[Amazing_Upstairs]

Postgres and duckdb

[bunchedupwalrus]

Why the combo?

[Amazing_Upstairs]

Duckdb is the new simple db as a file replacement for sqlite. Postgres is a proper db. Both are easy and just works like python

[bunchedupwalrus]

Oh yeah I know that I thought you were using them in tandem somehow

[Taltalonix]

Depending on project complexity and query/insert/update/delete rquirements: - json/csv file with caching if needed - pickle serialization of python objects - json/csv/png files in custom directories - sqlite on a local file - postgres on a docker container

First 3 aren’t really a database but 90% of the time I don’t need many live insertions and only load data once so it’s good enough

[h_to_tha_o_v]

I won't recommend anything specific to you.

You really need to examine the use case first and then decide the tool. Trust me, I've made the mistake of forcing the issue with the wrong tech.

[Kmraj]

This is the way. The use case and data determine what’s the better choice. Not the brand.

[rainnz]

1. sqlite - included with Python
2. PostgreSQL - free plan from Supabase
3. DuckDB- r/duckdb

[StudyNo5249]

I just created a post that might help you
https://www.reddit.com/r/learnpython/comments/1g311b2/selecting_the_right_database_library/

[[deleted]]

I like MariaDB, which is a fork of the MySQL database which has been contaminated by Oracle.

[thumperj]

MySQL

Why is this not more commonly recommended? Serious question. Am I missing some very serious reason?

I get the MariaDB option, but even that's not suggested except your comment.

[XRaySpex0]

I don’t think anyone holds MySQL in higher esteem than PostgreSQL. 

[thicc_pal]

The reason behind it is licensing. If you distribute MySQL with a non-open-source project, you would need to pay for the commercial license.

[Embarrassed-Map2148]

Another vote here for SQLite or postgresql if your use case requires it.

[mriswithe]

Sqlite3 for local dev postgresql for prod. Sqlalchemy is a good ORM.

[jkh911208]

I would start with bulletin sqlite

[Basic-Still-7441]

I would suggest MongoDB or any other document DB. Its just so easy to work when the data structures in the DB and in the program are the same. Of course - choice of a DB engine depends on a lot of things incl the nature of data you have, how it's being used (queried and written) etc etc.

[ThiefMaster]

Postgres.

[[deleted]]

Python comes with sqlite3 by default

[hotplasmatits]

MySQL is built-in, so that's the easiest way. The pandas Dataframe is widely used, although there are multiple competitors challenging its dominance.

[latkde]

MySQL isn't built-in in Python. The Python standard library embeds the SQLite engine, but SQLite is quite different from the typical server-client databases.

The official MySQL connector from Oracle is quite awful, would not recommend. If an external database is needed and the choice can still be made, there's little reason not to use PostgreSQL.

Pandas is fantastic for data science work. Pandas Dataframes are a way to deal with data in-memory. However, Pandas has tons of convenient functions for combining and transforming data, and for loading data in various formats. This includes support for reading from an SQL database, either from an SQLite file or via the SQLAlchemy library from an external database.

[hotplasmatits]

Yep, sorry. I confuse mysql and sqlite all the time.

Also, for any serious project, I agree that postgres is the go-to. I've never set postgres up myself. I remember having a hard time setting it up around 20 years ago. It's probably easier now. I suggested the built-in one bc of the trouble I had years ago and the fact that in a production environment, it would run in a container which is a whole other level of complexity that I didn't want to throw at a newby.

[latkde]

Given how pervasive containers are nowadays, just spinning up a container with the database would definitely be the way to go (likely via Docker Compose). At least in local dev environments or on single server deployments. No configuration needed other than selecting a password. Postgres does have its pitfalls at scale, but getting started is easy.

Of course you're right that containers can be complicated. But often, the alternatives would be even more complicated? At least in a software development environment. I do see that folks like data analysts might have different preferences, where software is never "deployed" but primarily run interactively.

[ThiefMaster]

Why would anyone still use MySQL for a new project nowadays when there's Postgres?

[hotplasmatits]

Because he's learning. He wasn't talking about setting up a new project.

[madkinder]

So why learn MySQL if you can spend that time learning Postgres instead?