SQL Alchemy performance if used only for raw sql?

Question

I intend to use very simple raw SQL in a Django project. example:

SELECT name,doc_id,doc_content,creation_date from doc_table where uid = 'xxx' limit 1 ;

Primary requirements:

Ease of doing SQL queries and getting data - eg. If I use MySQLdb , I do not want to repetitively do queries through cursor, then process the tuples to get a dictionary of required data.
Protection against SQL injection , and proper escaping of data.

There are two choices,

a) Using SQLAlchemy , even if used only for raw SQL.

b) Use a wrapper written over MySQLdb, or write one.

I am inclined towards SQLAlchemy as I have read a lot of good things about it.

Will there be any significant performance overhead of using SQLAlchemy eg CPU usage,Memory usage as compared to a simpler MySQLdb wrapper , if I intend to use it only for simple raw SQL. All other batteries of SQLAlchemy are not of much use.

FYI, you can get dicts back from MySQLdb directly without processing tuples. Just add: `import MySQLdb.cursors` and in your connect call add a named param `cursorclass=MySQLdb.cursors.DictCursor`. The iterator returned from `fetchall()` will be dictionaries. — sberry, Aug 08 '12 at 09:24
So the assumption here is that the query is against a db for which you don't have a model? — Burhan Khalid, Aug 08 '12 at 09:27
@sberry Yes,I have seen that,I would want a wrapper over that to ease inserts ,updates ,selects. — DhruvPathak, Aug 08 '12 at 09:31
*Why* don't you have a model? If it's an existing table, why not use `inspectdb` to create a model from the database? — Daniel Roseman, Aug 08 '12 at 10:40
@DanielRoseman Correction. I do have model, but not ORM . The models return dictionaries of data required which can be processed in view. — DhruvPathak, Aug 08 '12 at 10:56
What? What does "I do have model but not ORM" mean? Either you have a Django model, or you don't. And if you don't, why not? — Daniel Roseman, Aug 08 '12 at 11:00
Please see the answer to [Why is SQLAlchemy insert with `sqlite` 25 times slower than using sqlite3 directly?](http://stackoverflow.com/a/11769768/99594). Although it is not `mysql`, [Mike](http://stackoverflow.com/users/34549/zzzeek) posted a sample code for performance testing, which you can apply to your case with mySQL and extend to include all `CRUD` operation. From the results posted there, I would conclude that there should not be any significant performance degradation when using only [`SQLAlchemy Core`](http://docs.sqlalchemy.org/en/rel_0_7/core/tutorial.html). — van, Aug 08 '12 at 15:58
Also look at another SO answer to [Is SQLAlchemy still recommanded if only used for raw sql query?](http://stackoverflow.com/a/11543303/99594). — van, Aug 09 '12 at 05:54
@van Thanks for the input. It is helpful. Do you know any good lightweight wrapper written over MySQLdb ? — DhruvPathak, Aug 09 '12 at 05:55
@Van you can post your comment inputs as answers, I would be happy to accept it as correct answer. — DhruvPathak, Aug 09 '12 at 06:05

score 6 · Accepted Answer · edited May 23 '17 at 12:12

as requested, posting comments as an answer:

Please see the answer to Why is SQLAlchemy insert with sqlite 25 times slower than using sqlite3 directly?. Although it is not mysql, Mike posted a sample code for performance testing, which you can apply to your case with mySQL and extend to include all CRUD operation. From the results posted there, I would conclude that there should not be any significant performance degradation when using only SQLAlchemy Core.

Also look at another SO answer to Is SQLAlchemy still recommanded if only used for raw sql query?

SQL Alchemy performance if used only for raw sql?

1 Answers1