MySQLdb and Cursor

Discussion in 'Python' started by Michel Combe, Oct 9, 2003.

  1. Michel Combe

    Michel Combe Guest

    Hi all,

    I'm writing a program that will read an ASCII file periodically and update
    several tables in a MySQL database.
    My question is "Can I use the same cursor for several SQL requests (SELECT
    and INSERT) or do I have to close the cursor between 2 requests ?".

    Regards
    Michel Combe
    Michel Combe, Oct 9, 2003
    #1
    1. Advertising

  2. Michel Combe wrote:
    > Hi all,
    >
    > I'm writing a program that will read an ASCII file periodically and update
    > several tables in a MySQL database.
    > My question is "Can I use the same cursor for several SQL requests (SELECT
    > and INSERT) or do I have to close the cursor between 2 requests ?".


    You can use the same cursor object.

    -- Gerhard
    =?ISO-8859-1?Q?Gerhard_H=E4ring?=, Oct 9, 2003
    #2
    1. Advertising

  3. Michel Combe

    Glauco Guest

    Michel Combe wrote:

    > Hi all,
    >
    > I'm writing a program that will read an ASCII file periodically and update
    > several tables in a MySQL database.
    > My question is "Can I use the same cursor for several SQL requests (SELECT
    > and INSERT) or do I have to close the cursor between 2 requests ?".
    >
    > Regards
    > Michel Combe
    >
    >


    best way is to create a connection ar the start of one session work. use
    this connection to do an entire transaction. At the end of work you must
    commit or rollback. But this don't mean that you can misc select and
    insert on tha same cursor.

    con = MySql.connection....
    cur1 = con.cursor()
    cur1.execute("select.....
    for rec in cur1:
    do something
    cur2 = con.cursor()
    cur2.execute("insert ....

    conn.commit or rollback
    conn.close()

    In this stupid example you CANNOT use only cur1 !!
    Is not necessary to close cursors , last line do this for you


    Bye
    Glauco
    Glauco, Oct 9, 2003
    #3
  4. Michel Combe

    Andy Todd Guest

    Glauco wrote:

    > Michel Combe wrote:
    >
    >> Hi all,
    >>
    >> I'm writing a program that will read an ASCII file periodically and
    >> update
    >> several tables in a MySQL database.
    >> My question is "Can I use the same cursor for several SQL requests
    >> (SELECT
    >> and INSERT) or do I have to close the cursor between 2 requests ?".
    >>
    >> Regards
    >> Michel Combe
    >>
    >>

    >
    > best way is to create a connection ar the start of one session work. use
    > this connection to do an entire transaction. At the end of work you must
    > commit or rollback. But this don't mean that you can misc select and
    > insert on tha same cursor.
    >
    > con = MySql.connection....
    > cur1 = con.cursor()
    > cur1.execute("select.....
    > for rec in cur1:
    > do something
    > cur2 = con.cursor()
    > cur2.execute("insert ....
    >
    > conn.commit or rollback
    > conn.close()
    >
    > In this stupid example you CANNOT use only cur1 !!
    > Is not necessary to close cursors , last line do this for you
    >
    >
    > Bye
    > Glauco
    >
    >


    You don't need to close the connection. By issuing a commit or a
    rollback you are finishing the transaction (if you have transaction
    aware tables, e.g. InnoDB). The point that Glauco is making is that you
    can't use one cursor for two operations simultaneously.

    Following his example, if you try and do;

    >>> cur1.execute("SELECT a, b, c FROM table1")
    >>> for row in cur1.fetchone():
    >>> # do something
    >>> cur1.execute("INSERT INTO table2 VALUES (?, ?, ?)" % (row.a,

    row.b, row.c))

    Then you will get an exception because the second execute will
    obliterate the result set of the first - which you are trying to loop
    through. Its perfectly possibly though, to do;

    >>> cur1.execute("SELECT a, b, c FROM table1")
    >>> results = cur1.fetchall()
    >>> for row in results:
    >>> # do something
    >>> cur1.execute("INSERT INTO table2 VALUES (?, ?, ?)" % (row.a,

    row.b, row.c))

    Which is fine as long as the first select doesn't return too many rows ;-)

    As a general rule of thumb establish a connection when your program
    starts and close it when you finish. Oh, and don't create a new cursor
    within a loop, that will not be very efficient.

    Regards,
    Andy
    --
    --------------------------------------------------------------------------------
    From the desk of Andrew J Todd esq - http://www.halfcooked.com/
    Andy Todd, Oct 10, 2003
    #4
    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. Brian Kelley

    MySQLDB multiple cursor question

    Brian Kelley, Jan 7, 2004, in forum: Python
    Replies:
    7
    Views:
    1,406
    Brian Kelley
    Jan 16, 2004
  2. Tim Williams
    Replies:
    2
    Views:
    630
    Tim Williams
    May 28, 2004
  3. John Nagle
    Replies:
    4
    Views:
    9,850
    John Nagle
    Feb 4, 2008
  4. Paul O'Sullivan
    Replies:
    2
    Views:
    1,054
    Lie Ryan
    Nov 29, 2009
  5. dmaziuk
    Replies:
    3
    Views:
    549
    Chris Gonnerman
    Jan 25, 2011
Loading...

Share This Page