Differences between revisions 12 and 13
Revision 12 as of 2008-11-15 14:01:24
Size: 9858
Editor: localhost
Comment: converted to 1.6 markup
Revision 13 as of 2009-05-07 13:34:26
Size: 9859
Editor: 194
Comment:
Deletions are marked like this. Additions are marked like this.
Line 26: Line 26:
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}) 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})

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

System Message: ERROR/3 (<string>, line 25)

Malformed table. Text in column margin in table line 22.

================== ==================================================================================== ========================================================= ============================================================================== ================================================================== ======================================================================
              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])  curs.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

DbApiCheatSheet (last edited 2010-03-09 03:54:30 by bb14f857)

Unable to edit the page? See the FrontPage for instructions.