O'Reilly logo

Python Programming On Win32 by Mark Hammond, Andy Robinson

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Fetching Data with the PythonWin ODBC Module

PythonWin includes an ODBC module that is mature and stable, but no longer being developed and only Level 1.0-compliant. However, it has the advantage of being small, light, and present in every PythonWin distribution. It depends on the DBI module that defines certain data types (such as dates) and must be imported first: more on this later. It consists of two extension files in the win32 subdirectory, odbc.pyd and dbi.pyd . Here’s how we fetch some data:

>>> import dbi                   #database independence utilities
>>> import odbc                  #the ODBC module itself
>>> from pprint import pprint    #pretty-print function
>>> myconn = odbc.odbc('PYDBDEMOS')
>>> mycursor = myconn.cursor()
>>> mycursor.execute('SELECT ClientID, CompanyName, Address1 FROM Clients')
>>> mydata = mycursor.fetchall()
>>> mycursor.close()             #close cursor when done
>>> myconn.close()               #close database when done
>>> pprint(mydata)
[('MEGAWAD', 'MegaWad Investments', '1 St. Fredericks-Le-Grand'),
 ('NOSHCO', 'NoshCo Supermarkets', '17 Merton Road'),
 ('GRQ', 'GRQ Recruitment', None)]
>>>

The fetchall() method converts the entire result set to a list of tuples of Python variables in one call.

The connection object is constructed with an ODBC connection string. It can be as simple as the data source name or can include a username and password; a real-world connection string might looks like this:

DSN=MYDATABASE;UID=MYUSER;PWD=MYPASSWORD

If you attempt to connect to a secure database without a password, ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required