AnsweredAssumed Answered

Passing a python variable to a (my) sql querie

Question asked by jborgion Champion on Mar 4, 2020
Latest reply on Mar 4, 2020 by jborgion

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.

Outcomes