Oven logo

Oven

asyncmy0.2.10

Published

A fast asyncio MySQL driver

pip install asyncmy

Package Downloads

Weekly DownloadsMonthly Downloads

Requires Python

<4.0,>=3.8

Dependencies

    asyncmy - A fast asyncio MySQL/MariaDB driver

    image image pypi ci

    Introduction

    asyncmy is a fast asyncio MySQL/MariaDB driver, which reuse most of pymysql and aiomysql but rewrite core protocol with cython to speedup.

    Features

    • API compatible with aiomysql.
    • Faster by cython.
    • MySQL replication protocol support with asyncio.
    • Tested both MySQL and MariaDB in CI.

    Benchmark

    The result comes from benchmark.

    The device is iMac Pro(2017) i9 3.6GHz 48G and MySQL version is 8.0.26.

    benchmark

    Conclusion

    • There is no doubt that mysqlclient is the fastest MySQL driver.
    • All kinds of drivers have a small gap except select.
    • asyncio could enhance insert.
    • asyncmy performs remarkable when compared to other drivers.

    Install

    pip install asyncmy
    

    Installing on Windows

    To install asyncmy on Windows, you need to install the tools needed to build it.

    1. Download Microsoft C++ Build Tools from https://visualstudio.microsoft.com/visual-cpp-build-tools/
    2. Run CMD as Admin (not required but recommended) and navigate to the folder when your installer is downloaded
    3. Installer executable should look like this vs_buildtools__XXXXXXXXX.XXXXXXXXXX.exe, it will be easier if you rename it to just vs_buildtools.exe
    4. Run this command (Make sure you have about 5-6GB of free storage)
    vs_buildtools.exe --norestart --passive --downloadThenInstall --includeRecommended --add Microsoft.VisualStudio.Workload.NativeDesktop --add Microsoft.VisualStudio.Workload.VCTools --add Microsoft.VisualStudio.Workload.MSBuildTools
    
    1. Wait until the installation is finished
    2. After installation will finish, restart your computer
    3. Install asyncmy via PIP
    pip install asyncmy
    

    Now you can uninstall previously installed tools.

    Usage

    Use connect

    asyncmy provides a way to connect to MySQL database with simple factory function asyncmy.connect(). Use this function if you want just one connection to the database, consider connection pool for multiple connections.

    import asyncio
    import os
    
    from asyncmy import connect
    from asyncmy.cursors import DictCursor
    
    
    async def run():
        conn = await connect(user=os.getenv("DB_USER"), password=os.getenv("DB_PASSWORD", ""))
        async with conn.cursor(cursor=DictCursor) as cursor:
            await cursor.execute("CREATE DATABASE IF NOT EXISTS test")
            await cursor.execute("""
                """
    CREATE TABLE IF NOT EXISTS test.`asyncmy` (
        `id`       int primary key AUTO_INCREMENT,
        `decimal`  decimal(10, 2),
        `date`     date,
        `datetime` datetime,
        `float`    float,
        `string`   varchar(200),
        `tinyint`  tinyint
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
                """.strip()
            )
        await conn.ensure_closed()
    
    
    if __name__ == "__main__":
        asyncio.run(run())
    

    Use pool

    asyncmy provides connection pool as well as plain Connection objects.

    import asyncmy
    import asyncio
    
    
    async def run():
        pool = await asyncmy.create_pool()
        async with pool.acquire() as conn:
            async with conn.cursor() as cursor:
                await cursor.execute("SELECT 1")
                ret = await cursor.fetchone()
                assert ret == (1,)
        pool.close()
        await pool.wait_closed()
    
    if __name__ == '__main__':
        asyncio.run(run())
    

    Replication

    asyncmy supports MySQL replication protocol like python-mysql-replication, but powered by asyncio.

    from asyncmy import connect
    from asyncmy.replication import BinLogStream
    import asyncio
    
    
    async def run():
        conn = await connect()
        ctl_conn = await connect()
    
        stream = BinLogStream(
            conn,
            ctl_conn,
            1,
            master_log_file="binlog.000172",
            master_log_position=2235312,
            resume_stream=True,
            blocking=True,
        )
        async for event in stream:
            print(event)
        await conn.ensure_closed()
        await ctl_conn.ensure_closed()
    
    
    if __name__ == '__main__':
        asyncio.run(run())
    

    ThanksTo

    asyncmy is build on top of these awesome projects.

    • pymysql, a pure python MySQL client.
    • aiomysql, a library for accessing a MySQL database from the asyncio.
    • python-mysql-replication, pure Python Implementation of MySQL replication protocol build on top of PyMYSQL.

    License

    This project is licensed under the Apache-2.0 License.