GitHub

MySQL

A pooled MySQL client: prepared statements, row counts, errors read from MySQL's error numbers, and migrations.

pyfect.sql.mysql runs on asyncmy; install the pyfect-dev[mysql] extra. mysql.layer provides both MysqlClient and SqlClient, so the code from the SQL guide runs unchanged on MySQL.

The snippet on this page reads the server's URL from DATABASE_URL:

DATABASE_URL=mysql://user:password@localhost:3306/app python connect.py

Connecting

MysqlClientConfig holds the URL (a Redacted, so it never shows up in logs) or the separate host, port, database, username and password, and the pool's size and connection lifetime. connect_options passes anything else to the driver; with a URL it is ignored, as in Effect, and the URL's query takes mysql2's ssl object (?ssl={"rejectUnauthorized":true}, with ca, cert and key as PEM text) and connectTimeout in milliseconds. To read the settings from the environment, use mysql.layer_config({"url": fx.config.redacted("DATABASE_URL")}).

Statements are prepared on the server and cached per connection. Set disable_prepared_statements=True for servers or proxies that don't support them; the values are then escaped into the text. A few things work differently than on SQLite and PostgreSQL:

  • MySQL has no RETURNING. A statement that changes rows gives no rows; its .raw result is a ResultSetHeader with affected_rows and insert_id. .raw is typed object, as Effect's is unknown, because the same statement API can also give rows or a list of results, so read it with isinstance(header, mysql.ResultSetHeader).
  • sql(...).unprepared can run several statements separated by ;, and gives a list with each one's result.
  • A failure's reason comes from MySQL's error number: UniqueViolation (with the key's name as constraint), DeadlockError, LockTimeoutError, AuthenticationError, ...
  • Interrupting a statement ends it on the server with KILL QUERY, sent over a one-off connection, and waits for it to stop, so the connection and any transaction on it go on, as in Effect. A statement that hasn't stopped within five seconds has its connection closed and ended on the server (KILL). Ending a stream early reads past a short rest; a statement still sending rows after a moment gets KILL QUERY too.
connect.py
import os

import pyfect as fx
from pyfect import redacted
from pyfect.sql import mysql
from pyfect.sql.error import UniqueViolation

# DATABASE_URL=mysql://user:password@localhost:3306/app python connect.py
config = mysql.MysqlClientConfig(
    url=redacted.make(os.environ["DATABASE_URL"]),
    max_connections=5,
)


@fx.fn
def program():
    sql = yield from mysql.MysqlClient()
    yield from sql(
        "CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(191) UNIQUE)"
    )

    # A statement that changes rows gives no rows; .raw gives mysql2's
    # ResultSetHeader with the count and the generated id. .raw is typed object,
    # since a statement can give rows or several results instead, so check it.
    header = yield from sql("INSERT INTO users (email) VALUES ({})", "ada@example.com").raw
    assert isinstance(header, mysql.ResultSetHeader)
    print(header.insert_id, header.affected_rows)

    # A failed statement's SqlError says why, read from MySQL's error number.
    error = yield from sql("INSERT INTO users (email) VALUES ({})", "ada@example.com").flip()
    assert isinstance(error.reason, UniqueViolation)
    print(error.reason.constraint)

    # A transaction rolls back when its effect fails.
    insert = sql("INSERT INTO users (email) VALUES ({})", "grace@example.com")
    yield from sql.with_transaction(insert.and_then(fx.fail("changed my mind"))).ignore()
    print((yield from sql("SELECT email FROM users ORDER BY id")))


fx.run_main(program().pipe(fx.provide(mysql.layer(config))))

Migrations

mysql.migrator works like the SQLite migrator: migrator.layer runs the migrations when the layer is built, recording them in effect_sql_migrations. MySQL commits DDL as it runs, so a migration that fails part way leaves the statements before the failure applied. The new migrations are recorded before they run, as in Effect's migrator, and the first DDL statement commits that record too: the failed migration counts as applied, and the next run skips it. Fix such a database by hand, or keep each DDL migration to one statement.