[Python-au] Python and MySQlDb

Richard Jones rjones@ekit-inc.com
Wed, 29 Jan 2003 09:51:12 +1100


On Wed, 29 Jan 2003 8:30 am, Graeme Matthew wrote:
> I ll, hope the week is going well
>
> Im trying to execute a batch of statements via MysqlDb.
>
> Im using Mysql, InnoDb tables enabled and AUTOCOMMIT is off
>
> I need to execute INSERTS for an ORDER DETAILS statment followed by a
> number of ORDERLINE statements, if all of these (i.e. all statements
> related to the ORDER) succeed I then want to commit it
>
> for example (test only !):
>
> try:
> cursor.execute("INSERT INTO ORDER(ORDERNO) VALUES(12345);
>                 INSERT INTO ORDERLINE(ORDERNO,QTY) VALUES(12345,20);
>                 INSERT INTO ORDERLINE(ORDERNO,QTY) VALUES(12345,50);")
> except:
> conn.rollBack()
>
> any help or previous experience in doing this would be much appreciated
> sorry bout the formatting !! :0

If the database doesn't automatically commit when you close a connection, then 
just have the code

cursor.execute("INSERT INTO ORDER(ORDERNO) VALUES(12345);
                INSERT INTO ORDERLINE(ORDERNO,QTY) VALUES(12345,20);
                INSERT INTO ORDERLINE(ORDERNO,QTY) VALUES(12345,50);")
conn.commit()

Then if any exceptions are raised, the commit won't be reached and the 
transaction will be turfed.


      Richard