My ArcPython SQL Adventure

Looking for something in particular?
Search us! If you don't find it here, remember to share it with us when you do.

My ArcPython SQL Adventure

In ArcGIS Desktop, ESRI, GeoBase & Drafting, Microsoft SQL, Python | on June, 01, 2018 | by | 0 Comments

Hope you are having a wonderful Friday.  Just wanted to share some of my experience while working on a recent python script.  The story kind of goes like this….

There I was (deep in the coding jungle) trying to figure out how to extract data from a database without using something too crazy or off the wall.  I talked with one of the local developers initially to get some background as to how things were currently being done.  For some scripts the developers are using a library called pyDbUtils to parse their SQL queries in a close to native SQL.

–Example of pyDbUtils—

 

 

In this example, you will see the SQL command being setup as a variable sqlcmd with the full SQL statement that the script wants to execute.  {0} and {1} are variables that are filled by cadTblPrefix and user respectively found at the end of that line.  The result variable is defined after pyDbUtils.RunSQLStatement and its arguments are run.  Arguments are the variables and configuration (True).

It’s a great solution, but I didn’t want to have the script I was writing rely on anything other than what was provided out of the box from ESRI.  I opted to try something a bit different using only what ESRI had available within its ArcPy library.  After some research, this is what I ended up on the first attempt.

–Example of ArcPy 1’st Attempt—

In this example, I am using the arcpy.da.SearchCursor command execute my SQL query.  There is some argument that a cursor is not as efficient as native SQL.  This is entirely true.  They are also a huge resource hog.  Using SQL allows for the database to perform set based operations whereas cursors operate in a row based world.  In this example, I am telling python to with a cursor, access my data_source (Database Connections\\Some SDE.sde\\Schema_Name.Table_Name) to access the fields indicated in brackets, eg. Data_Owner, etc.  For each output, for each row, to take the output, row[], and low it to a corresponding variable.  The # within the row indicates what the column number is.  0 being the 1’st column.  In this case, the output from the Data_Owner field would be loaded to the target_owner variable.

What I discovered was that it was clunky and because its through Python it makes it 10 times harder to get the syntax just right, especially since I am not always working in Python.  I couldn’t pull it over to SQL and test it against anything without really re-working the code and…it was frustratingly inconsistent in results.

After a bit more research, I finally stumbled upon the hidden gem…a way to use ArcPy with native SQL without having to resort to an outside library for help.

–Example of ArcPy 2’nd Attempt—

 

As it turned out, ArcPy was hiding ArcSDESQLExecute from me the whole time.  That one command alone allows you to call your db_connection which in this case was something like this Database Connections\\Some SDE.sde.  After that, you specify execute and the argument (check_pending_updates_sql) you want it to run and viola it works every time.  The best part of all is, I can test it in SQL with very few changes, it pops right into Python.

–Example of ArcPy 2’nd Attempt w/ Query Built In—

This example has specific items its looking for in the database and only will kick out 1 result.  This query is looking to the database for what SDE connection it should use for a specific target database, database type (Production, Staging, or Test), and who the data owner or schema owner is.

Long story short, if you find yourself writing anything in Python for ArcGIS and need to run a SQL query through it, consider using ArcSDESQLExecute rather than pyDbUtils in the future.