Recipe7.17.Using Microsoft Jet via ADO

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.

Recipe7.17.Using Microsoft Jet via ADO