|
Size: 9382
Comment:
|
Size: 9858
Comment:
|
| Deletions are marked like this. | Additions are marked like this. |
| Line 5: | Line 5: |
| ================== ==================================================================================== ========================================================= =========================================================== ================================================================== ====================================================================== task postgresql sqlite MySQL Oracle ODBC ================== ==================================================================================== ========================================================= =========================================================== ================================================================== ====================================================================== create database createdb mydb created automatically when opened with sqlite3 created with Oracle XE install ------------------ ------------------------------------------------------------------------------------ --------------------------------------------------------- ----------------------------------------------------------- ------------------------------------------------------------------ ---------------------------------------------------------------------- command-line tool psql -d mydb sqlite3 mydb.sqlite 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('scott/tiger@xe') conn = odbc.odbc('DBALIAS') or odbc.odbc('DBALIAS/USERNAME/PASSWORD') ------------------ ------------------------------------------------------------------------------------ --------------------------------------------------------- ----------------------------------------------------------- ------------------------------------------------------------------ ---------------------------------------------------------------------- get 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') ------------------ ------------------------------------------------------------------------------------ --------------------------------------------------------- ----------------------------------------------------------- ------------------------------------------------------------------ ---------------------------------------------------------------------- fetch 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}) ------------------ ------------------------------------------------------------------------------------ --------------------------------------------------------- ----------------------------------------------------------- ------------------------------------------------------------------ ---------------------------------------------------------------------- commit conn.commit() (required) conn.commit() (required) conn.commit() (required) ================== ==================================================================================== ========================================================= =========================================================== ================================================================== ====================================================================== |
================== ==================================================================================== ========================================================= ============================================================================== ================================================================== ====================================================================== task postgresql sqlite MySQL Oracle ODBC ================== ==================================================================================== ========================================================= ============================================================================== ================================================================== ====================================================================== 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]) urs.execute('SELECT * FROM tbl WHERE col = :varnm', {'varnm':22}) ------------------ ------------------------------------------------------------------------------------ --------------------------------------------------------- ------------------------------------------------------------------------------ ------------------------------------------------------------------ ---------------------------------------------------------------------- commit conn.commit() (required) conn.commit() (required) conn.commit() (required) conn.commit() (required) ================== ==================================================================================== ========================================================= ============================================================================== ================================================================== ====================================================================== |
Some quick syntax reminders for common tasks using DB-API2 modules.
| task | postgresql | sqlite | MySQL | Oracle | ODBC |
|---|---|---|---|---|---|
| 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]) | urs.execute('SELECT * FROM tbl WHERE col = :varnm', {'varnm':22}) | |
| commit | conn.commit() (required) | conn.commit() (required) | conn.commit() (required) | conn.commit() (required) |
See also DbApiModuleComparison, DatabaseProgramming