topic Re: Passing a python variable to a (my) sql querie in Python Questions

Passing a python variable to a (my) sql querie

JoeBorgione — Sat, 11 Dec 2021 12:42:24 GMT

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.

Re: Passing a python variable to a (my) sql querie

RandyBurton — Sat, 11 Dec 2021 12:42:26 GMT

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;
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Passing a python variable to a (my) sql querie

JoeBorgione — Wed, 04 Mar 2020 20:16:35 GMT

Randy- you're a genius! Thanks!

Re: Passing a python variable to a (my) sql querie

RandyBurton — Sat, 11 Dec 2021 12:42:29 GMT

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)‍‍‍‍‍‍‍‍‍‍

Re: Passing a python variable to a (my) sql querie

JoeBorgione — Wed, 04 Mar 2020 20:41:04 GMT

Sure does! Thanks again!