DBApi Question with MySQL

[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