Using pymssql to create a table using python ArcMap 10.3

Question

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.

• PolyGeo: There does not seem to be a GIS component to your question. You import arcpy but do not use it. I think you should research/ask questions about pymssql at Database Administrators.
• user918967: I doubt the folks at DBA would know this - it is all within ArcMap and the python window in ArcMap. That is the whole point, I cannot create a table in ArcMap. I can do it outside no problem
• PolyGeo: It sounds like you cannot create your SQL Server table from the Python window of ArcMap but you have not told us whether the same code (minus import arcpy) works from IDLE (or another IDE). If it does, then can you tell us the error thrown by the Python window of ArcMap when you run that code there, please?
• user918967: hmm, I do not get any error when I do it in IDLE. It does not do anything.

Answer 1

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)

Answer 2

Just do conn.commit() and it creates the table