9858
Comment:
|
← Revision 15 as of 2010-03-09 03:54:30 ⇥
10683
added oursql
|
Deletions are marked like this. | Additions are marked like this. |
Line 5: | Line 5: |
================== ==================================================================================== ========================================================= ============================================================================== ================================================================== ====================================================================== task postgresql sqlite MySQL Oracle ODBC ================== ==================================================================================== ========================================================= ============================================================================== ================================================================== ====================================================================== |
================== ==================================================================================== ========================================================= ============================================================================== ================================================================== ====================================================================== ====================================================== task postgresql sqlite MySQL Oracle ODBC oursql ================== ==================================================================================== ========================================================= ============================================================================== ================================================================== ====================================================================== ====================================================== |
Line 9: | Line 9: |
------------------ ------------------------------------------------------------------------------------ --------------------------------------------------------- ------------------------------------------------------------------------------ ------------------------------------------------------------------ ---------------------------------------------------------------------- | ------------------ ------------------------------------------------------------------------------------ --------------------------------------------------------- ------------------------------------------------------------------------------ ------------------------------------------------------------------ ---------------------------------------------------------------------- ------------------------------------------------------ |
Line 11: | Line 11: |
------------------ ------------------------------------------------------------------------------------ --------------------------------------------------------- ------------------------------------------------------------------------------ ------------------------------------------------------------------ ---------------------------------------------------------------------- | ------------------ ------------------------------------------------------------------------------------ --------------------------------------------------------- ------------------------------------------------------------------------------ ------------------------------------------------------------------ ---------------------------------------------------------------------- ------------------------------------------------------ |
Line 13: | Line 13: |
------------------ ------------------------------------------------------------------------------------ --------------------------------------------------------- ------------------------------------------------------------------------------ ------------------------------------------------------------------ ---------------------------------------------------------------------- | ------------------ ------------------------------------------------------------------------------------ --------------------------------------------------------- ------------------------------------------------------------------------------ ------------------------------------------------------------------ ---------------------------------------------------------------------- ------------------------------------------------------ |
Line 15: | Line 15: |
------------------ ------------------------------------------------------------------------------------ --------------------------------------------------------- ------------------------------------------------------------------------------ ------------------------------------------------------------------ ---------------------------------------------------------------------- | ------------------ ------------------------------------------------------------------------------------ --------------------------------------------------------- ------------------------------------------------------------------------------ ------------------------------------------------------------------ ---------------------------------------------------------------------- ------------------------------------------------------ |
Line 17: | Line 17: |
------------------ ------------------------------------------------------------------------------------ --------------------------------------------------------- ------------------------------------------------------------------------------ ------------------------------------------------------------------ ---------------------------------------------------------------------- | ------------------ ------------------------------------------------------------------------------------ --------------------------------------------------------- ------------------------------------------------------------------------------ ------------------------------------------------------------------ ---------------------------------------------------------------------- ------------------------------------------------------ |
Line 19: | Line 19: |
------------------ ------------------------------------------------------------------------------------ --------------------------------------------------------- ------------------------------------------------------------------------------ ------------------------------------------------------------------ ---------------------------------------------------------------------- | ------------------ ------------------------------------------------------------------------------------ --------------------------------------------------------- ------------------------------------------------------------------------------ ------------------------------------------------------------------ ---------------------------------------------------------------------- ------------------------------------------------------ |
Line 21: | Line 21: |
------------------ ------------------------------------------------------------------------------------ --------------------------------------------------------- ------------------------------------------------------------------------------ ------------------------------------------------------------------ ---------------------------------------------------------------------- | ------------------ ------------------------------------------------------------------------------------ --------------------------------------------------------- ------------------------------------------------------------------------------ ------------------------------------------------------------------ ---------------------------------------------------------------------- ------------------------------------------------------ |
Line 23: | Line 23: |
------------------ ------------------------------------------------------------------------------------ --------------------------------------------------------- ------------------------------------------------------------------------------ ------------------------------------------------------------------ ---------------------------------------------------------------------- | ------------------ ------------------------------------------------------------------------------------ --------------------------------------------------------- ------------------------------------------------------------------------------ ------------------------------------------------------------------ ---------------------------------------------------------------------- ------------------------------------------------------ |
Line 25: | Line 25: |
------------------ ------------------------------------------------------------------------------------ --------------------------------------------------------- ------------------------------------------------------------------------------ ------------------------------------------------------------------ ---------------------------------------------------------------------- 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}) ------------------ ------------------------------------------------------------------------------------ --------------------------------------------------------- ------------------------------------------------------------------------------ ------------------------------------------------------------------ ---------------------------------------------------------------------- |
------------------ ------------------------------------------------------------------------------------ --------------------------------------------------------- ------------------------------------------------------------------------------ ------------------------------------------------------------------ ---------------------------------------------------------------------- ------------------------------------------------------ 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,)) ------------------ ------------------------------------------------------------------------------------ --------------------------------------------------------- ------------------------------------------------------------------------------ ------------------------------------------------------------------ ---------------------------------------------------------------------- ------------------------------------------------------ |
Line 29: | Line 29: |
================== ==================================================================================== ========================================================= ============================================================================== ================================================================== ====================================================================== | ================== ==================================================================================== ========================================================= ============================================================================== ================================================================== ====================================================================== ====================================================== |
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