sqlcycli 1.3.8

Fast MySQL driver build in Cython (Sync and Async).

Fast MySQL driver build in Cython (Sync and Async).

Created to be used in a project, this package is published to github for ease of management and installation across different modules.

Installation

Install from PyPi

pip install sqlcycli

Install from github

pip install git+https://github.com/AresJef/SQLCyCli.git

For Linux systems, if you encounter the following error when installing the SQLCyCli dependency mysqlclient:

Exception: Can not find valid pkg-config name.
Specify MYSQLCLIENT_CFLAGS and MYSQLCLIENT_LDFLAGS env vars manually

Try the following to fix dependency issue (source: Stack Overflow):

sudo apt-get install pkg-config python3-dev default-libmysqlclient-dev build-essential

Requirements

• Python 3.10 or higher.
• MySQL 5.5 or higher.

Features

• Written in Cython for optimal performance (especially for SELECT/INSERT query).
• All classes and methods are well documented and type annotated.
• Supports both Sync and Async connection to the server.
• API Compatiable with PyMySQL and aiomysql.
• Support conversion (escape) for most of the native python types, and objects from libaray numpy and pandas. Does NOT support custom conversion (escape).

Benchmark

The following result comes from benchmark:

• Device: MacbookPro M1Pro(2E8P) 32GB
• Python: 3.12.4
• MySQL: 8.3.0
• mysqlclient: 2.2.4
• PyMySQL: 1.1.1
• aiomysql: 0.2.0
• asyncmy: 0.2.9

# Unit: second | Lower is better
name        type    rows    insert-per-row  insert-bulk select-per-row  select-all
mysqlclient sync    50000   1.729575        0.435661    1.719481        0.117943
SQLCyCli    sync    50000   2.165910        0.275736    2.215093        0.056679
PyMySQL     sync    50000   2.553401        0.404618    4.212548        0.325706
SQLCyCli    async   50000   3.347850        0.282364    4.153874        0.135656
aiomysql    async   50000   3.478428        0.394711    5.101733        0.321200
asyncmy     async   50000   3.665675        0.397671    5.483239        0.313418

# Unit: second | Lower is better
name        type    rows    update-per-row  update-all  delete-per-row  delete-all
mysqlclient sync    50000   1.735787        0.345561    1.531275        0.105109
SQLCyCli    sync    50000   2.241458        0.343359    2.078324        0.104441
PyMySQL     sync    50000   2.516349        0.344614    2.264735        0.104326
SQLCyCli    async   50000   3.465996        0.343864    3.269337        0.103967
aiomysql    async   50000   3.534125        0.344573    3.345815        0.104281
asyncmy     async   50000   3.695764        0.352104    3.460674        0.104523

Usage

Use connect() to create a connection (Sync or Async) with the server.

import asyncio
import sqlcycli

HOST = "localhost"
PORT = 3306
USER = "root"
PSWD = "password"

# Connection (Sync & Async)
async def test_connection() -> None:
    # Sync Connection - - - - - - - - - - - - - - - - - -
    with sqlcycli.connect(HOST, PORT, USER, PSWD) as conn:
        with conn.cursor() as cur:
            cur.execute("SELECT 1")
            assert cur.fetchone() == (1,)

    # Connection closed
    assert conn.closed()

    # Async Connection - - - - - - - - - - - - - - - - -
    async with sqlcycli.connect(HOST, PORT, USER, PSWD) as conn:
        async with conn.cursor() as cur:
            await cur.execute("SELECT 1")
            assert await cur.fetchone() == (1,)

    # Connection closed
    assert conn.closed()

if __name__ == "__main__":
    asyncio.run(test_connection())

Use create_pool() to create a Pool for managing and maintaining connections (Sync or Async) with the server.

import asyncio
import sqlcycli

HOST = "localhost"
PORT = 3306
USER = "root"
PSWD = "password"

# Pool (Context: Connected)
async def test_pool_context_connected() -> None:
    async with sqlcycli.create_pool(HOST, PORT, USER, PSWD, min_size=1) as pool:
        # Pool is connected: 1 free connection (min_size=1)
        assert not pool.closed() and pool.free == 1

        # Sync Connection - - - - - - - - - - - - - - - - - -
        with pool.acquire() as conn:
            with conn.cursor() as cur:
                cur.execute("SELECT 1")
                assert cur.fetchone() == (1,)

        # Async Connection - - - - - - - - - - - - - - - - -
        async with pool.acquire() as conn:
            async with conn.cursor() as cur:
                await cur.execute("SELECT 1")
                assert await cur.fetchone() == (1,)

    # Pool closed
    assert pool.closed() and pool.total == 0

# Pool (Context: Disconnected)
async def test_pool_context_disconnected() -> None:
    with sqlcycli.create_pool(HOST, PORT, USER, PSWD, min_size=1) as pool:
        # Pool is not connected: 0 free connection (min_size=1)
        assert pool.closed() and pool.free == 0

        # Sync Connection - - - - - - - - - - - - - - - - - -
        with pool.acquire() as conn:
            with conn.cursor() as cur:
                cur.execute("SELECT 1")
                assert cur.fetchone() == (1,)

        # Async Connection - - - - - - - - - - - - - - - - -
        async with pool.acquire() as conn:
            async with conn.cursor() as cur:
                await cur.execute("SELECT 1")
                assert await cur.fetchone() == (1,)
        # 1 free async connection
        assert pool.free == 1

    # Pool closed
    assert pool.closed() and pool.total == 0

if __name__ == "__main__":
    asyncio.run(test_pool_context_connected())
    asyncio.run(test_pool_context_disconnected())

Use the Pool class to create a Pool instance. Must close manually.

import asyncio
import sqlcycli

HOST = "localhost"
PORT = 3306
USER = "root"
PSWD = "password"

# Pool (Instance: Connected)
async def test_pool_instance_connected() -> None:
    pool = await sqlcycli.create_pool(HOST, PORT, USER, PSWD, min_size=1)
    # Pool is connected: 1 free connection (min_size=1)
    assert not pool.closed() and pool.free == 1

    # Sync Connection - - - - - - - - - - - - - - - - - -
    with pool.acquire() as conn:
        with conn.cursor() as cur:
            cur.execute("SELECT 1")
            assert cur.fetchone() == (1,)

    # Async Connection - - - - - - - - - - - - - - - - -
    async with pool.acquire() as conn:
        async with conn.cursor() as cur:
            await cur.execute("SELECT 1")
            assert await cur.fetchone() == (1,)

    # Close pool manually
    await pool.close()
    assert pool.closed() and pool.total == 0


# Pool (Instance: Disconnected)
async def test_pool_instance_disconnected() -> None:
    pool = sqlcycli.Pool(HOST, PORT, USER, PSWD, min_size=1)
    # Pool is not connected: 0 free connection (min_size=1)
    assert pool.closed() and pool.free == 0

    # Sync Connection - - - - - - - - - - - - - - - - - -
    with pool.acquire() as conn:
        with conn.cursor() as cur:
            cur.execute("SELECT 1")
            assert cur.fetchone() == (1,)

    # Async Connection - - - - - - - - - - - - - - - - -
    async with pool.acquire() as conn:
        async with conn.cursor() as cur:
            await cur.execute("SELECT 1")
            assert await cur.fetchone() == (1,)
    # 1 free async connection
    assert pool.free == 1

    # Close pool manually
    await pool.close()
    assert pool.closed() and pool.total == 0

if __name__ == "__main__":
    asyncio.run(test_pool_instance_connected())
    asyncio.run(test_pool_instance_disconnected())

Use the sqlfunc module to escape values for MySQL functions.

import asyncio
import datetime
import sqlcycli
from sqlcycli import sqlfunc

HOST = "localhost"
PORT = 3306
USER = "root"
PSWD = "Password_123456"

# SQLFunction
def test_sqlfunction() -> None:
    with sqlcycli.connect(HOST, PORT, USER, PSWD) as conn:
        with conn.cursor() as cur:
            cur.execute("SELECT %s", sqlfunc.TO_DAYS(datetime.date(2007, 10, 7)))
            # SQLFunction 'TO_DAYS()' escaped as: TO_DAYS('2007-10-07')
            assert cur.executed_sql == "SELECT TO_DAYS('2007-10-07')"
            assert cur.fetchone() == (733321,)

    # Connection closed
    assert conn.closed()

if __name__ == "__main__":
    test_sqlfunction()

Acknowledgements

SQLCyCli is build on top of the following open-source repositories:

• aiomysql
• PyMySQL

SQLCyCli is based on the following open-source repositories:

• numpy
• orjson
• pandas
• mysqlclient