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

SQL Fiddle - Your Database Scratchpad

Ever want to test something out on your database but did not want to mess everything up. Now is your chance, check out: sqlfiddle.  Just go to this site and select your database.  You can then create a quick schema and dummy data and bam! Test away.


Enjoy

Finding Duplicates (3 Ways)

In this post I will show some methods of finding duplicate values in an enterprise SDE database.
The general query is quite simple.  We will perform a SELECT on the table and return the values and the number of times it repeats within that table
First I will use cx_Oracle module to query a table and return both the count and field value that has duplicates:
import cx_Oracle
#.... connection information
#.... create cursor
columnName = "ClientName" 
table = "clients" 
sql = "SELECT %s, count(*) from %s GROUP BY %s HAVING count(*) > 1" % (columnName, table, columnName) 
print cursor.execute(sql).fetchall()
That will return a 2 column list of tuples where as a developer you can then parse that information as needed.

Let's assume you want to do it a more ArcPy way.  ArcSDESQLExecute class can be used to perform the same SQL statement.  The ArcSDESQLExecute class provides a means of executing SQL statements via an ArcSDE connection (source: webhelp.esri.com).

# Two ways to create the object, which also creates the connection to ArcSDE. 
# Using the first method, pass a set of strings containing the connection properties: 
# sdeConn = arcpy.ArcSDESQLExecute("gpserver3","5151","#","toolbox","toolbox") 
# Using the second method pass the path to a valid ArcSDE connection file 
# sdeConn = arcpy.ArcSDESQLExecute("data\Connection to GPSERVER3.sde")
sdeConn = arcpy.ArcSDESQLExecute(r"c:\temp\connectionFile.sde"

columnName = "ClientName" 

table = "clients" 

sql = "SELECT %s, count(*) from %s GROUP BY %s HAVING count(*) > 1" % (columnName, table, columnName) 

results = sdeConn.execute(sql)
for result in results:
   print result # List with [column value, # of duplicates]


You can always turns to ArcToolbox for help if you do not like using SQL. Using Summary Statistics you can get the count of the values in a specific field. It most likely will be slower for large data sets compared to the other two method mentioned above, but it can be used for any supported data set in ArcGIS.

Happy Duplicate Finding

ArcPy - List Columns for Table

In my previous post, I showed you how to use pure SQL in oracle to list the fields of a table.  This can be done using ArcPy as well.
ArcPy has a function called: ListFields (dataset, {wild_card}, {field_type}) where all you are required to pass is the dataset. The wild card and field type are limiters that can help slim down the results.
sdeFile = r"c:\temp\connectionFile.sde" 
fc = sdeFile + os.sep + "myFeatClass" 
fields = arcpy.ListFields(fc) 
for field in fields:
   print field.name

The code returns a field object from which you can access a whole set of properties. This method is preferred when examining spatial data because it provides the data in a format that the Arc system can read and understand.
Enjoy

Listing Oracle Columns Using Python

I've started coding a set of helpful functions for oracle and python using the cx_oracle library for python.  Just download the library and install the one associated with your python install.

In this example, I will list a set of columns in a table using python. First we need to connect to the oracle database
import cx_Oracle
username = "user" 
password = "*****" 
ip = "111.11.11.11" 
port = 1521 
SID = 'ORCL' 
table = 'mytablename' 
dsn_tns = cx_Oracle.makedsn(ip, port, SID) 
db = cx_Oracle.connect(user,passwword, dsn_tns)

All I did was create the entry like it would appear in the tnsname.ora file then pass that information along with the username and password to the database.
Great, now we are connected to the oracle database.  Now the database can be queried.

cursor = db.cursor()
results = cursor.execute("select * from %s where 1=0" % table)
print results.description


[('OBJECTID', <type 'cx_oracle.number'="">, 39, 22, 38, 0, 0), ('NEWNAME', <type 'cx_oracle.unicode'="">, 50, 100, 0, 0, 1)]

Now you do not have to use arcpy to list the fields in a table.  Some other ideas you could do with your enterprise database is: rename fields, create views, etc...

Enjoy

Oracle's New Motto

My Suggestion for Oracle's Motto: "We're Not Happy Till You're Not Happy"