Q.Peter has created a table named Account in MySQL database, SCHOOL, having following structure : Stud_id – integer Sname – string Class – string Fees – float Help him in writing a Python program to display records of those students whose fees is less than 5000. Note the following to establish connectivity between Python and MySQL : Username – admin Password – root Host – localhost

To connect Python with a MySQL database, we use the mysql.connector library. This library acts as a bridge, allowing Python programs to execute SQL commands and retrieve data. The process involves establishing a connection using the provided credentials, creating a cursor to execute queries, running the desired SQL query, fetching the results, and then displaying them. It is crucial to close both the cursor and the connection to release resources.

import mysql.connector

try:
    # Establish connection to the MySQL database
    mydb = mysql.connector.connect(
        host="localhost",
        user="admin",
        password="root",
        database="SCHOOL"
    )

    # Create a cursor object to execute SQL queries
    mycursor = mydb.cursor()

    # SQL query to select records where Fees is less than 5000
    sql_query = "SELECT * FROM Account WHERE Fees < 5000"

    # Execute the query
    mycursor.execute(sql_query)

    # Fetch all the records that match the query
    results = mycursor.fetchall()

    # Display the fetched records
    print("Records of students with fees less than 5000:")
    if results:
        for record in results:
            print(record)
    else:
        print("No students found with fees less than 5000.")

except mysql.connector.Error as err: …

In the world of data management, databases like MySQL are crucial for storing information in an organised and persistent manner. When we build applications using a programming language like Python, we often need to interact with these databases – to store new data, retrieve existing records, or update information. This interaction is what allows our programs to be dynamic and work with real-world data.

Peter's task is a classic example: he has student data in a MySQL database and needs a Python program to selectively display records based on a specific condition. This involves a few key steps, starting with establishing a bridge between Python and MySQL.

The Bridge: Connecting Python to MySQL

Python doesn't inherently "understand" how to talk to a MySQL database. It needs a special library, often called a "connector," to facilitate this communication. For MySQL, the most common and recommended library is mysql.connector. Before writing any code, ensure this library is installed in your Python environment. If not, you would typically install it using pip: pip install mysql-connector-python.

Once the connector is available, the first step in your Python program is to import it and then establish a connection to the MySQL server. This connection requires specific details: the host where the database resides, the username and password for authentication, and the name of the database you wish to access.

import mysql.connector

# Database connection details
DB_CONFIG = {
    'host': 'localhost',
    'user': 'admin',
    'password': 'root',
    'database': 'SCHOOL'
}

connection = None # Initialize connection to None
try:
    # Establish the connection
    connection = mysql.connector.connect(**DB_CONFIG)
    if connection.is_connected():
        print("Successfully connected to MySQL database.")

except mysql.connector.Error as err:
    print(f"Error connecting to MySQL: {err}")
    # Exit or handle the error appropriately if connection fails
    exit()

The Navigator: Creating a Cursor

Once a connection is established, you need a way to send SQL commands to the database and receive results. This is where a "cursor" comes in. Think of a cursor as a pointer or a control structure that allows you to traverse the records in a database. It's the object through which you execute SQL queries and fetch the results.

# Create a cursor object
cursor = connection.cursor()

The Command: Executing the SQL Query

Now that you have a connection and a cursor, you can formulate your SQL query. Peter wants to display records of students whose fees are less than 5000. The SQL SELECT statement is used to retrieve data, and the WHERE clause is used to specify the condition.

The table Account has columns Stud_id, Sname, Class, and Fees. To select all columns for students meeting the criteria, the SQL query will be:

SELECT * FROM Account WHERE Fees < 5000;

You execute this query using the execute() method of the cursor object.

# SQL query to select students with fees less than 5000
sql_query = "SELECT Stud_id, Sname, Class, Fees FROM Account WHERE Fees < 5000;"

# Execute the query
cursor.execute(sql_query)

The Retrieval: Fetching and Displaying Results

After executing the query, the results are available through the cursor. To get all the rows that match your query, you use the fetchall() method of the cursor. This method returns a list of tuples, where each tuple represents a row from the database.

You can then iterate through this list and print each student's record.

# Fetch all the records that match the query
records = cursor.fetchall()

# Check if any records were found
if records:
    print("\nStudents with Fees less than 5000:")
    print("-" * 40)
    # Print header
    print(f"{'ID':<5} {'Name':<15} {'Class':<8} {'Fees':<8}")
    print("-" * 40)
    for row in records:
        # Each row is a tuple (Stud_id, Sname, Class, Fees)
        print(f"{row[0]:<5} {row[1]:<15} {row[2]:<8} {row[3]:<8.2f}")
    print("-" * 40)
else:
    print("\nNo students found with fees less than 5000.")

The Cleanup: Closing Resources

It's crucial to close the cursor and the database connection once you are done with them. This releases the resources held by your program and the database server, preventing resource leaks and ensuring efficient operation. This is typically done using the close() methods.

finally:
    # Close the cursor and connection …