Python - MS ADO

Python and ActiveX Data Objects (ADO)

This is the 'sister' page to Bill Wilkinson's page on using Python with Microsoft's Data Access Objects (DAO). It is intended to give the basics on using Python to connect to an MS Access 2000 database via Microsoft's ActiveX Data Objects (ADO). There are several reasons you might want to do this. ADO is lighter and it tends to be a bit and faster than DAO, unlike DAO it can easily be used with other databases (SQL Server, Oracle, MySQL, etc.), it can be used with XML and text files and pretty much any data at all, and Microsoft will be supporting it for a longer time than DAO. For this article to make sense, you should be mildly familiar with Python, and you should have installed the PythonWin IDE and Windows extensions. Probably having MS Access would be useful as well.

Table of Contents

  1. Running MakePy
  2. DSN
  3. Opening a Recordset
  4. Looking at the Recordset
  5. Adding new Records
  6. Database Table Information
  7. Closing Connections
  8. Using SQL with ADO
  9. Getting the RecordCount
 10. Common Questions and Answers about Python and ADO. 

1. The first thing to do is run the makepy utility. It isn't necessary, but it improves speed and makes life in the PythonWin IDE that much easier. To do this select COM Makepy Utility from the PythonWin tools menu, and then select Microsoft ActiveX Data Objects 2.5 Library.

1. Next you will need a Data Source Name [DSN] and a connection object. [I tend to use DSN-Less connection strings (as opposed to system DSNs as it improves performance and makes code portable)]

For MS Access you can just copy the DSN below. For other databases, or for more advanced options like passwords and the like go to [Control Panel | Administrative Tools | Data Sources (ODBC)]. From there you can set up a system DSN. You can then either use it as a system DSN, or copy it (it is just a text file) into a string and make a DSN-Less connection string. You might also do a search for DSN-Less connection strings on the web. Better yet, here are some samples for SQL Server, Access, FoxPro, Oracle , Oracle, Access, SQL Server, and finally MySQL.

              >>> import win32com.client
              >>> conn = win32com.client.Dispatch(r'ADODB.Connection')
              >>> DSN = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:/MyDB.mdb;'
              >>> conn.Open(DSN)

With these set up access to the database is fairly straight forward.

1. The next task is to open a Recordset. For fancy stuff like joins and whatnot, you can use SQL SELECT * FROM table_name type statements, or just use the table name surrounded by brackets []

              >>> rs = win32com.client.Dispatch(r'ADODB.Recordset')
              >>> rs_name = 'MyRecordset'
              >>> rs.Open('[' + rs_name + ']', conn, 1, 3)

[The 1 and the 3 are constants for adOpenKeyset and adLockOptimistic and are well beyond the scope of this tutorial. I typically use these settings as defaults, but your mileage may vary. Pick up a book on ADO for details.]

1. With the recordset open you can examine the field names/indices like ...

              >>> flds_dict = {}
              >>> for x in range(rs.Fields.Count):
              ...     flds_dict[x] = rs.Fields.Item(x).Name

A field's type and size are returned by ...

              >>> print rs.Fields.Item(1).Type
              202 # 202 is a text field
              >>> print rs.Fields.Item(1).DefinedSize
              50  # 50 Characters

1. Actual work can be done as well. Adding new records to recordsets can be done both with INSERT INTO (see below) statements, or directly using the AddNew() and Update() methods.

              >>> rs.AddNew()
              >>> rs.Fields.Item(1).Value = 'data'
              >>> rs.Update()

These values can be also be returned.

              >>> x = rs.Fields.Item(1).Value
              >>> print x
              'data'

So, if one wants to create a new Record, and know what number an AutoNumber field has generated for it without having to query the database ...

              >>> rs.AddNew()
              >>> x = rs.Fields.Item('Auto_Number_Field_Name').Value  
              # x contains the AutoNumber
              >>> rs.Fields.Item('Field_Name').Value = 'data'
              >>> rs.Update()

1. You can get a list of the Tables in a Database using ADO.

              >>> oCat = win32com.client.Dispatch(r'ADOX.Catalog')
              >>> oCat.ActiveConnection = conn
              >>> oTab = oCat.Tables
              >>> for x in oTab:
              ...     if x.Type == 'TABLE':
              ...         print x.Name

1. Close the connection. Notice that to close this connection the 'C' is upper case, whereas to close a file opened with python the 'c' is lower case.

              >>> conn.Close()

1. To use SQL to INSERT or UPDATE data, use a Connection object directly.

              >>> conn = win32com.client.Dispatch(r'ADODB.Connection')
              >>> DSN = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:/MyDB.mdb;'
              >>> sql_statement = "INSERT INTO [Table_Name] 
                                   ([Field_1], [Field_2]) VALUES ('data1', 'data2')"
              >>> conn.Open(DSN)
              >>> conn.Execute(sql_statement)
              >>> conn.Close()

1. Here is a last example that often seems to be a sticking point with ADO. Generally, if one wants to get the RecordCount of a table, one must move through all of the records counting them along the way like ...

              >>> # See example 3 above for the set-up to this 
              >>> rs.MoveFirst()
              >>> count = 0
              >>> while 1:
              ...     if rs.EOF:
              ...         break
              ...     else:
              ...         count = count + 1
              ...         rs.MoveNext()

Aside from being horribly inefficient, if the recordset is empty, moving to the first record will generate an error. ADO provides a way to correct this. Before opening the recordset, set the CursorLocation to 3. After opening the recordset, the recordcount will be available.

              >>> rs.Cursorlocation = 3 # don't use parenthesis here
              >>> rs.Open('SELECT * FROM [Table_Name]', conn) # be sure conn is open
              >>> rs.RecordCount # no parenthesis here either
              186

Again, the 3 is a constant.

This really just scratches the surface of ADO, but it should help getting connected from Python. For anything more than just simple database scripting it is worth looking into the object model. Here are some links that might be helpful. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadoobjmod.asp http://www.activeserverpages.ru/ADO/dadidx01_1.htm

Please contact me with questions or comments. I have placed a number of old questions and replies online. Feel free to contact me with questions, but I may use your question publicly. I will, of course, remove any identifying information.

References

• [1]

Examples

ado.py

This script was used to access a LoadRunner Access database repository.

import time
import win32com.client

engine = engine = win32com.client.Dispatch("DAO.DBEngine.36")
db     = engine.OpenDatabase(r"c:\lr\py\analysis.mdb")
rs     = db.OpenRecordset("Result")

while not rs.EOF:
  scenario  = rs.Fields("Scenario Name").Value
  name      = rs.Fields("Result Name").Value
  tz        = rs.Fields("Time Zone").Value / 3600
  startTime = rs.Fields("Start Time").Value
  endTime   = rs.Fields("Result End Time").Value
  rs.MoveNext()

print
print "  Scenario: %s " % scenario
print "    Run ID: %s " % name
print "        TZ: %s " % tz
print "Start Time: %s " % time.asctime(time.localtime(startTime))
print "  End Time: %s " % time.asctime(time.localtime(endTime))