Using pyODBC to Connect To Access with ArcPy

pyODBC allows users to access any data source with the proper ODBC driver installed on the system.  This is very convenient, and helpful because at 10.1 using ArcPy only you cannot create ODBC connection.

You can download pyODBC here.  Grab the correct python version and install it.

Once installed, let's try to access a 2010-2012 MS Access Database.

I create a dummy database called 'db_text.accdb' and create a table called 'Addresses' with 5 text fields: 

1. name
2. street
3. town
4. country
5. zipcode

After that I populated two rows with dummy data.  Once you have some dummy data, let's move forward.

import pyodbc

import arcpy

import numpy

arcpy.env.overwriteOutput = True

accb = r"C:\temp\db_test.accdb"
access_con_string = r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=%s" % accb
cnxn   = pyodbc.connect(access_con_string)
cursor = cnxn.cursor()
cursor.execute("select * from Addresses")
rows = cursor.fetchall()

dts = {'names': ('ID','name','street', 'town', 'country', 'zipcode'),
       'formats':(numpy.uint8, 'S255','S255','S255','S10','S10')}

array = numpy.rec.fromrecords(rows, dtype=dts)

arcpy.da.NumPyArrayToTable(array, r"c:\temp\scratch.gdb\outTable")

Here we connected to the MS Access database using the drivers installed on my local system. Next I performed a simple query to return all records from the table. With the new da.NumPyArrayToTable() at 10.1, I want to convert the pyodbc rows to a table that can be used in ArcMap. I convert the list of tuples (rows object) to a numpy.array object. After the conversion, I fire off the arcpy function and now I have a table on disk.

For a complete listing of 10.1 numpy functions, check out: http://resources.arcgis.com/en/help/main/10.1/index.html#/What_is_the_data_access_module/018w00000008000000/

There you can learn about extending tables, convert feature classes to numpy arrays, and rasters as well.

Enjoy