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 acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.