Skip to content

v2.1.0

New Features

1. Connection Init SQL (init_sql)

Added init_sql parameter to automatically execute preset SQL on each new TCP connection's first use. Ideal for ClickHouse async insert configuration, MySQL session variable setup, and similar scenarios.

Background:

In databases like ClickHouse, SET statements are scoped at the TCP connection level. Previously, SET had to be manually executed before every INSERT to configure async insert parameters, which was redundant and inefficient. The init_sql feature moves this capability down to the engine layer for automatic per-connection initialization.

How It Works:

  • init_sql execution is implemented in Result.__call__()
  • Uses weakref.WeakKeyDictionary with Pool as key to track initialized connection IDs (id(conn))
  • Executes init_sql on first SQL execution of a new connection; skips on subsequent reuses
  • When a connection is recycled via pool_recycle, the new connection re-executes init_sql
  • After init_sql execution, commit() is called immediately to ensure SET statements take effect

Usage:

from asmysql import Engine

# Via keyword argument
engine = Engine(
    host="192.168.62.195",
    port=9004,
    user="default",
    password="",
    init_sql=(
        "SET async_insert = 1, wait_for_async_insert = 0, "
        "async_insert_busy_timeout_ms = 1000, "
        "max_execution_time = 30"
    ),
)
await engine.connect()

# Via URL parameter
engine = Engine(
    url="mysql://user:pass@127.0.0.1:3306/?init_sql=SET SESSION wait_timeout=600"
)
await engine.connect()

Backward Compatible:

init_sql defaults to None. When not provided, behavior is identical to v2.0.0 — no impact on existing code.

Breaking Changes

1. Auto-Commit Mechanism Refactored

Removed auto_commit Engine parameter and commit execute/Result parameter, replaced with SQL statement type-based auto-commit mechanism:

  • Result internally uses _should_commit() method to analyze the first keyword of SQL statements
  • Write operation keywords (INSERT, UPDATE, DELETE, REPLACE, CREATE, ALTER, DROP, etc.) automatically COMMIT after execution
  • Read operations (SELECT) do not trigger COMMIT
  • stream=True mode does not auto-COMMIT (to avoid interrupting the stream)

Migration Guide:

# v2.0.0 usage (deprecated)
engine = Engine(url="...", auto_commit=False)
result = await engine.execute("INSERT ...", commit=False)

# v2.1.0 usage
engine = Engine(url="...")
async with engine.execute("INSERT ...") as result:
    pass  # Write operations auto-commit

2. Removed Engine stream Parameter

stream is no longer an Engine class attribute or constructor parameter, only available at the execute() / execute_many() method level.

3. Removed Engine result_class Parameter

result_class is no longer an Engine class attribute or constructor parameter, only available at the execute() / execute_many() method level, defaulting to tuple.

Migration Guide:

# v2.0.0 usage (deprecated)
engine = Engine(url="...", result_class=dict)
async with engine.execute("SELECT ...") as result:
    data = await result.fetch_one()  # data is dict

# v2.1.0 usage
engine = Engine(url="...")
async with engine.execute("SELECT ...", result_class=dict) as result:
    data = await result.fetch_one()  # data is dict

Bug Fixes

  • Fix fetch_all() connection leak for custom result types: When result_class is a custom class (e.g., Pydantic BaseModel), return _data exited before close(), leaking one connection per call. Unified return path to ensure close() always executes
  • Fix double connection release after query error: __call__() except branch closed cursor and released connection but didn't nullify __cursor. Added self.__cursor = None in except block
  • Fix async for + break connection leak: __aiter__ returned self, so after break, connection was never released. Rewritten as async generator with try/finally
  • Fix Result.__call__() only catching MySQLError causing connection leak: Non-MySQL exceptions were uncaught, leaving acquired connections unreleased. Changed to except Exception with re-raise for non-MySQL errors
  • Fix __del__ safety net crashes: __del__ during GC had no error handling and self.pool could be None. Added try/except and pool null check
  • Fix echo_sql_log URL parameter cannot be set to False: ?echo_sql_log=false was parsed as True. Now correctly parses boolean strings
  • Fix or operator overriding legitimate falsy values: Engine(min_pool_size=0) had 0 overridden to default. Unified to x if x is not None else default
  • Fix connect()/disconnect() concurrent race conditions: Multiple concurrent coroutines could create multiple pools or double-close. Added asyncio.Lock protection
  • Fix execute() passing self.__pool (possibly None): Disconnected calls produced Result with None pool. Now uses self.pool property
  • Fix Result[type[T]] generic parameter semantic error: Should be Result[T]. Corrected generic parameter
  • Fix release_connections() missing null check: Called before connect(), raised AttributeError. Added null check
  • Remove @lru_cache decorators: @lru_cache on __repr__/__str__ held strong references to self, preventing GC. Removed caching
  • Fix error_msg not using err_msg() formatting: Lost fallback handling for empty strings. Now calls err_msg() function
  • Fix iterate() truthiness check inconsistency: if data: treated empty tuples as falsy. Unified to is not None check
  • Fix row_count/last_rowid/row_number AttributeError after close: Accessing these properties after cursor was nullified caused crashes. Added null check
  • DDL/DML statements auto-release connection: When cursor.description is None (DDL/INSERT/UPDATE etc.), automatically closes cursor and releases connection back to pool, while caching rowcount / lastrowid

Documentation

  • Fixed homepage examples: Corrected non-existent API usage in both Chinese and English index.md, unified to use the correct async with engine.execute() + result.iterate() pattern. execute() / execute_many() return Result objects (not coroutines) and must be used via async with or async for
  • Added init_sql documentation: Synchronized init_sql parameter descriptions and usage examples across connection.md, api.md, and examples.md (both Chinese and English versions)
  • Fixed transaction control documentation: Removed auto_commit/commit parameter descriptions, replaced with SQL statement type-based auto-commit mechanism description
  • Fixed API documentation signatures: Removed close parameter from fetch_one()/fetch_many(), removed commit parameter from execute()/execute_many()

Testing

  • Added test_engine_init_sql.py test file covering init_sql core functionality and edge cases
  • Rewrote integration test engine fixture to auto-select a test database via init_sql=USE <test_db> on each pool connection
  • Removed Engine result_class related test cases from test_mock_engine.py