#FORMAT rst 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) ================== ==================================================================================== ========================================================= ============================================================================== ================================================================== ====================================================================== ====================================================== .. _note: http://catherinedevlin.blogspot.com/2007/10/cxoracle-and-oracle-xe-on-ubuntu.html See also DbApiModuleComparison_, DatabaseProgramming_ .. _DbApiModuleComparison: DbApiModuleComparison .. _DatabaseProgramming: DatabaseProgramming