Migrating from 0.1.9

0.2.0.0 breaks compatibility. This page lists what can break code that ran on 0.1.9.3.

0.1.9.3 shipped a single module, mysql.py. It had no transactions, no lock and no PostgreSQL backend. Everything this page says about those is new functionality, not a change of behaviour.

Summary

0.1.9.3 0.2.0.0
Statement errors printed to stdout raised
update() / delete() without WHERE syntax error, swallowed ValueError
Values str(value) interpolated into the SQL driver parameters
Reconnection before every statement only when you call reconnect()
Engines MySQL MySQL and PostgreSQL
Dependencies pymysql pymysql; psycopg2-binary as the [postgres] extra
Packaging setup.py with distutils pyproject.toml
Python undeclared >= 3.9
resetCache() raised AttributeError removed, now resetSession()

1. Errors are raised

# 0.1.9.3 — the INSERT fails, the error is printed to stdout
uid = db.insert('users', {'email': '[email protected]'})
# uid == None, and it committed anyway

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

It affects execute, query, select, insert, update, delete, executemany, execute_multiple and truncate.

What to review: everywhere that relied on a return value to know whether something worked. In 0.1.9.3 insert() returned None both when the statement failed and when the table generates no id.

2. update() and delete() require a WHERE

db.delete('table', '')      # 0.1.9.3: DELETE FROM table WHERE ;  syntax error, swallowed
                            # 0.2.0.0: ValueError
db.delete('table', '1=1')   # to delete everything, now explicit
db.truncate('table')        # or this

truncate() in 0.1.9.3 raised AttributeError and never reached the server. It works in 0.2.0.0.

3. The condition must be str, dict or Condition

db.delete('orders', 300)          # 0.1.9.3: TypeError from string concatenation
                                  # 0.2.0.0: TypeError, raised by the library
db.delete('orders', {'id': 300})

4. Empty data

db.insert('t', {})   # 0.1.9.3: INSERT INTO t () VALUES ()
                     # 0.2.0.0: ValueError

5. Values travel as parameters

0.1.9.3 interpolated str(value) into the SQL without escaping. What that produced depends on the value:

Value 0.1.9.3 0.2.0.0
True 'True' in the statement: error 1366 under the strict sql_mode MySQL 8.0 ships by default, swallowed; without strict mode, 0 in the column 1 in TINYINT(1), boolean on PostgreSQL
{'k': 'v'} the Python repr breaks the statement's syntax: swallowed, nothing written valid JSON
"O'Brien" the quote closes the literal: swallowed, nothing written stored as given
float('nan') 'nan' as text ValueError

JSON conversion is one-way: on the way back the column arrives as str.

6. count() and getLastId() are managed by the library

They used to return the cursor's raw rowcount and lastrowid. They are now reset before each operation and again if it fails, so count() gives 0 and getLastId() gives None after a failure.

Those values mean the metadata is unavailable, not that the server applied nothing.

7. resetCache() was removed

db.resetCache()      # 0.2.0.0: AttributeError
db.resetSession()

In 0.1.9.3 resetCache() called execute on the connection object, which has no such method, so it raised AttributeError on first use. resetSession() returns the session to its initial state. It requires a clean session: no open transaction and no pending work.

8. Delimited identifiers

Table and column names are always delimited and accept schema.table. A name containing a backtick or a double quote no longer breaks the query: the delimiter is doubled.

9. No reconnection or retry before a statement

0.1.9.3 reconnected before every statement. 0.2.0.0 reconnects only when you call reconnect(), and never resends a statement after a driver exception. 

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

connect() keeps a healthy connection instead of replacing it, and reopens an instance that was closed with close(). No other method reopens one.

10. Threads

0.1.9.3 had no lock: several threads sharing an instance overwrote each other's results. 0.2.0.0 serialises operations on an instance lock, and a transaction belongs to the thread that opened it — operations from other threads raise RuntimeError while it is open. See Concurrency.

11. Pending work from execute()

insert, update and delete commit; execute() does not. While an execute() has work pending outside 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

12. Installation and imports

pip install easymysql              # MySQL only
pip install easymysql[postgres]    # if you use PostgreSQL

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

from easymysql import MySQL, PostgreSQL, WriteResult   # capitalised aliases

What did not change

The signatures of select, insert, update, delete, query, execute, executemany, truncate, count and getLastId still accept the same positional arguments; select adds keyword-only clauses. A condition as a dictionary works as before.

New in this version