PostgreSQL + Python: Close connection

Question

I made a game server in Python that connects to a PostgreSQL db using psycopg2. I have seen examples, I have seen that when a connection to a data base is created, should close the connection when finished making queries, eg for each client:

#create connection to db
con = psycopg2.connect (database = 'testdb', user = 'janbodnar')
cur = con.cursor ()
#process query
.
.
.
#close connection
con.close ()

Ok, when I start my server, I have this:

Inside my class

def __init __ (self):
      #create connection to db
      con = psycopg2.connect (database = 'testdb', user = 'janbodnar')
      cur = con.cursor ()

# to all customers ...
def query(self):
      #process query, for example ...
      cur.execute ("DROP TABLE IF EXISTS Cars")
      #the connection never closes

That is, I use the same connection object for all inquiries from all customers and never close the connection,this looks better than to be opening and closing connections for each client, my server apparently works well. you think of this? this well done? not to do?. Thank you

Shiva · Accepted Answer · 2023-03-29 06:32:05Z

2

This may work but is not good. Problems: how to set datetime format for session? How to handle transactions? Temporary tables? How to handle errors? See also at: How can I pool connections using psycopg and gevent?

For such things you can use connection pooling. This way when you start with new client (new network connection) you get db connection from pool. After using it instead of closing connection you release it and it returns to pool. Now it may be used by other thread.

If your connection is somehow broken it may be simple closed instead of returning to pool. Every thread can use transaction and you can change session settings like datetime format.

I see that there is https://www.psycopg.org/docs/pool.html

PS In your methods you should use self.con and self.cur.

edited Mar 29, 2023 at 6:32

Shiva

2,86826 silver badges40 bronze badges

answered Dec 11, 2014 at 8:59

Michał Niklas

54.5k19 gold badges76 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Constantinius · Accepted Answer · 2014-12-11 08:44:32Z

1

I think the answer to this is quite easy: as long as the total number of clients connected at the same time does not exceed your max_connections setting of your postgres service you should be fine. Otherwise new connections cannot be accepted.

answered Dec 11, 2014 at 8:44

Constantinius

35.2k9 gold badges79 silver badges85 bronze badges

Collectives™ on Stack Overflow

PostgreSQL + Python: Close connection

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related