QtSql vs MySQLdb vs SQLAlchemy

Question

I'm developing a PyQt program that will soon switch from an xml type backend to one hosted on a local MySQL server. I've been trying to read around about each of the three options, but thought it might be best to ask ye SO gods.

My current experience in MySQL execution with MySQLdb at the moment and have been using that mostly due to ignorance about the existance of the other two methodologies. Question in short is what are some of the pros/cons of each and which would you choose? Cheers!

Answer 1

I'm not a SO god, but I do have some input. My main experience with SQL in Python is with Django.

The solution matters on what you're willing to commit to. If you want to stick with using the Qt libraries and just the PyQt libraries, then go with QtSql. If you want to just build your application quickly but pull in a few more dependencies, then I'd go with SQLAlchemy. You might run into some issues like the asker of this question, and then you would need to pull in more libraries or pull out your hair.

So, in a nice list style:

MySQLdb

• pro: Pure SQL
• con: Ugly
• con: requies you to write SQL
• con: requires you to manage the cursor, doesn't do any caching, parameterization, etc...
• con: can't switch to a different database backend without rewriting all of your database code

VERDICT: don't use this for anything that you would put into production

QtSQL

• pro: Only uses Qt libraries
• pro: will return Qt objects, so it will integrate with Qt's standard widgets
• pro: can use any database backend that Qt supports
• con: still requires you to write SQL

VERDICT: choose this if you want to write less UI code and more database code

SQLAlchemy

• pro: all database logic can be written out in Python code (since it's an ORM)
• pro: supports many database backends
• con: might require some extra configuration to get things working nicely with Qt

VERDICT: choose this if you want to write less database code and would be fine with solving some API issues.

---

Also, don't bother worrying about performance with any choice -- they should all be roughly equivalent, and the majority of the time will be spent on I/O.