[DB-SIG] Database connections don't stay alive

[Chris Cogdon]

On Jun 3, 2005, at 10:29, Olli Rajala wrote:

> I sent this to python-tutor mailing list, but they suggested me to ask
> here. So here I am with my quite basic/newbie question. Hope that it
> doesn't matter too much. :)
>
> I've made a little cgi program with Python and Mysql, but would like
> to change MySQL to Postgresql. There just seem not to be quite many
> tutorials about this combination. I've been able to gather some info,
> but much more would be necessary.
>
> I have quite big problem now, after I learned how to connect to the
> Postgresql database.  With MySQL I can do like this:
>
> import MySQLdb
> def connectDB():
>     try:
>         db = MySQLdb.connect(host='localhost', user='user',
> db='pictures', passwd='passwd')
>         cursor = db.cursor()
>         return cursor
>     except:
>         print 'Error'
>
> cursor = connectDB()
> cursor.execute('SELECT * FROM categories')
> print cursor.fetchall()
>
> And everything works as I thought. But with Postgre, it seems that the
> connection don't stay alive. I mean, with the same kind of code:
>
> from pyPgSQL import PgSQL
> def connectDB():
>     try:
>         db = PgSQL.connect(host='localhost', database='pictures',
> user='user', password='passwd')
>         return db.cursor()
>     except:
>         print "Error"

The 'problem' here is that the database object goes out of scope when 
the function exits. When 'out of scope' objects get cleaned up is 
fairly implementation dependant, which is likely why it was working 
with MySQL.

You really need to do something like this, instead:

from pyPgSQL import PgSQL
def connectDB():
     return PgSQL.connect( ...blahblahblah... )


# Do the following statement ONCE in your program
db = connectDB()

# Do the following block every time you need to do a database operation
try:
     cur = db.cursor ()
     ... all your stuff with the cursor.
     db.commit ()
except:
     db.rollback()


This method will also work with MySQL too.




-- 
    ("`-/")_.-'"``-._        Chris Cogdon <chris at cogdon.org>
     . . `; -._    )-;-,_`)
    (v_,)'  _  )`-.\  ``-'
   _.- _..-_/ / ((.'
((,.-'   ((,/   fL