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

Answer 1

@Michał Niklas Thanks for your answer and thanks for the correction self.con and self.cur, I forgot to put "self".

I clarify that I know very little of servers and databases.

I intend to do this:

My server handles "threads" separate processes for each user, then, in each separate process think open a connection for customer queries and then close this connection, something like this:

in my class: if a new request from client1... a "thread" for this client, then, the query runs...

def query (self):
       #create connection to db for client1
       con = psycopg2.connect (database = 'testdb', user = 'janbodnar')
       cur = con.cursor ()
       #process query for client1, for example ...
       cur.execute ("DROP TABLE IF EXISTS Cars")
       #close connection for this client
       con.close ()

what they think about this? seems to me better. I appreciate suggestions and support.

Answer 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.

Answer 3

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.