DBApi Question with MySQL

[=?ISO-8859-1?Q?Hans_M=FCller?=]

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

[Paul McNett]

Hans Müller wrote:

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

Quick questions before going any further:

1) What's the table type in MySQL: ISAM, INNO, or ? As you probably know
MyISAM doesn't support transactions.

2) Is MySQL set to AutoCommit? Issue:
cursor.execute("select @@autocomit")
print cursor.fetchall()

If so try sending:
cursor.execute("set autocommit=0")

....and then doing the code you posted.


Paul

--
http://paulmcnett.com