Other Ways to Join and Calculate fields

Blog Post created by Kyle.gonterwitz_KSDOT on Aug 1, 2018

Have you ever compared the speed of a Data Access cursor update vs using the field calculator?  Using data access cursors is a rite of passage for developers using python for ArcGIS, surely.  Write a few more lines of code to save some processing time.  Next thing you know, your python code is getting complicated.  


A few years ago I started using pymssql with SQL Servers and cx_Oracle with Oracle Databases in my python scripts. In ArcGIS Pro now, looks like pymssql is getting antiquated, because Conda cant install it - it's not easy to install with a wheel and its less easy to get that wheel to work in the propy conda environment.   


The ArcGIS Pro Conda package manager has no problem installing pyodbc so I set up a system DSN and used that to call sql scripts with Update TABLE set FIELD = Expression WHERE 1=1 within python scripts as a cursor.


Arcpy also gives you ArcSDESQLExecute—ArcPy classes | ArcGIS Desktop  to execute native SQL commands using a Geodatabase connection rather than a DSN, so I will have to try that next time.


Native SQL can make the da cursor look slow, it gives much more control over join types and integrity, and it can be done in less characters of code, easily, from a python script.