0

I have thousands of databases in Microsoft SQL Server and I want the user to select the server and DB they want so I cannot use arcpy.ArcSDESQLExecute

I am trying to use pymssql to connect to the database and run a query. I understand how to save it as a CSV but I am struggling to understand how to save the results of the query to an in_memory table.

Here is my code to save it as a CSV.

from os import getenv
import pymssql
import pandas as pd
import arcpy
import csv
from decimal import Decimal

conServer = arcpy.GetParameterAsText(0) #r"server\server"
#conUser = getenv("put your username")
#conPassword = getenv("put your password")
condb = arcpy.GetParameterAsText(1) #"databaseName"

conn = pymssql.connect(server=conServer, database=condb)
cursor = conn.cursor()

query1 = "some sql query"

cursor.execute(query1)

with open(r"D:\GIS Data\output.csv", "w") as outfile:
    writer = csv.writer(outfile, quoting=csv.QUOTE_NONNUMERIC)
    writer.writerow([i[0] for i in cursor.description]) # write headers
    for row in cursor:
        writer.writerow(row)

cursor.close()
conn.close()

arcpy.SetParameter(3, outfile)
  • This shows how to create an in-memory Table, add a record with a DA InsertCursor. desktop.arcgis.com/en/arcmap/10.3/analyze/modelbuilder/… – klewis Feb 4 at 20:03
  • I want to replace the creation of a csv with a table in a geodatabase. I tried this but I am getting an error in arcpy.AddField_management stating can only concatenate tuple (not "str") to tuple: query1Table = arcpy.CreateTable_management(r"D:\GIS Data\TOOLS\EV calc in Python\Scratch2.gdb", "temp_loc_loss_adj") columnNames = tuple(map(str, [i[0] for i in cursor.description])) print(columnNames) for field in columnNames: arcpy.AddField_management(*(query1Table,) + field) cursor = arcpy.da.InsertCursor(query1Table, columnNames) for row in cursor: cursor.inserRow(row) – Giacomo Feb 5 at 17:10
  • Seems you need to refactor code for AddField. You can only add 1 field at a time. Also, a field type is required. – klewis Feb 5 at 19:01
  • @klewis yes I am almost there but I have a "sequence size must match size in row" error, I believe it's because the geodatabase table has an OBJECTID field but I'm not sure how to skip that column query1Table = arcpy.CreateTable_management(r"D:\GIS Data\TOOLS\EV calc in Python\Scratch.gdb", "temp_loc_loss_adj") columnNames = tuple(map(str, [i[0] for i in cursor.description])) print(type(columnNames)) addfield = partial( arcpy.AddField_management, query1Table) – Giacomo Feb 5 at 19:33
  • for field in columnNames: addfield(field) #arcpy.AddField_management(*(query1Table,) + field) query1Table_field_names = [f.name for f in arcpy.ListFields(query1Table)] print(query1Table_field_names) for row in cursor: with arcpy.da.InsertCursor(query1Table, query1Table_field_names) as insCur: insCur.insertRow(row) – Giacomo Feb 5 at 19:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.