Running a Query from Python: execute, Parameters, commit and fetch

What will we learn?#

Until now we wrote “manual” SQL in Database Client. Now we connect it to code, following the path the syllabus emphasizes:

Python  →  Connection  →  Query  →  Database  →  Result  →  Python
(values)   (open)         (SQL +    (runs it)     (rows)     (tuples,
                           params)                            ints, strs)

• execute with parameters (%s).
• SQL Injection: the danger, and why parameters solve it.
• commit: saving changes.
• lastrowid: the id the database gave the new row.
• fetchone / fetchall on real data.

Why do we need this?#

In an API, the values come from the user: a name, an age and an email from the Body of a POST. The code needs to put them into the query safely, save, and return what was saved, including the new id (201 Created + the student). All of this happens in this lesson, in real code.

Explanation from the basics#

execute(sql, params)#

cursor.execute(
    "INSERT INTO students (name, age, email) VALUES (%s, %s, %s)",
    ("Dana", 24, "dana@mail.com"),
)

• In the SQL you write %s in place of every value (a Placeholder). In MySQL it is always %s — for every type, numbers too.
• The values are passed separately, as a tuple, in the same order as the Placeholders.
• The Driver (mysql.connector) puts them in place safely: strings as strings, and numbers as numbers. There is no need for quotes, and no need for conversion.

SQL Injection — why not to build SQL by concatenation#

It looks “simple” to build the query ourselves:

name = input("Student name to search: ")
sql = "SELECT id, name FROM students WHERE name = '" + name + "'"

If the user types Dana, you get the query ... WHERE name = 'Dana'. Fine. But if they type:

x' OR '1'='1

You get the query:

SELECT id, name FROM students WHERE name = 'x' OR '1'='1'

'1'='1' is always true, so all the students are returned. The user changed the meaning of the query through the input. This is SQL Injection, one of the most common and most dangerous security breaches in the world. Through it, people break into Databases, steal passwords, and delete tables.

With %s and parameters, the Driver treats the input always as a value, and never as part of the SQL. The user can type whatever they want, and the database will simply look for a student whose name is that strange string.

How exactly does %s protect?#

With concatenation, the attack works because of one quote: the quote the user typed after x closes our text, and everything after it (OR '1'='1') is already read as part of the command.

With %s, the Driver prepares the value before sending it: it adds a backslash (\) before every quote in the input. A backslash before a quote tells MySQL: “this is a regular quote inside the text, not the end of the text”.

The user’s quote can no longer close the text, so all the input stays inside as one name. There is no OR that became part of the command, and MySQL looks for a student with that name — and does not find one ([]).

And why not do it yourself? Because it is easy to miss a case: a double quote, a backslash in the input, a special encoding. The Driver knows how to handle all of them, every time — that is why it is always %s.

commit — really saving#

Commands that change data (INSERT, UPDATE, DELETE) first go into an open Transaction. The change is visible inside the current connection, but it is not saved finally until you call connection.commit(). If you close the connection without a commit, the changes are thrown away.

• SELECT does not need a commit, because it changes nothing.
• CREATE TABLE is saved in MySQL immediately, even without a commit.

lastrowid and rowcount#

After execute, the Cursor has information about what happened:

Syntax#

connection = get_connection()
try:
    cursor = connection.cursor()

    # change data: execute + commit
    cursor.execute("INSERT INTO t (a, b) VALUES (%s, %s)", (value_a, value_b))
    connection.commit()
    new_id = cursor.lastrowid

    # read data: execute + fetch
    cursor.execute("SELECT a, b FROM t WHERE b > %s", (10,))
    rows = cursor.fetchall()
    cursor.execute("SELECT a, b FROM t WHERE id = %s", (new_id,))
    row = cursor.fetchone()
finally:
    connection.close()

Notice the (10,): a tuple of one item must have a comma (remember tuples!). (10) is just the number 10, and execute will fail.

First code example#

week-4/school-db/students_demo.py:

import mysql.connector

def get_connection():
    return mysql.connector.connect(
        host="localhost", user="root", password="kodkod", database="school")

connection = get_connection()
try:
    cursor = connection.cursor()
    cursor.execute(
        """
        CREATE TABLE IF NOT EXISTS students (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            age INT NOT NULL,
            email VARCHAR(100) NOT NULL UNIQUE
        )
        """
    )

    cursor.execute(
        "INSERT INTO students (name, age, email) VALUES (%s, %s, %s)",
        ("Dana", 24, "dana@mail.com"),
    )
    connection.commit()
    print("Inserted student with id", cursor.lastrowid)

    cursor.execute("SELECT id, name, age, email FROM students")
    print("All rows:", cursor.fetchall())
finally:
    connection.close()

Output (the first run, on an empty table):

Inserted student with id 1
All rows: [(1, 'Dana', 24, 'dana@mail.com')]

Breaking down the code line by line#

Run it again:

Traceback (most recent call last):
  ...
mysql.connector.errors.IntegrityError: 1062 (23000): Duplicate entry 'dana@mail.com' for key 'students.email'

On the second run, Dana’s INSERT fails, because the email already exists, and the database enforced UNIQUE. (And despite the error, the connection was closed, thanks to finally.) Later in this topic we will handle this error properly. Meanwhile, to start over: in Database Client run DROP TABLE students;.

Another example#

Adding several students, searches with parameters, and a demonstration of SQL Injection.

import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
try:
    cursor = connection.cursor()
    cursor.execute("DROP TABLE IF EXISTS students")
    cursor.execute(
        "CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, "
        "name VARCHAR(100) NOT NULL, age INT NOT NULL)")
    for name, age in [("Dana", 24), ("Avi", 31), ("Noa", 22)]:
        cursor.execute("INSERT INTO students (name, age) VALUES (%s, %s)", (name, age))
    connection.commit()

    cursor.execute("SELECT id, name, age FROM students WHERE id = %s", (2,))
    print("By id 2:", cursor.fetchone())

    cursor.execute("SELECT name FROM students WHERE age > %s ORDER BY age", (23,))
    print("Older than 23:", cursor.fetchall())

    cursor.execute("SELECT id, name FROM students WHERE id = %s", (99,))
    print("By id 99:", cursor.fetchone())

    attack = "x' OR '1'='1"

    unsafe_sql = "SELECT name FROM students WHERE name = '" + attack + "'"
    print("UNSAFE query:", unsafe_sql)
    cursor.execute(unsafe_sql)
    print("UNSAFE result:", cursor.fetchall())

    cursor.execute("SELECT name FROM students WHERE name = %s", (attack,))
    print("SAFE result:", cursor.fetchall())
finally:
    connection.close()

Output:

By id 2: (2, 'Avi', 31)
Older than 23: [('Dana',), ('Avi',)]
By id 99: None
UNSAFE query: SELECT name FROM students WHERE name = 'x' OR '1'='1'
UNSAFE result: [('Dana',), ('Avi',), ('Noa',)]
SAFE result: []

Breakdown:

• DROP TABLE IF EXISTS students — deletes the table, if it exists, so that every run starts clean. (Convenient for demonstrations. You do not do this in real code!)
• INSERT in a loop, and one commit at the end — the three insertions are saved together.
• fetchone() on an existing id → a tuple. On an id that does not exist → None. That is the “not found” that will become a 404.
• (2,), (23,) — a tuple of one value, with a comma.
• The unsafe version returned all the students, because the input changed the query itself.
• The safe version looked for a student whose name is exactly x' OR '1'='1, and there is no such student, so []. The attack failed.

Common mistake#

Mistake 1: forgetting commit.

cursor.execute("INSERT INTO students (name, age) VALUES (%s, %s)", ("Tom", 27))
connection.close()

There is no error, and Tom was not saved. Closing without a commit = cancelling the changes. This is one of the most confusing mistakes, because “everything worked”.

Mistake 2: a single parameter without a comma.

cursor.execute("SELECT name FROM students WHERE id = %s", (2))

mysql.connector.errors.ProgrammingError: Could not process parameters: int(2), it must be of type list, tuple or dict

(2) is an int, not a tuple. → (2,).

Mistake 3: a number of parameters that does not match.

cursor.execute("INSERT INTO students (name, age) VALUES (%s, %s)", ("Tom",))

mysql.connector.errors.ProgrammingError: Not enough parameters for the SQL statement

Mistake 4: %s with quotes.

cursor.execute("SELECT * FROM students WHERE name = '%s'", ("Dana",))

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; ... near 'Dana''' at line 1

The Driver already adds quotes by itself, and now there are double quotes. %s always without quotes.

Mistake 5: %s in place of the name of a table or a column. "SELECT * FROM %s" does not work: Placeholders are only for values — the Driver turns students into 'students' (a string), and that is a syntax error. Names of tables and columns are written in the code, fixed.

Practice#

The exercises use only what was taught in this lesson and the lessons before it. Try on your own first, and only then open the solution.

Exercise 1: execute with parameters#

Write a function add_student(connection, name, age) that adds a student with parameters (%s), saves, and returns the new id.

cursor = connection.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS students "
               "(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, age INT NOT NULL)")
print(add_student(connection, "Dana", 24))
print(add_student(connection, "Avi", 31))
cursor.execute("SELECT * FROM students")
print(cursor.fetchall())

Expected output:

1
2
[(1, 'Dana', 24), (2, 'Avi', 31)]

import mysql.connector


def add_student(connection, name, age):
    cursor = connection.cursor()
    cursor.execute(
        "INSERT INTO students (name, age) VALUES (%s, %s)", (name, age))
    connection.commit()
    return cursor.lastrowid


connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
cursor = connection.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS students "
               "(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, age INT NOT NULL)")
print(add_student(connection, "Dana", 24))
print(add_student(connection, "Avi", 31))
cursor.execute("SELECT * FROM students")
print(cursor.fetchall())
connection.close()

1
2
[(1, 'Dana', 24), (2, 'Avi', 31)]

Exercise 2: SQL Injection#

The table has two students — Dana and Avi. Create it like this:

import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
cursor = connection.cursor()
cursor.execute("DROP TABLE IF EXISTS students")   # start clean, so ids start from 1
cursor.execute("CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100))")
cursor.execute("INSERT INTO students (name) VALUES ('Dana'), ('Avi')")
connection.commit()
connection.close()

Code that builds a query from user input:

name = "x' OR '1'='1"
sql = "SELECT name FROM students WHERE name = '" + name + "'"

1. What will the command that is created look like, and what will it return?
2. Show the difference in code: run the query by concatenation and print Unsafe: and the result, and then the same query with %s and print Safe: and the result.

Expected output:

Unsafe: [('Dana',), ('Avi',)]
Safe: []

import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
cursor = connection.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS students "
               "(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100))")
cursor.execute("INSERT INTO students (name) VALUES ('Dana'), ('Avi')")
connection.commit()

name = "x' OR '1'='1"

sql = "SELECT name FROM students WHERE name = '" + name + "'"
cursor.execute(sql)
print("Unsafe:", cursor.fetchall())

cursor.execute("SELECT name FROM students WHERE name = %s", (name,))
print("Safe:", cursor.fetchall())
connection.close()

Unsafe: [('Dana',), ('Avi',)]
Safe: []

Exercise 3: The forgotten commit#

connection = mysql.connector.connect(...)
cursor = connection.cursor()
cursor.execute("INSERT INTO students (name, age) VALUES (%s, %s)", ("Noa", 22))
connection.close()

The program ran without an error, but Noa is not in the database. Why?

Show it in code: start from an empty students table (with name and age). Insert Noa without commit, close, and in a new connection count how many rows there are in the table. Then insert her again with commit, and count again.

Expected output:

Without commit: 0 rows
With commit: 1 rows

import mysql.connector


def connect():
    return mysql.connector.connect(
        host="localhost", user="root", password="kodkod", database="school")


def count_rows():
    connection = connect()
    try:
        cursor = connection.cursor()
        cursor.execute("SELECT * FROM students")
        return len(cursor.fetchall())
    finally:
        connection.close()


setup = connect()
setup.cursor().execute("CREATE TABLE IF NOT EXISTS students (name VARCHAR(100), age INT)")
setup.close()

connection = connect()
cursor = connection.cursor()
cursor.execute("INSERT INTO students (name, age) VALUES (%s, %s)", ("Noa", 22))
connection.close()
print("Without commit:", count_rows(), "rows")

connection = connect()
cursor = connection.cursor()
cursor.execute("INSERT INTO students (name, age) VALUES (%s, %s)", ("Noa", 22))
connection.commit()
connection.close()
print("With commit:", count_rows(), "rows")

Without commit: 0 rows
With commit: 1 rows

Exercise 4: A safe search by id#

The table has one student — Dana, age 24. Create it like this:

import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
cursor = connection.cursor()
cursor.execute("DROP TABLE IF EXISTS students")   # start clean, so ids start from 1
cursor.execute("CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), age INT)")
cursor.execute("INSERT INTO students (name, age) VALUES ('Dana', 24)")
connection.commit()
connection.close()

Write find_by_id(student_id) that opens a connection, searches for a student with a parameter, and always closes. It returns the row or None.

print(find_by_id(1))
print(find_by_id(99))

Expected output:

(1, 'Dana', 24)
None

import mysql.connector


def get_connection():
    return mysql.connector.connect(
        host="localhost", user="root", password="kodkod", database="school")


def find_by_id(student_id):
    connection = get_connection()
    try:
        cursor = connection.cursor()
        cursor.execute("SELECT * FROM students WHERE id = %s", (student_id,))
        return cursor.fetchone()
    finally:
        connection.close()


setup = get_connection()
cursor = setup.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS students "
               "(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), age INT)")
cursor.execute("INSERT INTO students (name, age) VALUES ('Dana', 24)")
setup.commit()
setup.close()

print(find_by_id(1))
print(find_by_id(99))

(1, 'Dana', 24)
None

Exercise 5: The path of a query#

Put in order the steps of running a query from Python:

• (a) connection.commit() — only if we changed data
• (b) mysql.connector.connect(...) and then connection.cursor()
• (c) connection.close()
• (d) cursor.execute(sql, params)
• (e) cursor.fetchone() / fetchall() — only if we read data

Summary#

• The path: Python → Connection → Query (with %s and parameters) → Database → Result → Python.
• cursor.execute(sql, params) — values only through %s and a tuple. A tuple of one value: (x,).
• SQL Injection = input that changes the query. Never concatenate values from outside into SQL.
• Changing data (INSERT/UPDATE/DELETE) → commit(), otherwise the change is thrown away on closing.
• cursor.lastrowid = the new id. fetchone() = a row or None. fetchall() = a list of tuples.
• A function for every action, with its own connection and try/finally.

Official documentation#

• MySQLCursor.execute()
• Inserting Data Using Connector/Python
• Querying Data Using Connector/Python