Why do people use SQLAlchemy instead of MySQLdb? What advantages does it offer?
Add a comment
|
3 Answers
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).
2 Comments
Kit
SQLAlchemy claims to consist of a Core and an ORM. Do I need to install MySQLdb in order to interface Python and MySQL? I don't know anything, but the way I understand it, SQLAlchemy Core can replace MySQLdb. Is that right? sqlalchemy.org/features.html
Mike Graham
@Kit, no SQLAlchemy Core does not replace the actual DBAPI2 database adapter like MySQLdb. SQLAlchemy Core does not actually communicate with your database. You need to install MySQLdb (or, preferably, oursql) to communicate with MySQL.
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;-).
3 Comments
SeanJA
Pretty much the same reason for using Active Record in Ruby or Doctrine in PHP (or any other orm really...)
Yaroslav
MySQLdb has no unicode support at the moment (it was turned off after some unicode related bugs were discovered). SQLAlchemy abstracts those encode/decode calls your should perform manually without using it.
Eric Pruitt
"or any other orm really..." Well I had no clue ORMs existed or what they were before today.
In addition to what Alex said...
"Not wanting to learn SQL" is probably a bad thing. However, if you want to get more people who want to avoid SQL involved as part of the development process, ORMs do a pretty good job at it because it abstracts away "raw SQL," pushing that level of complexity down a notch. One of the elements that has made Django successful is its ability to let "newspaper journalists" maintain a website, rather than software engineers.
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.
4 Comments
Gregg Lind
Re: reason 2, part of what makes SA so nice in particular, is that it's trivial to stick raw sql anywhere you like: engine.execute("MY RAW SQL STRING;")
nomadSK25
reason 1: it is laughable that you call people non-technical who can use ORM but don't want to write SQL. It is other way around, SQL developers who are non-technical in the sense of developing real applications don't want to use anything other than SQL, because they are incapable of writing any code in programming languages. So those SQL guys should be called non-technical, right?
Phyo Arkar Lwin
I agree with @nomadSK25 , SQL is necessary complication for programmers. Mixing SQL in code is like flirting hackers in bright day light.
wescpy
Y'all have a valid point. This response was partially due to working at a company where the developers focus on the applications while the data team built the SQL queries. In some cases, we had some jr developers who "didn't do SQL" but didn't want to wait for a data person to write their queries for them, so an ORM worked for them. I tweaked the wording a bit in my response above to address your comments.