Why use SQLAlchemy? Is it very convinent for coding? [closed]

Question

Being very familar with writing plain SQLs in Python, I havn't realized the benefits I would get by using SQLAlchemy or other ORMs.

Is it designed for those who don't like SQLs in the programs? Or is it make your script crossing different types of database?

Or it makes your script more Pythonic?

Or is it very conveinent for some tasks dealing with underlying database, and it does a lot of trival things automatically for you, so you just write 2-3 lines of codes calling SQLAlchemy and it does all the trival database operations for you?

It would be highly apppreciated if someone can provide codes demostrating using plain SQL vs using SQLALchemy and show the benefits over plain SQL.

Thanks!

Answer 1

did you check this? https://groups.google.com/forum/?fromgroups=#!topic/sqlalchemy/VXXB12-3JCY

Also, a comparison between Django ORM and SQL Alchemy :

http://blog.mathieu-leplatre.info/sqlalchemy-a-brave-new-world.html

Pasting the response form the above google groups link:

Mark Erbaugh 22/11/2010

I don't have any example code, but I have written several Python applications (and one C++ app) using plain SQL and have started work on a new app using SQLAlchemy, so I'll share my experience.

Let me add that I spent several years maintaining a moderately sized SQL database and wrote lots of pure SQL, though not necessarily using Python.

As my programs accessed SQL, I found myself writing a lot of SQL code to access the data. A lot of this code while not identical, was very similar and seemed redundant. For example, take a simple single table lookup. If you want to do simple CRUD (create, update & delete), you have to write at least three separate SQL statements for each table. While the skeleton of these SQL statements are similar, the specific column names and the table name are different. I ended up writing some Python routines that would build the SQL statements if I supplied a list of columns and the table name. But this is what SQLAlchemy does (and much more) so why reinvent the wheel?

In the case of my C++ app (I hadn't found a suitable ORM), I ended up writing a Python script to generate SQL statements and C++ code to access the tables.

Another advantage is the relative ease with which you can handle changes to the database structure. As I was developing my SA app, I realized that I needed a new column in (at least) one of my tables. I'm using SA's declarative approach and I only had to add the column to my declarative. I didn't have to change any SQL or Python code.

One "problem" I have had with SQLAlchemy is unlearning the way I did things with pure SQL. At it's simplest level, SQLAlchemy can generate the SQL and Python code to access single tables and you might be tempted to write Python code to merge this kind of single table access into larger data graphs, but the true power (IMHO) of SQLAlchemy is that it can handle complex data graphs automatically.

Mark