DBApi Question with MySQL

[Hans Müller]

Hi,

I'd like to modify some tables in a database in one transaction.
This approach doesn't work:

import MySQLdb

con = MySQLdb.connect("servernam", user = "username", passwd = "verysecret, db = "test", use_unicode = True, charset = "utf8")

cursor = con.cursor()

con.begin()

cursor.execute("delete from table")

do-some-stuff and wait

cursor.execute("insert into table value(%s, %s)", (1, 2))

con.commit()


When I look into the databse while the script is running, all rows from table are gone.
The expected behavior would be to see the new lines only when the script is finished.
The deletion should be (since inside a transaction) invisible up to the commit().

Has someone an idea how to use transactions correctly ?

What I need is this

start transaction

delete a lot of date in some tables (about 2 million rows)

insert a lot of new date in these tables (also about 2 million lines)

commit all changes, so all changes become visible here and only here.


Thanks a lot,

Greetings
Hans