[GIS] Using pymssql to create a table using python ArcMap 10.3

arcgis-10.3arcgis-desktoparcpyenterprise-geodatabasesql server

I am using ArcMap 10.3 have a my data in a geodatabase (MS SQL Server Express) and I am trying to write a python script that does a bunch of spatial and non-spatial data manipulation.

I need to dump results into non-spatial tables in MS SQL Server so I can run queries off of them.

However, I cannot seem to get my CREATE table query to run properly. I have tried a bunch of variations of this query (e.g. putting dbo. etc) to no avail.

import arcpy
import os
#  Set environment variables for pymssql
import pymssql
server = os.getenv("DRAKE\SQLEXPRESS")

conn = pymssql.connect(server="DRAKE\SQLEXPRESS",database="NATA_Data")
cursor = conn.cursor()
cursor.execute('CREATE TABLE tempIntersect_withNATA (ZIP varchar(max), Measure varchar(max), HAP varchar(max), Result decimal(35,30))')
conn.close()

Nothing seems to happen, even when I paste it into the ArcMap python window. I have created a simple query such as cursor.execute('SELECT * FROM NATA_All') and that does hook to my database and retrieve data.

Best Answer

As an alternative to pymssql, you might consider using the arcpy.ArcSDESQLExecute class to execute your SQL statements. Something along the lines of (simplest form and untested):

import arcpy

sde = r"Database Connections\Connection-to-SDE.sde"
sde_conn = arcpy.ArcSDESQLExecute(sde)

sql = """CREATE TABLE tempIntersect_withNATA (ZIP varchar(max), 
                                              Measure varchar(max), 
                                              HAP varchar(max), 
                                              Result decimal(35,30))"""
sde_return = sde_conn.execute(sql)
Related Question