I use pypodbc to connect and query my SQL servers on occasion and was curious if anyone else was doing this as well. If you are, what module are you using?
I use arcpy.ArcSDESQLExecute.
I like arcpy.ArcSDESQLExecute but Im not usually looking for spatial data or even geodatabase data when I do this.
I have used pyodbc for SQL Server and cx_oracle for Oracle. Hovever recently i have turned more and more to AcSDESQLExecute. You don't have to install third party libraries (a problem at many sites) and I also like that the results are returned as lists.
I haven't tested performance, so I don't know if there's an overhead using AcSDESQLExecute compared to the native python libraries.
It's not always SDE data that is needed and non-spatial attribute data can be utilized in functional ways (mapping, joins, raster processing, etc...)
Using cx_Oracle here too. Great little extension
I downloaded and installed MySQLdb to access data on our SQL databases. It was the first thing I tried that worked and I've just been using it ever since.
I'm using win32com to instantiate ADODB objects:
from win32com.client import Dispatch
connection = Dispatch('ADODB.Connection')
rs = Dispatch('ADODB.RecordSet')
rs = connection.Execute(sql)
I use comtypes to interact with ArcObjects direcly. I've found it a lot less buggy than arcpy, although I don't do much more than retrieve data from our servers.
Creates and returns a connection to the Database
@return: SdeWorkspace as ISqlWorkspace
import comtypes.gen.esriSystem as esriSystem
import comtypes.gen.esriDataSourcesGDB as esriDataSourcesGDB
import comtypes.gen.esriGeoDatabase as esriGeodatabase
# Set connection properties for OLE DB connection
conprop = Snippets.NewObj(esriSystem.PropertySet, esriSystem.IPropertySet)
# Create workspace
SDEWorkspaceFactory = Snippets.NewObj(esriDataSourcesGDB.SdeWorkspaceFactory, esriGeodatabase.IWorkspaceFactory)
SDEWorkspace = SDEWorkspaceFactory.Open(conprop, 0)
sqlworkspace = Snippets.CType(SDEWorkspace, esriGeodatabase.ISqlWorkspace)
Wow! Ive never used the comtypes library before. That is really cool. Going to have to add that to the list of things to read up on.
pyodbc is really good.
I'm curious what pyodbc will give you. Arrays of the tables to use with Numpy? Do you use it to feed spatial objects into the arcpy tools?
I used comtypes and ArcObjects to connect and get the data from SQLServer because then I can play with it in ArcObjects. I'm guessing pyodbc gives you an object that Arcpy can use.
Using pyodbc for row-level access to business tables, SDE, SQL Spatial, and non-spatial. Reading and updating data. Inserting and deleting data in the case of non-SDE tables.
I haven't worked with pyodbc because we are an Oracle shop, but I think this example would likely apply just as well. To answer your question, yes, we convert back and forth between NumPy arrays, Pandas DataFrames and gdb objects. This is just showing acessing and using non-spatial table containing x/y coord values converting to a NumPy array and then a Feature Class.
import numpy as np
### Build a DSN (can be subsitited for a TNS name)
dsn = cx_Oracle.makedsn(param1, param2, param3)
oradb = cx_Oracle.connect("username", "password", dsn)
cursor = oradb.cursor()
sqlQry = """SELECT MyTableOrView.SomeField1 AS SomeTEXTField,
CAST(TO_CHAR(MyTableOrView.x_coords, 'fm9999999.90') AS FLOAT) AS x_coords,
CAST(TO_CHAR(MyTableOrView.y_coords, 'fm9999999.90') AS FLOAT) AS y_coords
datArray = 
cxRows = cursor.fetchall()
for cxRow in cxRows:
#close the conn to ora
del cxRows, cursor
numpyarr_out = np.array(datArray, np.dtype([('SomeTEXTField', '|S25'), ('x_coords', '<f8'), ('y_coords', '<f8')]))
#convert the numpyarray to a gdb feature class
outFC = r'C:\MyGDB\xyPoints_FromStrings
arcpy.da.NumPyArrayToFeatureClass(numpyarr_out, outFC, ("x_coords", "y_coords"))
Retrieving data ...