Using ArcPy to Determine the Underlying Database

Sometime you need to you what the underlying database is because you do not know what the database is.  I know it sounds strange, but it does happen.  I created a small script to solve this problem.

import arcpy
def checkDBType(dbPathOrConnectionFile):
    """ checks the db to get the database type """

    if dbPathOrConnectionFile.lower().endswith(".mdb"):
        return "PGDB"
    elif dbPathOrConnectionFile.lower().endswith(".gdb"):
        return "FGDB"
    elif dbPathOrConnectionFile.lower().endswith(".sde"):
        queries = {
            "Informix" : """select first 1 dbinfo("version", "full") from systables;""",
            "MSSQLServer" : """SELECT @@VERSION""",
            "Oracle" : """select * from v$version""",
            "PostGreSQL" : """select version()::varchar(255);"""
            }
        fail = True
        conn = arcpy.ArcSDESQLExecute(dbPathOrConnectionFile)
        for k,v in queries.iteritems():
            try:
                conn = arcpy.ArcSDESQLExecute(dbPathOrConnectionFile)
                conn.execute(v)
                return k
            except:
                pass
    return "Unknown"

Basically script just a tries the SQL snippet and it it works, then that is the database you are using. To run the sql statement, you use the arcpy.ArcSDESQLExecute()'s connection object which has a function called execute().

The results seem to be promising, but I didn't have an Informix DB to test this on. So if you have one, please let me know if that statement will work! Oracle, SQL Server and PostGreSQL all work well.

 Enjoy