topic Re: How to convert a sql query result into a table in Python Questions

How to convert a sql query result into a table

ChrisPedrezuela — Mon, 04 Jul 2016 21:30:35 GMT

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

Re: How to convert a sql query result into a table

PanagiotisPapadopoulos — Mon, 04 Jul 2016 22:03:47 GMT

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)

Re: How to convert a sql query result into a table

ChrisPedrezuela — Mon, 04 Jul 2016 22:45:48 GMT

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

Re: How to convert a sql query result into a table

PanagiotisPapadopoulos — Tue, 05 Jul 2016 07:05:48 GMT

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.

Re: How to convert a sql query result into a table

PanagiotisPapadopoulos — Tue, 05 Jul 2016 07:10:37 GMT

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

Re: How to convert a sql query result into a table

JoshuaBixby — Tue, 05 Jul 2016 15:00:18 GMT

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?

Re: How to convert a sql query result into a table

ChrisPedrezuela — Thu, 07 Jul 2016 04:27:03 GMT

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.