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:

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:

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

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.

include "adodb.inc.php";
$conn = adodb.NewADOConnection('mysql');
$conn->Connect('server','user','pwd','db');
$rs = $conn->Execute('select * from table');

while (!$rs->EOF) {
	print_r($rs->fields);
	$rs->MoveNext();
}
$rs->Close();
$conn->Close();
	

from adodb import adodb;
conn = adodb.NewADOConnection('mysql')
conn.Connect('server','user','pwd','db')
cursor = conn.Execute('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:

$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
from adodb import adodb
conn = adodb.NewADOConnection('oci8')
conn.Connect('scott/tiger@tns')
conn.Connect('tns', 'scott', 'tiger')

# Oracle using connection string
from adodb import adodb
conn = adodb.NewADOConnection('oci8://scott:tiger@tns/')

# MySQL
from adodb import adodb
conn = adodb.NewADOConnection('mysql')
conn.Connect('server', 'user', 'pwd', 'db')

# MySQL using connection string
from adodb import adodb
conn = adodb.NewADOConnection('mysql://user:pwd@server/db')

# PostgreSQL
from adodb 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
from adodb 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
from adodb import adodb
conn = adodb.NewADOConnection('mssql') # mxodbc required
conn.Connect("Driver={SQL Server};Server=localhost;Database=northwind;")

Other drivers such as "vfp" for foxpro are included.

Function Reference

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

        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,));

Change Log

Todo: add support for sequences. Need to add SelectLimit support for mxodbc derived classes.

1.13 6 May 2005

Fixed oci8 blob bugs. Also updateclob( ) was not working.

Close calls close( ) before freeing connectoin object.

1.12  24 March 2005

Added mxoracle driver (mxodbc for oracle)..

Fixed cursor iterator bug in mxodbc driver.

1.11 23 Feb 2005

Fixed commit code for mysql driver.

1.10 21 Dec 2004

Added sysDate and sysTimeStamp to mysql driver.

Added support for connection strings of the form driver://user:pwd@server/database in NewADOConnection.

DriverInfo( ) now checks paramStyle before thread safety, as some drivers do not implement thread safety.

MySQL insert_id() changed to lastrowid as insert_id() deprecated. Thx Alex Verstraeten.

GetRow() now closes cursor. Thx Guoda Rugeviciute

Postgresql metacolumns() did not work - not a tuple. Fixed. Thx Simon Hedberg.

1.01 30 July 2004
A debugging print in oci8 was removed.

1.00 12 July 2004

GetRowAssoc(), upper = False not implemented correctly. Fixed.

Tested with psyco. Works fine.

0.90 15 Apr 2004

Added ADONewConnection and NewADOConnection.

Added GetAssoc, GetDict, GetArray and GetCol.

Added mxodbc driver. Also vfp, access and mssql classes that inherit from mxodbc. Tested only on Windows.

Added MetaColumns and MetaType support for Oracle, mxODBC, MySQL

Perform explicit close for Get* functions.

0.03 31 Jan 2004

Implemented setup.py installer.

Some postgres bugs fixed.

0.02 28 Jan 2004

Revised GetRowAssoc( ).

Fixed use of false - should be False.

0.01 25 Jan 2004
First Release. MySQL, Oracle, PostgreSQL, generic ODBC and ODBC for MSSQL drivers.