我正在尝试在我的python脚本中使用sql表达式中的范围列表,但是每当我尝试运行它时,它都会给我以下错误。
ExecuteError: ERROR 999999: Error executing function.
An invalid SQL statement was used.
An invalid SQL statement was used. [PatientVisitsGeocoded]
An invalid SQL statement was used. [SELECT ObjectID FROM PatientVisitsGeocoded WHERE USER_CenterID = 1001 AND USER_MonthsSinceFHOpening = (1, 3)]
Failed to execute (FeatureClassToFeatureClass)
但是,当我只使用一个范围而不是范围列表时,脚本运行良好。
我很难找到包含范围的列表的示例,我从此页面上的最佳答案中开发了列表的语法:
带有范围的python列表
我不知道我的范围列表的语法是否只是错误的,或者我是否需要更改表达式构造方式。任何建议/帮助将不胜感激。
这是我的完整代码:
# Set environment settings
arcpy.env.workspace = r"C:arcGIS_SharedPythonCenterHeatMaps6.gdb"
#Declare variables
fc = 'Open_GoHealth_Centers'
fields = ['USER_market_id','USER_GoHealth_ID','USER_GoHealth_Center_Name','USER_Opening_Date', 'USER_MonthsSinceFHOpening']
fieldname = 'USER_market_id'
# Set Markets to loop through
markets = [1000]
# Set Years to loop through
years = [2014, 2015]
# Set Ranges to loop through
myranges = [(1,3),(1,4),(1,5)]
sqlclause = (None, 'Order By USER_market_id, USER_GoHealth_ID')
for market in markets:
print (market)
#Define WHERE clause statement
whereclause = """{} = """.format(arcpy.AddFieldDelimiters(fc, fieldname)) + str(market)
for year in years:
print (year)
for myrange in myranges:
print (myrange)
with arcpy.da.SearchCursor(in_table = fc, field_names = fields, where_clause=whereclause, sql_clause=(None, 'ORDER BY USER_market_id, USER_GoHealth_ID')) as cursor:
#Loop through each row established in cursor
for row in (cursor):
# Set local variables for the FeatureClasstoFeatureClass tool
inFeatures = "PatientVisitsGeocoded"
outLocation = r"C:arcGIS_SharedPythonCenterHeatMaps6.gdb"
outFeatureClass = "PatientVisits{0}_{1}_{3}".format(row[0], row[2], myrange, year)
delimitedfield = arcpy.AddFieldDelimiters(arcpy.env.workspace,"USER_CenterID")
expression = """{0} = {1} AND USER_MonthsSinceFHOpening = {2}""".format(delimitedfield, row[1], myrange)
print (expression)
# Execute FeatureClassToFeatureClass tool
arcpy.FeatureClassToFeatureClass_conversion(inFeatures, outLocation, outFeatureClass, expression)
# #Print Results
print(row[2])
print(year)
print(myrange)
应该是 In (1, 3)
而不是 = (1, 3)
。
如果要在值1和3之间的范围,则使用Between 1 and 3
。In
的意思是"匹配这些值之一",它不包含。