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?

[ChrisPedrezuela]

I use arcpy.ArcSDESQLExecute.

[ChrisMathers]

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

[CarstenBøcker1]

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.

[JamesCrandall]

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

[CameronMcCormick]

Using cx_Oracle here too.  Great little extension

[ZoeZaloudek]

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.

[KevinBrown2]

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>

[ThomasStanley-Jones]

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

[ChrisMathers]

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.