datatypes

[deleted-user-Pi5x1UjkS2PY]

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

[XanderBakker]

What would be the string that holds the dtypes?

Maybe these links are helpful:

• Using Numpy to Write a Dictionary to a Table
• Data type objects (dtype) — NumPy v1.9 Manual

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

[deleted-user-Pi5x1UjkS2PY]

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

[XanderBakker · Accepted solution]

Hi Michael,

I haven't looked at the entire function, but I did notice 2 things:

• The statement get_dtype = .... has an additional ")" just before the end. Remove this and the string is ok
• However, you can't supply the string to the np.dtype function, but you can use an eval statement to solve this (this way the string is converted to the required list)

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

[XanderBakker]

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

[deleted-user-Pi5x1UjkS2PY]

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. 

[curtvprice]

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.