Persistent db conn and fetchall() data with dict

[Gerhard Häring]

Jaros³aw Zabie³³o wrote:
> I have two questions.
> 
> First. Is there a possibillity for *persistent* connection to MySQL
> using MySQLdb module? Does it mean, I do not need to close db
> connection?

In a single-threaded application, you typically only need one database
connection. Of course your application may be spread over multiple
modules, in which case I use something like:

#v+
# file dbstuff.py
import MySQLdb

conn = None

def openCon():
    global conn
    conn = MySQLdb.connect(...)

def getCon():
    global conn
    return conn
#v-

The global keyword isn't strictly needed here, but I use it for
documentation purposes, too.

> What about opened cursor? Should I close it, or I can leave it as it
> is? 

You can typically use a single cursor, too. But there's IMO little
benefit compared to only having a /connection singleton/, so my code
typically looks like:

#v+
import dbstuff
...
def somefunction():
    cursor = dbstuff.conn().cursor()
    cursor.execute(...)
    for row in cursor.fetchall():
        ...
    cursor.close()
#v-

It's good practise to close the cursor, though in the current CPython
implementation of garbage collection it's collected (and closed) at
the end of 'somefunction', anyway.

> Second, is it possible to get the result from the database with
> dictionary (not pure tuple)? Eg.

You can use the nonstandard methods dictfetchXXX(). Or you can use one
of the database module independent dictionary wrappers. I believe Andy
Dustman (MySQLdb author) has one on his homepage called dbObj.

Gerhard
-- 
Favourite database:             http://www.postgresql.org/
Favourite programming language: http://www.python.org/
Combine the two:                http://pypgsql.sf.net/
Embedded database for Python:   http://pysqlite.sf.net/