table.query Filter by variable

Question

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

AdminGIS2 · Accepted Answer

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"