Passing a python variable to a (my) sql querie

[JoeBorgione]

I have a series of functions that scrape data out of a number of tables in a MySQL db.  Everything to date has been working, but I need to add an enhancement.  Each of the tables has a date field that indicates the date of a particular permit that has been issued. For example, to get all the building permits for the year to date I can use:

            query = """SELECT idProject, idController, siteAddress, 
                        siteApn,projectName, projectType,dateCreated,
                        datePermitIssued, permitNumber,applicationApprovedBy
                        FROM master_db.saltlakecountybuildingpermits
                        where datePermitIssued > '2019-12-31 23:59:59'
                        and siteAddress is not null;"""  ‍‍‍‍‍‍

However, line 5 needs to be passed the date the script last ran.  I can capture that time stamp easily but I haven't been able to  figure out how to pass its value as a variable to the query. For example:

ts = '2020-03-04 12:21:00'

           query = """SELECT idProject, idController, siteAddress, 
                        siteApn,projectName, projectType,dateCreated,
                        datePermitIssued, permitNumber,applicationApprovedBy
                        FROM master_db.saltlakecountybuildingpermits
                        where datePermitIssued > f'{ts}'
                        and siteAddress is not null;"""  ‍‍‍‍‍‍‍‍

ts is the last time the script ran and what I am hoping for, but no beuno (line 7 tosses an error). Once I put this into production, I only want to grab any records new since the last time the script was run, typically over night as a scheduled task.

[RandyBurton · Accepted solution]

Does something like this work for you:

>>> ts = '2020-03-04 12:21:00'
>>> query = """SELECT idProject, idController, siteAddress,
siteApn,projectName, projectType,dateCreated,
datePermitIssued, permitNumber,applicationApprovedBy
FROM master_db.saltlakecountybuildingpermits
where datePermitIssued > '{}'
and siteAddress is not null;""".format(ts)
>>> print query

SELECT idProject, idController, siteAddress,
siteApn,projectName, projectType,dateCreated,
datePermitIssued, permitNumber,applicationApprovedBy
FROM master_db.saltlakecountybuildingpermits
where datePermitIssued > '2020-03-04 12:21:00'
and siteAddress is not null;
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[JoeBorgione]

Randy- you're a genius!  Thanks!

[RandyBurton]

I think this also works for python3, put the "f" before the triple quotes ( not inside 😞

ts = '2020-03-04 12:21:00'

query = f"""SELECT idProject, idController, siteAddress,
siteApn,projectName, projectType,dateCreated,
datePermitIssued, permitNumber,applicationApprovedBy
FROM master_db.saltlakecountybuildingpermits
where datePermitIssued > '{ts}'
and siteAddress is not null;"""

print(query)‍‍‍‍‍‍‍‍‍‍

[JoeBorgione]

Sure does!  Thanks again!