How do you import an SQL query/view into arcgis

[deleted-user-Pi5x1UjkS2PY]

Have a view in SQL that I want to bring into ArcGIS using python.  It is part of a process of a longer python script.  how can I do this using python.

In 9.3 I used my ODBC connection  "AR"  to do this and then brought in my file "GISWEB.VW_MCYWEB" as an XY Event and then exported it as a shapefile with a coordinate system of WebMercator so that I could use it in my web maps.

I've tried several things but nothing is working for me at the moment.  I'm sure this is simple but I'm not see how to do this.

I thought maybe this would work.

import arcpy

import win32api, win32con

import sys

import pyodbc

from arcpy import env

def defAddSQLTable_CreateShapefile():

    ### Works Makes a list of all tables in SQL Database for that DSN Connection ###
    cnxn = pyodbc.connect("DSN=AR")
 
arcpy.env.workspace= 'C:/temp' 
in_Table = "AR.GISWEB.VW_MCYWEB" 
y_coords = "Longitude" 
x_coords = "Latitude" 
out_layer = "Stores" 
arcpy.env.overwriteOutput = 'true'

arcpy.MakeXYEventLayer_management(in_Table, x_coords, y_coords, out_layer)

defAddSQLTable_CreateShapefile()

[DanPatterson_Retired · Accepted solution]

Numpy lesson part 2....

If you are producing an array from a list of lists, you can run into some problems...

>>> import numpy as np
>>> data_list = [[0,'Walmart',-75.0,45.0],[1,'Costco',-75.5,45.1],[2,'Best Buy',-75.1,45.2],[3,'Shoppers',-75.2,45.3]]
>>> dtype = np.dtype([('ID','<i4'),('Store','|S25'),('Long','<f8'),('Lat','<f8')])
>>> np_array = np.array(data_list,dtype)
Traceback (most recent call last):
  File "<interactive input>", line 1, in <module>
TypeError: expected a readable buffer object
>>>

you can correct for this by using a generator to convert the list of lists to a list of tuples...and life is good

>>> data = [ tuple(row) for row in data_list]
>>> data
[(0, 'Walmart', -75.0, 45.0), (1, 'Costco', -75.5, 45.1), (2, 'Best Buy', -75.1, 45.2), (3, 'Shoppers', -75.2, 45.3)]
>>> np_array = np.array(data,dtype)
>>> np_array
array([(0, 'Walmart', -75.0, 45.0), (1, 'Costco', -75.5, 45.1),
      (2, 'Best Buy', -75.1, 45.2), (3, 'Shoppers', -75.2, 45.3)],
      dtype=[('ID', '<i4'), ('Store', 'S25'), ('Long', '<f8'), ('Lat', '<f8')])
>>>

The stores in no way sponsor this thread...

[deleted-user-Pi5x1UjkS2PY]

Changed to this and it works like a charm

datarray.append(tuple(row))

One last question.  I need to be able to add the spatial reference of webmercator to it.  I know you do it somehow like this.

Just not sure what to put in for webmercator?

SR = arcpy.Describe("C:/data/texas.gdb/fd").spatialReference

[DanPatterson_Retired]

stealing from the help files

SR = arcpy.Describe("C:/data/texas.gdb/fd").spatialReference

# Export the numpy array to a feature class using the XY field to

#  represent the output point feature

#

arcpy.da.NumPyArrayToFeatureClass(array, outFC, ['XY'], SR)

As long as you have a valid spatial reference, then you add it as SR when creating the output