Working with databases in Python without an ORM

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.

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.

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.