asyncpg

https://magicstack.github.io/asyncpg/current/
Admin
asyncpg is a database interface library designed specifically for PostgreSQL and Python/asyncio,...
Tokens:110,713
Snippets:652
Trust Score:8.3
Update:5 days ago
Show doc for...
Tokens:
Raw
### Install asyncpg with pip

Source: https://magicstack.github.io/asyncpg/current/installation.html

Install asyncpg using pip. This is the recommended method when not using GSSAPI/SSPI authentication.

```bash
$ pip install asyncpg

```

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

### get_parameters() example

Source: https://magicstack.github.io/asyncpg/current/api/index.html

Shows how to retrieve the parameter types of a prepared statement. The output is a tuple of asyncpg.types.Type objects, as shown in the printed example.

```python
stmt = await connection.prepare('SELECT ($1::int, $2::text)')
print(stmt.get_parameters())

# Will print:
#   (Type(oid=23, name='int4', kind='scalar', schema='pg_catalog'),
#    Type(oid=25, name='text', kind='scalar', schema='pg_catalog'))
```

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

### Install asyncpg with GSSAPI/SSPI support

Source: https://magicstack.github.io/asyncpg/current/installation.html

Install asyncpg with GSSAPI/SSPI authentication support. This installs SSPI on Windows and GSSAPI on non-Windows platforms.

```bash
$ pip install 'asyncpg[gssauth]'

```

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

### get_query() example

Source: https://magicstack.github.io/asyncpg/current/api/index.html

Shows how to retrieve the text of the query for a prepared statement. The example asserts that the returned query matches the original string.

```python
stmt = await connection.prepare('SELECT $1::int')
assert stmt.get_query() == "SELECT $1::int"
```

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

### fetchmany method implementation with example

Source: https://magicstack.github.io/asyncpg/current/_modules/asyncpg/connection.html

Runs a query for each sequence of arguments and returns a list of Record instances. The example shows usage with an INSERT ... RETURNING query and the resulting list of records.

```python
    async def fetchmany(
        self,
        query,
        args,
        *,
        timeout: typing.Optional[float]=None,
        record_class=None,
    ):
        """Run a query for each sequence of arguments in *args*
        and return the results as a list of :class:`Record`.

        :param query:
            Query to execute.
        :param args:
            An iterable containing sequences of arguments for the query.
        :param float timeout:
            Optional timeout value in seconds.
        :param type record_class:
            If specified, the class to use for records returned by this method.
            Must be a subclass of :class:`~asyncpg.Record`.  If not specified,
            a per-connection *record_class* is used.

        :return list:
            A list of :class:`~asyncpg.Record` instances.  If specified, the
            actual type of list elements would be *record_class*.

        Example:

        .. code-block:: pycon

            >>> rows = await con.fetchmany('''
            ...         INSERT INTO mytab (a, b) VALUES ($1, $2) RETURNING a;
            ...     ''', [('x', 1), ('y', 2), ('z', 3)])
            >>> rows
            [<Record row=('x',)>, <Record row=('y',)>, <Record row=('z',)>]

        .. versionadded:: 0.30.0
        """
        self._check_open()
        return await self._executemany(
            query, args, timeout, return_rows=True, record_class=record_class
        )
```

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

### Basic asyncpg connection and query example

Source: https://magicstack.github.io/asyncpg/current/usage.html

Establishes a connection to a PostgreSQL database, creates a table, inserts a record, fetches a row, and closes the connection. Uses the native PostgreSQL $n argument syntax. The example is run with asyncio.run().

```python
import asyncio
import asyncpg
import datetime

async def main():
    # Establish a connection to an existing database named "test"
    # as a "postgres" user.
    conn = await asyncpg.connect('postgresql://postgres@localhost/test')
    # Execute a statement to create a new table.
    await conn.execute('''
        CREATE TABLE users(
            id serial PRIMARY KEY,
            name text,
            dob date
        )
    ''')

    # Insert a record into the created table.
    await conn.execute('''
        INSERT INTO users(name, dob) VALUES($1, $2)
    ''', 'Bob', datetime.date(1984, 3, 1))

    # Select a row from the table.
    row = await conn.fetchrow(
        'SELECT * FROM users WHERE name = $1', 'Bob')
    # *row* now contains
    # asyncpg.Record(id=1, name='Bob', dob=datetime.date(1984, 3, 1))

    # Close the connection.
    await conn.close()

asyncio.run(main())
```

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

### fetchmany example

Source: https://magicstack.github.io/asyncpg/current/api/index.html

Shows how to use the fetchmany method to insert multiple rows and return the generated values. The example uses an INSERT ... RETURNING query with a list of argument tuples.

```python
>>> rows = await con.fetchmany('''
...         INSERT INTO mytab (a, b) VALUES ($1, $2) RETURNING a;
...     ''', [('x', 1), ('y', 2), ('z', 3)])
>>> rows
[<Record row=('x',)>, <Record row=('y',)>, <Record row=('z',)>]
```

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

### get_statusmsg() example

Source: https://magicstack.github.io/asyncpg/current/api/index.html

Shows how to retrieve the status message of an executed command. The example asserts that the status message is 'CREATE TABLE' after executing a CREATE TABLE statement.

```python
stmt = await connection.prepare('CREATE TABLE mytab (a int)')
await stmt.fetch()
assert stmt.get_statusmsg() == "CREATE TABLE"
```

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

### Start transaction or savepoint

Source: https://magicstack.github.io/asyncpg/current/_modules/asyncpg/transaction.html

Builds and executes the BEGIN or SAVEPOINT query based on nested state and isolation level. Sets state to STARTED on success or FAILED on exception.

```python
        if self._nested:
            self._id = con._get_unique_id('savepoint')
            query = 'SAVEPOINT {};'.format(self._id)
        else:
            query = 'BEGIN'
            if self._isolation == 'read_committed':
                query += ' ISOLATION LEVEL READ COMMITTED'
            elif self._isolation == 'read_uncommitted':
                query += ' ISOLATION LEVEL READ UNCOMMITTED'
            elif self._isolation == 'repeatable_read':
                query += ' ISOLATION LEVEL REPEATABLE READ'
            elif self._isolation == 'serializable':
                query += ' ISOLATION LEVEL SERIALIZABLE'
            if self._readonly:
                query += ' READ ONLY'
            if self._deferrable:
                query += ' DEFERRABLE'
            query += ';'

        try:
            await self._connection.execute(query)
        except BaseException:
            self._state = TransactionState.FAILED
            raise
        else:
            self._state = TransactionState.STARTED
```

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

### asyncpg copy_to_table example

Source: https://magicstack.github.io/asyncpg/current/_modules/asyncpg/connection.html

Example of using the copy_to_table method to copy data from a file into a table. Requires PostgreSQL 12+ for the 'where' parameter. Returns the status string of the COPY command.

```pycon
>>> import asyncpg
>>> import asyncio
>>> async def run():
...     con = await asyncpg.connect(user='postgres')
...     result = await con.copy_to_table(
...         'mytable', source='datafile.tbl')
...     print(result)
...
>>> asyncio.run(run())
'COPY 140000'
```

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

### Log queries with query_logger context manager

Source: https://magicstack.github.io/asyncpg/current/_modules/asyncpg/connection.html

This example demonstrates using the query_logger context manager to capture executed queries. The callback receives a LoggedQuery record, and the example shows how to collect query strings. The output shows the list of logged queries.

```python
>>> class QuerySaver:
        def __init__(self):
            self.queries = []
        def __call__(self, record):
            self.queries.append(record.query)
>>> with con.query_logger(QuerySaver()):
>>>     await con.execute("SELECT 1")
>>> print(log.queries)
['SELECT 1']
```

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

### Connect and fetch records with asyncpg

Source: https://magicstack.github.io/asyncpg/current/_modules/asyncpg/connection.html

Example usage of asyncpg.connect() to establish a connection and fetch all rows from the pg_type table. The example runs an async function using asyncio.run() and prints the resulting records.

```pycon
>>> import asyncpg
>>> import asyncio
>>> async def run():
...     con = await asyncpg.connect(user='postgres')
...     types = await con.fetch('SELECT * FROM pg_type')
...     print(types)
...
>>> asyncio.run(run())
[<Record typname='bool' typnamespace=11 ...
```

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

### Build asyncpg from source

Source: https://magicstack.github.io/asyncpg/current/installation.html

Install asyncpg from a Git checkout in editable mode. Requires a cloned repo with submodules, a C compiler, and CPython headers.

```bash
$ pip install -e .

```

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

### Create and use a connection pool with async with

Source: https://magicstack.github.io/asyncpg/current/api/index.html

Use create_pool in an async with block to automatically manage the pool lifecycle. The example sets user and command_timeout, then fetches a query.

```python
async with asyncpg.create_pool(user='postgres',
                               command_timeout=60) as pool:
    await pool.fetch('SELECT 1')

```

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

### asyncpg copy_records_to_table example

Source: https://magicstack.github.io/asyncpg/current/_modules/asyncpg/connection.html

Example of using the copy_records_to_table method to copy a list of records into a table. Returns the status string of the COPY command.

```pycon
>>> import asyncpg
>>> import asyncio
>>> async def run():
...     con = await asyncpg.connect(user='postgres')
...     result = await con.copy_records_to_table(
...         'mytable', records=[
...             (1, 'foo', 'bar'),
...             (2, 'ham', 'spam')])
...     print(result)
...
>>> asyncio.run(run())
'COPY 2'
```

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

### asyncpg copy_from_table example

Source: https://magicstack.github.io/asyncpg/current/_modules/asyncpg/connection.html

Example usage of Connection.copy_from_table to copy a table to a CSV file. Requires a running PostgreSQL server and appropriate credentials. Outputs the COPY status string, e.g., 'COPY 100'.

```pycon
>>> import asyncpg
>>> import asyncio
>>> async def run():
...     con = await asyncpg.connect(user='postgres')
...     result = await con.copy_from_table(
...         'mytable', columns=('foo', 'bar'),
...         output='file.csv', format='csv')
...     print(result)
...
>>> asyncio.run(run())
'COPY 100'
```

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

### Pool.acquire usage example

Source: https://magicstack.github.io/asyncpg/current/_modules/asyncpg/pool.html

Shows the recommended pattern for acquiring and releasing a connection from the pool using a try/finally block to ensure release.

```python
con = await pool.acquire()
try:
    await con.execute(...)
finally:
    await pool.release(con)
```

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

### Run asyncpg tests

Source: https://magicstack.github.io/asyncpg/current/installation.html

Execute the asyncpg test suite. Requires PostgreSQL to be installed.

```bash
$ python setup.py test

```

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

### Prepare and execute a statement with asyncpg

Source: https://magicstack.github.io/asyncpg/current/api/index.html

Shows how to prepare a statement using Connection.prepare() and execute it multiple times with fetchval(). The example prints the results of 2^10 and 2^20. Note that asyncpg maintains an LRU cache for queries executed via fetch(), fetchrow(), or fetchval().

```python
>>> import asyncpg, asyncio
>>> async def run():
...     conn = await asyncpg.connect()
...     stmt = await conn.prepare('''SELECT 2 ^ $1''')
...     print(await stmt.fetchval(10))
...     print(await stmt.fetchval(20))
...
>>> asyncio.run(run())
1024.0
1048576.0
```

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

### asyncpg.connect() parameters

Source: https://magicstack.github.io/asyncpg/current/api/index.html

Describes the parameters for the asyncpg.connect() function, including max_cacheable_statement_size, command_timeout, and ssl. Provides examples of programmatic SSL context configuration.

```APIDOC
## asyncpg.connect() parameters

### Description
Parameters for the asyncpg.connect() function, including statement cache size, command timeout, and SSL configuration.

### Parameters
- **max_cacheable_statement_size** (_int_) – The maximum size of a statement that can be cached (15KiB by default). Pass `0` to allow all statements to be cached regardless of their size.
- **command_timeout** (_float_) – The default timeout for operations on this connection (the default is `None`: no timeout).
- **ssl** – Pass `True` or an ssl.SSLContext instance to require an SSL connection. If `True`, a default SSL context returned by ssl.create_default_context() will be used. The value can also be one of the following strings:
  - `'disable'` - SSL is disabled (equivalent to `False`)
  - `'prefer'` - try SSL first, fallback to non-SSL connection if SSL connection fails
  - `'allow'` - try without SSL first, then retry with SSL if the first attempt fails.
  - `'require'` - only try an SSL connection. Certificate verification errors are ignored
  - `'verify-ca'` - only try an SSL connection, and verify that the server certificate is issued by a trusted certificate authority (CA)
  - `'verify-full'` - only try an SSL connection, verify that the server certificate is issued by a trusted CA and that the requested server host name matches that in the certificate.
  The default is `'prefer'`: try an SSL connection and fallback to non-SSL connection if that fails.
  With `'allow'` and `'prefer'`, a server error before `AuthenticationOk` permits one retry using the other transport.
  Errors in response to SSLRequest, timeouts, cancellation, client-side authentication errors, and errors after `AuthenticationOk` do not trigger transport retries. A server reporting that it cannot accept connections yet (SQLSTATE `57P03`) before `AuthenticationOk` causes asyncpg to try the next host.
  Note: _ssl_ is ignored for Unix domain socket communication.

### Example: SSL context equivalent to sslmode=verify-full&sslcert=..&sslkey=..&sslrootcert=..
```python
>>> import asyncpg
>>> import asyncio
>>> import ssl
>>> async def main():
...     # Load CA bundle for server certificate verification,
...     # equivalent to sslrootcert= in DSN.
...     sslctx = ssl.create_default_context(
...         ssl.Purpose.SERVER_AUTH,
...         cafile="path/to/ca_bundle.pem")
...     # If True, equivalent to sslmode=verify-full, if False:
...     # sslmode=verify-ca.
...     sslctx.check_hostname = True
...     # Load client certificate and private key for client
...     # authentication, equivalent to sslcert= and sslkey= in
...     # DSN.
...     sslctx.load_cert_chain(
...         "path/to/client.cert",
...         keyfile="path/to/client.key",
...     )
...     con = await asyncpg.connect(user='postgres', ssl=sslctx)
...     await con.close()
>>> asyncio.run(main())
```

### Example: SSL context equivalent to sslmode=require (no server certificate or host verification)
```python
>>> import asyncpg
>>> import asyncio
>>> import ssl
>>> async def main():
...     sslctx = ssl.create_default_context(
...         ssl.Purpose.SERVER_AUTH)
...     sslctx.check_hostname = False
...     sslctx.verify_mode = ssl.CERT_NONE
...     con = await asyncpg.connect(user='postgres', ssl=sslctx)
...     await con.close()
>>> asyncio.run(main())
```
```

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

### Web service using asyncpg.create_pool() and aiohttp

Source: https://magicstack.github.io/asyncpg/current/usage.html

Complete example of a simple web service that computes the requested power of two. It creates a connection pool with asyncpg.create_pool(), acquires connections from the pool, and uses transactions. The service is run with aiohttp's web.run_app().

```python
import asyncio
import asyncpg
from aiohttp import web


async def handle(request):
    """Handle incoming requests."""
    pool = request.app['pool']
    power = int(request.match_info.get('power', 10))

    # Take a connection from the pool.
    async with pool.acquire() as connection:
        # Open a transaction.
        async with connection.transaction():
            # Run the query passing the request argument.
            result = await connection.fetchval('select 2 ^ $1', power)
            return web.Response(
                text="2 ^ {} is {}".format(power, result))


async def init_db(app):
    """Initialize a connection pool."""
     app['pool'] = await asyncpg.create_pool(database='postgres',
                                             user='postgres')
     yield
     await app['pool'].close()


def init_app():
    """Initialize the application server."""
    app = web.Application()
    # Create a database context
    app.cleanup_ctx.append(init_db)
    # Configure service routes
    app.router.add_route('GET', '/{power:\d+}', handle)
    app.router.add_route('GET', '/', handle)
    return app


app = init_app()
web.run_app(app)
```

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

### Pool.acquire method usage with async with

Source: https://magicstack.github.io/asyncpg/current/_modules/asyncpg/pool.html

Shows how to use the acquire method of the pool in an async with block to get a connection and execute a query. The method can also be used in an await expression.

```python
async with pool.acquire() as con:
    await con.execute(...)
```

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

### Example PostgreSQL connection URI with IPv6 address

Source: https://magicstack.github.io/asyncpg/current/api/index.html

Shows a valid libpq connection URI format for asyncpg, including an IPv6 address enclosed in square brackets and a percent-encoded zone index. All URI components must be properly quoted with urllib.parse.quote_plus().

```text
postgres://dbuser@[fe80::1ff:fe23:4567:890a%25eth0]/dbname
```

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

### SSL context configuration equivalent to sslmode=verify-full

Source: https://magicstack.github.io/asyncpg/current/_modules/asyncpg/connection.html

Configures an SSL context for asyncpg.connect that verifies the server certificate and host name, equivalent to sslmode=verify-full with sslcert, sslkey, and sslrootcert. Loads a CA bundle, enables hostname checking, and loads a client certificate and key.

```pycon
>>> import asyncpg
>>> import asyncio
>>> import ssl
>>> async def main():
...     # Load CA bundle for server certificate verification,
...     # equivalent to sslrootcert= in DSN.
...     sslctx = ssl.create_default_context(
...         ssl.Purpose.SERVER_AUTH,
...         cafile="path/to/ca_bundle.pem")
...     # If True, equivalent to sslmode=verify-full, if False:
...     # sslmode=verify-ca.
...     sslctx.check_hostname = True
...     # Load client certificate and private key for client
...     # authentication, equivalent to sslcert= and sslkey= in
...     # DSN.
...     sslctx.load_cert_chain(
...         "path/to/client.cert",
...         keyfile="path/to/client.key",
...     )
...     con = await asyncpg.connect(user='postgres', ssl=sslctx)
...     await con.close()
>>> asyncio.run(main())
```

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

### Direct await with create_pool (not recommended)

Source: https://magicstack.github.io/asyncpg/current/_modules/asyncpg/pool.html

Create a pool and acquire a connection directly using await. This pattern requires manual release of the connection in a finally block and is not recommended.

```python
pool = await asyncpg.create_pool(user='postgres', command_timeout=60)
con = await pool.acquire()
try:
    await con.fetch('SELECT 1')
finally:
    await pool.release(con)
```

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

### asyncpg.connect()

Source: https://magicstack.github.io/asyncpg/current/api/index.html

Creates a new database connection. Accepts various parameters for SSL, server settings, connection class, record class, and more. Returns a Connection instance.

```APIDOC
## asyncpg.connect()

### Description
Creates a new database connection. Accepts various parameters for SSL, server settings, connection class, record class, and more. Returns a Connection instance.

### Method
connect()

### Parameters
- **direct_tls** (bool) - Optional - Pass `True` to skip PostgreSQL STARTTLS mode and perform a direct SSL connection. Requires `ssl='require'`, `'verify-ca'`, `'verify-full'`, `True`, or an explicit `SSLContext`. PostgreSQL 17+ requires the `postgresql` ALPN protocol for direct SSL connections: asyncpg sets it on the contexts it creates, but an explicit `SSLContext` must set it with `ctx.set_alpn_protocols(['postgresql'])`.
- **server_settings** (dict) - Optional - An optional dict of server runtime parameters. Refer to PostgreSQL documentation for a list of supported options.
- **connection_class** (type) - Optional - Class of the returned connection object. Must be a subclass of `Connection`.
- **record_class** (type) - Optional - If specified, the class to use for records returned by queries on this connection object. Must be a subclass of `Record`.
- **target_session_attrs** (SessionAttribute) - Optional - If specified, check that the host has the correct attribute. Can be one of: `"any"`, `"primary"`, `"standby"`, `"read-write"`, `"read-only"`, `"prefer-standby"`. If not specified, the value parsed from the _dsn_ argument is used, or the value of the `PGTARGETSESSIONATTRS` environment variable, or `"any"` if neither is specified.
- **krbsrvname** (str) - Optional - Kerberos service name to use when authenticating with GSSAPI. This must match the server configuration. Defaults to 'postgres'.
- **gsslib** (str) - Optional - GSS library to use for GSSAPI/SSPI authentication. Can be 'gssapi' or 'sspi'. Defaults to 'sspi' on Windows and 'gssapi' otherwise.

### Returns
A `Connection` instance.

### Example
```
>>> import asyncpg
>>> import asyncio
>>> async def run():
...     con = await asyncpg.connect(user='postgres')
...     types = await con.fetch('SELECT * FROM pg_type')
...     print(types)
...
>>> asyncio.run(run())
[<Record typname='bool' typnamespace=11 ...
```
```

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

### asyncpg copy_from_query example

Source: https://magicstack.github.io/asyncpg/current/_modules/asyncpg/connection.html

Example usage of Connection.copy_from_query to copy query results to a CSV file. The query includes a parameter ($1) and an argument (10). Outputs the COPY status string, e.g., 'COPY 10'.

```pycon
>>> import asyncpg
>>> import asyncio
>>> async def run():
...     con = await asyncpg.connect(user='postgres')
...     result = await con.copy_from_query(
...         'SELECT foo, bar FROM mytable WHERE foo > $1', 10,
...         output='file.csv', format='csv')
...     print(result)
...
>>> asyncio.run(run())
'COPY 10'
```

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

### set_type_codec example with interval type and tuple format

Source: https://magicstack.github.io/asyncpg/current/_modules/asyncpg/connection.html

Example usage of set_type_codec to register a custom encoder/decoder for the interval type using the 'tuple' format. It demonstrates encoding a relativedelta to a tuple and decoding back, then fetching and using the interval value.

```pycon
>>> import asyncpg
>>> import asyncio
>>> import datetime
>>> from dateutil.relativedelta import relativedelta
>>> async def run():
...     con = await asyncpg.connect(user='postgres')
...     def encoder(delta):
...         ndelta = delta.normalized()
...         return (ndelta.years * 12 + ndelta.months,
...                 ndelta.days,
...                 ((ndelta.hours * 3600 +
...                    ndelta.minutes * 60 +
...                    ndelta.seconds) * 1000000 +
...                  ndelta.microseconds))
...     def decoder(tup):
...         return relativedelta(months=tup[0], days=tup[1],
...                              microseconds=tup[2])
...     await con.set_type_codec(
...         'interval', schema='pg_catalog', encoder=encoder,
...         decoder=decoder, format='tuple')
...     result = await con.fetchval(
...         "SELECT '2 years 3 mons 1 day'::interval")
...     print(result)
...     print(datetime.datetime(2002, 1, 1) + result)
...
>>> asyncio.run(run())
relativedelta(years=+2, months=+3, days=+1)
2004-04-02 00:00:00
```

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

### Prepare and execute COPY FROM with binary format

Source: https://magicstack.github.io/asyncpg/current/_modules/asyncpg/connection.html

Builds a COPY FROM STDIN statement with optional WHERE clause and binary format, then executes it via the protocol's copy_in method.

```python
        intro_query = 'SELECT {cols} FROM {tab} LIMIT 1'.format(
            tab=tabname, cols=col_list)

        intro_ps = await self._prepare(intro_query, use_cache=True)

        cond = self._format_copy_where(where)
        opts = '(FORMAT binary)'

        copy_stmt = 'COPY {tab}{cols} FROM STDIN {opts} {cond}'.format(
            tab=tabname, cols=cols, opts=opts, cond=cond)

        return await self._protocol.copy_in(
            copy_stmt, None, None, records, intro_ps._state, timeout)
```

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

### Change composite type attribute and reload schema state

Source: https://magicstack.github.io/asyncpg/current/_modules/asyncpg/connection.html

This example shows how to change a composite type's attribute type from int to text and reload the schema state to update cached type information. It uses a transaction and locks the table to prevent concurrent changes. The example is from the reload_schema_state method documentation.

```python
>>> import asyncpg
>>> import asyncio
>>> async def change_type(con):
...     result = await con.fetch('SELECT id, info FROM tbl')
...     # Change composite's attribute type "int"=>"text"
...     await con.execute('ALTER TYPE custom DROP ATTRIBUTE y')
...     await con.execute('ALTER TYPE custom ADD ATTRIBUTE y text')
...     await con.reload_schema_state()
...     for id_, info in result:
...         new = (info['x'], str(info['y']))
...         await con.execute(
...             'UPDATE tbl SET info=$2 WHERE id=$1', id_, new)
...
>>> async def run():
...     # Initial schema:
...     # CREATE TYPE custom AS (x int, y int);
...     # CREATE TABLE tbl(id int, info custom);
...     con = await asyncpg.connect(user='postgres')
...     async with con.transaction():
...         # Prevent concurrent changes in the table
...         await con.execute('LOCK TABLE tbl')
...         await change_type(con)
...
>>> asyncio.run(run())
```

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

### Transaction.start()

Source: https://magicstack.github.io/asyncpg/current/api/index.html

Enter the transaction or savepoint block.

```APIDOC
## start()

### Description
Enter the transaction or savepoint block.

### Method
N/A (method call)

### Endpoint
N/A

### Parameters
None

### Returns
None

### Example
```python
tr = connection.transaction()
await tr.start()
try:
    ...
except:
    await tr.rollback()
    raise
else:
    await tr.commit()
```
```

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

### Check transaction state

Source: https://magicstack.github.io/asyncpg/current/_modules/asyncpg/transaction.html

Raises InterfaceError if the transaction is not yet started, otherwise delegates to __check_state_base.

```python
    def __check_state(self, opname):
        if self._state is not TransactionState.STARTED:
            if self._state is TransactionState.NEW:
                raise apg_errors.InterfaceError(
                    'cannot {}; the transaction is not yet started'.format(
                        opname))
            self.__check_state_base(opname)
```

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

### Get a ConnectionSettings value

Source: https://magicstack.github.io/asyncpg/current/api/index.html

Shows how to retrieve a setting value from a connection's settings, e.g., client_encoding.

```python
>>> connection.get_settings().client_encoding
'UTF8'
```

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

### asyncpg.connect()

Source: https://magicstack.github.io/asyncpg/current/_modules/asyncpg/connection.html

Establishes a connection to a PostgreSQL database. Returns a Connection instance. Supports various parameters for authentication, SSL, and session attributes.

```APIDOC
## asyncpg.connect()

### Description
Establishes a connection to a PostgreSQL database and returns a Connection instance. Supports various parameters for authentication, SSL, and session attributes.

### Method
connect()

### Parameters
- **record_class** (type) - Optional - The class to use for records returned by queries on this connection object. Must be a subclass of asyncpg.Record.
- **target_session_attrs** (SessionAttribute) - Optional - Check that the host has the correct attribute. Can be one of: "any", "primary", "standby", "read-write", "read-only", "prefer-standby". If not specified, the value parsed from the dsn argument is used, or the value of the PGTARGETSESSIONATTRS environment variable, or "any" if neither is specified.
- **krbsrvname** (str) - Optional - Kerberos service name to use when authenticating with GSSAPI. Defaults to 'postgres'.
- **gsslib** (str) - Optional - GSS library to use for GSSAPI/SSPI authentication. Can be 'gssapi' or 'sspi'. Defaults to 'sspi' on Windows and 'gssapi' otherwise.

### Return Value
A Connection instance.

### Example
```pycon
>>> import asyncpg
>>> import asyncio
>>> async def run():
...     con = await asyncpg.connect(user='postgres')
...     types = await con.fetch('SELECT * FROM pg_type')
...     print(types)
...
>>> asyncio.run(run())
[<Record typname='bool' typnamespace=11 ...
```

### Version History
- **0.10.0**: Added max_cached_statement_use_count parameter.
- **0.11.0**: Removed ability to pass arbitrary keyword arguments to set server settings. Added dedicated parameter server_settings.
- **0.11.0**: Added connection_class parameter.
- **0.16.0**: Added passfile parameter (and support for password files in general).
- **0.18.0**: Added ability to specify multiple hosts in the dsn and host arguments.
- **0.21.0**: The password argument now accepts a callable or an async function.
- **0.22.0**: Added the record_class parameter.
- **0.22.0**: The ssl argument now defaults to 'prefer'.
- **0.24.0**: The sslcert, sslkey, sslrootcert, and sslcrl options are supported in the dsn argument.
- **0.25.0**: The sslpassword, ssl_min_protocol_version, and ssl_max_protocol_version options are supported in the dsn argument.
- **0.25.0**: Default system root CA certificates won't be loaded when specifying a particular sslmode, following the same behavior in libpq.
- **0.25.0**: The sslcert, sslkey, sslrootcert, and sslcrl options in the dsn argument now have consistent default values of files under ~/.postgresql/ as libpq.
- **0.26.0**: Added the direct_tls parameter.
- **0.28.0**: Added the target_session_attrs parameter.
- **0.30.0**: Added the krbsrvname and gsslib parameters.
- **0.31.0**: Added the servicefile and service parameters.
- **0.32.0**: direct_tls=True requires an SSL mode of 'require' or higher, ssl=True, or an explicit SSLContext. Other values ('disable', 'allow', and 'prefer') will raise a ClientConfigurationError. SSL contexts created by asyncpg now set the postgresql ALPN protocol, which PostgreSQL 17+ requires for direct SSL connections.
```

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

### Get initial number of connections in pool

Source: https://magicstack.github.io/asyncpg/current/_modules/asyncpg/pool.html

Returns the initial number of connections in the pool. Added in version 0.32.0.

```python
    def get_init_size(self):
        """Return the initial number of connections in this pool.

        .. versionadded:: 0.32.0
        """
        return self._initsize
```

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

### async prepare(query, *, name=None, timeout=None, record_class=None)

Source: https://magicstack.github.io/asyncpg/current/api/index.html

Creates a prepared statement for the specified query.

```APIDOC
## async prepare(query, *, name=None, timeout=None, record_class=None)

### Description
Creates a prepared statement for the specified query.

### Method
async

### Parameters
- **query** (str) - Required - Query text.
- **name** (str) - Optional - Name for the prepared statement.
- **timeout** (float) - Optional - Timeout value in seconds.
- **record_class** (type) - Optional - Class to use for records returned by this method. Must be a subclass of `Record`.

### Returns
A prepared statement object.
```

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

### Get current number of connections in pool

Source: https://magicstack.github.io/asyncpg/current/_modules/asyncpg/pool.html

Returns the current number of connections in the pool. Added in version 0.25.0.

```python
    def get_size(self):
        """Return the current number of connections in this pool.

        .. versionadded:: 0.25.0
        """
        return sum(h.is_connected() for h in self._holders)
```

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

### Manual transaction control

Source: https://magicstack.github.io/asyncpg/current/api/index.html

Shows how to use transactions without an async with block, by manually calling start(), commit(), and rollback().

```python
tr = connection.transaction()
await tr.start()
try:
    ...
except:
    await tr.rollback()
    raise
else:
    await tr.commit()
```

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

### asyncpg.connect()

Source: https://magicstack.github.io/asyncpg/current/_modules/asyncpg/connection.html

Establishes a connection to a PostgreSQL database. Connection parameters can be specified via a connection URI (dsn) or as keyword arguments, with keyword arguments overriding values from the URI. Returns a new Connection object.

```APIDOC
## asyncpg.connect()

### Description
Establishes a connection to a PostgreSQL database. Connection parameters can be specified via a connection URI (dsn) or as keyword arguments, with keyword arguments overriding values from the URI. Returns a new Connection object.

### Method
connect

### Parameters
#### dsn (string) - Optional
Connection arguments specified as a single string in the libpq connection URI format: `postgres://user:password@host:port/database?option=value`. Recognized options: `host`, `port`, `user`, `database` (or `dbname`), `password`, `passfile`, `sslmode`, `sslcert`, `sslkey`, `sslrootcert`, and `sslcrl`. Unrecognized options are treated as server settings.

#### host (string or sequence of strings) - Optional
Database host address: an IP address, domain name, or absolute path to a directory containing the database server Unix-domain socket. A sequence of addresses can be provided, tried in order. If not specified, defaults to host from dsn, PGHOST environment variable, common Unix socket directories, or "localhost".

#### port (int or sequence of ints) - Optional
Port number to connect to at the server host (or Unix-domain socket file extension). If multiple hosts are specified, can be a sequence of port numbers of the same length, or a single port for all. Defaults to value from dsn, PGPORT environment variable, or 5432.

#### user (string) - Optional
The name of the database role used for authentication. Defaults to value from dsn, PGUSER environment variable, or the operating system user name.

#### database (string) - Optional
The name of the database to connect to. Defaults to value from dsn, PGDATABASE environment variable, or the computed value of the user argument.

#### password (string or callable) - Optional
Password for authentication. If a callable, it is called each time a new connection is established. Defaults to value from dsn or PGPASSWORD environment variable. Use of environment variable is discouraged; prefer passfile.

#### passfile (string) - Optional
The name of the file used to store passwords. Defaults to `~/.pgpass` or `%APPDATA%\postgresql\pgpass.conf` on Windows.

#### service (string) - Optional
The name of the postgres connection service stored in the postgres connection service file.

#### servicefile (string) - Optional
The location of the connection service file used to store connection parameters.

#### loop (asyncio event loop) - Optional
An asyncio event loop instance. If None, the default event loop will be used.

#### timeout (float) - Optional
Connection timeout in seconds.

#### statement_cache_size (int) - Optional
The size of prepared statement LRU cache. Pass 0 to disable the cache.

#### max_cached_statement_lifetime (int) - Optional
The maximum time in seconds a prepared statement will stay in the cache. Pass 0 to allow statements be cached indefinitely.

### Returns
A new `asyncpg.connection.Connection` object.
```

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

### Transaction async context manager

Source: https://magicstack.github.io/asyncpg/current/api/index.html

Start and commit/rollback the transaction or savepoint block automatically when entering and exiting the context manager block.

```APIDOC
## async with c:

### Description
Start and commit/rollback the transaction or savepoint block automatically when entering and exiting the code inside the context manager block.

### Method
N/A (context manager)

### Endpoint
N/A

### Parameters
None

### Example
```python
async with connection.transaction():
    await connection.execute("INSERT INTO mytable VALUES(1, 2, 3)")
```
```

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

### Get current number of idle connections in pool

Source: https://magicstack.github.io/asyncpg/current/_modules/asyncpg/pool.html

Returns the current number of idle connections in the pool. Added in version 0.25.0.

```python
    def get_idle_size(self):
        """Return the current number of idle connections in this pool.

        .. versionadded:: 0.25.0
        """
        return sum(h.is_connected() and h.is_idle() for h in self._holders)
```

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

### Get maximum allowed number of connections in pool

Source: https://magicstack.github.io/asyncpg/current/_modules/asyncpg/pool.html

Returns the maximum allowed number of connections in the pool. Added in version 0.25.0.

```python
    def get_max_size(self):
        """Return the maximum allowed number of connections in this pool.

        .. versionadded:: 0.25.0
        """
        return self._maxsize
```

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

### SSL context configuration equivalent to sslmode=require

Source: https://magicstack.github.io/asyncpg/current/_modules/asyncpg/connection.html

Configures an SSL context for asyncpg.connect that requires SSL but disables certificate and host verification, equivalent to sslmode=require. Sets check_hostname to False and verify_mode to ssl.CERT_NONE.

```pycon
>>> import asyncpg
>>> import asyncio
>>> import ssl
>>> async def main():
...     sslctx = ssl.create_default_context(
...         ssl.Purpose.SERVER_AUTH)
...     sslctx.check_hostname = False
...     sslctx.verify_mode = ssl.CERT_NONE
...     con = await asyncpg.connect(user='postgres', ssl=sslctx)
...     await con.close()
>>> asyncio.run(main())
```

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

### Perform multiple operations on a single connection from a pool

Source: https://magicstack.github.io/asyncpg/current/api/index.html

Acquire a connection from the pool to run multiple operations on the same connection. The example creates a table and then fetches a query.

```python
async with asyncpg.create_pool(user='postgres',
                               command_timeout=60) as pool:
    async with pool.acquire() as con:
        await con.execute('''
           CREATE TABLE names (
              id serial PRIMARY KEY,
              name VARCHAR (255) NOT NULL)
        ''')
        await con.fetch('SELECT 1')

```
Tokens:
Raw