We have just released EasyMySQL 0.2, and it is by far the biggest release the library has had. It is also the first time I have had to admit that some things were not working as well as they should and rewrite them completely, breaking backwards compatibility along the way. I brought AI into the development process, mainly to help find security holes, and it turned up quite a few surprises. It also made some interesting suggestions that ended up becoming new features.

To make sure I did not miss any changes, this article was written almost entirely with AI — about 99% of it — by comparing library versions, changelogs, and my own documents for planning and tracking development and testing.

Among the changes: PostgreSQL support with the same API, values passed as parameters instead of pasted into SQL strings, failed statements that raise exceptions instead of printing errors, and proper transactions. There are also upserts, bulk writes, and a handful of safeguards that raise exceptions where things used to fail silently. This release breaks compatibility in several places, all deliberately.

PostgreSQL, same API

The library was always a thin wrapper around PyMySQL. Now there is a second backend, and it speaks the same language:

from easymysql.mysql import mysql
from easymysql.postgres import postgresql

db = mysql('localhost', 'user', 'password', 'shop')
pg = postgresql('localhost', 'user', 'password', 'shop')

pg.select('users', {'active': True})

Methods, signatures, the error contract and transactions are identical. What differs is what has to differ: the identifier delimiter, RETURNING on insert, the shape of LIMIT, and ilike/icontains, which only PostgreSQL has.

Values are parameters now

This is the change I care about most.

The condition builders return a Condition object that carries the SQL and its parameters separately. Values reach the driver as parameters and never enter the query text:

from easymysql.my_query import equals, greater_than, any_, contains, in_list

cond = equals('active', True) & greater_than('stock', 0)
rows = db.select('products', cond)

db.select('products', any_(
    contains('name', '50%'),            # the % is escaped, not a wildcard
    in_list('category_id', [1, 2, 3]),  # an empty list matches nothing
))

There is no version of this API where a value can change the shape of the query. A Condition cannot even be interpolated into a string — f"WHERE {cond}" raises TypeError, because doing that would drop the parameters and silently produce the unsafe thing you were trying to avoid.

A plain dictionary is just as safe and is still the shortest way to express AND:

db.select('users', {'active': 1, 'role': 'admin'})

Errors are raised, not printed

execute() used to catch every exception and print it to stdout. A failed insert() returned None and committed anyway — and None is also what a successful insert returns on a table that generates no id. There was no way to tell the two apart from the return value, and the only sign that anything had gone wrong was a line on stdout.

try:
    uid = db.insert('users', {'email': '[email protected]'})
except Exception:
    ...   # the row was NOT inserted and NOT committed

Library messages now go through the standard logging module under the easymysql logger. Nothing is printed to stdout.

update() and delete() require a condition

A condition must now be a str, a dict or a Condition, and it must not be empty. Anything else raises TypeError, and an empty one raises ValueError, before any SQL is sent.

Deleting every row is something you now have to ask for explicitly:

db.delete('orders', '1=1')
db.truncate('orders')

The connection was being thrown away before every statement

This one took a while to find, because nothing about it was visible from the outside.

PyMySQL.Connection.ping() returns None, not a boolean. The library’s connection check was written as if conn.ping(reconnect=True): ... else: return False, so it evaluated as false every single time, and the library reconnected before every statement. Closing a MySQL connection performs an implicit ROLLBACK.

Two consequences. Every query paid for a full authentication handshake. And any work that had not been committed yet quietly disappeared on the next statement — there was no way to hold a unit of work open across two calls, and nothing said so.

That is also why 0.1.9.3 had no transaction API at all: begin, commit, rollback and transaction did not exist. They exist now, and nesting works through savepoints:

with db.transaction():
    db.insert('t', {'name': 'outer'})
    try:
        with db.transaction():          # SAVEPOINT
            db.insert('t', {'name': 'inner'})
            raise RuntimeError()
    except RuntimeError:
        pass                            # 'inner' rolled back, 'outer' still live

Nothing is retried behind your back

While fixing the reconnection, I took out the other half of it: the library no longer resends a statement after a driver error, and no longer reconnects on its own before an operation.

A statement is sent once. If the driver raises, the exception reaches you and nothing is repeated — a failed commit is not retried either, not even if you call commit() again. Reconnecting is now something you ask for:

try:
    db.insert('users', {'email': '[email protected]'})
except Exception:
    ...              # sent once
    db.reconnect()   # explicit

The reason this matters is that a lost response is not the same thing as a failed write. If the connection dies after the server applied your INSERT but before the acknowledgement got back, resending it inserts the row twice. The library cannot tell those two cases apart, so it does not guess — it hands you the exception and lets you check.

No operation commits work you did not finish

insert, update and delete commit. execute() does not. That combination used to mean a CRUD call would sweep up an earlier raw write into its own commit, before you had decided whether to keep it.

Now there is a guard. While an execute() has work pending outside a transaction, CRUD, the batch operations, begin(), truncate(), reconnect() and resetSession() raise RuntimeError:

db.execute("INSERT INTO audit (message) VALUES (%s)", ('x',))
db.insert('users', {'email': '[email protected]'})   # RuntimeError

db.commit()      # or rollback(); then it works again

Or you group them from the start, which is usually what you meant:

with db.transaction():
    db.execute("INSERT INTO audit (message) VALUES (%s)", ('x',))
    db.insert('users', {'email': '[email protected]'})

resetSession() follows the same rule: it needs a clean session and will not commit anything to get one.

Sharing a connection between threads

There was no lock. Four threads querying four different tables all received the rows of whichever query finished last, with no error at all — there is one cursor, and whatever response was left in it won.

Every operation now takes an instance-level lock, so operations from different threads take turns instead of overwriting each other. That is not parallelism; it is serialisation.

Transactions go further. A transaction belongs to the thread that opened it, and while it is open an operation from any other thread on that instance raises RuntimeError before it sends SQL:

# thread A
with db.transaction():
    db.insert('orders', {'customer_id': 7})
    ...

# thread B, same instance, while A is still inside
db.select('orders')      # RuntimeError

Without this, thread B’s commit() would commit thread A’s half-finished transaction, and neither thread would know. The rule is blunt on purpose: if two threads need to write at the same time, they need two connections.

For parallelism, one instance per thread.

New operations

Beyond the fixes, 0.2 adds the things I kept writing by hand on top of the old API.

upsert() uses each engine’s own conflict clause, so there is no select-then-insert race:

result = db.upsert('stock', {'sku': 'A-1', 'units': 10}, update_columns=['units'])
result.affected_rows
result.last_id

MySQL resolves the conflict against any unique key and rejects conflict_columns; PostgreSQL requires them and can return any column with returning=. The two engines disagree on affected_rows for an update — MySQL says 2, PostgreSQL says 1 — so that number is not comparable across backends.

One row, or just its existence, with the limit applied on the server:

user = db.select_one('users', {'email': '[email protected]'})   # a row, or None
db.row_exists('users', {'email': '[email protected]'})          # a bool

Bulk writes take dictionaries, validate the whole input before sending the first statement, and run inside a transaction:

db.insert_many('events', rows, batch_size=1000)
db.upsert_many('stock', rows, update_columns=['units'])

Values can be a bounded server-side expression instead of a literal, which is how you increment a counter without reading it first:

from easymysql.expressions import increment, current_timestamp

db.update('counters', {'hits': increment('hits'), 'seen': current_timestamp()}, {'id': 7})

And the clauses that used to be raw SQL fragments are now keyword arguments that the library builds and validates:

from easymysql.my_query import raw

db.select('products', cond, ['id', 'name'],
          order_by=[('price', 'DESC'), 'name'], limit=20, offset=40)

db.select('sales', fields=['region'],
          group_by=['region'], having=raw('SUM(total) > %s', (10000,)))

having used to be the one place in the query builder that could not carry parameters. Now it can.

Smaller things

The classes have capitalised aliases, importable from the package, and the PostgreSQL driver is only imported when you actually use it:

from easymysql import MySQL, PostgreSQL, WriteResult

upsert(), insert_many() and upsert_many() return a WriteResult — a frozen dataclass holding affected_rows and last_id, independent of the cursor, so it stays valid after the next operation. count() and getLastId() are still there, still instance metadata, and now with a stricter contract: they are cleared before every operation and again if it fails anywhere, including during the fetch or the commit.

One thing worth saying plainly about those two: count() == 0 and getLastId() is None mean the metadata is not available. They are not evidence that the server applied nothing.

Composing conditions with Python’s and, or or not now raises TypeError instead of silently dropping one of them:

cond = equals('active', True) and greater_than('stock', 0)   # TypeError
cond = equals('active', True) &   greater_than('stock', 0)   # correct

psycopg2 is an optional extra

pip install easymysql              # MySQL only
pip install easymysql[postgres]    # PostgreSQL as well

PostgreSQL is new in this release, so the question was how to add it without making every MySQL user pay for it. psycopg2 builds from source: it needs a C compiler and libpq-dev. Making it a hard dependency would have broken pip install easymysql on any machine without a toolchain, including the machines whose owners only ever wanted MySQL. So the base install stays PyMySQL alone, which is pure Python, and the extra pulls in psycopg2-binary, which ships prebuilt wheels.

While I was in there, packaging moved from setup.py to pyproject.toml. distutils was removed in Python 3.12, so 0.1.9.3 does not install on modern Python at all.

How it was verified

I did not want to ship this on “it looks right”.

• 1004 tests, 65 of them integration tests running against a real MySQL 8.0.46 and a real PostgreSQL 16.14.
• Green on Python 3.9, 3.10, 3.11, 3.12, 3.13 and 3.14, each in its own container.
• 97% coverage, with the condition module, the query builders and the new operations at 100%.
• Every code example in the documentation is executed against those databases as part of the release check. That is how two wrong examples got caught before publishing rather than after.

The reconnection bug is the reason for the integration tests. The unit suite could not see it: with a test double, the broken ping() looked fine. It took a real server to notice that the connection was being replaced underneath every statement.

The same check caught something more embarrassing. While writing this post I went back and ran 0.1.9.3 itself against MySQL 8.0, instead of reading its source and describing what I thought it did. Several things I had written about the old version were wrong — a failed insert returning the previous row’s id, an empty delete() emptying the table, resetCache() failing because of the query cache. None of those happen. The real behaviour was different in each case, and usually more boring: an error printed to stdout and a call that did nothing.

If you are documenting what you fixed, run the broken version. Reading it is not enough.

Upgrading

0.2.0.0 breaks compatibility on purpose. The full list, with before-and-after code, is in Migrating from 0.1.9. The short checklist:

• Statement errors now raise. Anything that read a return value to detect failure needs a try/except.
• update() and delete() need a non-empty condition.
• A condition must be a str, a dict or a Condition. Anything else raises TypeError.
• resetCache() is gone. It called execute on the connection object, which has no such method, so it raised AttributeError on first use — it never reached a server. The replacement is resetSession(), which actually resets the session.
• Nothing is retried or reconnected automatically. Code that relied on the library reconnecting on its own now has to call reconnect().
• A CRUD call after an execute() raises until you commit() or rollback() the pending work.
• Sharing one instance across threads while a transaction is open now raises instead of interleaving.
• PostgreSQL needs the [postgres] extra, and Python 3.9 or newer.

If your code already passed conditions as dictionaries, handled errors with try/except, always supplied a WHERE, and used one connection per thread, there is a good chance you have nothing to change.

The v2 documentation is up, and the v1 documentation stays where it is for anyone still on the old line.

As always, if you find something broken, open an issue on GitHub. The most valuable thing anyone did for this release was tell me that something did not work the way the docs claimed.