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?

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"))