How to convert a sql query result into a table

4345
6
07-04-2016 02:30 PM
ChrisPedrezuela
Occasional Contributor III

Hi guys, im using pypyodbc to connect to sql and perform some joins between several tables. How can I directly convert the results into a stand-alone table within a geodatabase?

Regards,
Thanos

0 Kudos
6 Replies
PanagiotisPapadopoulos
Esri Regular Contributor

You can try create an in memory table

results = arcpy.CreateTable_management("in_memory", "VSResults", template, config_keyword)

then open a cursor for this table

cursor = arcpy.da.InsertCursor(results ,("ITEMID", "NAME", "YTPEN"))

loop into sql statement result end insert the sql data into the table

for result in cur:

     cursor.insertRow((result[0], result[1], result[2])

this table can be stored on geodatabase or set as output parameter

feature_set = arcpy.RecordSet()

feature_set.load(results)    

arcpy.SetParameter(4, feature_set)

ChrisPedrezuela
Occasional Contributor III

Thanks for that. Its should be just as straight forward as you wrote there, but the thing about my sql query is that I have like more than 20 columns from that results. If i will create a table first in the GDB, how can I read the columns and column type of my result and use it in creating the fields dynamically? In the part where we loop through the results of the query, if I have 20 columns, I don't want to write "cursor.insertRow((result[0], result[1], .......result[19])".

Thanks

0 Kudos
PanagiotisPapadopoulos
Esri Regular Contributor

Before create the table define a template for this table

template = "C:/tableschema/Projects/vstamplate.dbf"

config_keyword = ""

# Execute CreateTable

results = arcpy.CreateTable_management("in_memory", "VSResults", template, config_keyword)

this template can be pre configure with fields you are expecting from the query or try with python create this dbf file based on the SQL query results.

PanagiotisPapadopoulos
Esri Regular Contributor

for the creation dbf files from python a have no experience but looking into the internet you can see some modules about that

Python modules for accessing .dbf (xBase) files

JoshuaBixby
MVP Esteemed Contributor

The more specifics you provide, the more specific the feedback others can give.

What type of DMBS are you working with?  Is the database you are connecting to with pypyodbc a geodatabase?

Have you checked out the Make Query Layer and Make Table Query tools?

0 Kudos
ChrisPedrezuela
Occasional Contributor III

Hi Joshua,

I'm working with SQL Server DB. I actually simplified the requirements of my query and managed to avoid using 20 tables and instead just three. So from there it was manageable to just add the results directly to my sde staging table. I think "Make Query Table" will also do as you've suggested but was quite a bit challenging to setup based on the number of table I was working with.

0 Kudos