Advice/help needed

[TateM]

Hello All,

I would like y'all advice and help, as I'm still new at this. What I'm trying to do is to create a python script that go to a particular SQL Server grab the data --> then using arcpy create a point feature class --> then puts in a SDE Geodatabase (different server). 

Steps:
1. import pyodbc to connect to the SQL Server, query out the data.
2. use Arcpy to create point feature class.
3. connect to SDE Geodatabase in a different Server then copy the point feature class into the Geodatabase.

This workflow seems to be correct? Thanks!

[TateM]

so far I got this is what I've got.

 
import pyodbc, arcpy
cnxn = pyodbc.connect(trusted_connection='yes', driver='(SQL Server)', server = 'abcserver', database='TEST')
cursor = cnxn.cursor()
try:
    query = " SELECT surflat, surflong FROM table1
    cursor.execute(query)
    cnxn.commit()
except:
    print "error"

And I think the next step is to use the  arcpy.MakeXYEventLayer_management --> arcpy.CopyFeatures_management into the SDE database.  Any pointers on how to do this?  TIA

[MathewCoyle]

After you get your data from your source, I think the easiest next step would be to use an insert cursor. Either directly into your SDE feature class or into a temp in memory feature class to then upload to SDE.

See the last example in this help page for inserting geometry. It is a little easier than depicted since you are only inserting point geometry.
http://resources.arcgis.com/en/help/main/10.1/index.html#//018w0000000t000000

[TateM]

After you get your data from your source, I think the easiest next step would be to use an insert cursor. Either directly into your SDE feature class or into a temp in memory feature class to then upload to SDE.

See the last example in this help page for inserting geometry. It is a little easier than depicted since you are only inserting point geometry.
http://resources.arcgis.com/en/help/main/10.1/index.html#//018w0000000t000000

@mzcoyle thanks for your respond.  But what if the query result is from a stand alone non-spatial sql server that is from a different server?

[City_of_RockfordIL]

Hello. Your workflow should work just fine. One thing I would recommend is setting up OLE DB Connections in ArcCatalog for your SQL Server connections. Define the Provider as SQL server and then select your server name, credentials, and database you are reading data from in your script.

For your workflow you should be able to simply Make XY event layer from your SQL table (e.g. name it "SQL_XY")and then use the Copy features GP tool. The input features for the tool would be your new "SQL_XY" table and the output feature class would be the SDE feature class you want to export the data to (just ensure that you have also setup a spatial database connection in Catalog to the SDE database) . See attached code and ModelBuilder workflow for an example. Good luck!

Tara

[TateM]

Hello. Your workflow should work just fine. One thing I would recommend is setting up OLE DB Connections in ArcCatalog for your SQL Server connections. Define the Provider as SQL server and then select your server name, credentials, and database you are reading data from in your script.

For your workflow you should be able to simply Make XY event layer from your SQL table (e.g. name it "SQL_XY")and then use the Copy features GP tool. The input features for the tool would be your new "SQL_XY" table and the output feature class would be the SDE feature class you want to export the data to (just ensure that you have also setup a spatial database connection in Catalog to the SDE database) . See attached code and ModelBuilder workflow for an example. Good luck!

Tara

Thanks Tara, I have look at your workflow and it works, but what if I wanted to input a sql expression to filter it or have multiple tables and do a inner join for those tables and then use the XYEvent Layer. Do I create a stand-alone sql expression, what about the Make Query Table tool or Make Table View tool?  TIA.