MySQLdb: commit before cursor close, or after?

Discussion in 'Python' started by John Nagle, Feb 4, 2008.

  1. John Nagle

    John Nagle Guest

    I'm getting some wierd commit-related behavior from MySQLdb. I'm
    using InnoDB, so transactions really matter.

    I'm currently doing

    cursor = db.cursor()
    cursor.execute(...)
    cursor.close()
    db.commit()

    Is that the correct order, or should I call "db.commit()" before
    "cursor.close()"? Does anyone know for sure? The MySQLdb documentation
    ("http://mysql-python.sourceforge.net/MySQLdb.html") doesn't
    say. There are some discussions of this in blogs, but nobody
    really seems to know.

    John Nagle
    John Nagle, Feb 4, 2008
    #1
    1. Advertising

  2. John Nagle

    Frank Aune Guest

    On Monday 04 February 2008 19:14:13 John Nagle wrote:
    > I'm getting some wierd commit-related behavior from MySQLdb. I'm
    > using InnoDB, so transactions really matter.
    >
    > I'm currently doing
    >
    > cursor = db.cursor()
    > cursor.execute(...)
    > cursor.close()
    > db.commit()
    >
    > Is that the correct order, or should I call "db.commit()" before
    > "cursor.close()"? Does anyone know for sure? The MySQLdb documentation
    > ("http://mysql-python.sourceforge.net/MySQLdb.html") doesn't
    > say. There are some discussions of this in blogs, but nobody
    > really seems to know.
    >
    > John Nagle



    No, you obviously need to commit your changes before closing the cursor. I'm
    surprised if your code above even works if adding content to the db.

    Regards,
    Frank
    Frank Aune, Feb 4, 2008
    #2
    1. Advertising

  3. On Mon, 2008-02-04 at 19:53 +0100, Frank Aune wrote:
    > No, you obviously need to commit your changes before closing the cursor. I'm
    > surprised if your code above even works if adding content to the db.


    Why is that obvious? Is this some MySQL-specific oddity? In other
    databases, it's the cursor's execute() method that adds the content to
    the db (pending a commit of the transaction), and closing the cursor
    simply means that you are explicitly releasing the resources that the
    cursor used. Whether the cursor is closed before or after the
    transaction is committed, or even whether the cursor is explicitly
    closed at all or not, should make no difference whatsoever.

    --
    Carsten Haese
    http://informixdb.sourceforge.net
    Carsten Haese, Feb 4, 2008
    #3
  4. John Nagle

    Steve Holden Guest

    Carsten Haese wrote:
    > On Mon, 2008-02-04 at 19:53 +0100, Frank Aune wrote:
    >> No, you obviously need to commit your changes before closing the cursor. I'm
    >> surprised if your code above even works if adding content to the db.

    >
    > Why is that obvious? Is this some MySQL-specific oddity? In other
    > databases, it's the cursor's execute() method that adds the content to
    > the db (pending a commit of the transaction), and closing the cursor
    > simply means that you are explicitly releasing the resources that the
    > cursor used. Whether the cursor is closed before or after the
    > transaction is committed, or even whether the cursor is explicitly
    > closed at all or not, should make no difference whatsoever.
    >

    Certainly isn't "obvious" to me. The whole point of the way
    connection/cursor relationship is structured is to allow the possibility
    of several cursors on the same connection. So cursors can be created,
    used and closed at will without any effect on the underlying connection.

    regards
    Steve
    --
    Steve Holden +1 571 484 6266 +1 800 494 3119
    Holden Web LLC http://www.holdenweb.com/
    Steve Holden, Feb 4, 2008
    #4
  5. John Nagle

    John Nagle Guest

    Steve Holden wrote:
    > Carsten Haese wrote:
    >> On Mon, 2008-02-04 at 19:53 +0100, Frank Aune wrote:
    >>> No, you obviously need to commit your changes before closing the
    >>> cursor. I'm surprised if your code above even works if adding content
    >>> to the db.

    >>
    >> Why is that obvious? Is this some MySQL-specific oddity? In other
    >> databases, it's the cursor's execute() method that adds the content to
    >> the db (pending a commit of the transaction), and closing the cursor
    >> simply means that you are explicitly releasing the resources that the
    >> cursor used. Whether the cursor is closed before or after the
    >> transaction is committed, or even whether the cursor is explicitly
    >> closed at all or not, should make no difference whatsoever.
    >>

    > Certainly isn't "obvious" to me. The whole point of the way
    > connection/cursor relationship is structured is to allow the possibility
    > of several cursors on the same connection. So cursors can be created,
    > used and closed at will without any effect on the underlying connection.


    It really isn't clear. MySQL has no concept of "cursors". See
    the C api: "http://dev.mysql.com/doc/refman/5.0/en/c-api-function-overview.html"
    Cursors are an illusion created by the MySQLdb library. What MySQL does
    have is a "current result set". After doing something that generates a
    result set, the C API call mysql_result_set() is called, then mysql_fetch_row()
    is called for each row, followed by mysql_free_result().

    You can't really have multiple cursors on the same connection at the same
    time. MySQL doesn't allow issuing new queries until mysql_result_set() is done.
    See "http://dev.mysql.com/doc/refman/5.0/en/mysql-use-result.html". If
    you try, a "commands out of sync" error should be generated. The
    MySQL C interface only allows one thing at a time per connection, and
    MySQLdb is written on top of that.

    Given that, it makes sense to close the cursor before committing. That
    frees up the connection for the next command.
    But I'm not sure, and I have a bug that seems to be related to
    a transaction not committing properly.

    John Nagle
    John Nagle, Feb 4, 2008
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    1
    Views:
    483
    bruce barker
    Apr 3, 2007
  2. Mike Barnard
    Replies:
    27
    Views:
    1,184
    John Hosking
    Feb 18, 2008
  3. carmelo
    Replies:
    7
    Views:
    968
    Arved Sandstrom
    May 18, 2010
  4. dmaziuk
    Replies:
    3
    Views:
    540
    Chris Gonnerman
    Jan 25, 2011
  5. Iñaki Baz Castillo
    Replies:
    7
    Views:
    807
    Iñaki Baz Castillo
    Jan 12, 2010
Loading...

Share This Page