Python Database Applications (CBSE Class 12 Computer Science - 083)

In database applications, values entered by users should not be written directly into SQL statements. Python provides parameterized queries using the %s placeholder to safely pass values to SQL statements. This technique improves security, makes programs easier to maintain, and is recommended for real-world applications.

Learning Objectives

• Understand parameterized queries.
• Use the %s placeholder in SQL statements.
• Pass values using tuples.
• Use the format() method.
• Create Python-MySQL CRUD applications.

---

Why Use Parameterized Queries?

Suppose the user enters a student's name and marks. Instead of writing these values directly inside the SQL statement, placeholders can be used. The actual values are supplied separately when executing the query.

---

Using the %s Placeholder

The %s placeholder is used in SQL statements, regardless of whether the value is a string, integer, or floating-point number.

Syntax

sql = "INSERT INTO Student VALUES(%s, %s, %s, %s)"

values = (101, "Riya", "Jaipur", 95)

cursor.execute(sql, values)

---

Example 1: Insert Record Using %s

import mysql.connector

con = mysql.connector.connect(
    host="localhost",
    user="root",
    password="root",
    database="School"
)

cur = con.cursor()

sql = """
INSERT INTO Student
VALUES(%s, %s, %s, %s)
"""

values = (105, "Neha", "Delhi", 90)

cur.execute(sql, values)

con.commit()

print(cur.rowcount, "Record Inserted")

con.close()

Output

1 Record Inserted

---

Example 2: Update Record Using %s

sql = """
UPDATE Student
SET Marks=%s
WHERE StudentID=%s
"""

values = (98, 101)

cur.execute(sql, values)

con.commit()

---

Example 3: Delete Record Using %s

sql = """
DELETE FROM Student
WHERE StudentID=%s
"""

values = (105,)

cur.execute(sql, values)

con.commit()

Notice the comma after 105. A single value must still be passed as a tuple.

---

Example 4: Select Record Using %s

sql = """
SELECT *
FROM Student
WHERE City=%s
"""

value = ("Jaipur",)

cur.execute(sql, value)

records = cur.fetchall()

for row in records:
    print(row)

---

Using format()

Python's format() method can also be used to create SQL queries dynamically. However, it directly inserts values into the SQL string.

Example

city = "Jaipur"

sql = """
SELECT *
FROM Student
WHERE City='{}'
""".format(city)

cur.execute(sql)

---

Creating a Simple CRUD Application

A CRUD application performs the following operations:

• Create (INSERT)
• Read (SELECT)
• Update (UPDATE)
• Delete (DELETE)

---

Menu-Driven Example

1. Insert Record

2. Display Records

3. Update Record

4. Delete Record

5. Exit

---

Program Logic

Start

Display Menu

Read User Choice

If Choice = 1
    Insert Record

Else If Choice = 2
    Display Records

Else If Choice = 3
    Update Record

Else If Choice = 4
    Delete Record

Else
    Exit

Stop

---

Advantages of Parameterized Queries

• Improves program readability.
• Handles different data types automatically.
• Prevents SQL Injection attacks.
• Recommended by Python DB-API.
• Suitable for professional applications.

---

SQL Injection (Basic Introduction)

SQL Injection is a technique in which malicious SQL code is entered as user input to manipulate database queries.

Parameterized queries prevent such attacks by treating user input as data rather than executable SQL code.

---

Difference Between %s and format()

%s Placeholder	format()
Uses parameterized queries.	Creates SQL strings dynamically.
More secure.	Less secure.
Recommended for database applications.	Suitable only for simple demonstrations.
Protects against SQL Injection.	Does not provide built-in protection.

---

Execution Flow

Read User Input
      │
      ▼
Create SQL Query
      │
      ▼
Pass Values using Tuple
      │
      ▼
execute()
      │
      ▼
commit() (if required)
      │
      ▼
Display Result

---

Common Errors

Error	Reason
Incorrect number of parameters	The number of %s placeholders and tuple values do not match.
ProgrammingError	Invalid SQL syntax.
TypeError	Passing a single value instead of a tuple.
Record not inserted	commit() not called.

---

Exam Tips

---

Frequently Asked Questions (FAQs)

1. Why is the %s placeholder used?

It safely passes values to SQL queries and prevents SQL Injection.

2. Can %s be used for integers?

Yes. The %s placeholder is used for all data types.

3. Why is a comma required in a single-value tuple?

Without the comma, Python treats the value as a normal variable instead of a tuple.

4. Which is better: %s or format()?

The %s placeholder is recommended because it is more secure and follows the Python DB-API standard.

5. What is a CRUD application?

A CRUD application performs Create, Read, Update, and Delete operations on a database.

---

Summary

• Parameterized queries use the %s placeholder to safely pass values.
• Values are supplied as tuples to the execute() method.
• The format() method can also create SQL queries but is less secure.
• CRUD applications perform Create, Read, Update, and Delete operations.
• Parameterized queries improve security and code quality.
• Using %s with execute() is the recommended approach for Python-MySQL applications.