hasql 0.7.1

数据库 URL 规范

• 应通过逗号分隔传递多个主机

  • 多主机示例

    • postgresql://db1,db2,db3/

  • 分割结果

    • postgresql://db1:5432/

    • postgresql://db2:5432/

    • postgresql://db3:5432/

• 可以在主机名之后传递每个主机的非默认端口号。例如。

  • 多主机示例

    • postgresql://db1:1234,db2:5678,db3/

  • 分割结果

    • postgresql://db1:1234/

    • postgresql://db2:5678/

    • postgresql://db3:5432/

• 所有主机非默认端口的特殊情况

  • 多主机示例

    • postgresql://db1,db2,db3:6432/

  • 分割结果

    • postgresql://db1:6432/

    • postgresql://db2:6432/

    • postgresql://db3:6432/

对于 aiopg 或 aiopg.sa

aiopg 必须作为依赖项安装。

使用 aiopg 的代码示例

from hasql.aiopg import PoolManager

hosts = ",".join([
    "master-host:5432",
    "replica-host-1:5432",
    "replica-host-2:5432",
])

multihost_dsn = f"postgresql://user:password@{hosts}/dbname"

async def create_pool(dsn) -> PoolManager:
    pool = PoolManager(multihost_dsn)

    # Waiting for 1 master and 1 replica will be available
    await pool.ready(masters_count=1, replicas_count=1)
    return pool

使用 aiopg.sa 的代码示例

from hasql.aiopg_sa import PoolManager

hosts = ",".join([
    "master-host:5432",
    "replica-host-1:5432",
    "replica-host-2:5432",
])

multihost_dsn = f"postgresql://user:password@{hosts}/dbname"

async def create_pool(dsn) -> PoolManager:
    pool = PoolManager(multihost_dsn)

    # Waiting for 1 master and 1 replica will be available
    await pool.ready(masters_count=1, replicas_count=1)
    return pool

对于 asyncpg

asyncpg 必须作为依赖项安装

from hasql.asyncpg import PoolManager

hosts = ",".join([
    "master-host:5432",
    "replica-host-1:5432",
    "replica-host-2:5432",
])

multihost_dsn = f"postgresql://user:password@{hosts}/dbname"

async def create_pool(dsn) -> PoolManager:
    pool = PoolManager(multihost_dsn)

    # Waiting for 1 master and 1 replica will be available
    await pool.ready(masters_count=1, replicas_count=1)
    return pool

对于 sqlalchemy

sqlalchemy[asyncio] & asyncpg 必须作为依赖项安装

from hasql.asyncsqlalchemy import PoolManager

hosts = ",".join([
    "master-host:5432",
    "replica-host-1:5432",
    "replica-host-2:5432",
])

multihost_dsn = f"postgresql://user:password@{hosts}/dbname"


async def create_pool(dsn) -> PoolManager:
    pool = PoolManager(
        multihost_dsn,

        # Use master for acquire_replica, if no replicas available
        fallback_master=True,

        # You can pass pool-specific options
        pool_factory_kwargs=dict(
            pool_size=10,
            max_overflow=5
        )
    )

    # Waiting for 1 master and 1 replica will be available
    await pool.ready(masters_count=1, replicas_count=1)
    return pool

对于 asyncpgsa

asyncpgsa 必须作为依赖项安装

from hasql.asyncpgsa import PoolManager

hosts = ",".join([
    "master-host:5432",
    "replica-host-1:5432",
    "replica-host-2:5432",
])

multihost_dsn = f"postgresql://user:password@{hosts}/dbname"

async def create_pool(dsn) -> PoolManager:
    pool = PoolManager(multihost_dsn)

    # Waiting for 1 master and 1 replica will be available
    await asyncio.gather(
        pool.wait_masters_ready(1),
        pool.wait_replicas_ready(1)
    )
    return pool

对于 psycopg3

psycopg3 必须作为依赖项安装（包名为 psycopg）

from hasql.psycopg3 import PoolManager


hosts = ",".join([
    "master-host:5432",
    "replica-host-1:5432",
    "replica-host-2:5432",
])
multihost_dsn = f"postgresql://user:password@{hosts}/dbname"

async def create_pool(dsn) -> PoolManager:
    pool = PoolManager(multihost_dsn)

    # Waiting for 1 master and 1 replica will be available
    await pool.ready(masters_count=1, replicas_count=1)
    return pool

获取主连接

async def do_something():
    pool = await create_pool(multihost_dsn)
    async with pool.acquire(read_only=False) as connection:
        ...

或

async def do_something():
    pool = await create_pool(multihost_dsn)
    async with pool.acquire_master() as connection:
        ...

获取副本连接

async def do_something():
    pool = await create_pool(multihost_dsn)
    async with pool.acquire(read_only=True) as connection:
        ...

或

async def do_something():
    pool = await create_pool(multihost_dsn)
    async with pool.acquire_replica() as connection:
        ...

不使用上下文管理器（实际上不推荐）

async def do_something():
    pool = await create_pool(multihost_dsn)
    connection = await pool.acquire(read_only=False)
    await pool.release(connection)

或更有用

async def do_something():
    pool = await create_pool(multihost_dsn)
    try:
        connection = await pool.acquire(read_only=False)
    finally:
        await pool.release(connection)