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) |
See also DbApiModuleComparison, DatabaseProgramming