471,355 Members | 2,069 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,355 software developers and data experts.

DBApi Question with MySQL

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
Dec 12 '07 #1
1 1198
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
Dec 12 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by Penn Markham | last post: by
2 posts views Thread by Rene Pijlman | last post: by
6 posts views Thread by Bob Parnes | last post: by
reply views Thread by Greg Copeland | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.