DBApi Question with MySQL

[Jeff McNeil]

Which storage engine are you using?  My assumption is that you're using
standard MyISAM tables, which will not support what you're trying to do.  If
you run the code below against MySQL tables created using InnoDB, it should
work as expected.
See http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-transactions.html.

-Jeff


On 12/12/07, Hans Müller <HeinTest at web.de> 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
> --
> http://mail.python.org/mailman/listinfo/python-list
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-list/attachments/20071212/9bc76a7a/attachment.html>