Query Builder

The recommended way to write conditions. The constructors live at module level, one module per backend, and return Condition objects that carry the SQL and its parameters separately.

from easymysql.my_query import equals, greater_than, in_list   # MySQL
from easymysql.pg_query import equals, greater_than, in_list   # PostgreSQL

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

Composition

With operators: & (AND), | (OR), ~ (NOT). Or with the list forms: all_(...), any_(...), not_(c).

db.select('products', any_(
    contains('name', 'chair'),
    in_list('category_id', [1, 2, 3]),
))

Each operand is wrapped in parentheses, so mixing AND and OR groups the way you expect:

raw("a = %s OR b = %s", (1, 2)) & equals('c', 3)
# ((a = %s OR b = %s) AND (`c` = %s))     params=(1, 2, 3)

all_() and any_() skip None, which makes optional filters straightforward:

db.select('products', all_(
    equals('published', True),
    contains('title', text) if text else None,
    in_list('category', cats) if cats else None,
))

A Condition is not a boolean

and, or and not return one of their operands, so they drop a condition. bool(cond), if cond: and cond and other raise TypeError:

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

For an optional filter, compare with None, or hand the None to all_() / any_(), which skip it.

A Condition cannot be interpolated

f"WHERE {cond}"     # TypeError: the parameters would be lost

Pass it straight to select / update / delete, or unpack it:

sql, params = cond      # an iterable of 2, not indexable
repr(cond)              # to inspect it

Every constructor, and the SQL it produces

Field names are shown as f, values as the %s placeholders that reach the driver. Where the two backends differ, both are shown.

Behaviour worth knowing

• contains / starts_with / ends_with / not_contains escape % and _: searching for '50%' looks for the literal 50%, not "anything after 50".
• like / not_like / ilike take the pattern raw: there % and _ really are wildcards.
• date_equals(f, d) produces a whole-day range, not DATE(col) = ..., which would defeat the index.
• Field names are delimited and escaped, so a delimiter inside the name does not break the SQL.

Escape hatches

For what the constructors do not cover. Both take parameters, so they stay safe:

raw('age BETWEEN %s AND %s', (18, 65))
raw('MATCH(title) AGAINST (%s IN BOOLEAN MODE)', ('+python',))
exists('SELECT 1 FROM orders o WHERE o.user_id = users.id AND o.total > %s', (100,))

Clauses

select() and select_one() take the ordering, grouping, paging and locking clauses as keyword arguments. Identifiers are delimited and having keeps its values as parameters:

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

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

with db.transaction():
    row = db.select_one('accounts', {'id': 7}, for_update=True)

A direction other than ASC / DESC, a negative limit, an offset without limit, or mixing these with the raw order= fragment raises ValueError. A having that is not a Condition raises TypeError. for_update outside a transaction raises RuntimeError.

Fragment helpers

These return a SQL string, not a Condition, and go in the fourth argument of select(). Join them with spaces, in SQL order.

from easymysql.my_query import order_by, limit, group_by, having

db.select('sales', {'active': 1},
          fields='category, COUNT(*) AS n',
          order=" ".join([group_by('category'),
                          having('COUNT(*) > 2'),
                          order_by('category'),
                          limit(5)]))

They validate their input instead of escaping it:

• order_by validates the direction: order_by('a', '; DROP') raises ValueError.
• limit casts to int: limit('5; DROP') raises ValueError.
• group_by delimits the identifier but does not validate it: do not pass user input without an allowlist.
• having as a fragment is raw SQL and takes no parameters. For a HAVING with values, use the having= keyword argument above with raw(sql, params).

(sql, params) builders

To build the query without running it:

from easymysql.my_query import (insert_params, update_params, delete_params,
                                select_params, condition_params)

sql, params = insert_params('users', {'name': 'Ann'})
# ('INSERT INTO `users` (`name`) VALUES (%s)', ('Ann',))
db.execute(sql, params)

select_params('t', {'a': 1}, fields='id', order=limit(5))
# ('SELECT id FROM `t` WHERE `a` = %s LIMIT 5', (1,))

condition_params({'a': 1, 'b': None})
# ('`a` = %s AND `b` IS NULL', (1,))

The same guards apply: delete_params('t', '') raises ValueError.