Is anyone else using python to directly interact with their DBMS?

[ChrisMathers]

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?

[TedChapin]

pyodbc is really good.

[ThomasStanley-Jones]

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.

[TedChapin]

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. 

[JamesCrandall]

Thomas,

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 arcpy  
import cx_Oracle  
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  
              FROM MyTableOrView"""  

cursor.execute(sqlQry)  
datArray = []  

cxRows = cursor.fetchall()  
for cxRow in cxRows:  
   datArray.append(cxRow)  

#close the conn to ora  
cursor.close()   
oradb.close()  
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  

if arcpy.Exists(outFC):  
  arcpy.Delete_management(outFC)  

arcpy.da.NumPyArrayToFeatureClass(numpyarr_out, outFC, ("x_coords", "y_coords"))