table.query Filter by variable

markkellermannBWB — Thu, 06 Jan 2022 14:23:31 GMT

Hi,

I have a build a date as string from yesterday:

now_dt = dt.utcnow()
yday_dt = now_dt - datetime.timedelta(days=1)
yesterday = print(yday_dt.strftime("%m/%d/%Y"))

And I want to query a table text-column "akt_datum" with a Filter: where akt_datum=yesterday:

I tried this, but it doesnt work:

q_yday=table.query(where="akt_datum = yesterday", out_fields = ["titel, typ, views, akt_datum, aenderung"]).sdf

So my question is, how works the table.query Filter with a calculated variable?

Thanks and Greets

Re: table.query Filter by variable

AdminGIS2 — Fri, 07 Jan 2022 14:17:30 GMT

You can use f-strings to include a dynamic variable into a string. However, it's probably easier to create a direct SQL query searching for yesterday. I've added both examples:

# your code now_dt = dt.utcnow() yday_dt = now_dt - datetime.timedelta(days=1) yesterday = yday_dt.strftime("%m/%d/%Y") # create query with f-string q_string = f"akt_datum='{yesterday}'" # SQL q_string = "akt_datum=CURRENT_DATE()-1" q_yday=table.query(where=q_string, out_fields = ["titel, typ, views, akt_datum, aenderung"]).sdf

A final note, if akt_datum is a datetime field you may need to cast the field to DATE first for your query to work

# SQL q_string = "CAST(akt_datum AS DATE)=CURRENT_DATE()-1"

topic Re: table.query Filter by variable in ArcGIS API for Python Questions

table.query Filter by variable

Re: table.query Filter by variable