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.

I wrote this page long before PGDB support was added to GDAL. http://gdal.org/drv_pgeo.html They use ODBC.

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.