Replacing old data with new data using python

[inkprs at gmail.com]

I have 2 tables TBL1 and TBL2.

TBL1 has 2 columns id, nSql.
TBL2 has 3 columns date, custId, userId.
I have 17 rows in TBL1 with id 1 to 17. Each nSql has a SQL query in it.

For example nSql for

id == 1 is: "select date, pId as custId, tId as userId from TBL3"
id == 2 is: "select date, qId as custId, rId as userId from TBL4" ...

nSql result is always same 3 columns.

Below query runs and puts data into the table TBL2. If there is already data in TBL2 for that day, I want the query to replace the data with new data. If there is not data in TBL2, I want to put data in normal way.

Any time I run the query, it will push the data for yesterday into TBL2.

For example, if I run the query in the morning and if I want to run it again in evening, I want new data to replace old data for yesterday, since data will be inserted into TBL2 everyday.

It is also precaution that if the data already exists (if run by coworker), I do not want duplicate data for that day.

I think we can use it in 'if else' statement. something pseudocode like: if there is data in TBL2 for  date_sub(curdate(), interval 1 day), remove the database data and insert new data. else insert new data into database. 



How can I do it?

Thank you.

(I am new to python, I would appreciate if someone could explain in steps and show in the code)

import MySQLdb

# Open connection
con = MySQLdb.Connection(host="localhost", user="root", passwd="root", db="test")

# create a cursor object 
cur = con.cursor()

selectStatement = ("select nSql from TBL1") 
cur.execute(selectStatement)
res = cur.fetchall()
for outerrow in res:
    nSql = outerrow[0]
    cur.execute(nSql)
    reslt = cur.fetchall()

    for row in reslt:
        date = row[0]
        custId = row[1]
        userId = row[2]
        insertStatement = ("insert into TBL2( date, custId, userId) values ('%s', %d, %d)" % (date, custId, userId))
        cur.execute(insertStatement)
        con.commit()