fetching data through odbc connection

BartKowalski — Mon, 15 Aug 2011 23:08:27 GMT

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")

Re: fetching data through odbc connection

HemingZhu — Sat, 11 Dec 2021 20:30:43 GMT

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.

topic fetching data through odbc connection in Python Questions

fetching data through odbc connection

Re: fetching data through odbc connection