Passing a python variable to a (my) sql querie

163
4
Jump to solution
03-04-2020 11:41 AM
JoeBorgione
MVP Esteemed Contributor

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.

That should just about do it....
0 Kudos
1 Solution

Accepted Solutions
RandyBurton
MVP Regular Contributor

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;

View solution in original post

4 Replies
RandyBurton
MVP Regular Contributor

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
MVP Esteemed Contributor

Randy- you're a genius!  Thanks!

That should just about do it....
0 Kudos
RandyBurton
MVP Regular Contributor

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
MVP Esteemed Contributor

Sure does!  Thanks again!

That should just about do it....
0 Kudos