topic Re: How to pass variable in where sql expression in Python in ArcGIS Online Questions

How to pass variable in where sql expression in Python

jdaniel — Tue, 06 Sep 2022 18:51:48 GMT

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.

Re: How to pass variable in where sql expression in Python

MikeLachance1 — Tue, 06 Sep 2022 19:34:17 GMT

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.

Re: How to pass variable in where sql expression in Python

Clubdebambos — Wed, 07 Sep 2022 08:20:11 GMT

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.

Your query will look similar to

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

Where tStamp represents the date as YYYY-MM-DD

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

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

Re: How to pass variable in where sql expression in Python

jdaniel — Fri, 09 Sep 2022 17:29:03 GMT

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.

Re: How to pass variable in where sql expression in Python

MikeLachance1 — Fri, 09 Sep 2022 17:35:38 GMT

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.

Re: How to pass variable in where sql expression in Python

jdaniel — Sat, 10 Sep 2022 19:19:58 GMT

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.