Inserting data into database by Python

Question

I have to run a python script that enters data into MySQL database table.

My below query runs and inserts data into MySQL database. But I want to optimize it and I have 2 requests:

1. I want to use try except error handling inside the for loop. Probably before insertstmt or where it would be more efficient. If there is bug while executing nSql, program should not terminate, rather continue in other nSql tests. nSql has complex SQL queries with multiple joins, below shown is for simplicity. If any one of the nSql fails during quering, I want the error message to be displayed.

2. If the data already exists for yesterday, I want to be deleted it as well. As the loop iterates, I want data to be deleted if it exists for yesterday as loop iterates through the nSql.

I have:

#! /usr/bin/env python
import mysql.connector
con=mysql.connector.connect(user='root', password ='root', database='test')
cur=con.cursor()

sel=("select id, custName, nSql from TBLA")
cur.execute(sel)
res1=cur.fetchall()

for outrow in res1:
    print 'Customer ID : ', outrow[0], ': ', outrow[1]
    nSql = outrow[2]
    cur.execute(nSql)
    res2=cur.fetchall()

    for inrow in res2:
        dateK =inrow[0]
        id= inrow[1]
        name= inrow[2]
        city=inrow[3]
        insertstmt=("insert into TBLB (dateK, id, name, city) values ('%s', '%s', '%s', '%s')" % (dateK, id, name, city))
        cur.execute(insertstmt)

con.commit()
con.close()

Database schema is:

create table TBLA (id int, custName varchar(20), nSql text);

insert into TBLA values ( 101, 'cust1', "select date_sub(curdate(), interval 1 day) as dateK, id, 'name', 'city' from t1"), ( 102, 'cust2', "select date_sub(curdate(), interval 1 day) as dateK, id, 'name', 'city' from t2"), ( 103, 'cust3', "select date_sub(curdate(), interval 1 day) as dateK, id, 'name', 'city' from t3");

create table t1 (id int, name varchar(20), city varchar(20));
create table t2 (id int, name varchar(20), city varchar(20));
create table t3 (id int, name varchar(20), city varchar(20));

insert into t1 values( 101, 'bob', 'dallas'), ( 102, 'boby', 'dallas');

insert into t2 values( 101, 'bob', 'dallas'), ( 102, 'boby', 'dallas');

insert into t3 values( 101, 'bob', 'dallas'), ( 102, 'boby', 'dallas');

create table TBLB (dateK date, id int, name varchar(20), city varchar (20));

Answer 1

OK, here's the simpliest version of try:except: block for your case:

# some code
for inrow in res2:
    # some code
    try:
        cur.execute(insertstmt)
    except MySQLdb.ProgrammingError:
        pass

That's pretty much it. You probably want to know which queries failed, so you should use for example that version:

    try:
        cur.execute(insertstmt)
    except MySQLdb.ProgrammingError:
        print "The following query failed:"
        print insertstmt

Now as for the other question. You have to use a delete query. For example:

DELETE FROM TBLB WHERE dateK < CURDATE();

or something like that (note that CURDATE is a built-in MySQL function). Read more about delete queries here:

http://dev.mysql.com/doc/refman/5.0/en/delete.html