Insert, Select, Update & Delete

All four take the table name as their first argument. The name is delimited for you, and a dot separates schema from table: select('schema.table') produces FROM `schema`.`table` on MySQL and FROM "schema"."table" on PostgreSQL.

Values always travel as driver parameters, never interpolated into the query text.

insert

insert(table, data) -> int | None                     # MySQL
insert(table, data, returning='id') -> int | None     # PostgreSQL

data is a dictionary: the key is the column name, the value is the value to insert.

uid = db.insert('users', {
    'email':  '[email protected]',
    'active': True,
    'meta':   {'source': 'web'},   # dicts and lists are stored as JSON
})

It returns the generated id, or None if the table produces none.

On PostgreSQL the id comes from a RETURNING clause, so the column is your choice:

pg.insert('users', {'email': '[email protected]'}, returning='user_id')
pg.insert('audit_log', {'message': 'x'}, returning=None)   # table without an id

An empty data raises ValueError.

update

update(table, data, condition)

db.update('users', {'active': 0}, {'id': 7})

delete

delete(table, condition)

db.delete('users', {'id': 7})
db.delete('users', '1=1')    # delete everything, explicitly
db.truncate('users')         # or this

select

select(table, condition="", fields="*", order="", *,
       order_by=None, limit=None, offset=None,
       group_by=None, having=None, for_update=False) -> list[dict]

db.select('users')
db.select('users', {'active': 1})
db.select('users', {'active': 1}, fields='id, email')
db.select('users', {'active': 1}, order='ORDER BY id DESC LIMIT 10')

pg.select('public.users', {'id': 1})    # schema.table, PostgreSQL
db.select('shop.users', {'id': 1})      # database.table, MySQL

The result is always a list of dictionaries, one per row:

[{'id': 1, 'email': '[email protected]', 'active': 1},
 {'id': 2, 'email': '[email protected]', 'active': 1}]

No matches gives []. Iterate with a plain for:

for row in db.select('users'):
    print(row['email'])

A list of fields is delimited as identifiers instead:

db.select('users', fields=['id', 'email'])   # SELECT `id`, `email` FROM `users`

The ordering, grouping, paging and locking clauses are keyword arguments — see Query Builder:

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

select_one and row_exists

select_one(table, condition="", fields="*", **clauses) -> dict | None
row_exists(table, condition="") -> bool

user = db.select_one('users', {'email': '[email protected]'})
if user is None:
    ...

if db.row_exists('users', {'email': '[email protected]'}):
    ...

The limit is applied on the server. select_one() takes the same keyword clauses as select() and sets limit=1 itself; passing the raw order= fragment or a different limit raises ValueError. Order with order_by=.

upsert

upsert(table, data, *, conflict_columns=None, update_columns=None,
       returning=None) -> WriteResult

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

pg.upsert('stock', {'sku': 'A-1', 'units': 10},
          conflict_columns=['sku'], returning='id')

update_columns defaults to every column that is not a conflict column. Naming a conflict column there raises ValueError. On PostgreSQL, returning= also works for non-integer keys such as a UUID.

insert_many and upsert_many

insert_many(table, rows, *, batch_size=1000) -> WriteResult
upsert_many(table, rows, *, conflict_columns=None, update_columns=None,
            batch_size=1000) -> WriteResult

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

rows is an iterable of dictionaries. Every row must be non-empty and carry the same keys; key order does not matter, values are reordered to match. The whole input is materialised and validated before the first statement is sent.

The write runs in a transaction — a savepoint when nested — and batch_size splits it into several statements inside it. An empty input sends no SQL. Both return a WriteResult whose affected_rows totals the batch and whose last_id is None.

WriteResult

from easymysql import WriteResult

result.affected_rows   # int
result.last_id         # the id, or None

A frozen dataclass, independent of the cursor, returned by upsert(), insert_many() and upsert_many(). insert(), update() and delete() keep their previous return values.

Server-side expressions

from easymysql.expressions import increment, current_timestamp

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

db.insert('events', {'name': 'signup', 'created': current_timestamp()})

Values in insert(), update(), upsert() and the batch writers can be one of these instead of a literal. increment() takes an optional amount, which travels as a parameter, and is accepted only in an update; insert() and upsert() raise ValueError for it. current_timestamp() is accepted in both.

The condition: three accepted forms

A dictionary joins its entries with AND, and None becomes IS NULL:

db.select('users', {'active': 1, 'deleted_at': None})
# ... WHERE `active` = %s AND `deleted_at` IS NULL

Any other type raises TypeError.

Raw SQL

query(sql, params=None) -> list[dict]     # returns rows
execute(sql, params=None) -> None         # no result set
executemany(sql, params)                  # params: a sequence of tuples
execute_multiple(queries)                 # a list of strings, atomic

Both backends use %s as the placeholder:

db.query('SELECT * FROM users WHERE age > %s AND role = %s', (18, 'admin'))
db.executemany('INSERT INTO t (a, b) VALUES (%s, %s)', [(1, 'x'), (2, 'y')])
db.execute_multiple(['UPDATE a SET x=1', 'UPDATE b SET y=2'])   # all or nothing

executemany() and execute_multiple() always run inside a transaction, and take their own savepoint when nested in an outer one. An empty batch sends no SQL. See Transactions.

Metadata

count()      -> int          # rows affected by the last successful operation
getLastId()  -> int | None   # id of the last successful insert
version()    -> str          # the easymysql version, NOT the server's
ping()       -> bool
is_in_transaction() -> bool

affected_rows()  -> int      # alias of count()
get_last_id()                # alias of getLastId()
reset_session()              # alias of resetSession()

They describe the last operation that completed successfully. After a failed validation, a failed statement, a failed fetch or an uncertain commit they are 0 and None. Close, reset, reconnect and rollback clear them; a successful commit keeps them.

For a batch, count() describes the last data statement of a successful batch and getLastId() is None. executemany() keeps the aggregate count the driver reports.

Data types