Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PostgreSQL + Python: Close connection

Tags:

python

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

like image 372
ProgNova Avatar asked Dec 11 '14 08:12

ProgNova


People also ask

Does psycopg2 close connection automatically?

Changed in version 2.5: if the connection is used in a with statement, the method is automatically called if no exception is raised in the with block. Roll back to the start of any pending transaction. Closing a connection without committing the changes first will cause an implicit rollback to be performed.

How do I close an open connection in PostgreSQL?

pg_close() closes the non-persistent connection to a PostgreSQL database associated with the given connection instance. Note: Using pg_close() is not usually necessary, as non-persistent open connections are automatically closed at the end of the script.

How connect PostgreSQL with Python?

To establish connection with the PostgreSQL database, make sure that you have installed it properly in your system. Open the PostgreSQL shell prompt and pass details like Server, Database, username, and password. If all the details you have given are appropriate, a connection is established with PostgreSQL database.


3 Answers

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

like image 88
ProgNova Avatar answered Oct 18 '22 22:10

ProgNova


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 http://initd.org/psycopg/docs/pool.html

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

like image 39
Michał Niklas Avatar answered Oct 18 '22 23:10

Michał Niklas


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.

like image 1
Constantinius Avatar answered Oct 19 '22 00:10

Constantinius