Community

fetching data through odbc connection

924
1
08-15-2011 04:08 PM
BartKowalski
by
New Contributor
‎08-15-2011 04:08 PM
Hi,
I am trying to connect to an external SQL server with a table with an x and y fields using pyodbc.
Next I am making an XYEventLayer using arcpy, and then converting the XYEventLayer to a feature class.
When I run the code outside of ArcView it chokes and says the dataset is not supported or does not exist.  I am not sure if calling the APP keyword is the correct way to do this. When I use the ???arcpy.ListDatasets()??? command I just get ???()??? return.
Thank you in advance for your help

import arcpy
import pyodbc
from arcpy import env
cnxn = pyodbc.connect('Driver={SQL Server Native Client 10.0};UID=user;PWD=Pass;SERVER=****.usace.army.mil; DATABASE=ftord; APP=MyApp')
arcpy.env.workspace= 'MyApp'
in_Table = "dbo.Wells_view"
y_coords = "northing"
x_coords = "easting"
out_layer = "wells"
arcpy.env.overwriteOutput = 'true'
saved_Layer = r"G:\Temp\Bart\layers\Wells.lyr"
arcpy.MakeXYEventLayer_management(in_Table, x_coords, y_coords, out_layer)
arcpy.FeatureClassToFeatureClass_conversion("Wells", r"G:\Temp\Bart\scratch.gdb", "wells")
Tags (2)
0 Kudos
1 Reply
HemingZhu
by
Occasional Contributor III
‎08-16-2011 11:55 AM
Hi,
I am trying to connect to an external SQL server with a table with an x and y fields using pyodbc.
Next I am making an XYEventLayer using arcpy, and then converting the XYEventLayer to a feature class.
When I run the code outside of ArcView it chokes and says the dataset is not supported or does not exist.  I am not sure if calling the APP keyword is the correct way to do this. When I use the �??arcpy.ListDatasets()�?? command I just get �??()�?? return.
Thank you in advance for your help

import arcpy
import pyodbc
from arcpy import env
cnxn = pyodbc.connect('Driver={SQL Server Native Client 10.0};UID=user;PWD=Pass;SERVER=****.usace.army.mil; DATABASE=ftord; APP=MyApp')
arcpy.env.workspace= 'MyApp'
in_Table = "dbo.Wells_view"
y_coords = "northing"
x_coords = "easting"
out_layer = "wells"
arcpy.env.overwriteOutput = 'true'
saved_Layer = r"G:\Temp\Bart\layers\Wells.lyr"
arcpy.MakeXYEventLayer_management(in_Table, x_coords, y_coords, out_layer)
arcpy.FeatureClassToFeatureClass_conversion("Wells", r"G:\Temp\Bart\scratch.gdb", "wells")


arcpy.MakeXYEventLayer_management wouldn't work in this case. Because in_Table is not a arcpy Table View rather than a table or view from pyodbc. One solution you can try is to create a feature class using arcpy.CreateFeatureclass_management. Then loop through the pyodbc cursor on your in_Table and use arcpy.InsertCursor to add each row from pyodbc cursor to the feature class.
0 Kudos