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
--
http://mail.python.org/mailman/listinfo/python-list
