FAQ
Hi,

I use MySQLdb lib to access my DB, because I want to opttimize my sql
queries. The application I'm working on has quite a few traffic load,
so I want to minimize the time of creating/destroying cursors:

My typical code is sth like:

cursor=con.cursor()
cursor.execute(sql)
all= cursor.fetchall()
cursor.close()

So, the question is... how can I minimize this cost? I was thinking on
Connection Pools, but I didn't find any good documentation or sample.
Any idea?

Search Discussions

  • Diez B. Roggisch at Jul 22, 2008 at 1:29 pm

    bcurtu wrote:

    Hi,

    I use MySQLdb lib to access my DB, because I want to opttimize my sql
    queries. The application I'm working on has quite a few traffic load,
    so I want to minimize the time of creating/destroying cursors:

    My typical code is sth like:

    cursor=con.cursor()
    cursor.execute(sql)
    all= cursor.fetchall()
    cursor.close()

    So, the question is... how can I minimize this cost? I was thinking on
    Connection Pools, but I didn't find any good documentation or sample.
    Any idea?
    What has the above (getting *cursors* from a given connection) to do with
    connection-pooling?

    I'm not sure what the actual costs of creating a cursor are (might well be
    that these are neglibel) but why don't you stop closing the cursor and just
    re-use it?

    Connection pooling is of course useful - frameworks such as SQLAlchemy and
    SQLObject do that for you, if used proper.


    Diez
  • John Nagle at Jul 22, 2008 at 4:07 pm

    bcurtu wrote:
    Hi,

    I use MySQLdb lib to access my DB, because I want to optimize my SQL
    queries. The application I'm working on has quite a few traffic load,
    so I want to minimize the time of creating/destroying cursors:

    My typical code is sth like:

    cursor=con.cursor()
    cursor.execute(sql)
    all= cursor.fetchall()
    cursor.close()

    So, the question is... how can I minimize this cost? I was thinking on
    Connection Pools, but I didn't find any good documentation or sample.
    Any idea?
    Creating and destroying MySQLdb cursors is cheap, because it
    doesn't really do anything. MySQL doesn't actually support
    cursors; you can only do one thing at a time per connection, and
    thus you can only have one cursor per connection. So cursor creation
    is a dummy operation for MySQLdb.

    Creating connections to the database is more expensive, and it
    helps to reuse those. There was once something called the "pool
    module" for doing this ("http://dustman.net/andy/python/Pool")
    but it's a dead link now.

    This is usually an issue only in multithreaded programs.
    Also, it's OK to have multiple connections open to the database
    from multiple threads, until you have so many that the database
    server starts to run out of resources.

    John Nagle

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppython-list @
categoriespython
postedJul 22, '08 at 10:19a
activeJul 22, '08 at 4:07p
posts3
users3
websitepython.org

People

Translate

site design / logo © 2022 Grokbase