[Tutor] Database connections don't stay alive

[Olli Rajala]

Well, I asked about tutorials, but maybe this was not so good day,
because it has been quite "silent". :)

So, good tutorials are still welcome, though I know now how to connect
to the Postgresql database.  I just have some problems, though. 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. :)

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