SQL-Statement with Python

[Alex Martelli]

"Juergen Hofer" <juergen.hofer at students.uni-linz.ac.at> wrote in message
news:3A62E224.7AEE805E at students.uni-linz.ac.at...
> Hi to all!
>
> Is it possible to connect to a M$-Access Database (*.mdb) with Python
> and make simple SQL-Queries and import the results into a list?
> I'm using Python2.0 under Windows98.

Yes, it's pretty easy -- easiest with Microsoft's ADO, driven via
win32com (part of win32all -- you already have it if you got the
ActiveState distribution, but even if you started with the standard
distribution you probably want to add win32all anyway if you work
with Python under Windows... it has just too many nifty things!-).

For example, say you have the usual "NorthWinds" sample database
available as d:\mssdk\bin\nwind.mdb, and a reasonably late release
of the MS Data Access Components package (freely downloadable from
the MS site, also comes with just about any MS development product,
SDK, Office installation, etc).

import win32com.client

connection = win32com.client.Dispatch("AdoDb.Connection")
connection.Provider = "Microsoft.Jet.OLEDB.4.0"
filename = r'd:\\mssdk\\bin\\nwind.mdb'
connection.Open(filename)
recordset, result = connection.Execute(
  "Select FirstName, LastName From Employees Order By LastName")
rows = recordset.GetRows()
for first, last in zip(rows[0], rows[1]):
    print "%s, %s" % (last, first)

This should emit:

Buchanan, Steven
Callahan, Laura
Davolio, Nancy
Dodsworth, Anne
Fuller, Andrew
King, Robert
Leverling, Janet
Peacock, Margaret
Suyama, Michael


Of course, you can massage the recordset in a zillion ways,
specify where the cursor-caching occurs and how, etc, etc,
just as for any other use of ADO.  You'll find tons of docs,
info, tutorials, etc, about ADO, though they're often VB
or VBscript oriented (but pretty easy to read anyway).


Alex