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

[JamesCrandall]

AR.GISWEB.VW_MCYWEB is not a table (as in how you perceive your "in_Table" variable).  You have to issue SQL against this to fill a cursor object and then use the cursor to build your table.

GettingStarted - pyodbc - Quick Examples To Get You Started - Python ODBC library - Google Project H...

We do something similar, but we access Oracle using cx_Oracle library.  basically once you get your cursor you can just append rows to a new list and/or numpy array which is easy to convert to a feature class with arcpy.da.NumPyArrayToFeatureClass() or arcpy.da.NumPyArrayToTable()

[deleted-user-Pi5x1UjkS2PY]

Thanks but I need further guidance please. I can use what is below to print out a list of Storenames from my table.  So it is connecting which is good.  One step closer.

However, I want all of my field names and values to go into this array to make the feature class which will hopefully make my shapefile.  Should be a simple way to loop through the table adding all of the column names and field values into the array.  Any help would be greatly appreciated.

def defAddSQLTable_CreateShapefile():
    cnxn = pyodbc.connect("DSN=AR")
    cursor = cnxn.cursor()

    cursor.execute("select * from GISWEB.VW_MCYWEB")

     fc = "c://temp/myfc

    for row in cursor:
        ##grab field value  print row.column_name   
    #    print 'name:', row[1]          # access by column index
    #    print 'name:', row.STORENAME   # or access by name

[JamesCrandall]

Something like this (untested and just typed out from memory and other snippets):

datArray = []
for row in cursor:
       datArray.append(row)

cursor.close()
del cursor
if len(datArray)>0:
   npyarr = np.array(datArray, np.dtype([('storename', '|S25'), ('xCoord', '<f8'), ('yCoord', '<f8')]))
   outFC = ws_mem + "\\MyFeatureClass" 
   if arcpy.Exists(outFC):
     arcpy.Delete_management(outFC)
     print "deleted " + str(outFC)

   arcpy.da.NumPyArrayToFeatureClass(npyarr, outFC, ("xCoord", "yCoord"))
   print "created..." + outFC

[JamesCrandall]

Also, please don't use "SELECT * FROM Table/View..." --- not only is it bad SQL, it's going to make your life really difficult when setting up your arrays and/or conversion into GIS-objects.  Be sure to specify the fields you want... ALWAYS.

[deleted-user-Pi5x1UjkS2PY]

Thanks but I want all of the fields and we will add to these fields as we grow this product.

I don't understand this line below.  what is the |S25' for and what is the <f8 for?

npyarr = np.array(datArray, np.dtype([('STORENAME', '|S25'), ('xCoord', '<f8'), ('yCoord', '<f8')]))

[JamesCrandall]

but I want all of the fields and we will add to these fields as we grow this product

Then add them, don't be lazy.  "SELECT *" is fine for quick look at things, but not a great idea for production code.

what is the |S25' for and what is the <f8 for?

ArcGIS Help 10.1

[DanPatterson_Retired]

He is producing a numpy array using the arcpy.da.NumPyArrayToFeatureClass.... line

This line npyarr = np.array(datArray, np.dtype([('STORENAME', '|S25'), ('xCoord', '<f8'), ('yCoord', '<f8')]))

sets the data type so that STORENAME is a string, 25 characters wide and xCoord, yCoord are floating point numbers (details in numpy dtype documentation)

in essence, you will end up with an array (npyarr) that you can query by field name and or coordinates.

for example

>>> data = [ ("Walmart", -75.0, 45.0),("Costco", -75.5,45.1)]
>>> import numpy as np
>>> dtype =np.dtype([('STORENAME', '|S25'), ('xCoord', '<f8'), ('yCoord', '<f8')])
>>> npyarr = np.array(data,dtype)
>>> npyarr
array([('Walmart', -75.0, 45.0), ('Costco', -75.5, 45.1)], 
      dtype=[('STORENAME', 'S25'), ('xCoord', '<f8'), ('yCoord', '<f8')])
>>> npyarr['STORENAME']
array(['Walmart', 'Costco'], 
      dtype='|S25')
>>> npyarr[0] 
('Walmart', -75.0, 45.0)
>>> npyarr[1]
('Costco', -75.5, 45.1)
>>>

such arrays can be extremely powerful

[DanPatterson_Retired]

y_coords = "Longitude" 

x_coords = "Latitude"

Interesting...these are reversed

[deleted-user-Pi5x1UjkS2PY]

augh.  I'm getting an error I'm not sure what to do about.  I've tried a lot of different samples so my code looks a little different.  Everything works fine when I do a print for the array but I continue to have the problem below.

I am getting this error below

TypeError: expected a readable buffer object

On this line:

npyarr = numpy.array(datArray, numpy.dtype([('storename', '|S25'), ("xCoord", '<f8'), ('yCoord', '<f8')]))

def defAddSQLTable_CreateShapefile():
    cnxn = pyodbc.connect("DSN=AR")
    cursor = cnxn.cursor()
    cursor.execute("Select STORENAME, LATITUDE, LONGITUDE FROM GISWEB.VW_MCYWEB WHERE DISTRICTID = 944")

    datArray = []
    for row in cursor:
           datArray.append(row)

    dtype = np.dtype([('STORENAME', '|S25'), ('xCoord', '<f8'), ('yCoord', '<f8')])
    npyarr = np.array(datArray, dtype)

    out_fc = "C:/inetpub/mystores3"
    arcpy.da.NumPyArrayToFeatureClass(npyarr, out_fc, ['xCoord','yCoord' ])