I am storing my dypes in a string but it does not seem to like it. Anyway that I can do something like this. I'm sure their is a simple fix. Code works if I hard code instead of the string but I am trying to make this a function so that I can reuse this code.
datarray = []
#for row in cursor:
for row in cursor.fetchall():
dtype = np.dtype(get_dtype)
#dtype = np.dtype([('YEAR', '<f8'), ('REGIONID', '<f8'), ('DISTRICTID', '<f8'), ('REGIONNAME', '|S255'), ('DISTRICTNAME', '|S255'), ('DIVLOC', '|S255'), ('STORENAME', '|S255'), ('CLUSTER', '|S255'), ('SUBID', '|S255'), ('SUBNAME', '|S255'), ('SEQUENCE', '<f8'), ('S_AREA', '<f8'), ('FS_AREA', '<f8'), ('ST_AREA', '<f8'), ('FR_AREA', '<f8'), ('SO_AREA', '<f8'), ('SL_AREA', '<f8'), ('TOTALSALES', '<f8'), ('OWNEDSALES', '<f8'), ('LEASEDSALES', '<f8'), ('GM', '<f8'), ('PROD', '<f8'), ('GMPROD', '<f8'), ('LATITUDE', '<f8'), ('LONGITUDE', '<f8'), ('RANKSALESSUB', '<f8'), ('RANKPRODSUB', '<f8'), ('RANKSALESSUBD', '<f8'), ('RANKPRODSUBD', '<f8'), ('RANKSALESSUBR', '<f8'), ('RANKPRODSUBR', '<f8'), ('RANKSALESSUBS', '<f8'), ('RANKPRODSUBS', '<f8'), ('RANKSALESSUBC', '<f8'), ('RANKPRODSUBC', '<f8')])
Getting this error:
File "T:/Current/GISWeb/Python Script/PublishBaseMapsMXDToServer.py", line 400, in defAddSQLTable_CreateShapefile
dtype = np.dtype(get_dtype)
File "C:\Python27\ArcGIS10.2\lib\site-packages\numpy\core\_internal.py", line 234, in _commastring
newitem = (dtype, eval(repeats))
File "<string>", line 1
(
^
SyntaxError: unexpected EOF while parsing
Solved! Go to Solution.
Hi Michael,
I haven't looked at the entire function, but I did notice 2 things:
get_dtype="[('YEAR', '<f8'), ('REGIONID', '<f8'), ('DISTRICTID', '<f8'), ('REGIONNAME', '|S255'), ('DISTRICTNAME', '|S255'), ('DIVLOC', '|S255'), ('STORENAME', '|S255'), ('CLUSTER', '|S255'), ('SUBID', '|S255'), ('SUBNAME', '|S255'), ('SEQUENCE', '<f8'), ('S_AREA', '<f8'), ('FS_AREA', '<f8'), ('ST_AREA', '<f8'), ('FR_AREA', '<f8'), ('SO_AREA', '<f8'), ('SL_AREA', '<f8'), ('TOTALSALES', '<f8'), ('OWNEDSALES', '<f8'), ('LEASEDSALES', '<f8'), ('GM', '<f8'), ('PROD', '<f8'), ('GMPROD', '<f8'), ('LATITUDE', '<f8'), ('LONGITUDE', '<f8'), ('RANKSALESSUB', '<f8'), ('RANKPRODSUB', '<f8'), ('RANKSALESSUBD', '<f8'), ('RANKPRODSUBD', '<f8'), ('RANKSALESSUBR', '<f8'), ('RANKPRODSUBR', '<f8'), ('RANKSALESSUBS', '<f8'), ('RANKPRODSUBS', '<f8'), ('RANKSALESSUBC', '<f8'), ('RANKPRODSUBC', '<f8')]" dtype = np.dtype(eval(get_dtype))
Kind regards, Xander
What would be the string that holds the dtypes?
Maybe these links are helpful:
The other thing is, that it might not be a good idea to use fetchall. See this post:
Arcpy - execute sql query in oracle error
It is a bit difficult to see what is going on, when you post a small part of the code.
Kind regards, Xander
This is the whole function
I am hoping to be able to create the dtype on the file using all of the fields in a table to create a shapefile. We use a view in SQL to limit the fields we want so I just want all of them. Since we update these all of the time and we have quite a few I am trying to automate this process.
so this works:
dtype = np.dtype([('YEAR', '<f8'), ('REGIONID', '<f8'), ('DISTRICTID', '<f8'), ('REGIONNAME', '|S255'), ('DISTRICTNAME', '|S255'), ('DIVLOC', '|S255'), ('STORENAME', '|S255'), ('CLUSTER', '|S255'), ('SUBID', '|S255'), ('SUBNAME', '|S255'), ('SEQUENCE', '<f8'), ('S_AREA', '<f8'), ('FS_AREA', '<f8'), ('ST_AREA', '<f8'), ('FR_AREA', '<f8'), ('SO_AREA', '<f8'), ('SL_AREA', '<f8'), ('TOTALSALES', '<f8'), ('OWNEDSALES', '<f8'), ('LEASEDSALES', '<f8'), ('GM', '<f8'), ('PROD', '<f8'), ('GMPROD', '<f8'), ('LATITUDE', '<f8'), ('LONGITUDE', '<f8'), ('RANKSALESSUB', '<f8'), ('RANKPRODSUB', '<f8'), ('RANKSALESSUBD', '<f8'), ('RANKPRODSUBD', '<f8'), ('RANKSALESSUBR', '<f8'), ('RANKPRODSUBR', '<f8'), ('RANKSALESSUBS', '<f8'), ('RANKPRODSUBS', '<f8'), ('RANKSALESSUBC', '<f8'), ('RANKPRODSUBC', '<f8')])
however this does not:
get_dtype="[('YEAR', '<f8'), ('REGIONID', '<f8'), ('DISTRICTID', '<f8'), ('REGIONNAME', '|S255'), ('DISTRICTNAME', '|S255'), ('DIVLOC', '|S255'), ('STORENAME', '|S255'), ('CLUSTER', '|S255'), ('SUBID', '|S255'), ('SUBNAME', '|S255'), ('SEQUENCE', '<f8'), ('S_AREA', '<f8'), ('FS_AREA', '<f8'), ('ST_AREA', '<f8'), ('FR_AREA', '<f8'), ('SO_AREA', '<f8'), ('SL_AREA', '<f8'), ('TOTALSALES', '<f8'), ('OWNEDSALES', '<f8'), ('LEASEDSALES', '<f8'), ('GM', '<f8'), ('PROD', '<f8'), ('GMPROD', '<f8'), ('LATITUDE', '<f8'), ('LONGITUDE', '<f8'), ('RANKSALESSUB', '<f8'), ('RANKPRODSUB', '<f8'), ('RANKSALESSUBD', '<f8'), ('RANKPRODSUBD', '<f8'), ('RANKSALESSUBR', '<f8'), ('RANKPRODSUBR', '<f8'), ('RANKSALESSUBS', '<f8'), ('RANKPRODSUBS', '<f8'), ('RANKSALESSUBC', '<f8'), ('RANKPRODSUBC', '<f8')])"
dtype = np.dtype(get_dtype)
# All of the function.
#--------------------------------------------------------------------------------------
def defAddSQLTable_CreateShapefile(strTableName, strFilePath, strShapeFileName):
##Trying to make one function for publishing files to server.
defSQLTableGetFieldNamesForSQLStatement
print strTableName
cnxn = pyodbc.connect("DSN=AR")
cursor = cnxn.cursor()
get_select_statement = "Select "
get_dtype = "["
#"Returns all rows from a cursor as a dict"
for row in cursor.columns(table=strTableName):
if row.data_type == 4 or row.data_type == 6 or row.data_type == 2:
get_select_statement = get_select_statement + "ISNULL(" + row.column_name + ", 0), "
else:
get_select_statement = get_select_statement + row.column_name + ", "
if row.data_type == 4 or row.data_type == 6 or row.data_type == 2:
get_dtype = get_dtype + "('" + row.column_name + "', '<f8'), "
else:
get_dtype = get_dtype + "('" + row.column_name + "', '|S255'), "
get_select_statement = get_select_statement[0:-2]
get_dtype = get_dtype[0:-2] +"]"
print get_select_statement
#print get_dtype
defDeleteFileIfItExist(strFilePath, strShapeFileName)
cnxn = pyodbc.connect("DSN=AR")
cursor = cnxn.cursor()
strfields = get_select_statement
strfrom = " From GISWEB."+strTableName
cursor.execute(strfields+strfrom)
datarray = []
#for row in cursor:
for row in cursor.fetchall():
datarray.append(tuple(row))
#dtype = np.dtype([('YEAR', '<f8'), ('REGIONID', '<f8'), ('DISTRICTID', '<f8'), ('REGIONNAME', '|S255'), ('DISTRICTNAME', '|S255'), ('DIVLOC', '|S255'), ('STORENAME', '|S255'), ('CLUSTER', '|S255'), ('SUBID', '|S255'), ('SUBNAME', '|S255'), ('SEQUENCE', '<f8'), ('S_AREA', '<f8'), ('FS_AREA', '<f8'), ('ST_AREA', '<f8'), ('FR_AREA', '<f8'), ('SO_AREA', '<f8'), ('SL_AREA', '<f8'), ('TOTALSALES', '<f8'), ('OWNEDSALES', '<f8'), ('LEASEDSALES', '<f8'), ('GM', '<f8'), ('PROD', '<f8'), ('GMPROD', '<f8'), ('LATITUDE', '<f8'), ('LONGITUDE', '<f8'), ('RANKSALESSUB', '<f8'), ('RANKPRODSUB', '<f8'), ('RANKSALESSUBD', '<f8'), ('RANKPRODSUBD', '<f8'), ('RANKSALESSUBR', '<f8'), ('RANKPRODSUBR', '<f8'), ('RANKSALESSUBS', '<f8'), ('RANKPRODSUBS', '<f8'), ('RANKSALESSUBC', '<f8'), ('RANKPRODSUBC', '<f8')])
print get_dtype
dtype = np.dtype(get_dtype)
WKID = 4326 # WGS-1984
sr = arcpy.SpatialReference()
sr.factoryCode = WKID
sr.create()
env.outputCoordinateSystem = sr
npyarr = np.array(datarray, dtype)
out_fc = strFilePath + strShapeFileName
if arcpy.Exists(out_fc):
#memoryFeature = "in_memory" + "\\" + "myMemoryFeature"
#arcpy.CopyFeatures_management(out_fc, memoryFeature)
arcpy.Delete_management(out_fc)
arcpy.Delete_management("in_memory")
env.overwriteOutput = 1
arcpy.da.NumPyArrayToFeatureClass(npyarr, out_fc, ['LONGITUDE','LATITUDE'], sr)
cnxn.close
cursor.close
Hi Michael,
I haven't looked at the entire function, but I did notice 2 things:
get_dtype="[('YEAR', '<f8'), ('REGIONID', '<f8'), ('DISTRICTID', '<f8'), ('REGIONNAME', '|S255'), ('DISTRICTNAME', '|S255'), ('DIVLOC', '|S255'), ('STORENAME', '|S255'), ('CLUSTER', '|S255'), ('SUBID', '|S255'), ('SUBNAME', '|S255'), ('SEQUENCE', '<f8'), ('S_AREA', '<f8'), ('FS_AREA', '<f8'), ('ST_AREA', '<f8'), ('FR_AREA', '<f8'), ('SO_AREA', '<f8'), ('SL_AREA', '<f8'), ('TOTALSALES', '<f8'), ('OWNEDSALES', '<f8'), ('LEASEDSALES', '<f8'), ('GM', '<f8'), ('PROD', '<f8'), ('GMPROD', '<f8'), ('LATITUDE', '<f8'), ('LONGITUDE', '<f8'), ('RANKSALESSUB', '<f8'), ('RANKPRODSUB', '<f8'), ('RANKSALESSUBD', '<f8'), ('RANKPRODSUBD', '<f8'), ('RANKSALESSUBR', '<f8'), ('RANKPRODSUBR', '<f8'), ('RANKSALESSUBS', '<f8'), ('RANKPRODSUBS', '<f8'), ('RANKSALESSUBC', '<f8'), ('RANKPRODSUBC', '<f8')]" dtype = np.dtype(eval(get_dtype))
Kind regards, Xander
Since the line is long and the width limited here is what I was referring to (check the how the statement ends):
get_dtype="[('YEAR', '<f8'), ... , ('RANKPRODSUBC', '<f8')]"
That was exactly what I was missing.
For anyone in the future trying to do this this was what helped me.
dtype = np.dtype(eval(get_dtype))
This will save me a lot of work in the future. Thanks very much.
Michael, please consider moving this discussion to the Python space using the "move" link you see at right when you're look at this this thread.