asyncpg

https://magicstack.github.io/asyncpg/
Admin
asyncpg is an efficient database interface library designed specifically for PostgreSQL and Python...
Tokens:101,839
Snippets:638
Trust Score:9.7
Update:5 days ago
Show doc for...
Tokens:
Raw
### Get server version example

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

Example showing how to retrieve the server version using con.get_server_version().

```pycon
>>> con.get_server_version()
ServerVersion(major=9, minor=6, micro=1,
              releaselevel='final', serial=0)
```

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

### 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]'

```

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

### 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

```

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

### 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())
```

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

### get_statusmsg() example

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

Shows how to get the status message of an executed command. After fetching, the status message is asserted to be 'CREATE TABLE'.

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

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

### Prepare and execute a query with asyncpg

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

Shows how to prepare a query using Connection.prepare() and execute it with fetchval(). The example prints the results of 2^10 and 2^20.

```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
```

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

### Execute SQL command with arguments

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

Example showing con.execute() with query arguments, executed as a prepared statement.

```pycon
>>> await con.execute('''
...     INSERT INTO mytab (a) VALUES ($1), ($2)
... ''', 10, 20)
INSERT 0 2
```

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

### Transaction.start()

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

Starts the transaction.

```APIDOC
## Transaction.start()

### Description
Starts the transaction.

### Method
async function

### Endpoint
Transaction.start()

### Parameters
None.

### Response
None.
```

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

### 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
```

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

### 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 comment.

```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'))
```

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

### get_query() example

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

Demonstrates retrieving the query text of a prepared statement. The assertion confirms the returned string matches the original query.

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

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

### fetchmany usage example

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

Example from the fetchmany docstring showing how to insert multiple rows and return the generated values. The query uses $1 and $2 placeholders and the args are a list of tuples. The output shows the returned Record objects.

```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',)>]
```

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

### 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

```

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

### 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 .

```

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

### 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)
```

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

### 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)
```

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

### Using create_pool with async with

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

Use this pattern to create a connection pool and automatically close it when the block exits. The example sets a command timeout of 60 seconds and fetches a simple query.

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

```

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

### Execute many SQL commands with executemany

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

Example demonstrating con.executemany() executing a command for each sequence of arguments.

```pycon
>>> await con.executemany('''
...     INSERT INTO mytab (a) VALUES ($1, $2, $3);
... ''', [(1, 2, 3), (4, 5, 6)])
```

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

### 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
```

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

### 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 output shows a list of Record objects with fields like typname and typnamespace.

```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 ...
```

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

### copy_from_query example

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

Example usage of the copy_from_query method, copying the results of a query to a CSV file. The output shows the status string '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'
```

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

### copy_from_table example

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

Example usage of the copy_from_table method, copying data from a table to a CSV file. The output shows the status string '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'
```

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

### asyncpg copy_records_to_table example with async iterable

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

Example usage of the copy_records_to_table method with an asynchronous record iterable. Output shows the status string 'COPY 100'.

```pycon
>>> import asyncpg
>>> import asyncio
>>> async def run():
...     con = await asyncpg.connect(user='postgres')
...     async def record_gen(size):
...         for i in range(size):
...             yield (i,)
...     result = await con.copy_records_to_table(
...         'mytable', records=record_gen(100))
...     print(result)
...
>>> asyncio.run(run())
'COPY 100'
```

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

### connect()

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

Establishes a connection to a PostgreSQL database.

```APIDOC
## connect()

### Description
Establishes a connection to a PostgreSQL database.

### Method
async function

### Endpoint
asyncpg.connect()

### Parameters
- **dsn** (str) - Required - Connection string or DSN.
- **host** (str) - Optional - Database host.
- **port** (int) - Optional - Database port.
- **user** (str) - Optional - Database user.
- **password** (str) - Optional - Database password.
- **database** (str) - Optional - Database name.

### Response
Returns a Connection object.
```

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

### asyncpg copy_records_to_table example with list of records

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

Example usage of the copy_records_to_table method to copy a list of records to a table. Output shows the status string 'COPY 2'.

```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.connection: _format_copy_opts method - build COPY options string

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

Builds a parenthesized list of COPY options (e.g., FORCE_QUOTE, DELIMITER) from keyword arguments. Handles special formatting for force_quote, force_not_null, force_null, oids, freeze, and header. Returns an empty string if no options are provided.

```python
    def _format_copy_opts(self, *, format=None, oids=None, freeze=None,
                          delimiter=None, null=None, header=None, quote=None,
                          escape=None, force_quote=None, force_not_null=None,
                          force_null=None, encoding=None):
        kwargs = dict(locals())
        kwargs.pop('self')
        opts = []

        if force_quote is not None and isinstance(force_quote, bool):
            kwargs.pop('force_quote')
            if force_quote:
                opts.append('FORCE_QUOTE *')

        for k, v in kwargs.items():
            if v is not None:
                if k in ('force_not_null', 'force_null', 'force_quote'):
                    v = '(' + ', '.join(utils._quote_ident(c) for c in v) + ')'
                elif k in ('oids', 'freeze', 'header'):
                    v = str(v)
                else:
                    v = utils._quote_literal(v)

                opts.append('{} {}'.format(k.upper(), v))

        if opts:
            return '(' + ', '.join(opts) + ')'
        else:
            return ''
```

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

### 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 copy_to_table example

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

Example usage of the copy_to_table method to copy data from a file to a table. Requires PostgreSQL 12+ for the 'where' parameter. Output shows the status string 'COPY 140000'.

```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'
```

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

### Set custom codec for interval type

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

Example usage of set_type_codec with a custom encoder and decoder for the interval data type, using the 'tuple' format. The encoder converts a relativedelta to a tuple, and the decoder converts the tuple back. The example connects to a PostgreSQL database, sets the codec, and fetches an 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
```

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

### 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 with CA bundle, client certificate, and hostname verification. Equivalent to sslmode=verify-full with sslcert, sslkey, and sslrootcert DSN parameters. Set check_hostname to False for sslmode=verify-ca.

```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())
```

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

### 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)
```

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

### asyncpg.connect()

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

Establishes a connection to a PostgreSQL database and 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. 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.

### 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 a 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 attributes of a prepared statement

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

Demonstrates get_attributes() on a prepared statement, printing the column attributes of the result.

```python
st = await self.con.prepare('''
    SELECT typname, typnamespace FROM pg_type
''')
print(st.get_attributes())

# Will print:
#   (Attribute(
#       name='typname',
#       type=Type(oid=19, name='name', kind='scalar',
#                 schema='pg_catalog')),
#    Attribute(
#       name='typnamespace',
#       type=Type(oid=26, name='oid', kind='scalar',
#                 schema='pg_catalog')))
```

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

### 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
```

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

### 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)
```

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

### asyncpg.connect() Parameters

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

Parameters for the asyncpg.connect() function, including max_cacheable_statement_size, command_timeout, ssl, direct_tls, server_settings, and connection_class.

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

### Description
Parameters for establishing a connection to a PostgreSQL database using asyncpg.

### Parameters

#### max_cacheable_statement_size
- **Type**: int
- **Default**: 15KiB
- **Description**: The maximum size of a statement that can be cached. Pass `0` to allow all statements to be cached regardless of size.

#### command_timeout
- **Type**: float
- **Default**: None (no timeout)
- **Description**: The default timeout for operations on this connection.

#### ssl
- **Type**: bool, str, or ssl.SSLContext
- **Default**: 'prefer'
- **Description**: SSL mode for the connection. Can be `True`, `False`, an `ssl.SSLContext` instance, or one of the strings: 'disable', 'prefer', 'allow', 'require', 'verify-ca', 'verify-full'. Ignored for Unix domain socket communication.

#### direct_tls
- **Type**: bool
- **Default**: False
- **Description**: 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.

#### server_settings
- **Type**: dict
- **Description**: An optional dict of server runtime parameters. Refer to PostgreSQL documentation for a list of supported options.

#### connection_class
- **Type**: type
- **Description**: Class of the returned connection object. Must be a subclass of `asyncpg.connection.Connection`.

### SSL Configuration Examples

#### Example 1: verify-full equivalent
```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 2: require equivalent
```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())
```
```

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

### Execute multiple SQL commands without arguments

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

Example demonstrating con.execute() executing multiple SQL commands when no arguments are provided.

```pycon
>>> await con.execute('''
...     CREATE TABLE mytab (a int);
...     INSERT INTO mytab (a) VALUES (100), (200), (300);
... ''')
INSERT 0 3
```

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

### EXPLAIN ANALYZE with transaction rollback

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

Shows how to use EXPLAIN ANALYZE without affecting data by wrapping it in a transaction and rolling back. This is recommended for INSERT, UPDATE, DELETE, MERGE, CREATE TABLE AS, or EXECUTE statements.

```python
BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;
```

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

### Connection.prepare()

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

Prepares a statement for repeated execution.

```APIDOC
## Connection.prepare()

### Description
Prepares a statement for repeated execution.

### Method
async function

### Endpoint
Connection.prepare(query)

### Parameters
- **query** (str) - Required - SQL query to prepare.

### Response
Returns a PreparedStatement object.
```

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

### 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
```

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

### Using Connection.transaction() with async with

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

Shows the recommended way to create a transaction using an async with statement. When not in an explicit transaction block, changes are applied immediately (auto-commit).

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

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

### Check INSERT status string

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

Example verifying that a single row was inserted by checking the status string equals 'INSERT 0 1'.

```pycon
>>> result = await con.execute(
...     'INSERT INTO mytab (a) VALUES ($1)', 1)
>>> assert result == 'INSERT 0 1'
```

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

### Manual transaction control

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

Alternative to the async with block: explicitly start, commit, or rollback the transaction. The ellipsis (...) represents the transaction body.

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