Python Programming On Win32

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, ...