Databases and python

Question

I am writing a music catalogue application with PyQt using to display GUI. I have a problem about choosing database engine. There are simply too many options. I can use:
-PyQt build-in QSql
-sqlite3
-SQLAlchemy (Elixir)
-SQLObject
-Python DB-API
Probably there are far more options, this list is what I got from google (I'm open for any other propositions). If I decide to use some ORM which database system should I use? MySql, PostgreSQL or other? I know some MySql, but I heard a lot of good thing about PostgreSQL, on the other hand sqlite3 seems be most popular in desktop applications. I would be grateful for any advice.

EDIT: An application is meant to work on Linux and Windows. I think database size should be around 100-10k entries.

What platform are you targeting? What database size do you expect? — Ali, Mar 25 '12 at 10:11
Good read [orm vs plain sql](http://stackoverflow.com/q/494816/205528) — Kien Truong, Mar 26 '12 at 09:45

score 4 · Answer 1 · answered Mar 25 '12 at 10:24

4

SQLite3 has the advantage of shipping with Python, so it doesn't require any installation. It has a number of nice features (easy-of-use, portability, ACID, storage in a single file, and it is reasonably fast).

SQLite makes a good starting-off point. Python's DB API assures a consistent interface to all the popular DBs, so it shouldn't be difficult to switch to another DB later if you change you mind.

The decision about whether to use an ORM is harder and it is more difficult to change your mind later. If you can isolate the DB access in just a few functions, then you may not need an ORM at all.

answered Mar 25 '12 at 10:24

Raymond Hettinger

216,523
63
388
485

Thanks. SQLite3 probably will be my choice. I'm just curious, which BD would be better if for some reason cannot use SQLite3? MySql or PostgreSQL? Another question: how about QSql object? I know it's hard to decide, but generally, is it faster/slower than Python's DB API? – fenuks Mar 25 '12 at 11:18
5

@fenuks Well, if you don't know which one is better for your tasks - there is a fat chance that the difference won't matter to you anyway. Pick one at random, and if you are stuck with performance (say in 2-3 years) you will precisely know *what kind* of performance you seek. Only then, you will be able to decide on some *better* database. – bezmax Mar 25 '12 at 12:25

score 1 · Answer 2 · edited Mar 10 '13 at 04:59

You would be better off using an ORM (Object Relational Library) Library that would allow you to design in an OOP way, and let it take care of the database for you. There are many advantages; but one of the greatest is that you won't be tied to a database engine. You can use sqlite for development, and keep your project compatible with Postgresql, Mysql and even Oracle D, depending on a single change in a configuration parameter.

Given that, my ORM of choice is SQLAlchemy, due to its maturity for being well known and used (but others could be fine as well).

Databases and python

2 Answers2