Postgres Client

__init__

__init__(
    user: str,
    password: str,
    host: str,
    port: int = 5432,
    database: str = "postgres",
    pool_min_size: int = 10,
    pool_max_size: int = 50,
    patch: Optional[AsyncConnectionPatch] = None,
    name: str = "python-postgres",
    timeout: float = 30.0,
    max_waiting: int = 0,
    max_lifetime: float = 60 * 60.0,
    max_idle: float = 10 * 60.0,
    reconnect_timeout: float = 5 * 60.0,
)

Initialize the Postgres class to connect to a PostgreSQL database. This will create a connection Pool with the given parameters. The connection pool is not opened until the first query is executed. This has little performance impact, since you can use the first connection while the others are opened in the background and prevents prematurely acquiring connections that are not needed.

Parameters:

Name	Type	Description	Default
user	str	The username to connect to the database.	required
password	str	The password for the given user to connect to the database.	required
host	str	The host of the database.	required
port	int	The port of the database, default is 5432.	5432
database	str	The database name to connect to, default is postgres.	'postgres'
pool_min_size	int	The minimum number of connections to keep in the pool.	10
pool_max_size	int	The maximum number of connections to keep in the pool.	50
patch	Optional[AsyncConnectionPatch]	A list of functions to call on the connection after it is created. This is useful to set up the connection with custom settings, like enabling extensions.	None
name	str	An optional name to give to the connection pool, to identify it in the logs. Default to python-postgres to distinguish it from other pools that may be active.	'python-postgres'
timeout	float	The timeout in seconds to wait for a connection to be acquired from the pool. Default is 30 seconds.	30.0
max_waiting	int	The maximum number of waiting connections to allow. Default is 0, which means no limit.	0
max_lifetime	float	The maximum lifetime of a connection in seconds. Default is 60 minutes.	60 * 60.0
max_idle	float	The maximum idle time of a connection in seconds. Default is 10 minutes.	10 * 60.0
reconnect_timeout	float	The timeout in seconds to wait for a connection to be reconnected from the pool. Default is 5 minutes.	5 * 60.0

__call__ async

__call__(
    query: Query,
    params: Params = (),
    binary: bool = True,
    model: Optional[Type[T]] = None,
    **kwargs,
) -> list[T] | list[tuple] | int

Execute a query and return the results, or the number of affected rows. You can pass any query to this method. The Connections in the pool are in autocommit mode by default. This means that changes to the database are automatically committed and generally is more performant. It further allows for Operations that cannot be called in a Transaction like VACUUM or CALL. If you want to execute queries in a Transaction context, use the transaction method.

Parameters:

Name	Type	Description	Default
query	Query	The query to execute.	required
params	Params	The parameters to pass to the query.	()
binary	bool	Whether to use binary mode for the cursor. Default is True, which is more performant for most queries, but may not work with all queries. If you need to use text mode i.e. for type adapters, set this to False.	True
model	Optional[Type[T]]	The Pydantic model to parse the results into.	None
kwargs		Keyword arguments passed to the Pydantic serialization method, such as by_alias, exclude, etc. This is usually the easiest way to make sure your model fits the table schema definition. exclude_none is always set.	{}

Returns:

Type	Description
list[T] | list[tuple] | int	The results of the query.

one async

one(
    query: Query,
    params: Params = (),
    binary: bool = True,
    model: Optional[Type[T]] = None,
    **kwargs,
) -> T | tuple | None

Execute a query and return the first result, or None if no results are found. Otherwise, this behaves identically to the __call__ method.

Parameters:

Name	Type	Description	Default
query	Query	The query to execute.	required
params	Params	The parameters to pass to the query.	()
model	Optional[Type[T]]	The Pydantic model to parse the results into. If not provided, a new model with all columns in the query will be used.	None
binary	bool	Whether to use binary mode for the cursor. Default is True, which is more performant for most queries, but may not work with all queries. If you need to use text mode i.e. for type adapters, set this to False.	True
kwargs		Keyword arguments passed to the Pydantic serialization method, such as by_alias, exclude, etc. This is usually the easiest way to make sure your model fits the table schema definition. exclude_none is always set.	{}

Returns:

Type	Description
T | tuple | None	The first result of the query, or None if there isn't one.

insert async

insert(
    table_name: LiteralString,
    params: PydanticParams,
    prepare: bool = False,
    binary: bool = True,
    returning: Optional[list[LiteralString]] = None,
    **kwargs,
) -> list[tuple] | tuple | int

Dynamically expand an insert query to correctly handle pydantic models with optional fields, applying default values rather than explicitly passing None to the query. This always produces one single Query. The column names to insert are determined by all the non-None fields across all given models.

This will not be particularly efficient for large inserts and solves a specific problem. If you have uniform models and can construct one query to achieve the same, you should prefer that.

Parameters:

Name	Type	Description	Default
table_name	LiteralString	The name of the table to insert into.	required
params	PydanticParams	The Pydantic model or list of models to insert.	required
prepare	bool	Whether to use prepared statements. Default is False, due to the dynamic nature and possibly rather large size of the query.	False
binary	bool	Whether to use binary mode for the cursor. Default is True, which is more performant for most queries, but may not work with all queries. If you need to use text mode i.e. for type adapters, set this to False.	True
returning	Optional[list[LiteralString]]	An optional list of Column Names to return after the insert.	None
kwargs		Keyword arguments passed to the Pydantic serialization method, such as by_alias, exclude, etc. This is usually the easiest way to make sure your model fits the table schema definition. exclude_none is always set.	{}

Returns:

Type	Description
list[tuple] | tuple | int	The number of rows inserted.

transaction async

transaction(binary: bool = True) -> AsyncIterator[Transaction]

Create a transaction context manager to execute multiple queries in a single transaction. You can call the transaction the same way you would call the Postgres instance itself.

Parameters:

Name	Type	Description	Default
binary	bool	Whether to use binary mode for the cursor. Default is True, which is more performant for most queries, but may not work with all queries. If you need to use text mode i.e. for type adapters, set this to False.	True

connection async

connection() -> AsyncIterator[psycopg.AsyncConnection]

Acquire a psycopg AsyncConnection from the pool for direct use. The connection will be in autocommit mode by default.

is_open property

is_open: bool

Check if the pool is open and available for new clients.

Returns:

Type	Description
bool	True if the pool is open, False otherwise.

close async

close() -> None

Close the pool and make it unavailable to new clients.

All the waiting and future clients will fail to acquire a connection with a PoolClosed exception. Currently used connections will not be closed until returned to the pool.

Wait timeout seconds for threads to terminate their job, if positive. If the timeout expires the pool is closed anyway, although it may raise some warnings on exit.