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

sel=("select id, custName, nSql from TBLA")

for outrow in res1:
    print 'Customer ID : ', outrow[0], ': ', outrow[1]
    nSql = outrow[2]

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


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));
  • \$\begingroup\$ You seem to know what you want and how to do it, so... where's the question? \$\endgroup\$
    – freakish
    Apr 7, 2013 at 21:13
  • \$\begingroup\$ I want to use try except inside the code and would like to delete data if exists as said in 1 in 2. Btw, I am a new to Python World ! \$\endgroup\$
    – Rio
    Apr 7, 2013 at 21:15
  • \$\begingroup\$ I know what you want, but where's the problem? You know how to use try:except: right? You know how to make delete query, right? So where's the problem? \$\endgroup\$
    – freakish
    Apr 7, 2013 at 21:16
  • \$\begingroup\$ I am sorry, but I am stuck in using try except and delete. \$\endgroup\$
    – Rio
    Apr 7, 2013 at 21:18
  • 1
    \$\begingroup\$ just one tip: it's never a good idea to create sql statements like you do. mysql.connector supports python format style parameter expansion, so if you change your query to cur.execute("insert into TBLB (dateK, id, name, city) values (%s, %s, %s, %s)", (dateK, id, name, city)) you won't have to worry about sql injection. It's best to get used to this as early as possible. \$\endgroup\$
    – mata
    Apr 7, 2013 at 21:34

1 Answer 1


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

# some code
for inrow in res2:
    # some code
    except MySQLdb.ProgrammingError:

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

    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:


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


  • \$\begingroup\$ @ freakish thank you for the response. During the nSql execution, if one of the SQL execution fails, how can I continue with other SQL execution without getting interrupted. \$\endgroup\$
    – Rio
    Apr 7, 2013 at 22:18
  • 1
    \$\begingroup\$ I would suggest writing except MySQLdb.ProgrammingError: and not except Exception:. You should always catch the most specific error class that makes sense. See PEP 249 for the exceptions that can be generated by DB-API methods. \$\endgroup\$ Apr 8, 2013 at 12:00
  • \$\begingroup\$ ProgrammingError might not be the right one (or the only one) you need to catch here: it all depends on the OP's purpose in catching these errors. \$\endgroup\$ Apr 8, 2013 at 23:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.