topic Re: Is anyone else using python to directly interact with their DBMS? in Python Questions

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

ChrisMathers — Wed, 10 Sep 2014 20:09:38 GMT

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?

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

ChrisPedrezuela — Thu, 11 Sep 2014 01:27:44 GMT

I use arcpy.ArcSDESQLExecute.

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

CarstenBøcker1 — Thu, 11 Sep 2014 09:28:07 GMT

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.

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

JamesCrandall — Thu, 11 Sep 2014 12:59:43 GMT

cx_Oracle here.

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

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

CameronMcCormick — Thu, 11 Sep 2014 13:51:02 GMT

Using cx_Oracle here too. Great little extension

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

ZoeZaloudek — Thu, 11 Sep 2014 16:58:13 GMT

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.

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

KevinBrown2 — Thu, 11 Sep 2014 18:12:16 GMT

I'm using win32com to instantiate ADODB objects:

<CODE>

from win32com.client import Dispatch

connection = Dispatch('ADODB.Connection')

connection.Open(dsn)

rs = Dispatch('ADODB.RecordSet')

rs = connection.Execute(sql)

</CODE>

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

ThomasStanley-Jones — Thu, 11 Sep 2014 18:48:55 GMT

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.

def getConnection():

"""

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)

conprop.SetProperty("INSTANCE", "sde:sqlserver:?????")

conprop.SetProperty("DATABASE", "?????")

conprop.SetProperty("AUTHENTICATION_MODE", "OSA")

# Create workspace

SDEWorkspaceFactory = Snippets.NewObj(esriDataSourcesGDB.SdeWorkspaceFactory, esriGeodatabase.IWorkspaceFactory)

SDEWorkspace = SDEWorkspaceFactory.Open(conprop, 0)

sqlworkspace = Snippets.CType(SDEWorkspace, esriGeodatabase.ISqlWorkspace)

return sqlworkspace

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

ChrisMathers — Thu, 11 Sep 2014 19:07:42 GMT

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.

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

ChrisMathers — Thu, 11 Sep 2014 19:10:25 GMT

I like arcpy.ArcSDESQLExecute but Im not usually looking for spatial data or even geodatabase data when I do this.

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

TedChapin — Thu, 11 Sep 2014 19:33:08 GMT

pyodbc is really good.

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

ThomasStanley-Jones — Thu, 11 Sep 2014 21:28:08 GMT

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.

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

TedChapin — Fri, 12 Sep 2014 12:24:49 GMT

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.

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

JamesCrandall — Fri, 12 Sep 2014 18:59:25 GMT

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