SQL-Statement with Python

[Steve Holden]

"Alex Martelli" <aleaxit at yahoo.com> wrote in message
news:93ut4q031hd at news1.newsguy.com...
> "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!-).
>
I might take issue with this (despite my last run-in with the martellibot...
Perhaps I should just do this first [thwack, thwack, thwack]). There, that's
better.

> 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)
>
That's the bit I don't like.  You call the GetRows method, and what does it
return? Columns, mate, that's what. At least to the Python user. Not what I
call intuitive.

> 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
>
I'm sure you'll have a great explanation for it, though.  Mark Hammond's was
pretty good.

regards
 Steve