Replacing old data with new data using python

[Chris Angelico]

On Fri, Mar 29, 2013 at 2:23 AM,  <inkprs at gmail.com> wrote:
> 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