ADOdb for Python

ADOdb for Python
(c) 2004-2005 John Lim (jlim#natsoft.com.my) This software
is licensed under a BSD-style license. See LICENSE.txt.
Introduction
Databases Supported
Bug Reports and Help
Installation
Tutorial
Connection Examples
Function Reference
Error Handling
Bind Parameters
  
Changelog
Introduction
ADOdb is a database abstraction library (modelled on Microsoft's
database API's). ADOdb was originally developed for PHP, and ported to
Python. The Python version implements a subset of the PHP version.
ADOdb is licensed using a BSD-style license.
Download:  Python
version  PHP
version
You might ask why Python needs a database abstraction library when
Python provides the official DB API. Unfortunately the DB API does not
encapsulate differences in the database implementations. For example,
to select a limited number of rows, say 10 rows, you would have to use
very different SQL for different databases:
  
   
      MS SQL
      select top 10 from table
   
   
      MySQL and PostgreSQL
      select * from table limit 10
   
   
      Oracle
      select * from (select * from table) where rownum
   
  
These differences are handled by ADOdb (using SelectLimit),
but not by the Python DB API. Other important database differences
transparently handled by ADOdb include date-formating, associative
arrays (records as dictionaries) and LOB-handling.
This class library assumes you are using Python 2.3 or later.
Internally it uses the standard Python 2.3 datetime class.
Databases Supported
PHP bundles most database extensions in the official release. In
Python, most database extensions are not part of the official release.
You have to manually download and install the extensions yourself. The
requirements are listed below:
  
   
      Class
      Requirements
      Notes
   
   
      odbc
      Download
PythonWin
extensio
      
      
No support for SelectLimit, UpdateBlob, UpdateBlobFile, Insert_ID,
RecordCount and Affected_Rows.
      
   
   
      access
      Requires mxodbc.
       Only SelectLimit( ) with no offset parameter supported. RecordCount( ) not supported.
   
   
      mssql
      Requires mxodbc.
       Only SelectLimit( ) with no offset parameter supported. RecordCount( ) not supported.
   
   
      mysql
      Download
MySQL-python
extension
      
   
   
      mxodbc
      Superior
odbc extension
.
Licensing fee required for commercial us
      
      
SelectLimit( ) not supported. RecordCount( ) not supported.
      
   
   
      mxoracle
      
      Requires mxodbc. Connect to Oracle using ODBC.
      
      Only SelectLimit( ) with no offset parameter supported. Requires Oracle client installed. RecordCount( ) not supported.
      
   
      oci8
      Download
cx_Oracle
extension. Also requires Oracle client to be installed.
      
      
Despite the name, it works with Oracle 8, 9 and later.
SelectLimit( ) does not support the offset parameter. RecordCount( ) not supported.
      
   
   
      odbc
      Download
PythonWin
extension
      
      
SelectLimit( ) only works with Access,VFP and Microsoft SQL
Server. The offset parameter is not supported.
      
No support for UpdateBlob, UpdateBlobFile, Insert_ID,
RecordCount and Affected_Rows.
      
   
   
      odbc_mssql
      Download
PythonWin
extension
      
      
Same limitations as adodb_odbc extension, except that
Insert_ID and Affected_Rows supported.
      
   
   
      postgres
      Download
psycopg
extension
      
   
   
      vfp
      Requires mxodbc.
      
   
      sqlite
      
      Requires
pysqlite
.
      
      Contributed by Glenn Washburn.
      
   
  
Installation
Run from the command prompt:
> python setup.py
install
This will perform the adodb package installation.
You will need to install database specific extensions to use ADOdb
for Python.
Bug Reports and Help
To report bugs, discuss ADOdb, and ask for help, post to the forums at:
http://phplens.com/lens/lensforum/topics.php?id=4
Tutorial
The easiest way to learn how to use ADOdb for python is with a few
examples. Here's one that contrasts PHP with Python. This example
select some data from a table, prints each row, then closes the
connection.
  
   
      PHP
      Python
   
   
      
      include "adodb.inc.php";
$conn = adodb.NewADOConnection([color="#660000"]'mysql');
$conn->Connect([color="#660000"]'server','user','pwd','db');
$rs = $conn->Execute([color="#660000"]'select * from table');
while (!$rs->EOF) {
        print_r($rs->fields);
        $rs->MoveNext();
}
$rs->Close();
$conn->Close();
       
      
      
      import adodb;
conn = adodb.NewADOConnection([color="#660000"]'mysql')
conn.Connect([color="#660000"]'server','user','pwd','db')
cursor = conn.Execute([color="#660000"]'select * from table')
while not cursor.EOF:
        print cursor.fields
        cursor.MoveNext()
cursor.Close()
conn.Close()
      
   
  
First we create a database connection object, conn. Then we
login to the database using Connect( ). We now call Execute( ) to
compile and execute the given SQL. The will return a recordset or
cursor that will hold the current row in the fields property.
Fields are numerically indexed, starting at index zero. When we want to
move to the next record, we call MoveNext( ) which also updates the fields
property. Lastly, we check to see whether there are any more records
left to process by monitoring the EOF property.
As you can see, the PHP and Python code is very similar. The main
difference is Execute( ) returns a recordset in PHP, while a cursor is
returned in Python. A PHP recordset and Python cursor work identically
with SELECT statements. However Python cursors work differently with
INSERT/UPDATE/DELETE statements. You can see that below:
  
   
      PHP
      Python
   
   
      
      $sql = "update table set col='abc'";
$rs = $conn->Execute($sql);
$rows = $conn->Affected_Rows();
      
      
      sql = "update table set col='abc'"
cursor = conn.Execute($sql)
rows = cursor.Affected_Rows()
      
   
  
In PHP, Affected_Rows( ) runs in the connection. In Python, all
information related to an Execute( ) is retrieved from the cursor. This
is because Python is multi-threaded, so it is no longer possible to
store the affected_rows globally in the connection. Similarly,
Insert_ID( ) is called from the cursor in Python.
We support the iterator protocol, which allows you to iterate
through the data using a for-loop:
cursor = conn.Execute('select * from table')
for row in cursor:
        print row
for row in conn.Execute('select id from table'):
        dofunction(row[0])
And we support associative arrays (dictionaries), where the keys are
the field names:
cursor = conn.Execute('select id,name from table')
while not cursor.EOF:
        arr = cursor.GetRowAssoc(0) # 0 is lower, 1 is upper-case
        print  'id=',arr['id'],' name=',arr['name']
        cursor.MoveNext()
Connection Examples
# Oracle connection
import adodb
conn = adodb.NewADOConnection('oci8')
conn.Connect('scott/tiger@tns')
conn.Connect('tns', 'scott', 'tiger')
# Oracle using connection string
import adodb
conn = adodb.NewADOConnection('oci8://scott:tiger@tns/')
# MySQL
import adodb
conn = adodb.NewADOConnection('mysql')
conn.Connect('server', 'user', 'pwd', 'db')
# MySQL using connection string
import adodb
conn = adodb.NewADOConnection('mysql://user:pwd@server/db')
# PostgreSQL
import adodb
conn = adodb.NewADOConnection('postgres')
conn.Connect('server', 'user', 'pwd', 'db')
conn.Connect('host=server user=user password=pwd dbname=db port=4341')
# ODBC
import adodb
conn = adodb.NewADOConnection('access') # mxodbc required
dsn = "Driver={Microsoft Access Driver (*.mdb)};Dbq=d:\\inetpub\\adodb\\northwind.mdb;"
conn.Connect(dsn)
# ODBC for mssql
import adodb
conn = adodb.NewADOConnection('mssql') # mxodbc required
conn.Connect("Driver={SQL Server};Server=localhost;Database=northwind;")
# sqlite
import adodb
conn = adodb.NewADOConnection('sqlite') # pysqlite required
conn.Connect(database = "c:\\sqlite\\mydata.db")
Other drivers such as "vfp" for foxpro are included.
Function Reference
  
   
      Connection Class
      Description
   
   
      Execute(sql, [params])
      Execute sql, returning a cursor object. The
optional params is a dictionary that contains the bind
variables. All blob fields are automatically and transparently
retrieved for you.
   
   
      SelectLimit(sql, limit, [offset])
      Execute sql, retrieving only limit rows, an
optional offset from the beginning of the recordset, returning
a cursor object.
   
   
      
      
UpdateBlob(table, field, blob,
      whereclause, blobtype='BLOB')
      
      
      
Executes the equivalent following sql statement:
      
UPDATE table SET field = blob WHERE whereclause
      
The blobtype field should be set to either 'BLOB' or 'CLOB'.
Any special encoding required for the blob is applied
transparently.
      
   
   
      
      
UpdateBlobFile(table, field, filepath,
      whereclause, blobtype='BLOB')
      
      
      
Loads the binary file filepath into blob. Then
calls UpdateBlob( ).
      
   
   
      ErrorMsg( )
      Returns last database error message. This function is not
thread-safe.
   
   
      IsConnected( )
      Returns boolean indicating whether connected.
   
   
      qstr(str)
      Quotes a varchar or char variable.
   
   
      quote(str)
      Synonym for qstr( )
   
   
      GetAll(sql)
      Execute sql and return 2-dimensional array of tuples, the
data recordset.
   
   
      GetArray(sql)
      Synonym for GetAll(sql).
   
   
      GetRow(sql)
      Execute sql and return first row of recordset as a tuple.
   
   
      GetOne(sql)
      Execute sql and return 1 element of first row of recordset.
   
   
      GetAssoc(sql)
      Returns a dictionary, with the first columns as the keys to
the dictionary. If more than 2 columns are returned, then the
dictionary values is a tuple of the 2nd to last columns. If 2 columns
are returned, then the 2nd column becomes the dictionary values. If one
column is returned, then the values are set to None.
   
   
      GetDict(sql)
      Synonym for GetAssoc().
   
   
      GetCol(sql)
      Returns the first column of each row as an array.
   
   
      MetaType(fieldtype)
      Returns the ADOdb metatype of a native field type.
      

• C: character fields that fit into a text input field.
• X: larger character fields that fit into a textarea.
• B: Blobs, or Binary Large Objects. Typically images.
          
• D: Date field
• T: Timestamp field
• L: Logical field (boolean or bit-field)
• I:  Integer field
• N: Numeric field. Includes autoincrement, numeric,
  floating point, real and integer.

      
   
   
      MetaColumns(table)
      Returns a 2-dimensional array containing information on the
fields of a table. Each row contains [fieldname, fieldtype, maxlength].
Maxlength can be -1, indicating that the maximum length is not known.
Note that some databases return more information in each row.
   
   
      DBDate(datetime)
      Given a Python 2.3 datetime object, convert into a date
string acceptable to the database.
   
   
      DBTimeStamp(datetime)
      Given a Python 2.3 datetime object, convert into a timestamp
string acceptable to the database.
   
   
      Date(field)
      Converts a date returned by a select statement into a Python
2.3 datetime object
   
   
      TimeStamp(field)
      Converts a timestamp returned by a select statement into a
Python 2.3 datetime object
   
   
      BeginTrans( )
      ADOdb defaults to auto-commit mode. Call BeginTrans( ) to
start a transaction. This might not be thread-safe.
   
   
      RollbackTrans( )
      Rollback transaction initiated by BeginTrans( ).
   
   
      CommitTrans( )
      Commit transaction initiated by BeginTrans( ).
   
   
      Close( )
      Close database connection. This is optional, as the
connection is closed when the object is freed.
   
   
      Module( )
      Returns the DB module object.
   
   
      Conn( )
      Returns DB connection object.
   
   
      DriverInfo( )
      Returns the threadsafety, apilevel and paramstyle values
   
  
  
   
      Connection Class Properties
      Description
   
   
      debug
      Set to 1 to output SQL generated to stdout. Set to 2 to
output to stdout as HTML. Set to a filepath (a string) if you want the
debug output to be logged into a file.
   
   
      getLOBs
      Boolean that determines whether LOBs (large data objects) are
loaded automatically. Default is True, autoload. For best performance
(especially for cursors with no LOBs), set this to False.
   
   
      sysDate
      SQL to generate current date.
   
   
      sysTimeStamp
      SQL to generate current timestamp.
   
  
  
   
      Cursor Class Functions
      Description
   
   
      RecordCount( )
      Number of rows returned by SELECT statement, or number of
rows affected by INSERT/UPDATE/DELETE. Returns -1 if not supported.
   
   
      Affected_Rows( )
      Synonym for RecordCount( ).
   
   
      MoveNext( )
      Move to next row of recordset. Returns current EOF value.
   
   
      FetchRow( )
      Retrieves the current row of the recordset, then moves to the
next row. The row retrieved is returned as a tuple.
   
   
      GetRowAssoc(upper=1)
      Returns the current row as a dictionary, with the key's being
the field names. Setting upper = 0 will lower-case the keys.
Setting upper=1 will upper-case the keys. Setting upper
to any other value, and the keys will be left in the natural case.
   
   
      Insert_ID( )
      Returns last insert id generated by an auto-incrementing
field. Only supported by mysql and odbc_mssql drivers
currently.
   
   
      FetchField(fieldoffset)
      
      
Returns field information from a SELECT statement. The fieldoffset
is zero-based, so to retrieve info on the 1st field use FetchField(0).
A tuple is returned, consisting of:
      
(name, type_code,display_size, internal_size, precision,
scale,null_ok).
      
   
   
      Close( )
      Close cursor. This is optional, as the cursor is closed when
the object is freed.
   
   
      Cursor( )
      Returns DB cursor object.
   
  
  
   
      Cursor Class Properties
      Description
   
   
      fields
      Property that holds the current row of the recordset as a
tuple (or list).
   
   
      EOF
      When moving true the recordset, EOF is set to True after we
pass the last row.
   
  
Error Handling
ADOdb for Python by default relies on the standard Python exception
mechanism. Here's how to capture an error:
try:
        curs = conn.Execute('select * from badtable'); # table does not exist
except:
        print sys.exc_info()[1]; # retrieve the error message returned by database
Alternatively, you can use PHP style ErrorMsg( ) by setting the connection.useExceptions
flag to True. ErrorMsg( ) is not thread-safe.
conn.useExceptions = False
curs = conn.Execute('select * from badtable'); # table does not exist
if curs == None:
        print conn.ErrorMsg()
Bind Parameters
Python drivers do not use a consistent bind parameter convention. Here is
  a brief summary of some drivers, obtained by running Connection.DriverInfo(
  ), which outputs the following to the console:
Driver       = mysql
API Level    = 2.0
Param Style  = format
Thread Safety= 1  (0=none, 1=module, 2=connections, 3=cursors)
Driver       = oci8
API Level    = 2.0
Param Style  = named
Thread Safety= 2  (0=none, 1=module, 2=connections, 3=cursors)
Driver       = postgres
API Level    = 2.0
Param Style  = pyformat
Thread Safety= 2  (0=none, 1=module, 2=connections, 3=cursors)
Driver       = mssql (and all odbc drivers)
API Level    = 2.0
Param Style  = qmark
The bind format you use is defined in Param Style. From the Python DB API docs:
        paramstyle
           String constant stating the type of parameter marker
            formatting expected by the interface. Possible values are
            [2]:
                'qmark'         Question mark style,
                                e.g. '...WHERE name=?'
                'numeric'       Numeric, positional style,
                                e.g. '...WHERE name=:1'
                'named'         Named style,
                                e.g. '...WHERE name=:name'
                'format'        ANSI C printf format codes,
                                e.g. '...WHERE name=%s'
                'pyformat'      Python extended format codes,
                                e.g. '...WHERE name=%(name)s'
So for mysql:
        cur_name = "snake"
        new_name = "turtle"
        connection.Execute ("UPDATE animal SET name = %s WHERE name = %s", (new_name, cur_name))
For oci8, the cx_oracle
docs say:
Parameters may be passed as a dictionary or sequence or as keyword
arguments. If the arguments are a dictionary, the values will be bound
by name and if the arguments are a sequence the values will be bound by
position.
For odbc, the paramstyle is qmark, eg.
  
        connection.Execute('select * from table where id=?',(100,));