1. Introduction

Python is a versatile programming language that can connect to various database management systems including Oracle, SQL Server, and MySQL. This interface allows developers to build dynamic web applications, perform data analytics, handle IoT applications, manage banking systems, and create real-time chat applications.

Applications of Python with Database:

• Web Applications with Dynamic Data: Create applications that fetch and display data from databases
• Data Analytics and Reporting: Analyze large datasets and generate reports
• IoT Applications: Collect and store sensor data from Internet of Things devices
• Banking and Financial Systems: Manage transactions and financial records securely
• Real-Time Chat Applications: Store and retrieve message data in real-time

2. Connecting Python with MySQL

To connect Python with MySQL, we need to use a MySQL driver library. The most commonly used driver is mysql-connector-python or PyMySQL.

2.1 Installing MySQL Driver:

pip install mysql-connector-python

2.2 Import MySQL Module:

import mysql.connector

3. Database Connection Methods

3.1 connect() Function:

The connect() function establishes a connection with the MySQL database. It returns a connection object.

Syntax:

connection = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

Example:

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password123",
    database="school"
)

if connection.is_connected():
    print("Successfully connected to MySQL database")

3.2 cursor() Function:

The cursor() function creates a cursor object from the connection. The cursor is used to execute SQL queries.

Syntax:

cursor = connection.cursor()

Example:

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password123",
    database="school"
)

cursor = connection.cursor()
print("Cursor created successfully")

4. execute() Function

The execute() function executes SQL queries. It takes the SQL query as a parameter and executes it.

Syntax:

cursor.execute("SQL query")

Example:

cursor.execute("SELECT * FROM student")

5. INSERT Operation Using Cursor

The INSERT operation adds new records to a database table using the execute() method and cursor object.

5.1 Basic INSERT Syntax:

cursor.execute("INSERT INTO table_name (col1, col2, col3) 
                VALUES (val1, val2, val3)")

5.2 INSERT with %s Format Specifier:

sql = "INSERT INTO student (roll_no, name, mark) 
        VALUES (%s, %s, %s)"
val = (101, "John", 85)
cursor.execute(sql, val)

5.3 Complete INSERT Example:

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password123",
    database="school"
)

cursor = connection.cursor()

sql = "INSERT INTO student (roll_no, name, mark) 
       VALUES (%s, %s, %s)"
val = (102, "Alice", 92)

cursor.execute(sql, val)
connection.commit()

print(cursor.rowcount, "record inserted successfully")

6. UPDATE Operation Using Cursor

The UPDATE operation modifies existing records in a database table.

6.1 UPDATE Syntax:

cursor.execute("UPDATE table_name SET col1 = value1 
                WHERE col2 = condition")

6.2 UPDATE with %s Format Specifier:

sql = "UPDATE student SET mark = %s WHERE roll_no = %s"
val = (95, 101)
cursor.execute(sql, val)

6.3 Complete UPDATE Example:

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password123",
    database="school"
)

cursor = connection.cursor()

sql = "UPDATE student SET mark = %s WHERE roll_no = %s"
val = (88, 102)

cursor.execute(sql, val)
connection.commit()

print(cursor.rowcount, "record updated successfully")

7. DELETE Operation Using Cursor

The DELETE operation removes records from a database table based on a condition.

7.1 DELETE Syntax:

cursor.execute("DELETE FROM table_name WHERE condition")

7.2 DELETE with %s Format Specifier:

sql = "DELETE FROM student WHERE roll_no = %s"
val = (105,)
cursor.execute(sql, val)

7.3 Complete DELETE Example:

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password123",
    database="school"
)

cursor = connection.cursor()

sql = "DELETE FROM student WHERE roll_no = %s"
val = (105,)

cursor.execute(sql, val)
connection.commit()

print(cursor.rowcount, "record deleted successfully")

8. Fetching Single Row - fetchone()

The fetchone() method retrieves a single row from the query result set and returns it as a tuple. Each subsequent call returns the next row.

Syntax:

cursor.execute("SELECT * FROM table_name")
row = cursor.fetchone()

Example:

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password123",
    database="school"
)

cursor = connection.cursor()
cursor.execute("SELECT * FROM student")

row = cursor.fetchone()
print(row)  # Prints first row
print(row[0], row[1], row[2])  # Access by index

row = cursor.fetchone()
print(row)  # Prints second row

9. Fetching All Rows - fetchall()

The fetchall() method retrieves all rows from the query result set and returns them as a list of tuples.

Syntax:

cursor.execute("SELECT * FROM table_name")
rows = cursor.fetchall()

Example:

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password123",
    database="school"
)

cursor = connection.cursor()
cursor.execute("SELECT * FROM student")

rows = cursor.fetchall()

for row in rows:
    print(row)

# Or access specific records
for row in rows:
    print("Roll No:", row[0], "Name:", row[1], "Mark:", row[2])

10. commit() Function

The commit() function saves (commits) all pending transactions to the database. Any INSERT, UPDATE, or DELETE operations are not permanent until commit() is called.

Syntax:

connection.commit()

Example:

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password123",
    database="school"
)

cursor = connection.cursor()

sql = "INSERT INTO student (roll_no, name, mark) 
       VALUES (%s, %s, %s)"
val = (103, "Bob", 87)

cursor.execute(sql, val)
connection.commit()  # Save the changes to database

print("Data committed to database")

11. rowcount Property

The rowcount property returns the number of rows affected by the last execute() operation. It is useful for verifying how many rows were inserted, updated, or deleted.

Syntax:

cursor.rowcount

Example:

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password123",
    database="school"
)

cursor = connection.cursor()

# INSERT operation
sql = "INSERT INTO student (roll_no, name, mark) 
       VALUES (%s, %s, %s)"
val = [(104, "Charlie", 90), (105, "Diana", 85)]

for record in val:
    cursor.execute(sql, record)

connection.commit()
print(cursor.rowcount, "rows inserted")

# UPDATE operation
sql = "UPDATE student SET mark = mark + 5 WHERE mark > 85"
cursor.execute(sql)
connection.commit()
print(cursor.rowcount, "rows updated")

# DELETE operation
sql = "DELETE FROM student WHERE roll_no > 110"
cursor.execute(sql)
connection.commit()
print(cursor.rowcount, "rows deleted")

12. Format Specifiers - %s and format()

Format specifiers are used to safely insert dynamic values into SQL queries, preventing SQL injection attacks.

12.1 Using %s Format Specifier:

The %s placeholder is replaced with the actual value from the tuple during execution.

sql = "SELECT * FROM student WHERE roll_no = %s"
val = (101,)
cursor.execute(sql, val)

12.2 Multiple %s Placeholders:

sql = "INSERT INTO student (roll_no, name, mark) 
       VALUES (%s, %s, %s)"
val = (106, "Eve", 88)
cursor.execute(sql, val)

12.3 Using format() Method:

The format() method can also be used, but %s with parameterized queries is safer.

# Using format() - Less safe approach
name = "Frank"
sql = "SELECT * FROM student WHERE name = '{}'".format(name)
cursor.execute(sql)

# Better approach with %s
sql = "SELECT * FROM student WHERE name = %s"
val = ("Frank",)
cursor.execute(sql, val)

12.4 Why Use Parameterized Queries:

• SQL Injection Prevention: Parameterized queries prevent malicious SQL injection attacks
• Data Type Handling: Automatically handles different data types correctly
• Security: Special characters are properly escaped

13. Creating Database Connectivity Applications

A complete database connectivity application combines connection, cursor, CRUD operations, and error handling.

13.1 Complete Application Example:

import mysql.connector
from mysql.connector import Error

def connect_db():
    """Establish database connection"""
    try:
        connection = mysql.connector.connect(
            host="localhost",
            user="root",
            password="password123",
            database="school"
        )
        if connection.is_connected():
            print("Connected to database")
        return connection
    except Error as e:
        print("Error:", e)
        return None

def insert_student(connection, roll_no, name, mark):
    """Insert a new student record"""
    try:
        cursor = connection.cursor()
        sql = "INSERT INTO student (roll_no, name, mark) 
               VALUES (%s, %s, %s)"
        val = (roll_no, name, mark)
        cursor.execute(sql, val)
        connection.commit()
        print(cursor.rowcount, "record inserted")
    except Error as e:
        print("Error:", e)

def update_student(connection, roll_no, mark):
    """Update student marks"""
    try:
        cursor = connection.cursor()
        sql = "UPDATE student SET mark = %s WHERE roll_no = %s"
        val = (mark, roll_no)
        cursor.execute(sql, val)
        connection.commit()
        print(cursor.rowcount, "record updated")
    except Error as e:
        print("Error:", e)

def delete_student(connection, roll_no):
    """Delete a student record"""
    try:
        cursor = connection.cursor()
        sql = "DELETE FROM student WHERE roll_no = %s"
        val = (roll_no,)
        cursor.execute(sql, val)
        connection.commit()
        print(cursor.rowcount, "record deleted")
    except Error as e:
        print("Error:", e)

def display_all_students(connection):
    """Display all student records"""
    try:
        cursor = connection.cursor()
        cursor.execute("SELECT * FROM student")
        rows = cursor.fetchall()
        for row in rows:
            print("Roll No:", row[0], "Name:", row[1], "Mark:", row[2])
    except Error as e:
        print("Error:", e)

def search_student(connection, roll_no):
    """Search for a specific student"""
    try:
        cursor = connection.cursor()
        sql = "SELECT * FROM student WHERE roll_no = %s"
        val = (roll_no,)
        cursor.execute(sql, val)
        result = cursor.fetchone()
        if result:
            print("Found:", result)
        else:
            print("Student not found")
    except Error as e:
        print("Error:", e)

# Main program
if __name__ == "__main__":
    conn = connect_db()
    
    if conn:
        # Insert students
        insert_student(conn, 101, "Alice", 85)
        insert_student(conn, 102, "Bob", 78)
        
        # Display all
        print("\nAll Students:")
        display_all_students(conn)
        
        # Update
        update_student(conn, 101, 90)
        
        # Search
        search_student(conn, 101)
        
        # Delete
        delete_student(conn, 102)
        
        conn.close()

13.2 Menu-Driven Application:

import mysql.connector

def main():
    connection = mysql.connector.connect(
        host="localhost",
        user="root",
        password="password123",
        database="school"
    )
    
    while True:
        print("\n=== Student Management System ===")
        print("1. Insert Student")
        print("2. Display All Students")
        print("3. Search Student")
        print("4. Update Student")
        print("5. Delete Student")
        print("6. Exit")
        
        choice = int(input("Enter your choice: "))
        
        if choice == 1:
            roll_no = int(input("Enter Roll No: "))
            name = input("Enter Name: ")
            mark = int(input("Enter Marks: "))
            
            cursor = connection.cursor()
            sql = "INSERT INTO student (roll_no, name, mark) 
                   VALUES (%s, %s, %s)"
            val = (roll_no, name, mark)
            cursor.execute(sql, val)
            connection.commit()
            print("Record inserted successfully")
        
        elif choice == 2:
            cursor = connection.cursor()
            cursor.execute("SELECT * FROM student")
            rows = cursor.fetchall()
            for row in rows:
                print(row)
        
        elif choice == 3:
            roll_no = int(input("Enter Roll No to search: "))
            cursor = connection.cursor()
            sql = "SELECT * FROM student WHERE roll_no = %s"
            val = (roll_no,)
            cursor.execute(sql, val)
            result = cursor.fetchone()
            if result:
                print("Found:", result)
            else:
                print("Not found")
        
        elif choice == 4:
            roll_no = int(input("Enter Roll No to update: "))
            mark = int(input("Enter new marks: "))
            cursor = connection.cursor()
            sql = "UPDATE student SET mark = %s WHERE roll_no = %s"
            val = (mark, roll_no)
            cursor.execute(sql, val)
            connection.commit()
            print("Updated successfully")
        
        elif choice == 5:
            roll_no = int(input("Enter Roll No to delete: "))
            cursor = connection.cursor()
            sql = "DELETE FROM student WHERE roll_no = %s"
            val = (roll_no,)
            cursor.execute(sql, val)
            connection.commit()
            print("Deleted successfully")
        
        elif choice == 6:
            connection.close()
            break
        
        else:
            print("Invalid choice")

if __name__ == "__main__":
    main()

14. Quick Reference - Key Functions and Methods