Raw SQL & Utilities

The dictionary helpers cover the common cases, but the same connection is available for arbitrary SQL. These methods are the escape hatch whenever insert(), select(), update() or delete() cannot express what you need — joins, transactions, NULL values, DDL, or anything on Python 3.10+ where the dictionary paths are unavailable.

Method summary

Method Returns Commits? Description
execute(query) None No Runs a statement and discards any result set.
query(query) list[dict] No Runs a statement and returns all rows as dictionaries.
count() int — The cursor's rowcount from the last statement.
getLastId() int — The cursor's lastrowid from the last statement.
truncate(table) None — Broken in this release — see below.
resetCache() None — Broken in this release — see below.

query() — read arbitrary SQL

query() executes whatever you give it and maps the result set to a list of dictionaries, exactly like select() does. Use it for joins, subqueries, aggregates and anything else the select() helper cannot build. 

rows = db.query("""
    SELECT o.id, o.total, c.name
    FROM orders o
    JOIN customers c ON c.id = o.customer_id
    WHERE o.status = 'paid'
    ORDER BY o.created_at DESC
    LIMIT 20
""")

for row in rows:
    print(row['id'], row['name'], row['total'])

Column keys come from the cursor description, so alias your expressions (COUNT(*) AS n) to get predictable key names.

On an empty result set query() returns []. If the statement raises, the error is printed and query() returns None — so a for loop over the result will fail with TypeError rather than surfacing the real problem. Guard with rows = db.query(...) or [] if you cannot afford that.

execute() — statements with no result set

execute() runs a statement and ignores whatever it returns. It is the right call for DDL and for writes the dictionary helpers cannot express. 

db.execute("""
    CREATE TABLE IF NOT EXISTS audit_log (
        id INT AUTO_INCREMENT PRIMARY KEY,
        message TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
""")

# A real NULL, which insert() cannot produce
db.execute("INSERT INTO products (name, discontinued_at) VALUES ('Mouse', NULL)")
execute() does not commit

Unlike insert(), update() and delete(), execute() leaves the transaction open. Writes made this way are rolled back when the connection closes unless you commit them yourself. The connection object is name-mangled and private, so the practical way to commit is a raw statement: 

db.execute("INSERT INTO audit_log (message) VALUES ('imported')")
db.execute("COMMIT")

Transactions

Because execute() does not commit, it is also the only way to group several writes into one transaction: 

db.execute("START TRANSACTION")
db.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
db.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
db.execute("COMMIT")

Do not mix insert(), update() or delete() into a block like this — each one commits on its own and would end the transaction early.

count() — rows affected or returned

count() exposes the cursor's rowcount for the most recent statement. Read it immediately after the call you care about, since the next statement overwrites it. 

db.delete('orders', "status = 'cancelled'")
print(db.count(), "rows deleted")

db.select('products', "stock = 0")
print(db.count(), "products out of stock")

getLastId() — last auto-increment ID

Returns the cursor's lastrowid. insert() already returns this value, so getLastId() is mostly useful after an execute() that inserted a row. 

db.execute("INSERT INTO products (name, discontinued_at) VALUES ('Mouse', NULL)")
new_id = db.getLastId()

Broken helpers: truncate() and resetCache()

Both methods call execute() on the connection object rather than on the instance. PyMySQL connections have no execute() method, so both raise AttributeError: 'Connection' object has no attribute 'execute' on every call in the current release.

Use raw statements instead:

# Instead of db.truncate('logs')
db.execute("TRUNCATE logs;")

# Instead of db.resetCache()
db.execute("RESET QUERY CACHE;")

Note that the MySQL query cache — and therefore RESET QUERY CACHE — was removed in MySQL 8.0. On a modern server that statement is a syntax error, which execute() will print rather than raise.

Next step

Read the known limitations →