Python Cookbook 2Nd Edition Jun 1002005 [Electronic resources] نسخه متنی

Recipe 7.17. Using Microsoft Jet via ADO

Credit: Souman Deb

Problem

You need to access a Microsoft Jet
database via Microsoft's ADO, for example from a
Python-coded CGI script for the Apache web-server.

Solution

The CGI script must live in Apache's
cgi-bin directory and can use the PyWin32
extensions to connect, via COM, to ADO and hence to Microsoft Jet.
For example:

#!C:\Python23\python
print "Content-type:text/html\n\n"
import win32com
db='C:\\Program Files\\Microsoft Office\\Office\\Samples\\Northwind.mdb'
MAX_ROWS=2155
def connect(query):
con = win32com.client.Dispatch('ADODB.Connection')
con.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source="+db)
result_set = con.Execute(query + ';')
con.Close( )
return result_set
def display(columns, MAX_ROWS):
print "<table border=1>"
print "<th>Order ID</th>"
print "<th>Product</th>"
print "<th>Unit Price</th>"
print "<th>Quantity</th>"
print "<th>Discount</th>"
for k in range(MAX_ROWS):
print "<tr>"
for field in columns:
print "<td>", field[k], "</td>"
print "</tr>"
print "</table>"
result_set = connect("select * from [Order details]")
columns = result_set[0].GetRows(MAX_ROWS)
display(columns, MAX_ROWS)
result_set[0].Close

Discussion

This recipe uses the "Northwind
Database" example that Microsoft distributes with
several of its products, such as Microsoft Access. To run this
recipe, you need a machine running Microsoft Windows with working
installations of other Microsoft add-ons such as OLEDB, ADO, and the
Jet database driver, which is often (though not correctly) known as
"the Access database". (Microsoft
Access is a product to build database frontend applications, and it
can work with other database drivers, such as Microsoft SQL Server,
not just with the freely distributable and downloadable Microsoft Jet
database drivers.) Moreover, you need an installation of Mark
Hammond's PyWin32 package
(formerly known as win32all); the Python
distribution known as ActivePython, from ActiveState, comes with
(among other things) PyWin32 already
installed.

If you want to run this recipe specifically as an Apache CGI script,
of course, you also need to install Apache and to place this script
in the cgi-bin directory where Apache expects to
find CGI scripts (the location of the cgi-bin
directory depends on how you have installed Apache on your machine).

Make sure that the paths in the script are correct, depending on
where, on your machine, you have installed the
python.exe file you want to use, and the
Northwind.mdb database you want to query. The
paths indicated in the recipe correspond to default installations of
Python 2.3 and the "Northwind"
example database. If the script doesn't work
correctly, check the Apache
error.log file, where you will find error
messages that may help you find out what kind of error
you're dealing with.

To try the script, assuming that, for example, you have saved it as
cgi-bin/adoexample.py and that your Apache
server is running correctly, visit with any browser the URL
http://localhost/cgi-bin/adoexample.py. One
known limitation of the interface between Python and Jet databases
with ADO is on fields of type currency: such
fields are returned as some strange tuples, rather than as plain
numbers. This recipe does not deal with that limitation.

See Also

Documentation for the Win32 API in PyWin32
(http://starship.python.net/crew/mhammond/win32/Downloadsl)
or ActivePython (http://www.activestate.com/ActivePython/);
Windows API documentation available from Microsoft (http://msdn.microsoft.com); Mark Hammond and
Andy Robinson, Python Programming on Win32
(O'Reilly).