Replacing old data with new data using python

[inkprs]

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

 

[Chris Angelico]

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?

Can you simply do a searched DELETE?

DELETE from TBL2 WHERE date>date_sub(curdate(), interval 1 day)

That will happily do nothing if there are no such records. Be careful
of what it'll do, of course. Make sure you won't accidentally delete
too much!

(BTW, isn't "date" a reserved word? Maybe it isn't in MySQL.)

insertStatement = ("insert into TBL2( date, custId, userId) values ('%s', %d, %d)" % (date, custId, userId))
cur.execute(insertStatement)
con.commit()

I recommend you get used to parameterized queries. Assuming your date
field, coming from the other table, is clean, this will be safe; but
if there's any chance that date might have an apostrophe in it, this
code is very dangerous.

But there's a really neat trick you can do. If you have a guarantee
that all the SQL statements follow the structure you've given, just
prepend a simple string to them, thus:

"select date, pId as custId, tId as userId from TBL3"
-->
"insert into TBL2 (date, custId, userId) select date, pId as custId,
tId as userId from TBL3"

That'll do the whole transfer in a single statement! Something like this:

cur.execute("select nSql from TBL1")
for outerrow in cur.fetchall():
cur.execute("insert into TBL2 (date, custId, userId) "+outerrow[0])
con.commit()

Note: I've backtabbed the commit() call so that the whole job happens
in a single transaction. You may wish to reindent it, to preserve the
semantics of your previous version; but I recommend doing the whole
job as one transaction, rather than committing each row separately. If
this job is interrupted, you'll have to start over anyway, so you may
as well have the database be clean.

ChrisA