4

I am trying to write a python script to select records created within the past 7 hours (date field - database time) using a where-clause within arcpy. The table is ArcSDE database (Microsoft SQL Server).

I've written a script that calculates the time 7 hours prior to the current time. Then I want to use the "report_time" variable to select the relevant records in a table view but I'm getting an invalid expression error. I've tried to reformat the SQL statement in every way I could think of and still get the invalid expression error (I've kept them in my script and commented them out for reference).

-- Get start and end times for report

start_time= datetime.timedelta(hours = 7)
end_time = datetime.datetime.now()

report_time = end_time-start_time #this is the time that gets used to filter records

-- find all records that are later than or = to report_time

SQL = "created_date >= " + report_time.strftime('%m/%d/%Y')
print SQL

arcpy.SelectLayerByAttribute_management(ViewTable,"NEW_SELECTION", SQL)

-- SQL = "created_date >= " + str(report_time.strftime('%m/%d/%Y'))

-- SQL = '"created_date"<='+ report_time.strftime('%Y/%m/%d')

-- SQL = '"created_date"<='+ report_time.strftime('%m/%d/%Y')

-- SQL = "'created_date'<= "+ report_time.strftime('%m/%d/%Y')

-- SQL = '"created_date"<='+ report_time.strftime('%m/%d/%Y %I:%M:%S %p') - ExecuteError: ERROR 000358: Invalid expression

-- SQL = "'created_date'<= "+ report_time.strftime('%m/%d/%Y %I:%M:%S %p') - ExecuteError: ERROR 000358: Invalid expression

-- SQL = "'created_date'<= "+ str(report_time.strftime('%m/%d/%Y %I:%M:%S %p'))

-- SQL = '"created_date"<='+ str(report_time.strftime('%m/%d/%Y %I:%M:%S %p'))

--SQL = '"created_date"<= report_time.strftime'('%m/%d/%Y %I:%M:%S %p') - TypeError: 'str' object is not callable

--SQL = '"created_date" <= report_time' #this returns an expression error

--SQL = "'created_date' <= report_time" #also tried this - expression error

--SQL = 'created_date'<= report_time # returns error: TypeError: can't compare datetime.datetime to str
Improve this question
20
  • 1
    before trying to calculate the difference and pass that as your SQL, have you tried your created_date >= somestaticdate and checking that part works?
    – Midavalo ♦
    Feb 5, 2016 at 21:07
  • 2
    How is your data stored?
    – Midavalo ♦
    Feb 5, 2016 at 21:11
  • 2
    Then it looks like you just need single quotes around the time and not around the field name.
    – Tom
    Feb 5, 2016 at 22:57
  • 3
    It is important to understand that ArcGIS is NOT executing the query -- The database is. You must format the WHERE clause as it will be used by the database.
    – Vince
    Feb 5, 2016 at 23:34
  • 2
    @rralbritton I don't think that link means what you think it means.
    – Tom
    Feb 6, 2016 at 0:50

2 Answers 2

Reset to default
4

So I figured it out - @Yanes was close but instead of three sets of double quotes, it needed to be double-single-double. So the following script works:

-- Turn ReportsTable into a View Table

arcpy.MakeTableView_management(ReportsTable, ViewTable)

-- Get start and end times for report

start_time= datetime.timedelta(hours = 24)

end_time = datetime.datetime.now()

report_time = end_time-start_time #this is the time that gets used to filter records

-- find all records that are later than or = to report_time

SQL = "created_date >="+ "'"+report_time.strftime('%Y-%m-%d %H:%M:%S')+"'"

print SQL

-- select records within the specified time range using arcpy
arcpy.SelectLayerByAttribute_management(ViewTable,"NEW_SELECTION", SQL)
Improve this answer
8
  • 3
    SQL = "created_date >= '{}'".format(report_time.strftime('%Y-%m-%d %H:%M:%S'))
    – Midavalo ♦
    Feb 5, 2016 at 23:56
  • 2
    This is why you should never concatenate strings with +.
    – Tom
    Feb 6, 2016 at 0:00
  • great! however check with arcpy.GetCount_management('ViewTable') , to see if you've correctly selected records. or add print arcpy.GetMessages() after your select by attribute line. To check what it did. Not that I am saying it doesn't work, just wondering because I didn't think specifying field without quotations worked. Also please accept your answer so that people could see the Q has an accepted answer.
    – yanes
    Feb 6, 2016 at 0:20
  • 1
    Interesting, because your solution is exactly what I said earlier: "Then it looks like you just need single quotes around the time and not around the field name." Maybe it wasn't clear that I was referring to quotes within the SQL query itself.
    – Tom
    Feb 6, 2016 at 0:49
  • 1
    @yanes - good point on using the GetCount tool - I did use that to check the records being selected, I just didn't include that info here - but its useful advice for anyone in the same/similar situation
    – reactFullStackDeveloper
    Feb 8, 2016 at 15:12
2

Try wrapping your Where statement with three double quotations so that Arcpy understands it as an expression for your select function not a standalone SQL task - I may be wrong on the reasoning, I will check.

SQL = """ "created_date" >= '{}'""".format(report_time.strftime('%Y-%m-%d %H:%M:%S')) 
arcpy.SelectLayerByAttribute_management(ViewTable,"NEW_SELECTION", SQL)

Also why do you have two operands in the SQL?

Improve this answer
8
  • Yanes - thanks for the reply but its still doesn't work - By adding all of the quotes its reading the entire SQL as a string which is incorrect: SQL = """ "created_date >= " report_time.strftime('%m/%d/%Y')""" returns this as the SQL statement: "created_date >= " report_time.strftime('%m/%d/%Y'). Also - someone asked why I had a double operand - that was just a mistake type. Sorry I should have proofed that better
    – reactFullStackDeveloper
    Feb 5, 2016 at 22:40
  • 1
    Help me understand the SQL isn't it supposed to be a simple statement where: you are looking at a field named "Created_date" and selecting all the records that are later or on the date specified by your variable report_time.strftime('%m/%d/%Y'). In that case this statement should work. 2 things. 1. please confirm created_date is your field, 2. confirm that the variable report_time.strftime('%m/%d/%Y') doesn't give you error, when run on its own.
    – yanes
    Feb 5, 2016 at 22:56
  • Also Vince's comment under the original post is valid, check in a GIS software how exactly the time/date is formatted in your table your report_time.strftime('%m/%d/%Y') need to reproduce the exact format. You can also use searchcursor to print your records out in python and see the format.
    – yanes
    Feb 5, 2016 at 23:03
  • 1
    Thank you for your input. I figured it out. Please see my post below
    – reactFullStackDeveloper
    Feb 5, 2016 at 23:55
  • 1
    @Midavalo, thanks! for the correction, it was a mistake! I have edited it now. I didn't know about the second option, good to know. It looks less of a coloring book without the triple double quotes :). I am not sure if I remember correctly - there is an unimaginative alternative that goes as - /"created_date >= '{}'/".format(report_time.strftime('%Y-%m-%d %H:%M:%S'))
    – yanes
    Feb 6, 2016 at 2:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.