I wrote a CherryPy webapp the other day. Some data is queried from PostgreSQL and I use psycopg2 for that. While everything worked alright in my local testing, it sometimes falls apart in real conditions (much more pageloads).
Unfortunately, I do not have the exact traceback messages right now but they were about not being able to connect or a connection being closed already if I recall correctly. I will add them as soon as I can.
Psycopg / Postgres : Connections hang out randomly seems to have a very similar problem but their solution seems like a hack. Another comment on that suggests using psycopg2.pool.ThreadedConnectionPool.
My relevant code snippets would be:
def connect_database(thread_index):
cherrypy.thread_data.dbconn = psycopg2.connect(mydbstring)
cherrypy.thread_data.dbconn.autocommit=True
if __name__ == '__main__':
cherrypy.engine.subscribe('start_thread', connect_database)
cherrypy.quickstart(Root())
cherrypy.engine.start()
cherrypy.engine.block()
I use the database connection(s) like this:
with cherrypy.thread_data.dbconn.cursor() as cursor:
cursor.execute("SELECT * FROM posts;")
results = cursor.fetchall()
I got this far by reading through the (very random) http://tools.cherrypy.org/wiki/Databases . The comments on that page are almost 10 years old and seem very complicated. There must be a better way?(tm)
I assume that using psycopg2.pool.ThreadedConnectionPool would be the best way. How can I do that?
