Select to view content in your preferred language

How to pass variable in where sql expression in Python

2725
5
Jump to solution
09-06-2022 11:51 AM
jdaniel
New Contributor II

I need to select records from a QuickCapture app that were created after a certain timestamp which is written to a feature service table each time the script runs - this is done in order to not append duplicates to the target feature class. I am attempting to pass my "tStamp" variable into 'where_clause' where I'm comparing it to CreationDate in SelectLayerByAttribute. Here's what I have at the moment:

QC_selection = arcpy.management.SelectLayerByAttribute(in_layer_or_view=QC_Feature_Layer, selection_type="NEW_SELECTION", where_clause = """'CreationDate' > timestamp '{0}'""".format(tStamp), invert_where_clause="NON_INVERT")

 I've tried combinations of single and double quotes, curly braces, and brackets. I've tried storing the sql expression in a variable and passing that. I've tried formatted string literals syntax of f'{tStamp}' - all of which have thrown the error: 'invalid expression'

When I put CreateDate into a python print statement it returns this format: [1662486472000]. For testing I have a string and date type in my timestamp table. I've tried using both in the where clause with no luck.

I've also tried using arcgis.features.query in the Python API with no luck either. The error here is: 'where' parameter is invalid.

I feel like I've tried everything - anyone have an idea?  Thanks.

Tags (3)
0 Kudos
1 Solution

Accepted Solutions
jdaniel
New Contributor II
where_clause = f"CreationDate > timestamp '{time_stamp}'"

This is what ended up working after I came back to it. My datetime format was a little off, as well. Thanks.

View solution in original post

0 Kudos
5 Replies
MikeLachance1
New Contributor III

Hello,

When you say that it is "this format", do you mean that it has the square brackets around it? If yes, we should get rid of those. Those brackets indicate a data type of a list. So your variable is a list with one item, the number you are after. We can keep that number and ditch the list holding it by referencing the number's position in the list: it is the first and only position in the list. We call this position an index. Indexes in Python always start at 0, not 1.

Can you post the part of your code where the variable tStart is defined? We will want to modify that part to make sure tStart holds the number itself, not a list with a number in it.

jdaniel
New Contributor II

Thanks, that was an excellent observation and it was part of the issue. However, I still could not get the where clause to operate correctly while comparing epoch time. I was able to solve my problem with comparing the OBJECTIDs instead of timestamps.

0 Kudos
MikeLachance1
New Contributor III

Yes, I believe you would have to convert your epoch time to the 'YYYY-MM-DD HH:MM:SS' format that SQL uses for a Select query.

0 Kudos
Clubdebambos
Occasional Contributor III

Hi @jdaniel 

If you open up your APRX and use the SelectByAttributes tool and add a query on the date field, check the SQL button (highlighted in yellow) and you will see the format that the SQL query needs to be in for a date field. Similar to below.

Clubdebambos_0-1662538587728.png

 

Your query will look similar to 

"CreationDate > timestamp '{0} 00:00:00'".format(tStamp)

Where tStamp represents the date as YYYY-MM-DD

or 

"CreationDate > timestamp '{0}'".format(tStamp)

Where tStamp represents the date and time as YYYY-MM-DD HH:MM:SS

~ learn.finaldraftmapping.com
0 Kudos
jdaniel
New Contributor II
where_clause = f"CreationDate > timestamp '{time_stamp}'"

This is what ended up working after I came back to it. My datetime format was a little off, as well. Thanks.

0 Kudos