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.pyConnecting#
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.rawresult is aResultSetHeaderwithaffected_rowsandinsert_id..rawis typedobject, as Effect's isunknown, because the same statement API can also give rows or a list of results, so read it withisinstance(header, mysql.ResultSetHeader). sql(...).unpreparedcan run several statements separated by;, and gives a list with each one's result.- A failure's
reasoncomes from MySQL's error number:UniqueViolation(with the key's name asconstraint),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 getsKILL QUERYtoo.
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.