What's the difference between connection and connect, and when should I use one over the other?

Question

I'm using peewee extensively with the play_house.db_url.connect to connect to a database. This method allows for a lot of flexibility and it is very straightforward when opening a single connection to a database i.e., open and close a single connection.

However, this gets unclear when attempting to reuse the Pooled versions of the URL. For instance, if I do:

from playhouse.db_url import connect

db_url = 'postgresql+pool://...?max_connections=20&stale_timeout=300'
db = connect(db_url)

what is db ?? a single connection or a connection pool? in case it is the later, how do I from a multi-threaded application e.g. Flask, acquire a separate connection from the pool? using connect or connection? which one and why?

or do I instead, every time I need a new connection should do over again? or is this creating a new separate pool?

db = connect(db_url)

and if so will calling db.close_all() apply to all the opened connections?

Answer 1

In the above, db = connect(...), db is just a database instance. The database instance manages the connections.

Example code:

db = connect('postgresql+pool....')
# At this point no connections are opened.

# Now open a connection.
db.connect()

# Now call close() -- this will return the connection to the pool.
db.close()

# Calling connect again will use the conn from the pool.
db.connect()

When your application has multiple threads, peewee will automatically ensure you have a connection-per-thread:

# thread 1:
db.connect()  # Open a connection.

# thread 2:
db.connect()  # Opens a separate connection.

Read the docs on connection management for more details, http://docs.peewee-orm.com/en/latest/peewee/database.html#connection-management