DB API Cheat Sheet¶

Legacy Wiki Page

This page was migrated from the old MoinMoin-based wiki. Information may be outdated or no longer applicable. For current documentation, see python.org.

Some quick syntax reminders for common tasks using DB-API2 modules.

task	postgresql	sqlite	MySQL	Oracle	ODBC	oursql
create database	createdb mydb	created automatically when opened with sqlite3		created with Oracle XE install
command-line tool	psql -d mydb	sqlite3 mydb.sqlite	mysql testdb	sqlplus scott/tiger
GUI tool	pgadmin3		mysql-admin	sqldeveloper
install module	easy_install psycopg2	included in Python 2.5 standard library	easy_install mysql-python or apt-get install python-mysqldb	easy_install cx_oracle (but see note)
import	from psycopg2 import *	from sqlite3 import *	from MySQLdb import *	from cx_Oracle import *
connect	conn = connect(“dbname=’testdb’ user=’me’ host=’localhost’ password=’mypassword’”)	conn = connect(‘mydb.sqlite’) or conn=connect(‘:memory:’)	conn = connect (host=”localhost”, db=”testdb”, user=”me”, passwd=”mypassword”)	conn=connect(‘scott/tiger@xe’)	conn = odbc.odbc(‘DBALIAS’) or odbc.odbc(‘DBALIAS/USERNAME/PASSWORD’)
get cursor	curs = conn.cursor()	curs = conn.cursor()	curs = conn.cursor()	curs = conn.cursor()
execute SELECT	curs.execute(‘SELECT * FROM tbl’)	curs.execute(‘SELECT * FROM tbl’)	curs.execute(‘SELECT * FROM tbl’)	curs.execute(‘SELECT * FROM tbl’)
fetch	curs.fetchone(); curs.fetchall(); curs.fetchmany()	curs.fetchone(); curs.fetchall(); curs.fetchmany()	curs.fetchone(); curs.fetchall(); curs.fetchmany()	curs.fetchone(); curs.fetchall(); curs.fetchmany(); for r in curs
use bind variables	curs.execute(‘SELECT * FROM tbl WHERE col = %(varnm)s’, {‘varnm’:22})	curs.execute(‘SELECT * FROM tbl WHERE col = ?’, [22])	curs.execute(‘SELECT * FROM tbl WHERE col = %s’, [22])	curs.execute(‘SELECT * FROM tbl WHERE col = :varnm’, {‘varnm’:22})		curs.execute(‘SELECT * FROM tbl WHERE col = ?’, (22,))
commit	conn.commit() (required)	conn.commit() (required)	conn.commit() (required)	conn.commit() (required)