Accessing Personal Geodatabases with Python

A "personal geodatabase" (PGDB) is a format used by ESRI ArcGIS software. It is really a Microsoft Access database so methods to access MS "MDB" files will allow you to directly read and write PGDB's.

Currently you can only update attributes with this method. If you have to update spatial data, use ArcObjects instead.

• ODBC is the generic way. Works on Windows or UNIX.
• OLEDB is Microsoft's "new and improved" ODBC
• ADO is the Microsoft ActiveX / .net way.

So far I have found Python and ADO actually work together so that is what I am using. The documentation I have been able to find is the best for ADO, too.

Sample ADO code

#!/usr/bin/env python

import sys, os
import win32com.client

mdb = 'C:/AGIProduct/IncidentView_Data/Data/CA/Davis/IncidentView.mdb'
dsn = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=' + mdb + ';'

if not os.path.exists(mdb) :
    print "File does not exist: " + mdb
    sys.exit(-1)

conn = win32com.client.Dispatch(r'ADODB.Connection')
conn.Open(dsn)

# Create a record set for table called "Addresses"
recordset = win32com.client.Dispatch(r'ADODB.Recordset')
tablename = 'Addresses'
recordset.Open('SELECT * FROM ' + tablename, conn, 1, 3)

# Print names of fields in the table
for x in range(recordset.Fields.Count) :
    n = recordset.Fields.Item(x).Name
    print n

# You can now iterate over all records in the record set.
counter = 0
while not recordset.EOF :
    print recordset.Item("ST_NAME").Value
    recordset.MoveNext()
    counter += 1
print "Total records ", counter

Links

ADO -- http://www.mayukhbose.com/python/ado/ado-connection.php and http://www.markcarter.me.uk/computing/python/ado.html

ODBC -- ?? If I were using Perl I'd probably use ODBC but I have not tapped into the right source for documentation on ODBC + Python yet apparently.

OLEDB -- http://pyoledb.datadmin.com/ This apparently does nothing! Don't bother.