Psycopg 3

https://www.psycopg.org/psycopg3/docs/
Admin
Psycopg 3 is a modern PostgreSQL database adapter for Python that supports asynchronous operations,...
Tokens:150,473
Snippets:1,220
Trust Score:9.6
Update:2 weeks ago
Show doc for...
Tokens:
Raw
### Install Psycopg with C speed-up (local installation)

Source: https://www.psycopg.org/psycopg3/docs/basic/install.html

Run this command to install Psycopg 3 with the C extension, linking to system libraries. Requires a C compiler, Python development headers, PostgreSQL client headers, and pg_config in PATH. Not supported by PyPy.

```bash
pip install "psycopg[c]"
```

--------------------------------

### Install libpq system package (Debian/Ubuntu)

Source: https://www.psycopg.org/psycopg3/docs/basic/install.html

Install the libpq client library required by the pure Python installation. On Debian systems, this package provides the library that Psycopg loads dynamically via ctypes.

```bash
sudo apt install libpq5
```

--------------------------------

### Install pure Python Psycopg

Source: https://www.psycopg.org/psycopg3/docs/basic/install.html

Run this command to install the pure Python implementation of Psycopg 3. This is slower than the binary or local installations and is intended for debugging or small tasks.

```bash
pip install psycopg
```

--------------------------------

### Install psycopg with binary extra

Source: https://www.psycopg.org/psycopg3/docs/basic/install.html

Install psycopg with binary packages on supported systems. First upgrades pip, then installs the psycopg package with the binary extra. After this, you should be ready to start.

```bash
pip install --upgrade pip
pip install "psycopg[binary]"

```

--------------------------------

### tuple_row example

Source: https://www.psycopg.org/psycopg3/docs/api/rows.html

Shows the default row factory returning rows as tuples. Use when no row_factory is specified. The example executes a query and fetches a row as a tuple.

```python
>>> cur = conn.cursor(row_factory=tuple_row)
>>> cur.execute("SELECT 10 AS foo, 'hello' AS bar").fetchone()
(10, 'hello')
```

--------------------------------

### tpc_begin(xid)

Source: https://www.psycopg.org/psycopg3/docs/api/connections.html

Starts a two-phase transaction with the given transaction ID.

```APIDOC
## tpc_begin(xid)

### Description
Starts a TPC transaction with the given transaction ID. Must be called outside of a transaction, and it is an error to call commit() or rollback() within the TPC transaction.

### Method
Method call on Connection object

### Parameters
- **xid** (_Xid_ or _str_) - Required - The id of the transaction. Can be an object returned by xid() or a plain string.

### Errors
- **ProgrammingError** - Raised if commit() or rollback() is called within the TPC transaction.

### Notes
- Must be called when transaction_status is IDLE.
- See also tpc_recover().
```

--------------------------------

### ConnectionPool

Source: https://www.psycopg.org/psycopg3/docs/api/pool.html

Documentation for the ConnectionPool class constructor, including all parameters and an example instantiation.

```APIDOC
## ConnectionPool

### Description
Creates a new connection pool instance. The pool manages a set of database connections, providing them to clients on demand and handling their lifecycle.

### Constructor
`ConnectionPool(conninfo, *, connection_class=Connection, kwargs=None, min_size=4, max_size=None, open=None, configure=None, check=None, reset=None, name=None, close_returns=False, timeout=30.0, max_waiting=0, max_lifetime=3600.0, max_idle=600.0, reconnect_timeout=300.0, reconnect_failed=None, num_workers=3)`

### Parameters
- **conninfo** (`str` or `Callable[[], str]`) - Required - The connection string. If a callable, it is called at every connection attempt.
- **connection_class** (`type`, default: `Connection`) - Optional - The class of connections to serve. Must be a `Connection` subclass.
- **kwargs** (`dict[str, Any]` or `Callable[[], dict[str, Any]]`) - Optional - Extra arguments to pass to `connect()`. If a callable, it is called at every connection attempt.
- **min_size** (`int`, default: 4) - Optional - The minimum number of connections the pool will hold.
- **max_size** (`int`, default: `None`) - Optional - The maximum number of connections the pool will hold. If `None` or equal to `min_size`, the pool will not grow or shrink.
- **open** (`bool`, default: `True`) - Optional - If `True`, open the pool on init. If `False`, open when `open()` is called or when the pool context is entered.
- **configure** (`Callable[[Connection], None]`) - Optional - A callback to configure a connection after creation.
- **check** (`Callable[[Connection], None]`) - Optional - A callback to check that a connection is working correctly when obtained by the pool.
- **close_returns** (`bool`, default: `False`) - Optional - If `True`, calling `close()` on a connection returns it to the pool instead of closing it.
- **reset** (`Callable[[Connection], None]`) - Optional - A callback to reset a connection after it has been returned to the pool.
- **name** (`str`) - Optional - An optional name for the pool, useful for identification in logs.
- **timeout** (`float`, default: 30 seconds) - Optional - The default maximum time in seconds a client can wait to receive a connection.
- **max_waiting** (`int`, default: 0) - Optional - Maximum number of requests that can be queued to the pool. 0 means no limit.
- **max_lifetime** (`float`, default: 1 hour) - Optional - The maximum lifetime of a connection in the pool, in seconds.

### Example
```python
from psycopg_pool import ConnectionPool

pool = ConnectionPool(
    "postgresql://user:password@localhost/dbname",
    min_size=2,
    max_size=10,
    timeout=10,
)
```
```

--------------------------------

### Single expression connection and fetch

Source: https://www.psycopg.org/psycopg3/docs/basic/usage.html

Example of connecting, executing, and fetching a result in a single expression.

```python
print(psycopg.connect(DSN).execute("SELECT now()").fetchone()[0])
# 2042-07-12 18:15:10.706497+01:00
```

--------------------------------

### Output of notifies() generator example

Source: https://www.psycopg.org/psycopg3/docs/advanced/async.html

Example output from the Python process after running the notifies() generator example, showing the printed Notify objects and the final message.

```text
Notify(channel='mychan', payload='hello', pid=961823)
Notify(channel='mychan', payload='hey', pid=961823)
Notify(channel='mychan', payload='stop', pid=961823)
there, I stopped

```

--------------------------------

### make_conninfo example

Source: https://www.psycopg.org/psycopg3/docs/api/conninfo.html

Merges a connection string and keyword parameters into a single conninfo string. Raises ProgrammingError if the input doesn't make a valid conninfo string.

```python
>>> make_conninfo("dbname=db user=jeff", user="piro", port=5432)
'dbname=db user=piro port=5432'
```

--------------------------------

### Install Psycopg 3 binary package

Source: https://www.psycopg.org/psycopg3/docs/basic/install.html

Installs the self-contained binary package for Psycopg 3, which includes all necessary libraries. This is the equivalent of installing psycopg2-binary for Psycopg 2 users. Binary packages are not supported by PyPy and may not be available for all platforms or Python versions.

```bash
pip install "psycopg[binary]"
```

--------------------------------

### class_row example

Source: https://www.psycopg.org/psycopg3/docs/api/rows.html

Shows how to use class_row to return rows as instances of a user-defined class. The class must support the query's column names as keyword arguments. The example defines a Person dataclass and uses class_row(Person) as the row factory.

```python
from dataclasses import dataclass
import psycopg
from psycopg.rows import class_row

@dataclass
class Person:
    first_name: str
    last_name: str
    age: int = None

conn = psycopg.connect()
cur = conn.cursor(row_factory=class_row(Person))

cur.execute("select 'John' as first_name, 'Smith' as last_name").fetchone()
# Person(first_name='John', last_name='Smith', age=None)
```

--------------------------------

### Boolean adaptation example

Source: https://www.psycopg.org/psycopg3/docs/basic/adapt.html

Shows how Python True and False are converted to PostgreSQL boolean literals in a query.

```python
>>> cur.execute("SELECT %s, %s", (True, False))
# equivalent to "SELECT true, false"
```

--------------------------------

### AsyncConnectionPool.open

Source: https://www.psycopg.org/psycopg3/docs/api/pool.html

Opens the pool by starting connecting and accepting clients. Can wait for the pool to be ready or return immediately.

```APIDOC
## open(_wait: bool = False, _timeout: float = 30.0) -> None

### Description
Opens the pool by starting connecting and accepting clients. If `_wait` is `False`, returns immediately and lets the background worker fill the pool if `min_size` > 0. Otherwise waits up to `_timeout` seconds for the requested number of connections to be ready.

### Method
async

### Parameters
- `_wait` (bool) - Optional - If `True`, wait for the pool to be ready; otherwise return immediately.
- `_timeout` (float) - Optional - Maximum time in seconds to wait for connections to be ready when `_wait` is `True`.

### Returns
None
```

--------------------------------

### dict_row example

Source: https://www.psycopg.org/psycopg3/docs/api/rows.html

Shows the row factory that returns rows as dictionaries with column names as keys. Use when you need dictionary access to row fields.

```python
>>> cur = conn.cursor(row_factory=dict_row)
>>> cur.execute("SELECT 10 AS foo, 'hello' AS bar").fetchone()
{'foo': 10, 'bar': 'hello'}
```

--------------------------------

### PGcancelConn.start()

Source: https://www.psycopg.org/psycopg3/docs/api/pq.html

Requests that the server abandons processing of the current command in a non-blocking manner. See `PQcancelStart` for details.

```APIDOC
## PGcancelConn.start()

### Description
Requests that the server abandons processing of the current command in a non-blocking manner. See `PQcancelStart` for details.

### Method
Method call

### Endpoint
`PGcancelConn.start()`

### Parameters
None

### Returns
None
```

--------------------------------

### Install dnspython package

Source: https://www.psycopg.org/psycopg3/docs/api/dns.html

The psycopg._dns module depends on the dnspython package, which is not installed automatically as a Psycopg dependency. Install it manually with pip.

```bash
$ pip install "dnspython >= 2.1"

```

--------------------------------

### SerializationFailure error example

Source: https://www.psycopg.org/psycopg3/docs/basic/transactions.html

Example error raised by PostgreSQL when a serialization failure occurs at REPEATABLE_READ or SERIALIZABLE isolation level. Applications must be prepared to repeat the operation that caused this exception.

```text
psycopg2.errors.SerializationFailure: could not serialize access
due to concurrent update

```

--------------------------------

### sql.Identifier escaping examples

Source: https://www.psycopg.org/psycopg3/docs/api/sql.html

Shows how sql.Identifier escapes identifiers with double quotes, handling single and double quotes inside the identifier. The example joins multiple identifiers with a comma separator.

```python
>>> t1 = sql.Identifier("foo")
>>> t2 = sql.Identifier("ba'r")
>>> t3 = sql.Identifier('ba"z')
>>> print(sql.SQL(', ').join([t1, t2, t3]).as_string(conn))
"foo", "ba'r", "ba""z"

```

--------------------------------

### AdaptContext

Source: https://www.psycopg.org/psycopg3/docs/api/abc.html

Documentation for the AdaptContext protocol, including its properties.

```APIDOC
## `AdaptContext`

### Description
A context describing how types are adapted. Examples of `AdaptContext` are `Connection`, `Cursor`, `Transformer`, and `AdaptersMap`. This is a `Protocol`, so objects implementing `AdaptContext` don’t need to explicitly inherit from this class.

### Class
`class psycopg.abc.AdaptContext(*args, **kwargs)`

### Properties

#### `adapters`

##### Description
The adapters configuration that this object uses.

##### Returns
- `AdaptersMap` - The adapters configuration.

#### `connection`

##### Description
The connection used by this object, if available.

##### Returns
- `BaseConnection[Any] | None` - The connection, or `None` if not available.
```

--------------------------------

### rollback

Source: https://www.psycopg.org/psycopg3/docs/api/connections.html

Rolls back to the start of any pending transaction.

```APIDOC
## rollback

### Description
Roll back to the start of any pending transaction.

### Method
Not applicable (Python method)

### Signature
`rollback() -> None`
```

--------------------------------

### Async application initialization with psycopg_pool and SQLAlchemy

Source: https://www.psycopg.org/psycopg3/docs/advanced/pool.html

Creates an AsyncConnectionPool with close_returns=True and uses it as the async_creator for a SQLAlchemy engine with NullPool. Requires psycopg_pool 3.3+.

```python
import psycopg_pool
import sqlalchemy.pool
import sqlalchemy.ext.asyncio

db_url = "postgresql://postgres:postgres@hostname:port/database"

async def main():
    # Create and open a Psycopg pool
    pgpool = psycopg_pool.AsyncConnectionPool(
        conninfo=db_url, open=False, close_returns=True
    )
    await mypool.open()

    # Create a SQLAlchemy engine using the pool just created
    engine = sqlalchemy.ext.asyncio.create_async_engine(
        url="postgresql+psycopg://",
        poolclass=sqlalchemy.pool.NullPool,
        async_creator=pgpool.getconn,
    )

    # Use the `engine` in the rest of your application.
    await run_my_app(engine)

if __name__ == "__main__":
    asyncio.run(main())
```

--------------------------------

### Using a custom row factory

Source: https://www.psycopg.org/psycopg3/docs/advanced/rows.html

Shows how to use a custom row factory (DictRowFactory) with connect() and execute().

```python
conn = psycopg.connect(row_factory=DictRowFactory)
cur = conn.execute("SELECT first_name, last_name, age FROM persons")
person = cur.fetchone()
print(f"{person['first_name']} {person['last_name']}")

```

--------------------------------

### Transaction status property examples

Source: https://www.psycopg.org/psycopg3/docs/basic/transactions.html

Examples of using the `status` property on a `Transaction` object (added in version 3.3). Shows the status values for successful commit, rollback due to an error, explicit rollback via `Rollback()`, and forced rollback with `force_rollback=True`. The status remains accessible after the `with` block exits.

```python
from psycopg import Rollback

# Successful transaction
with conn.transaction() as tx:
    assert tx.status == tx.Status.ACTIVE
    conn.execute("INSERT INTO data VALUES (%s)", ("Hello",))
assert tx.status == tx.Status.COMMITTED

# Transaction rolled back with an error
try:
    with conn.transaction() as tx:
        conn.execute("INSERT INTO data VALUES (%s)", ("World",))
        1 / 0  # Causes an exception
except ZeroDivisionError:
    pass
assert tx.status == tx.Status.ROLLED_BACK_WITH_ERROR

# Explicit rollback
with conn.transaction() as tx:
    conn.execute("INSERT INTO data VALUES (%s)", ("Test",))
    raise Rollback()
assert tx.status == tx.Status.ROLLED_BACK_EXPLICITLY

# Force rollback
with conn.transaction(force_rollback=True) as tx:
    conn.execute("INSERT INTO data VALUES (%s)", ("Temp",))
assert tx.status == tx.Status.ROLLED_BACK_EXPLICITLY

```

--------------------------------

### Using RawCursor with cursor_factory

Source: https://www.psycopg.org/psycopg3/docs/advanced/cursors.html

Creates a connection with cursor_factory=RawCursor so all cursors use raw placeholders. Executes a query with positional parameters as a list and asserts the result.

```python
from psycopg import connect, RawCursor

with connect(dsn, cursor_factory=RawCursor) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT $1, $2", [1, "Hello"])
        assert cur.fetchone() == (1, "Hello")
```

--------------------------------

### open()

Source: https://www.psycopg.org/psycopg3/docs/api/pool.html

Opens the pool by starting to create connections and accepting clients. If wait is False, returns immediately and lets background workers fill the pool. If wait is True, waits up to the specified timeout for the pool to be ready.

```APIDOC
## open()

### Description
Opens the pool by starting to create connections and accepting clients. If `wait` is False, returns immediately and lets background workers fill the pool. If `wait` is True, waits up to `timeout` seconds for the pool to be ready.

### Method
Python method

### Parameters
- **wait** (bool) - Optional - If True, wait for the pool to be ready. Default is False.
- **timeout** (float) - Optional - Maximum time in seconds to wait for the pool to be ready. Default is 30.0.

### Notes
- Safe to call on an already open pool, but cannot re-open a closed pool.
- Added in version 3.1.
```

--------------------------------

### Recommended: Using sql.SQL and sql.Identifier

Source: https://www.psycopg.org/psycopg3/docs/api/sql.html

Shows the recommended way to build dynamic queries safely using the psycopg.sql module, separating identifiers from parameters.

```python
from psycopg import sql

cur.execute(
    sql.SQL("INSERT INTO {} VALUES (%s, %s)")
        .format(sql.Identifier('my_table')),
    [10, 20])

```

--------------------------------

### Default transaction behavior without commit

Source: https://www.psycopg.org/psycopg3/docs/basic/transactions.html

Shows the default transaction behavior: operations start a transaction implicitly, and closing the connection without commit discards changes. The comments explain that cursor creation does not start a transaction, the first execute sends BEGIN and the statement, and close() without COMMIT discards the INSERT.

```python
conn = psycopg.connect()

# Creating a cursor doesn't start a transaction or affect the connection
# in any way.
cur = conn.cursor()

cur.execute("SELECT count(*) FROM my_table")
# This function call executes:
# - BEGIN
# - SELECT count(*) FROM my_table
# So now a transaction has started.

# If your program spends a long time in this state, the server will keep
# a connection "idle in transaction", which is likely something undesired

cur.execute("INSERT INTO data VALUES (%s)", ("Hello",))
# This statement is executed inside the transaction

conn.close()
# No COMMIT was sent: the INSERT was discarded.
```

--------------------------------

### Create a global pool with open=True and use it in another module

Source: https://www.psycopg.org/psycopg3/docs/advanced/pool.html

Shows creating a ConnectionPool as a global object with open=True, which starts connecting immediately at import time. Then, in another module, the pool is imported and used with pool.connection() to execute queries.

```python
# module db.py in your program
from psycopg_pool import ConnectionPool

pool = ConnectionPool(..., open=True, ...)
# the pool starts connecting immediately.

# in another module
from .db import pool

def my_function():
    with pool.connection() as conn:
        conn.execute(...)

```

--------------------------------

### Check capabilities at import time and runtime

Source: https://www.psycopg.org/psycopg3/docs/api/module.html

Shows how to use psycopg.capabilities to verify client library features. The first check fails at import time if encrypted passwords are not available, while the second checks at runtime.

```python
# Fail at import time if encrypted passwords is not available
import psycopg
psycopg.capabilities.has_encrypt_password(check=True)

# Verify at runtime if a feature can be used
if psycopg.capabilities.has_hostaddr():
    print(conn.info.hostaddr)
else:
    print("unknown connection hostadd")

```

--------------------------------

### Async transaction context manager

Source: https://www.psycopg.org/psycopg3/docs/api/connections.html

Use this context manager to start a new transaction or nested transaction.

```python
async with conn.transaction() as tx:
    ...

```

--------------------------------

### Fetching and registering extension TypeInfo

Source: https://www.psycopg.org/psycopg3/docs/advanced/adapt.html

Fetches TypeInfo for an extension type (hstore) using psycopg.types.TypeInfo.fetch(conn, "hstore"), registers it globally with t.register(), and then accesses it from the global registry.

```python
>>> (t := psycopg.types.TypeInfo.fetch(conn, "hstore"))
<TypeInfo: hstore (oid: 770082, array oid: 770087)>

>>> t.register()  # globally

>>> psycopg.adapters.types["hstore"]
<TypeInfo: hstore (oid: 770082, array oid: 770087)>
```

--------------------------------

### Using ConnectionPool as a context manager

Source: https://www.psycopg.org/psycopg3/docs/api/pool.html

Shows how to use the pool as a context manager, which opens the pool on entering the block and closes it on exiting. This is the recommended way to manage the pool lifecycle.

```python
with ConnectionPool(...) as pool:
    # code using the pool

```

--------------------------------

### deferrable

Source: https://www.psycopg.org/psycopg3/docs/api/connections.html

The deferrable state of new transactions started on the connection. None means use the server default.

```APIDOC
## deferrable

### Description
The deferrable state of the new transactions started on the connection. `None` means use the default set in the `default_transaction_deferrable` configuration parameter of the server.

### Method
Not applicable (Python property)

### Signature
`deferrable` (property)
```

--------------------------------

### Connection execute shortcut

Source: https://www.psycopg.org/psycopg3/docs/basic/usage.html

Comparison of cursor execution patterns between Psycopg 2 and Psycopg 3.

```python
# In Psycopg 2
cur = conn.cursor()
cur.execute(...)

# In Psycopg 3
cur = conn.execute(...)
```

--------------------------------

### read_only

Source: https://www.psycopg.org/psycopg3/docs/api/connections.html

The read-only state of new transactions started on the connection. None means use the server default.

```APIDOC
## read_only

### Description
The read-only state of the new transactions started on the connection. `None` means use the default set in the `default_transaction_read_only` configuration parameter of the server.

### Method
Not applicable (Python property)

### Signature
`read_only` (property)
```

--------------------------------

### Sample psycopg connection DEBUG log

Source: https://www.psycopg.org/psycopg3/docs/basic/usage.html

Example DEBUG log output from the connection attempt. It shows the timeout on the first host (192.0.2.1) and the successful connection to localhost, including SSL startup and polling states.

```text
2045-05-10 11:45:54,364 DEBUG connection attempt: host: '192.0.2.1', port: None, hostaddr: '192.0.2.1'
2045-05-10 11:45:54,365 DEBUG connection started: <psycopg_c.pq.PGconn [STARTED] at 0x79dff6d26160>
2045-05-10 11:45:54,365 DEBUG connection polled: <psycopg_c.pq.PGconn [MADE] at 0x79dff6d26160>
2045-05-10 11:46:04,392 DEBUG connection failed: host: '192.0.2.1', port: None, hostaddr: '192.0.2.1': connection timeout expired
2045-05-10 11:46:04,392 DEBUG connection attempt: host: 'localhost', port: None, hostaddr: '127.0.0.1'
2045-05-10 11:46:04,393 DEBUG connection started: <psycopg_c.pq.PGconn [STARTED] at 0x79dff6d26160>
2045-05-10 11:46:04,394 DEBUG connection polled: <psycopg_c.pq.PGconn [MADE] at 0x79dff6d26160>
2045-05-10 11:46:04,394 DEBUG connection polled: <psycopg_c.pq.PGconn [SSL_STARTUP] at 0x79dff6d26160>
2045-05-10 11:46:04,411 DEBUG connection polled: <psycopg_c.pq.PGconn [SSL_STARTUP] at 0x79dff6d26160>
2045-05-10 11:46:04,413 DEBUG connection polled: <psycopg_c.pq.PGconn [SSL_STARTUP] at 0x79dff6d26160>
2045-05-10 11:46:04,423 DEBUG connection polled: <psycopg_c.pq.PGconn [MADE] at 0x79dff6d26160>
2045-05-10 11:46:04,424 DEBUG connection polled: <psycopg_c.pq.PGconn [AWAITING_RESPONSE] at 0x79dff6d26160>
2045-05-10 11:46:04,426 DEBUG connection polled: <psycopg_c.pq.PGconn [IDLE] (host=localhost database=piro) at 0x79dff6d26160>
2045-05-10 11:46:04,426 DEBUG connection succeeded: host: 'localhost', port: None, hostaddr: '127.0.0.1'

```

--------------------------------

### isolation_level

Source: https://www.psycopg.org/psycopg3/docs/api/connections.html

The isolation level of new transactions started on the connection. None means use the server default.

```APIDOC
## isolation_level

### Description
The isolation level of the new transactions started on the connection. `None` means use the default set in the `default_transaction_isolation` configuration parameter of the server.

### Method
Not applicable (Python property)

### Signature
`isolation_level` (property)
```

--------------------------------

### CREATE EXTENSION hstore

Source: https://www.psycopg.org/psycopg3/docs/basic/pgtypes.html

Loads the hstore extension in a PostgreSQL database, required before using the hstore data type.

```sql
=# CREATE EXTENSION hstore;

```

--------------------------------

### transaction

Source: https://www.psycopg.org/psycopg3/docs/api/connections.html

Starts a context block with a new transaction or nested transaction. Supports savepoints and forced rollback.

```APIDOC
## transaction

### Description
Start a context block with a new transaction or nested transaction. Can be used with a savepoint name and forced rollback.

### Method
Not applicable (Python context manager)

### Signature
`transaction(savepoint_name: str | None = None, force_rollback: bool = False) -> Iterator[Transaction]`

### Parameters
- **savepoint_name** (`str | None`) - Optional - Name of the savepoint used to manage a nested transaction. If `None`, one will be chosen automatically.
- **force_rollback** (`bool`) - Optional - Roll back the transaction at the end of the block even if there were no error (e.g. to try a no-op process).

### Return type
- `Transaction`

### Usage
```
with conn.transaction():
    ...

with conn.transaction() as tx:
    ...
```

### Notes
Inside a transaction block it will not be possible to call `commit()` or `rollback()`. See Transaction contexts for details.
```

--------------------------------

### Use conn.pipeline() context manager

Source: https://www.psycopg.org/psycopg3/docs/api/connections.html

Use this context manager to switch the connection into pipeline mode. At the end of the block, a synchronization point is established and the connection returns to normal mode. You can call it recursively from within a pipeline block; innermost blocks establish a synchronization point on exit, but pipeline mode is kept until the outermost block exits.

```python
with conn.pipeline() as p:
    ...

```

--------------------------------

### Register TypeInfo and custom Loader for a PostgreSQL type

Source: https://www.psycopg.org/psycopg3/docs/api/types.html

Shows how to fetch and register TypeInfo for a custom type, then register a custom Loader to convert the type to Python objects. After registering the loader, queries return lists of MyType instances instead of strings.

```python
from psycopg.adapt import Loader
from psycopg.types import TypeInfo

t = TypeInfo.fetch(conn, "mytype")
t.register(conn)

for record in conn.execute("SELECT mytypearray FROM mytable"):
    # records will return lists of "mytype" as string

class MyTypeLoader(Loader):
    def load(self, data):
        # parse the data and return a MyType instance

conn.adapters.register_loader("mytype", MyTypeLoader)

for record in conn.execute("SELECT mytypearray FROM mytable"):
    # records will return lists of MyType instances

```

--------------------------------

### Await TypeInfo.fetch() on an async connection

Source: https://www.psycopg.org/psycopg3/docs/api/types.html

For async connections, TypeInfo.fetch() is a coroutine and must be awaited. This example shows the await syntax.

```python
t = await TypeInfo.fetch(aconn, "mytype")

```

--------------------------------

### COPY with parameters

Source: https://www.psycopg.org/psycopg3/docs/basic/copy.html

Pass parameters to copy() like in execute(), using %s placeholders. This example limits the number of records copied to three.

```python
with cur.copy("COPY (SELECT * FROM table_name LIMIT %s) TO STDOUT", (3,)) as copy:
    # expect no more than three records

```
Tokens:
Raw