current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
1  
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. –  PolyGeo♦ Mar 27 at 22:35
    
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 –  user918967 Mar 27 at 22:56
1  
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? –  PolyGeo♦ Mar 27 at 23:33
    
hmm, I do not get any error when I do it in IDLE. It does not do anything. –  user918967 Mar 30 at 20:59

1 Answer 1

up vote 3 down vote accepted

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)
share|improve this answer
    
This works for me! Thanks Chad! –  user918967 Mar 30 at 20:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.