larryjll04

TableToTable_conversion: Problem with SQL Query

Discussion created by larryjll04 on Oct 10, 2011
Latest reply on Dec 19, 2011 by larryjll04
I've just recently started using Python to try to automate task and be more efficient.

I have a table that I would like to create multiple tables from based on the value of a field.  The problem is when I set my my SQL query in the arcpy.TableToTable_conversion funtion I'm pretty sure the query doesn't recognize the variable I'm trying to reference...but I'm struggling with finding a different way to accomplish my goal.  Is there a way to use variables in an SQL query using this tool?

My code:

import arcpy

#set variables
in_table = r"\\Server\Folder1\Folder2\MeterRouteFix\CogsdaleTest.dbf"
out_path = r"\\Server\Folder1\Folder2\MeterRouteFix"
ThisRoute = 1

MeterTable = "Meters.dbf"
MTableFull = out_path + "\\" + MeterTable
RField = "Route"
RtField = "!RTEID!"

arcpy.Delete_management(MTableFull)

arcpy.TableToTable_conversion (in_table, out_path, "Meters.dbf")

#Needed to make Route field a number so the While loop below could be used
##arcpy.AddField_management(MTableFull, RField, "TEXT","","",3)

arcpy.AddField_management(MTableFull, RField, "SHORT","","")

#This Function wasn't working for some Reason
##arcpy.CalculateField_management(MTableFull,RField,RtField[-3:], "PYTHON")

#UpdateCursor used instead of Calculate Field, probably will have to use this most often
#Help for arcpy.UpdateCursor: http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//000v0000003m000000

rows = arcpy.UpdateCursor(MTableFull)

for row in rows:
    if row.RTEID[-3:-1]=="00":
        row.Route = row.RTEID[-1:]
        rows.updateRow(row)
    elif row.RTEID[-3:-2]=="0" and row.RTEID[-3:-1]<>"00":
        row.Route = row.RTEID[-2:]
        rows.updateRow(row)
    else:
        row.Route = row.RTEID[-3:]
        rows.updateRow(row)

#Delete cursor and row objects in order to remove locks on the data
del row
del rows



#export to multiple tables
while ThisRoute < 130:
  arcpy.TableToTable_conversion (MTableFull, out_path, str(ThisRoute)+".dbf", """ "Route" = ThisRoute """)
  ThisRoute=+1

It will create first table, but fails at the SQL query:

ExecuteError: ERROR 999999: Error executing function.
A column was specified that does not exist.
A column was specified that does not exist.
Failed to execute (TableToTable).

Outcomes