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

Olli Rajala olli.rajala at gmail.com
Fri Jun 3 19:29:13 CEST 2005


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"

cursor = connectDB()
cursor.execute("SELECT * FROM categories")
print cursor.fetchall()

The result is:

Traceback (most recent call last):
  File "test.py", line 23, in ?
    cursor.execute("SELECT * FROM categories")
  File "/usr/lib/python2.4/site-packages/pyPgSQL/PgSQL.py", line 2992,
in execute
    raise InterfaceError, "execute failed - the cursor is closed."
libpq.InterfaceError: execute failed - the cursor is closed.

So, what's the solution for this? I saw somewhere some mentions about
'connection pooling', what's that and how I'm supposed to use that?

It's quite hard to code when you don't have good manuals and have to
put together information from very different sources and try to make
it work... For example, this is from a manual I've used:

2.1.3.1 PQconnectdb
Syntax:
    c = PQconnectdb(conninfo)
    Where conninfo is a string containing connection information.

What the heck is 'conninfo', I mean, what's it's syntax? Yeah, I was
finally able to figure it out, but it took hours googling, trying and
stumbling.

Okay, okay, back to business. Hope that someone will be able to help me. :)

My system is Python2.4+Postgresql 7.4.7 running on Ubuntu 5.04. if
that matters...

Yours sincerely,


-- 
Olli Rajala <><
Tampere, Finland
http://www.students.tut.fi/~rajala37/

"In theory, Theory and Practice should be
the same. But in practice, they aren't."
- Murphy's Proverbs


More information about the DB-SIG mailing list