Working with databases in Python without an ORM

Question

What is the standard/recommended way to work with a database (MySQL, mostly) in Python?

I don't want an ORM, and the lighter the better. I don't mind writing my own SELECTs but I'd like something that maps a dictionary, for example, to do INSERTs and UPDATEs. I'm mostly a PHP programmer and I'm used to do something like this:


$data = array(
  'foo' => 'bar'
);

$insert = $db->insert('table', $data);

Is there anything like this for Python? I looked into SQLAlchemy, which apparently everybody uses, and its "SQL Expression" seems nice, but it seems like overkill to use the whole thing to basically write raw SQL.

First. Search Stack Overflow. Many, many question have been asked on MySQL and Python. Please read up on MySQL-Python (sourceforge.net/projects/mysql-python) and the Python DB API (python.org/dev/peps/pep-0249). After reading the resources, please update your question with something specific. — S.Lott
– S.Lott, Commented Feb 8, 2011 at 13:13
Reasons to not use orm are usually linked to performance. And use an orm to write raw SQL loses the essence of an orm (work with objects). — Natacha
– Natacha, Commented Feb 7 at 9:22

Community · Accepted Answer · 2017-05-23 11:46:42Z

5

To use a dict for inserts, you are looking for a DB driver that supports the "named" (placeholder) paramstyle (in the link, search for "paramstyle"):

From the sqlite3 docs:

import sqlite3
con = sqlite3.connect("mydb")    
cur = con.cursor()

who = "Yeltsin"
age = 72

cur.execute("select name_last, age from people where name_last=:who and age=:age",
    {"who": who, "age": age})
print cur.fetchone()

Unfortunately for MySQL, the two main drivers, MySQLdb and oursql do not support the "named" paramstyle. MySQLdb uses the "format" paramstyle (using '%s') and oursql uses the "qmark" paramstyle (using '?'). Both these paramstyles require you to supply a list rather than a dict of args:

x={'bar':1.0,'baz':2.0}
sql='INSERT INTO foo (bar,baz) VALUES (?,?)'
cur.execute(sql,[x['bar'],x['baz']])

By the way, if you need a driver to connect with MySQL, use oursql. MySQLdb has historically been the go-to driver for Python-MySQL interaction, but that driver uses regex to manipulate your SQL and arguments into one quoted string which is then sent to MySQL. In contrast, oursql uses prepared statements, sends the SQL and the arguments separately, eschewing the need for escaping data. The oursql way is the right way, and avoids certain bugs that plagues MySQLdb.

edited May 23, 2017 at 11:46

CommunityBot

11 silver badge

answered Feb 8, 2011 at 13:27

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jochen Ritzel Over a year ago

Nice, I had no idea that there is finally an alternative to MySQLdb. It has become more of a hassle with each new Python version.

Thomas K · Accepted Answer · 2011-02-08 13:12:30Z

0

As far as I know, you need a specific module for the Database system you're working with. SQLite is in the standard library. For mySQL, try mySQLdb. There's a tutorial here.

answered Feb 8, 2011 at 13:12

Thomas K

40.7k7 gold badges88 silver badges89 bronze badges

Collectives™ on Stack Overflow

Working with databases in Python without an ORM

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related