Chapter 11: Interfacing Python with SQL

While SQL is great for manipulating databases, users rarely interact with a database directly through an SQL command line. They interact through applications (like websites or desktop software). In this chapter, we will learn how to write a Python application that connects to an SQL database (like MySQL) to perform database operations programmatically.

11.1 The Architecture

To connect Python to MySQL, we need a connector module. The most common one used in the CBSE curriculum is mysql-connector-python.

Steps for Database Connectivity:

Import the module: Bring the connector library into your Python script.
Establish a connection: Open a connection to the MySQL server using credentials (host, user, password, database).
Create a cursor instance: A cursor is an object that executes SQL queries and retrieves results.
Execute a query: Pass your SQL command as a string to the cursor.
Extract the results: If it was a SELECT query, fetch the data. If it was INSERT, UPDATE, or DELETE, commit() the changes.
Clean up: Close the cursor and the connection.

11.2 Connecting to the Database

import mysql.connector

try:
    # 1. Establish connection
    mydb = mysql.connector.connect(
      host="localhost",
      user="yourusername",
      password="yourpassword",
      database="SchoolDB" # Assuming this database already exists
    )

    print("Connection established successfully!")

except mysql.connector.Error as err:
    print(f"Error connecting to database: {err}")

# Optional: Close connection immediately if just testing
# if mydb.is_connected():
#     mydb.close()

11.3 Executing Queries using the Cursor

1. Retrieving Data (SELECT Queries)

To fetch data, we use the cursor’s execute() method to run the query, and then fetch the results.

fetchone(): Returns the next row of the result set as a tuple. Returns None if no more rows are available.
fetchall(): Returns all remaining rows as a list of tuples.
rowcount: An attribute of the cursor that tells you how many rows were returned or affected by the last executed statement.

import mysql.connector

# ... (Connection code from above) ...
mydb = mysql.connector.connect(host="localhost", user="root", password="", database="SchoolDB")

# 2. Create the cursor
mycursor = mydb.cursor()

# 3. Execute the query
mycursor.execute("SELECT * FROM Student")

# 4. Extract results
results = mycursor.fetchall()

print("Total rows retrieved:", mycursor.rowcount)

for row in results:
    # row is a tuple containing the data for exactly one record
    print(f"RollNo: {row[0]}, Name: {row[1]}, City: {row[3]}")

# 5. Clean up
mycursor.close()
mydb.close()

2. Modifying Data (INSERT, UPDATE, DELETE)

When you make structural changes to the database or modify the data, you MUST call the commit() method on the connection object. If you don’t commit, the changes will be rolled back (undone) when the script ends!

import mysql.connector

mydb = mysql.connector.connect(host="localhost", user="root", password="", database="SchoolDB")
mycursor = mydb.cursor()

# Example: UPDATE operation
sql = "UPDATE Student SET Fee = Fee + 100 WHERE City = 'Delhi'"
mycursor.execute(sql)

# IMPORTANT: Commit the transaction to save changes
mydb.commit()

# Output the number of rows affected
print(mycursor.rowcount, "record(s) updated.")

mycursor.close()
mydb.close()

11.4 Parameterized Queries (Handling Dynamic Input)

Often, you don’t want to hardcode the SQL query. You want to insert data provided by a user (e.g., from an input() statement).

Warning: Never use simple string concatenation (+ or f-strings) to inject user input directly into an SQL query string. This makes your application vulnerable to SQL Injection attacks, where a malicious user could potentially delete your entire database.

Instead, use the %s format specifier. The connector will safely sanitize the inputs before executing the query.

import mysql.connector

mydb = mysql.connector.connect(host="localhost", user="root", password="", database="SchoolDB")
mycursor = mydb.cursor()

# Get data from the user
new_roll = int(input("Enter Roll No: "))
new_name = input("Enter Name: ")
new_city = input("Enter City: ")
new_fee = float(input("Enter Fee: "))

# The SQL query string uses %s placeholders
sql = "INSERT INTO Student (RollNo, Name, City, Fee) VALUES (%s, %s, %s, %s)"

# The data MUST be provided as a tuple
val = (new_roll, new_name, new_city, new_fee)

# Execute by passing both the query string and the data tuple
mycursor.execute(sql, val)

mydb.commit()
print(mycursor.rowcount, "record inserted.")

mycursor.close()
mydb.close()

(Note: While some older Python code uses the string .format() method to achieve parameterization, using the %s binding built into the MySQL connector is the safest and most standard practice).

By combining the logic of Python with the data management power of SQL, you can build incredibly robust, data-driven applications.

Keyboard shortcuts