Wednesday, April 24, 2013

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

2 comments:

Anonymous said...

great stuff. thanks for posting.

when it comes to the intermediate table what about using cursor.description() to gain access to the attribute names?

something like...

attribute_array = [i[0] for i in cursor.description]

...we can also use cursor.description to gain access to the data types...

type_array = [i[1] for i in cursor.description]

...and the rest of the attribute information like size, precision, scale, etc. too

maybe then use those to build the new table and append them?

Mike said...

One problem I've run into with this is some of my dtypes are Object, which Arc doesn't know how to handle. I've yet to figure out a way to programatically convert these to String values, so I've resorted to converting the recarray to a pandas dataframe, saving it as a csv and then importing the table that way. Surely there's got to be a better way though.