CyMySQL

A high-performance Python MySQL client library forked from PyMySQL, accelerated using Cython for improved performance. CyMySQL implements the Python Database API Specification v2.0 and supports both synchronous and asynchronous operations through asyncio, with features like connection pooling, prepared statements, and comprehensive MySQL protocol implementation.

Package Information

• Package Name: CyMySQL
• Language: Python
• Installation: pip install cymysql
• Cython-free Installation: NO_CYTHON=1 pip install cymysql
• Requirements: Python 3.10+, MySQL 5.7+/MariaDB
• Repository: https://github.com/nakagami/CyMySQL

Core Imports

import cymysql

For dictionary cursors:

from cymysql.cursors import DictCursor

For async operations:

import cymysql.aio
from cymysql.aio import AsyncDictCursor

Basic Usage

Synchronous Database Operations

import cymysql

# Connect to database
conn = cymysql.connect(
    host='127.0.0.1',
    user='root',
    passwd='',
    db='database_name'
)

# Execute queries using cursor
cur = conn.cursor()
cur.execute('SELECT foo, bar FROM baz')

# Fetch results
for row in cur.fetchall():
    print(row[0], row[1])

# Clean up
cur.close()
conn.close()

Asynchronous Database Operations

import asyncio
import cymysql.aio

async def database_operations():
    # Single connection
    conn = await cymysql.aio.connect(
        host="127.0.0.1",
        user="root",
        passwd="",
        db="database_name"
    )
    
    cur = conn.cursor()
    await cur.execute("SELECT 42")
    result = await cur.fetchall()
    print(result)
    
    # Connection pool
    pool = await cymysql.aio.create_pool(
        host="127.0.0.1",
        user="root",
        passwd="",
        db="database_name",
        minsize=1,
        maxsize=10
    )
    
    async with pool.acquire() as conn:
        async with conn.cursor() as cur:
            await cur.execute("SELECT * FROM users")
            users = await cur.fetchall()
    
    pool.close()
    await pool.wait_closed()

asyncio.run(database_operations())

Architecture

CyMySQL follows the Python DB-API 2.0 specification with performance enhancements:

• Connection Layer: Manages MySQL protocol communication with server
• Cursor Layer: Provides interface for SQL execution and result handling
• Async Layer: Asyncio-compatible connection and cursor implementations
• Connection Pooling: Efficient connection reuse for high-throughput applications
• Cython Acceleration: Optional Cython compilation for performance-critical components
• Type Conversion: Automatic conversion between Python and MySQL data types

Capabilities

Database Connections

Core connection functionality for establishing and managing database connections with support for SSL, authentication, character sets, and connection parameters.

def connect(host='localhost', user=None, password='', passwd='', port=3306, db=None, charset='utf8mb4', **kwargs):
    """Create database connection."""

Database Connections

Cursor Operations

SQL execution interface providing methods for running queries, managing transactions, and retrieving results with support for prepared statements and parameterized queries.

class Cursor:
    def execute(self, query, args=None): ...
    def executemany(self, query, args): ...
    def fetchone(self): ...
    def fetchall(self): ...
    def fetchmany(self, size=None): ...

class DictCursor(Cursor):
    """Cursor that returns rows as dictionaries instead of tuples."""
    def fetchone(self): ...  # Returns dict
    def fetchall(self): ...  # Returns tuple of dicts

Cursor Operations

Asynchronous Operations

Asyncio-compatible database operations including async connections, cursors, and connection pooling for high-performance concurrent database access.

async def connect(host='localhost', user=None, password='', **kwargs):
    """Create async database connection."""

async def create_pool(minsize=1, maxsize=10, **kwargs):
    """Create async connection pool."""

class AsyncDictCursor:
    """Async cursor that returns rows as dictionaries."""
    async def fetchone(self): ...  # Returns dict
    async def fetchall(self): ...  # Returns tuple of dicts

Asynchronous Operations

Data Type Handling

Comprehensive MySQL data type support with automatic conversion between Python and MySQL types, including support for dates, times, binary data, and JSON.

def escape_string(value): ...
def escape_dict(val, charset): ...
def Binary(x): ...
Date = date
Time = time
Timestamp = datetime

Data Type Handling

Error Handling

Complete exception hierarchy following DB-API 2.0 specification for handling database errors, operational issues, and programming mistakes.

class MySQLError(Exception): ...
class DatabaseError(MySQLError): ...
class OperationalError(DatabaseError): ...
class ProgrammingError(DatabaseError): ...

Error Handling

Database API Specification

CyMySQL fully implements Python Database API Specification v2.0:

• API Level: 2.0
• Thread Safety: 1 (Threads may share the module)
• Parameter Style: format (using %s placeholders)

Performance Features

• Cython Acceleration: Core components can be compiled with Cython for improved performance
• Connection Pooling: Async connection pooling for high-throughput applications
• Efficient Protocol: Optimized MySQL protocol implementation
• Memory Management: Careful memory usage for large result sets