Purpose of SQLAlchemy over MySQLdb

Question

Why do people use SQLAlchemy instead of MySQLdb? What advantages does it offer?

Answer 1

Easier portability among different DB engines (say that tomorrow you decide you want to move to sqlite, or PostgreSQL, or...), and higher level of abstraction (and thus potentially higher productivity).

Those are some of the good reasons. There are also some bad reasons for using an ORM, such as not wanting to learn SQL, but I suspect SQLAlchemy in particular is not really favored by people for such bad reasons for wanting an ORM rather than bare SQL;-).

Answer 2

You don't use SQLAlchemy instead of MySQLdb—you use SQLAlchemy to access something like MySQLdb, oursql (another MySQL driver that I hear is nicer and has better performance), the sqlite3 module, psycopg2, or whatever other database driver you are using.

An ORM (like SQLAlchemy) helps abstract away the details of the database you are using. This allows you to keep from the miry details of the database system you're using, avoiding the possibility of errors some times (and introducing the possibility of others), and making porting trivial (at least in theory).

Answer 3

In addition to what Alex said...

1. "not wanting to learn SQL" is probably a bad thing, however, if you want to get more non-technical people involved as part of the development process, ORMs do a pretty good job at it because it does push this level of complexity down a level. One of the elements that has made Django successful is its ability to let "newspaper journalists" maintain a website, rather than software engineers.

2. one of the limitations of ORMs is that they are not as scalable as using raw SQL. at a previous job, we wanted to get rid of a lot of manual SQL generation and switched to an ORM for ease-of-use (SQLAlchemy, Elixir, etc.), but months later, I ended up having to write raw SQL again to get around the inefficient or high latency queries that were generated by the ORM system.