cursor.execute() — The Complete Guide to Python Database Execution

If you write Python code that touches a database, you use cursor.execute(). It is the single method that sends your SQL to the database engine, binds your parameters, and kicks off every query, insert, update, and delete you will ever run. It is also the exact point where SQL injection either happens or doesn't.

Despite being one of the most heavily used methods in Python's database ecosystem, cursor.execute() is widely misunderstood. Developers string-format their queries into it and wonder why their app gets breached. They pass parameters in the wrong format and get cryptic type errors. They assume it returns results when it doesn't. This article takes cursor.execute() apart completely: what the specification says, what actually happens when you call it, every parameter style, the security boundary it enforces, the gotchas that trip people up, and how to use it correctly in production code.

What the Specification Says

cursor.execute() is defined in PEP 249, the Python Database API Specification v2.0, authored by Marc-André Lemburg. The specification defines the method as follows:

"Prepare and execute a database operation (query or command). Parameters may be provided as sequence or mapping and will be bound to variables in the operation. Variables are specified in a database-specific notation (see the module's paramstyle attribute for details)." — PEP 249, Cursor methods, peps.python.org

The method signature is:

cursor.execute(operation [, parameters])

Two things to note immediately. First, parameters is optional — you can execute raw SQL with no parameter binding. Second, the return value is explicitly undefined. PEP 249 states that return values from .execute() "are not defined." Some modules return None, some return the cursor itself (psycopg3 does this, enabling method chaining), but you should never write code that depends on the return value being anything specific.

The specification also notes an optimization: "A reference to the operation will be retained by the cursor. If the same operation object is passed in again, then the cursor can optimize its behavior." In practice, this means some drivers can cache the parsed SQL statement and reuse it when the same query string is executed repeatedly with different parameters — the Python equivalent of a prepared statement.

The Two Calling Patterns

There are exactly two ways to call cursor.execute(): without parameters and with parameters. The difference between them is the difference between vulnerable and secure code.

Pattern 1: No Parameters (Static SQL)

When your SQL contains no variable data — no user input, no dynamic values — you can pass the query string alone:

import sqlite3

conn = sqlite3.connect(":memory:")
cursor = conn.cursor()

# Static SQL -- no external data, no parameters needed
cursor.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)")
cursor.execute("CREATE INDEX idx_users_email ON users(email)")

This is safe because the SQL is entirely controlled by your source code. There is nothing for an attacker to inject into.

Pattern 2: With Parameters (Parameterized Query)

When your SQL includes any data that comes from outside your source code — user input, API responses, file contents, configuration values, anything — you must use parameterized execution:

# The SQL template with a placeholder
sql = "INSERT INTO users (name, email) VALUES (?, ?)"

# The data, passed separately
cursor.execute(sql, ("Alice Chen", "alice@example.com"))

The SQL string and the data never meet inside your Python code. They are sent to the database driver separately. The driver ensures the data is treated as literal values, never as executable SQL. This structural separation is what makes parameterized queries resistant to SQL injection.

The Five Parameter Styles

PEP 249 defines five paramstyle values, and each database module declares which one it uses. This is the most common source of confusion when switching between database modules, because the placeholder syntax changes.

qmark — Question Mark Style

Used by: sqlite3, pyodbc

# sqlite3
cursor.execute(
    "SELECT * FROM users WHERE name = ? AND email = ?",
    ("Alice Chen", "alice@example.com")
)

Parameters are passed as a tuple (or list). The position of each ? corresponds to the position in the tuple. The first ? gets the first element, the second ? gets the second, and so on.

numeric — Numbered Positional Style

Used by: Some Oracle adapters

cursor.execute(
    "SELECT * FROM users WHERE name = :1 AND email = :2",
    ("Alice Chen", "alice@example.com")
)

Similar to qmark, but the placeholders include explicit position numbers. Note that the Python sqlite3 documentation explicitly states that numeric placeholders are not supported and will be interpreted as named placeholders if used — a silent, confusing bug if you make that mistake.

named — Named Style

Used by: sqlite3 (as an alternative), python-oracledb

# sqlite3 supports named style as well
cursor.execute(
    "SELECT * FROM users WHERE name = :name AND email = :email",
    {"name": "Alice Chen", "email": "alice@example.com"}
)

Parameters are passed as a dictionary. Each :name placeholder maps to a key in the dictionary. This is more readable for complex queries and eliminates position-counting errors.

format — printf Style

Used by: mysql-connector-python, mysqlclient

# MySQL connector
cursor.execute(
    "SELECT * FROM users WHERE name = %s AND email = %s",
    ("Alice Chen", "alice@example.com")
)

pyformat — Python Extended Format Style

Used by: psycopg2

# psycopg2 (PostgreSQL)
cursor.execute(
    "SELECT * FROM users WHERE name = %(name)s AND email = %(email)s",
    {"name": "Alice Chen", "email": "alice@example.com"}
)

Parameters are passed as a dictionary, with %(key)s placeholders. Again, despite the visual similarity to Python's % operator, the driver handles the binding — you are not doing string interpolation.

Checking Your Module's Style

Every DB-API 2.0 module exposes its parameter style as a module-level constant:

import sqlite3
print(sqlite3.paramstyle)  # 'qmark'

import psycopg2
print(psycopg2.paramstyle)  # 'pyformat'

Always check this before writing your first query with a new module. Using the wrong placeholder style will raise an error or, worse, silently produce incorrect results.

Why String Formatting Will Destroy You

This is the section where security matters, so let's be explicit about what goes wrong. Consider a login function:

def authenticate_user(cursor, username, password):
    # CATASTROPHICALLY WRONG -- SQL injection vulnerability
    query = f"SELECT * FROM users WHERE name = '{username}' AND pass = '{password}'"
    cursor.execute(query)
    return cursor.fetchone()

If someone enters this as their username:

' OR '1'='1' --

The query becomes:

SELECT * FROM users WHERE name = '' OR '1'='1' --' AND pass = ''

The -- comments out the password check. The OR '1'='1' is always true. The attacker gets the first user record from the database — often the admin account.

Injection vulnerabilities have appeared in every edition of the OWASP Top 10. In the 2025 edition, Injection is ranked A05, and the OWASP SQL Injection Prevention Cheat Sheet identifies parameterized queries as "Option 1" for prevention, stating that prepared statements ensure "an attacker cannot change the intent of a query, even if SQL commands are inserted." SQL injection alone accounts for more than 14,000 CVEs cataloged in the 2025 OWASP data.

The fix requires exactly one change — stop building the query string yourself and let cursor.execute() handle the parameter binding:

def authenticate_user(cursor, username, password):
    # CORRECT -- parameterized query
    cursor.execute(
        "SELECT * FROM users WHERE name = ? AND pass = ?",
        (username, password)
    )
    return cursor.fetchone()

Now, if the attacker enters ' OR '1'='1' -- as their username, the database searches for a user whose name column literally contains the string ' OR '1'='1' --. It finds nothing. Attack neutralized.

The Subtle Trap: Format-Then-Execute

Some developers know not to use f-strings, but still manage to defeat the protection:

# STILL WRONG -- building the query with % formatting before execute
query = "SELECT * FROM users WHERE name = '%s'" % username
cursor.execute(query)

This is just as dangerous as using f-strings. The string substitution happens in Python before cursor.execute() ever sees the query. By the time the driver gets it, the user input is already embedded in the SQL string. This pattern of pre-formatting queries using f-strings or the % operator completely bypasses driver-level protection.

What cursor.execute() Does Internally

When you call cursor.execute(sql, params), the driver performs several steps. The exact implementation varies by module, but the general process is:

1. Parse the SQL template. The driver identifies placeholder positions in the query string.
2. Validate parameters. The driver checks that you've provided the right number (and sometimes type) of parameters.
3. Serialize parameters. Each Python value is converted to its database wire format. A Python str becomes a SQL text value, an int becomes a SQL integer, None becomes SQL NULL, and so on.
4. Send to the database. The SQL and the serialized parameters are transmitted to the database engine. Depending on the driver, this may use the database's native prepared statement protocol (sending the SQL template and parameters separately) or it may safely escape the parameters and inline them into the SQL string.
5. Set cursor state. After execution, the cursor's .description attribute is populated (for queries that return rows), .rowcount is updated, and the cursor is ready for .fetch*() calls.

The critical detail is step 4. Some drivers (like psycopg2 using libpq) use true server-side prepared statements where the SQL template and the data are genuinely separate in the database protocol. Others (like sqlite3) perform safe escaping and substitution on the client side before sending a complete SQL string to the engine. Both approaches provide the same SQL injection protection — the distinction matters for performance, not security.

Cursor State After execute()

Calling cursor.execute() changes the cursor's state. Understanding these state changes is essential for correct result handling.

.description — Column Metadata

After executing a SELECT, .description contains a tuple of 7-item tuples describing each result column. PEP 249 specifies that .description is None for operations that do not return rows or if no .execute*() method has been called yet.

cursor.execute("SELECT id, name, email FROM users")
for column in cursor.description:
    print(f"Name: {column[0]}, Type: {column[1]}")
# Name: id, Type: None  (sqlite3 doesn't provide type codes)
# Name: name, Type: None
# Name: email, Type: None

Before any execute call, or after executing a statement that doesn't return rows (like INSERT), .description is None. This is how you can check whether a statement produced a result set.

.rowcount — Affected Rows

For INSERT, UPDATE, and DELETE, .rowcount tells you how many rows were affected:

cursor.execute("UPDATE users SET active = 1 WHERE last_login > ?", ("2025-01-01",))
print(f"Activated {cursor.rowcount} users")

PEP 249 specifies that .rowcount is -1 when no .execute*() has been performed or when the rowcount of the last operation cannot be determined by the interface. For SELECT statements, .rowcount behavior is database-dependent — some drivers set it to the total row count, others set it to -1. Don't rely on .rowcount after a SELECT; use len(cursor.fetchall()) or count as you iterate if you need the number.

.lastrowid — Last Inserted Row ID

This is a DB-API extension (not part of the core PEP 249 specification), but it is supported by sqlite3 and many other modules:

# sqlite3
cursor.execute(
    "INSERT INTO users (name, email) VALUES (?, ?)",
    ("Bob Smith", "bob@example.com")
)
print(f"New user ID: {cursor.lastrowid}")

For auto-increment columns, this gives you the generated primary key without needing a separate query. Note that support varies by driver. In psycopg2 (PostgreSQL), the equivalent pattern is to append a RETURNING id clause to the INSERT statement and call cursor.fetchone(), since PostgreSQL does not use auto-increment in the same way as SQLite or MySQL.

# psycopg2 -- use RETURNING to get the new row's ID
cursor.execute(
    "INSERT INTO users (name, email) VALUES (%s, %s) RETURNING id",
    ("Bob Smith", "bob@example.com")
)
new_id = cursor.fetchone()[0]
print(f"New user ID: {new_id}")

Common Mistakes and Their Fixes

Mistake 1: Single-Value Tuple Without Comma

This is a frequent beginner error with cursor.execute():

# WRONG -- this passes a string, not a tuple
cursor.execute("SELECT * FROM users WHERE name = ?", ("Alice"))
# TypeError or incorrect behavior

# RIGHT -- trailing comma makes it a tuple
cursor.execute("SELECT * FROM users WHERE name = ?", ("Alice",))

In Python, ("Alice") is just a parenthesized string. ("Alice",) is a one-element tuple. The database driver expects a sequence, not a string. A string is technically iterable in Python (it iterates over characters), so instead of an error, you might get sqlite3 trying to bind 'A' to the first placeholder — a silent, maddening bug.

Mistake 2: Using execute() for Bulk Inserts

# SLOW -- executes one statement per iteration
for user in users_list:
    cursor.execute(
        "INSERT INTO users (name, email) VALUES (?, ?)",
        (user["name"], user["email"])
    )

# FAST -- batch execution
cursor.executemany(
    "INSERT INTO users (name, email) VALUES (?, ?)",
    [(u["name"], u["email"]) for u in users_list]
)

executemany() exists specifically for this pattern. Depending on the driver, it may batch the operations into fewer round-trips to the database, significantly reducing execution time for large datasets.

Mistake 3: Trying to Parameterize Table or Column Names

# WILL NOT WORK -- parameters can only be used for values
table_name = "users"
cursor.execute("SELECT * FROM ?", (table_name,))
# sqlite3.OperationalError: near "?": syntax error

This is a fundamental limitation, not a bug. Prepared statement parameters can only substitute for data values — the things that go in WHERE clauses, INSERT value lists, and SET assignments. Table names, column names, SQL keywords, and other structural elements of a query cannot be parameterized because the database engine needs to know the query structure at parse time.

The safe approach for dynamic identifiers uses allowlist validation:

ALLOWED_TABLES = {"users", "products", "orders"}
ALLOWED_SORT_COLUMNS = {"name", "created_at", "price"}

def query_table(cursor, table_name, sort_column="name"):
    if table_name not in ALLOWED_TABLES:
        raise ValueError(f"Invalid table: {table_name}")
    if sort_column not in ALLOWED_SORT_COLUMNS:
        raise ValueError(f"Invalid sort column: {sort_column}")

    # Safe because we've validated against a fixed set of known-good values
    cursor.execute(f"SELECT * FROM {table_name} ORDER BY {sort_column}")
    return cursor.fetchall()

This is one of the few cases where string formatting in a SQL statement is acceptable — but only because you've verified the input against a hardcoded set of valid values.

Mistake 4: Forgetting That execute() Doesn't Return Results

# WRONG -- execute() return value is undefined per PEP 249
results = cursor.execute("SELECT * FROM users")
for row in results:  # may work in some modules but is not portable
    print(row)

# RIGHT -- call fetch methods after execute
cursor.execute("SELECT * FROM users")
results = cursor.fetchall()
for row in results:
    print(row)

# ALSO RIGHT -- iterate the cursor directly (supported by most modules)
cursor.execute("SELECT * FROM users")
for row in cursor:
    print(row)

PEP 249 explicitly says that .execute() return values are not defined. Some modules (like psycopg3) return the cursor itself to enable chaining, but writing portable code means treating execute() as if it returns nothing and always using the .fetch*() methods or cursor iteration.

Mistake 5: Mixing Up Placeholder Styles

# WRONG when using psycopg2 -- sqlite3 uses ?, but psycopg2 uses %s
cursor.execute("SELECT * FROM users WHERE name = ?", ("Alice",))
# psycopg2.errors.ProgrammingError

# RIGHT for psycopg2
cursor.execute("SELECT * FROM users WHERE name = %s", ("Alice",))

There is no universal placeholder. Each module declares its style in the paramstyle constant. If you're building code that needs to work across multiple databases, you either need an abstraction layer (like SQLAlchemy) or you need to account for the different styles explicitly.

execute() vs. executemany() vs. executescript()

PEP 249 defines two execution methods. Some modules add a third.

execute(operation, parameters) — Executes a single SQL statement with optional parameters. This is what you use for individual queries, inserts, updates, and deletes.

executemany(operation, seq_of_parameters) — Executes the same SQL statement once for each parameter set in a sequence. PEP 249 allows modules to implement this as either multiple .execute() calls internally or as a single batch operation. The specification states that using executemany() for operations that produce result sets is "undefined behavior."

# executemany -- same statement, multiple parameter sets
data = [
    ("Alice", "alice@example.com"),
    ("Bob", "bob@example.com"),
    ("Charlie", "charlie@example.com"),
]
cursor.executemany("INSERT INTO users (name, email) VALUES (?, ?)", data)

executescript(sql_script) — This is a sqlite3-specific method (not part of PEP 249) that executes multiple SQL statements separated by semicolons. It does not support parameter binding:

# sqlite3 only -- executes multiple statements, no parameter binding
cursor.executescript("""
    DROP TABLE IF EXISTS users;
    CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);
    INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
""")

Because executescript() takes no parameters, it should only be used with entirely static SQL that contains no external data.

Performance: When execute() Speed Matters

For single queries, cursor.execute() performance is rarely a bottleneck — the database round-trip dominates. But for high-volume operations, there are optimization strategies worth knowing.

Reusing the Query String Object

PEP 249 states that drivers may cache parsed statements when the same operation object is reused. In practice:

# The same string object is passed repeatedly -- driver may optimize
insert_sql = "INSERT INTO events (timestamp, message) VALUES (?, ?)"
for event in events:
    cursor.execute(insert_sql, (event.timestamp, event.message))

Assigning the SQL to a variable and reusing it is a minor optimization in drivers that support statement caching. However, using executemany() is almost always faster for this pattern.

Batch Size with executemany()

For very large datasets, splitting into batches can help balance memory usage and transaction size:

BATCH_SIZE = 1000

def bulk_insert(cursor, conn, records):
    sql = "INSERT INTO measurements (sensor_id, value, timestamp) VALUES (?, ?, ?)"
    for i in range(0, len(records), BATCH_SIZE):
        batch = records[i:i + BATCH_SIZE]
        cursor.executemany(sql, batch)
        conn.commit()  # Commit each batch to avoid huge transactions

Connection-Level execute() Shortcuts

Some modules provide a shortcut on the connection object itself. In sqlite3:

# Connection.execute() creates a cursor, calls execute(), and returns the cursor
rows = conn.execute("SELECT * FROM users WHERE active = ?", (1,)).fetchall()

This is syntactic sugar — it creates a temporary cursor behind the scenes. It is convenient for simple queries but makes resource management less explicit.

cursor.execute() Across Modules: A Comparison

Here is the exact same operation — inserting a user and retrieving them — shown in four different database modules. The structural pattern is identical; only the import, connection parameters, and placeholder style change.

# === sqlite3 (standard library) ===
import sqlite3
conn = sqlite3.connect("app.db")
cur = conn.cursor()
cur.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Alice", "a@ex.com"))
conn.commit()
cur.execute("SELECT * FROM users WHERE name = ?", ("Alice",))
print(cur.fetchone())

# === psycopg2 (PostgreSQL) ===
import psycopg2
conn = psycopg2.connect(host="localhost", dbname="app", user="dev", password="secret")
cur = conn.cursor()
cur.execute("INSERT INTO users (name, email) VALUES (%s, %s)", ("Alice", "a@ex.com"))
conn.commit()
cur.execute("SELECT * FROM users WHERE name = %s", ("Alice",))
print(cur.fetchone())

# === mysql.connector (MySQL) ===
import mysql.connector
conn = mysql.connector.connect(host="localhost", database="app", user="dev", password="secret")
cur = conn.cursor()
cur.execute("INSERT INTO users (name, email) VALUES (%s, %s)", ("Alice", "a@ex.com"))
conn.commit()
cur.execute("SELECT * FROM users WHERE name = %s", ("Alice",))
print(cur.fetchone())

# === psycopg (v3, PostgreSQL) ===
import psycopg
conn = psycopg.connect("host=localhost dbname=app user=dev password=secret")
cur = conn.cursor()
cur.execute("INSERT INTO users (name, email) VALUES (%s, %s)", ("Alice", "a@ex.com"))
conn.commit()
cur.execute("SELECT * FROM users WHERE name = %s", ("Alice",))
print(cur.fetchone())

The pattern is always the same: cursor.execute(sql_with_placeholders, tuple_of_values). That is PEP 249 at work — a specification that still governs how Python talks to databases across every major database engine.

Async execute(): The Modern Extension

PEP 249 predates Python's async/await syntax (PEP 492, Python 3.5, 2015), so it does not address asynchronous execution. Modern async database libraries provide their own execute() methods that follow the DB-API pattern but operate as coroutines:

import asyncio
import aiosqlite

async def main():
    async with aiosqlite.connect("app.db") as conn:
        cursor = await conn.execute(
            "SELECT * FROM users WHERE active = ?", (1,)
        )
        rows = await cursor.fetchall()
        for row in rows:
            print(row)

asyncio.run(main())

The psycopg v3 library provides both synchronous and asynchronous interfaces — for each synchronous object like Connection and Cursor, there is an async counterpart like AsyncConnection and AsyncCursor. The execute() call is the same conceptually; it just needs to be awaited.

import asyncio
import psycopg

async def main():
    async with await psycopg.AsyncConnection.connect(
        "host=localhost dbname=app user=dev"
    ) as conn:
        async with conn.cursor() as cur:
            await cur.execute(
                "SELECT * FROM users WHERE active = %s", (1,)
            )
            rows = await cur.fetchall()
            for row in rows:
                print(row)

asyncio.run(main())

Key Takeaways

1. Always use the parameters argument for external data. Never build SQL strings with user input. The structural separation between SQL and data is what makes parameterized queries injection-resistant.
2. Check your module's paramstyle first. The placeholder syntax changes between modules. sqlite3 uses ?, psycopg2 uses %s, python-oracledb uses :name. Using the wrong style produces errors or silent failures.
3. Never rely on the return value of execute(). PEP 249 says return values are undefined. Always use .fetchone(), .fetchall(), .fetchmany(), or iterate the cursor directly.
4. Single-value tuples require a trailing comma. ("Alice") is a string. ("Alice",) is a one-element tuple. The difference produces a silent bug that can take significant time to diagnose.
5. Table and column names cannot be parameterized. Use allowlist validation when you need dynamic identifiers. String-format only against a hardcoded set of valid values.
6. Use executemany() for bulk operations. Calling execute() in a loop is always slower. executemany() batches the work and reduces round-trips to the database.

cursor.execute() is the bridge between your Python code and your database. Every interaction crosses it. The method is deceptively simple — just a string and an optional tuple — but getting it right requires understanding the parameter binding system that makes it secure, the placeholder styles that make it database-specific, the state changes it triggers on the cursor, and the undefined return value that makes fetch methods necessary. The two rules that matter are worth repeating: always pass external data through the parameters argument, and always check your module's paramstyle before writing your first query. Everything else follows from those two principles.