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

• freakish: You seem to know what you want and how to do it, so... where's the question?
• Rio: 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 !
• freakish: 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?
• Rio: I am sorry, but I am stuck in using try except and delete.
• mata: 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.

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

• Rio: @ 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.
• Gareth Rees: 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.
• Gareth Rees: 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.