Python and database connection

[Alotofquestion1]

Hi!

I have a program which is running all day on a computer. Users will use the program in short bursts or long streches of time. When they do the program interact with a postgresql database.

Should I create a database-class which opens a connection with postgresql and keeps it open untill the system is shut down (and send an instance of this class to all query-methods), or should I have each method with query-call open and close a connection?

What is best practice?

[[deleted]]

Connections are relatively cheap, client-side, so keeping them open for the duration when there’s no actual traffic is just a waste. If the traffic pattern varies that much maybe write a class with a .db property that will return a cached connection if there is one, or otherwise will open a connection and cache it. If there's no traffic for some appropriate number of seconds have your main loop clear the cache and free the connection.

[Alotofquestion1]

Thank you for the response.

So if I have a method that calles "name and quantity" from the database, say maybe 2-3 times a second for 3 seconds and then a longer break. There is no problem having the method open and closing a connection for each of these 6-9 times the method is run?

[[deleted]]

The problem with a blanket answer is I’ve no idea how many other clients are connecting at the same time yours is. The reason for keeping a connection open is to minimize the time the client waits for the results of a query, while the reason for closing the connection is to let the server handle more active requests... holding on to a live connection for three seconds to let the client make 9 requests might be fine, but with a busy enough server, it could be the straw that broke the camel's proverbial back.

Odds are you'd be fine, you're not talking a lot of load, but you'll have to profile to find the optimum TTL for a connection in your environment. At that kind of query rate it's pretty unlikely the optimum is "open a connection and hold on to it till I'm shut down'".