Database connection caching

[lbolognini]

Hi all,

is there an alternative way of:

- create a connection object
- open the connection
- close the connection

every time one has to run a query.

I assume that big sites do something to prevent the performance hit
given by these operations, don't they?

Would you kindly point me to some examples of good practices in this
sense beside http://www.danga.com/words/2004_oscon/oscon2004.pdf ?

Thanks,
Lorenzo

 

[Swaroop C H]

Hi all,

is there an alternative way of:

- create a connection object
- open the connection
- close the connection

every time one has to run a query.

Why not use cursor objects with a single connection object?

You can have any number of cursor objects and can run with a single
connection object

A good introduction is at http://www.amk.ca/python/writing/DB-API.html

Regards,

 

[Skip Montanaro]

Lorenzo> is there an alternative way of:

Lorenzo> - create a connection object
Lorenzo> - open the connection
Lorenzo> - close the connection

Lorenzo> every time one has to run a query.

Sure, create a Queue.Queue object and stuff a number of connections into
it. When you want a connection call the queue's .get() method. When you're
done with it .put() it back. This has the other nice feature that a program
with a large number of threads can't overwhelm your database.

Skip

 

[Simon Brunning]

is there an alternative way of:

- create a connection object
- open the connection
- close the connection

every time one has to run a query.

It's actually morte like:

create connection
create cursor
execute SQL
process cursor
close cursor
close connection

The bit that you can avoid it the creation of the connection - that's
an intensive process. If your system is single threaded, or if it's a
very small app, you can just create a single open connection and keep
using that. I usually find that I need a pool of open connections,
though.

 

[Istvan Albert]

Hi all,

is there an alternative way of:

- create a connection object
- open the connection
- close the connection

psycopg, a Postgresql database adapter does connection pooling
automatically

http://initd.org/projects/psycopg1

Most Zope database adapters also have implicit
connection pooling.

Istvan.

 

[lbolognini]

Thanks everybody for their replies.

Lorenzo