Creating an SQL expression and writing/reading it to/from a file

Question

I'm working on a way to take the number for projects that are marked as active in a spreadsheet and put that into a SQL expression that I can use to select from. So far I've got this

dirname = os.path.dirname(__file__)
gdbname = 'G:\\GIS\\General Maps\\AltumProjects\\AltumProjects.gdb'
env.workspace = gdbname
env.overwriteOutput = True
Riv = gdbname + '\\RivParcels'
df = pd.read_csv(dirname + '/ActiveProjects.csv')
newdf = df.loc[df['Status'] == 'Active', 'Project'].reset_index()
print(newdf)
Proj = np.array(newdf['Project'])
with open("Output.txt", "w") as text_file:
 print(f"\"Project IN ({Proj})\"", file=text_file)
with fileinput.FileInput("Output.txt", inplace=True, backup='.bak') as file:
 for line in file:
 print(line.replace("' ", "', "), end='')
with fileinput.FileInput("Output.txt", inplace=True, backup='.bak') as file:
 for line in file:
 print(line.replace("[", ""), end='')
with fileinput.FileInput("Output.txt", inplace=True, backup='.bak') as file:
 for line in file:
 print(line.replace("]", ""), end='')
expr = open('./Output.txt').readline()

Which creates a text file that is:

"Project IN ('C1335', 'C1410', 'C1237')"

This all works fine but when I add it to SelectLayerByAttribute:

Projects = arcpy.MakeFeatureLayer_management('Projects', 'Projects.lyr')
Env = arcpy.SelectLayerByAttribute_management(Projects, "NEW_SELECTION", expr)
arcpy.CopyFeatures_management(Env, gdbname + '\\ActiveEnvProjects')
EnvProjects = gdbname + '\\ActiveEnvProjects'
SelectEnv = arcpy.SelectLayerByLocation_management(Riv, 'COMPLETELY_CONTAINS', EnvProjects, 0, 'NEW_SELECTION')
arcpy.CopyFeatures_management(SelectEnv, gdbname + '\\EnvProjectParcels')
print("Environmental Projects Done")

I get:

ERROR 000358: Invalid expression

If I add this in manually as a string it works fine but I can't get it working like this. Is there something with how I'm writing the expression wrong?

• PolyGeo: Welcome to GIS SE! We're a little different from other sites; this isn't a discussion forum but a Q&A site. Please check out our short tour to learn about our focussed Q&A format. Have you reviewed previous Q&As with this error? gis.stackexchange.com/questions/tagged/error-000358?tab=Votes

Answer 1

You need to remove the double quotes in the file. Either don't create them in the text file in the first place (better) or otherwise delete them from the variable when reading it back in using replace():

expr = open('./Output.txt').readline().replace('"', '')

Ie, your expr in/from the file should be:

Project IN ('C1335', 'C1410', 'C1237')

NOT:

"Project IN ('C1335', 'C1410', 'C1237')"

You only need to use the double quotes if assigning it to the variable as a string literal within code.