# Running a Query from Python: execute, Parameters, commit and fetch<no value>

## 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:

```text
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)`

```python
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:

```python
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:

```text
x' OR '1'='1
```

You get the query:

```sql
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".

| Step | What you see |
|---|---|
| The user types | `x' OR '1'='1` |
| The Driver adds `\` before every quote | `x\' OR \'1\'=\'1` |
| What MySQL receives | `SELECT name FROM students WHERE name = 'x\' OR \'1\'=\'1'` |
| How MySQL reads it | **One** text: a long, strange name — `x' OR '1'='1` |

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`.

{{< box "warn" "An iron rule" >}}
**Never** build SQL with `+` from values that come from outside. **Always `%s` and parameters.** There are no exceptions.
{{< /box >}}

{{< box "analogy" "Analogy: a form with boxes" >}}
Concatenating strings = letting a stranger write freely on your check, including on the amount line. Parameters = a printed check with a dedicated box for the name, and what is written in the box is **only the name**: you cannot "overflow" from it and change the amount.
{{< /box >}}

### `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:

| Attribute | What it contains | Use |
|---|---|---|
| `cursor.lastrowid` | The id the database gave **the last row that was inserted** | Returning the new item from a POST |
| `cursor.rowcount` | **How many rows changed** in an INSERT/UPDATE/DELETE | Discovering "not found" in an UPDATE/DELETE (later in this topic) |

## Syntax

```python
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`:

```python
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):**

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

## Breaking down the code line by line

| Code | Step in the path |
|---|---|
| `get_connection()` | **Connection**: opening. |
| `cursor = connection.cursor()` | The Cursor through which we will run all the queries. |
| `"""..."""` | **A multi-line string** (triple quotes). Convenient for long SQL. |
| `execute(CREATE TABLE ...)` | Creating the table, if it does not exist. |
| `execute("INSERT ... VALUES (%s, %s, %s)", ("Dana", 24, "dana@mail.com"))` | A **Query** with 3 Placeholders and 3 values. **Python → Database.** |
| `connection.commit()` | **Saving.** Without it, the student would disappear on closing. |
| `cursor.lastrowid` | The id the database gave: 1. |
| `execute("SELECT ...")` + `fetchall()` | **Result → Python**: a list of tuples. |
| `finally: connection.close()` | Closing, always. |

**Run it again:**

```text
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.**

```python
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:**

```text
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`.**

```python
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.**

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

```text
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.**

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

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

**Mistake 4: `%s` with quotes.**

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

```text
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.

<!-- exercises:start — generated by scripts/intro6/sync_site_exercises.py; edit scripts/intro6/exercises/ -->
## 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`.

```python
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:**

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

{{< solution >}}
```python
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()
```

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

- `%s` are **placeholders**, and the values are passed separately as a tuple. (In MySQL you write `%s` for every type — numbers too.)
- `cursor.lastrowid` — the `id` the database gave the new row.
{{< /solution >}}

### Exercise 2: SQL Injection

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

```py
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:

```text
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:**

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

{{< solution >}}
1. The command is `SELECT name FROM students WHERE name = 'x' OR '1'='1'` — and the condition `'1'='1'` is always true, so it returns **all the students**. The user's input became part of the **command**.
2.

```python
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()
```

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

With `%s` the value is passed **separately** — the Driver treats it as a strange name, never as a command. **Never build SQL by concatenating input.**
{{< /solution >}}

### Exercise 3: The forgotten commit

```text
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:**

```text
Without commit: 0 rows
With commit: 1 rows
```

{{< solution >}}
Without `commit()` the change stays **temporary**, and `close()` silently cancels it.

```python
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")
```

```text
Without commit: 0 rows
With commit: 1 rows
```

`CREATE TABLE` is saved immediately, but every `INSERT`, `UPDATE` and `DELETE` waits for `commit`.
{{< /solution >}}

### Exercise 4: A safe search by id

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

```py
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`.

```python
print(find_by_id(1))
print(find_by_id(99))
```

**Expected output:**

```text
(1, 'Dana', 24)
None
```

{{< solution >}}
```python
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))
```

```text
(1, 'Dana', 24)
None
```

Notice the `(student_id,)` — a tuple with **one** item needs a comma. Without the comma it is just brackets around a number.
{{< /solution >}}

### 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

{{< solution >}}
The order: **(b), (d), then (e) or (a), and at the end (c)**

1. **connect + cursor** — open a connection and create a Cursor.
2. **execute** — run the query with the parameters.
3. If it is a read (`SELECT`) — **fetch** the results. If it is a change (`INSERT`, `UPDATE`, `DELETE`) — **commit**.
4. **close** — close, inside `finally`.
{{< /solution >}}

<!-- exercises:end -->

## 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`.

<!-- docs:start — generated by scripts/intro6/site_docs.py; edit the links there -->

## Official documentation

- [MySQLCursor.execute()](https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html)
- [Inserting Data Using Connector/Python](https://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-transaction.html)
- [Querying Data Using Connector/Python](https://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-select.html)

<!-- docs:end -->