EasyMySQL 0.2 Documentation

EasyMySQL is a Python library that wraps PyMySQL and psycopg2 behind one common API. You pass table names and plain dictionaries; the library builds the SQL, manages the cursor, and commits for you.

This is the documentation for the 0.2.0.0 release line. It adds first-class PostgreSQL support, and every value now travels as a driver parameter instead of being interpolated into the query text.

Coming from 0.1.9?

0.2.0.0 breaks compatibility: read Migrating from 0.1.9 before upgrading. The v1 documentation stays available.

Quick Start

Install the library: 

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

Connect and run your first query: 

from easymysql.mysql import mysql

db = mysql('localhost', 'root', 'password', 'mydb')

# Insert a row — returns the new id
new_id = db.insert('users', {
    'name':  'Alice',
    'email': '[email protected]',
})

# Query it back
for user in db.select('users', {'name': 'Alice'}):
    print(user['name'], user['email'])

db.close()

PostgreSQL is the same API, from its own submodule: 

from easymysql.postgres import postgresql

db = postgresql('localhost', 'postgres', 'password', 'mydb')
db.select('users', {'active': True})

What defines this version

Values are parameters, never text

A condition given as a dictionary or built with the query builder sends its values separately from the SQL. Nothing you pass as a value can change the shape of the query. 

from easymysql.my_query import equals, greater_than

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

Errors are raised

A failed statement raises the driver exception and commits nothing. There is no return value that means failure.

A transaction belongs to one thread

While a transaction opened with transaction() or begin() is open, operations from other threads on that instance raise RuntimeError. Nothing is retried or reconnected automatically, and no operation commits work left pending by an earlier execute().

One API, two backends

Methods, signatures, the error contract and transactions are identical on MySQL and PostgreSQL. What differs — the identifier delimiter, RETURNING, the shape of LIMIT — is listed at the bottom of this page.

API at a glance

Method Returns Purpose
mysql(hostname, username, password, database, **kwargs) instance Connects immediately
postgresql(hostname, username, password, database, **kwargs) instance Same, for PostgreSQL
insert(table, data) int | None Inserts a row, returns its id
select(table, condition, fields, order, **clauses) list[dict] Reads rows
update(table, data, condition) None Updates rows — condition required
delete(table, condition) None Deletes rows — condition required
select_one(table, condition, fields, **clauses) dict | None One row, limited on the server
row_exists(table, condition) bool Whether a matching row exists
upsert(table, data, ...) WriteResult Insert or update through the engine's conflict clause
insert_many(table, rows, batch_size=1000) WriteResult Multi-row insert
upsert_many(table, rows, ...) WriteResult Multi-row upsert
truncate(table) None Empties a table
query(sql, params) list[dict] Raw SQL that returns rows
execute(sql, params) None Raw SQL with no result set
executemany(sql, params) None One statement, many parameter sets
execute_multiple(queries) None A batch of statements, all or nothing
transaction() context manager Commits on exit, rolls back on error
begin(), commit(), rollback() — Manual transaction control
count() int Rows affected by the last successful statement
getLastId() int | None Id of the last successful insert
is_in_transaction() bool Is a library transaction open?
affected_rows(), get_last_id(), reset_session() — Aliases of count(), getLastId(), resetSession()
resetSession() None Returns the session to its initial state
ping(), connect(), reconnect(), close() — Connection lifecycle

Differences between the backends

MySQL PostgreSQL
Identifier delimiter `field` "field"
insert() no returning returning='id' by default, configurable or None
limit(n, offset) LIMIT offset, n LIMIT n OFFSET offset
date_equals DATE_ADD(%s, INTERVAL 1 DAY) %s + INTERVAL '1 day'
ilike / icontains not available available
upsert() ON DUPLICATE KEY UPDATE; rejects conflict_columns ON CONFLICT; requires conflict_columns
upsert() affected_rows on update 2 1
SELECT outside autocommit leaves nothing pending opens a transaction
truncate TRUNCATE t TRUNCATE t RESTART IDENTITY CASCADE
resetSession reconnects DISCARD ALL
Query builder import easymysql.my_query easymysql.pg_query

What's inside