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

Q: Why do we use a cursor object instead of the connection object directly?

The connection object only manages the link to the database server; all SQL commands (execute, fetchall, fetchone) are issued through a cursor, which also tracks the result set position row by row.

Q: What happens if the database name is omitted from connect()?

The connection succeeds but no database is selected, so any query on a table will raise an error; you would then need to run `USE SCHOOL` via cursor.execute() before querying Account.

Q: Is fetchall() the only way to retrieve rows?

No — fetchone() returns a single row at a time and fetchmany(n) returns n rows; fetchall() is simplest for small result sets like this one.

Interface of Python with SQL Database

Python-MySQL Database Connectivity

Official Answer

A correct program must import the MySQL connector, open a connection with the given credentials, run a filtered SELECT query, and print the fetched rows.

Program:

``python


import mysql.connector as sql


con = sql.connect(host="localhost", user="admin", password="root", database="SCHOOL")
mycursor = con.cursor()
mycursor.execute("SELECT * FROM Account WHERE Fees<5000")
data = mycursor.fetchall()
for row in data:
    print(row)
con.close()

Output: every tuple (Stud_id, Sname, Class, Fees) of the Account table where Fees is below 5000 is printed, one record per line.

mysql.connectorcursorexecute()fetchall()connect()WHERE Fees<5000SCHOOL databaseAccount table

Marking Scheme

11 mark: correct import of mysql.connector module.
21 mark: correct connect() call with host, user (admin), password (root) and database (SCHOOL).
31 mark: correct cursor creation and execute() with the SQL query 'SELECT * FROM Account WHERE Fees<5000'.
41 mark: fetchall()/loop and print statement to display the records (partial credit if fetchone() in a loop is used correctly).

Hint

Import mysql.connector, connect() with the four given credentials plus database="SCHOOL", execute a SELECT with WHERE Fees<5000, then fetchall() and print each row.

Quick Oral Answer

I connect Python to MySQL using mysql.connector.connect() with the host, user, password and database name, create a cursor, execute a SELECT query with a WHERE clause on Fees, and print each row returned by fetchall().

Analysis & Explanation

This question tests the Python Database Connectivity (PDBC) syntax used to interface Python with a MySQL back end.

Concept:

mysql.connector.connect() opens the connection using host, user, password and database.
A cursor object is the handle used to send SQL commands to the server.
cursor.execute(query) sends the SQL string; cursor.fetchall() retrieves all matching rows as a list of tuples.

Exam trap:

Students often reverse the keyword arguments of connect() or forget database= for connecting directly to SCHOOL, forcing an extra USE SCHOOL statement.
Forgetting to loop over fetchall() and instead printing the cursor object directly gives no visible output.

Real-world application: This exact pattern (connect → cursor → execute → fetchall → loop) is the foundation of every data-driven web or desktop application that reads from a relational database, from school ERP systems to e-commerce inventories.

Common Mistakes

1Forgetting `import mysql.connector as sql` (or the equivalent import) before calling connect().
2Mixing up the order/names of connect() parameters, e.g. passing password where user is expected.
3Printing the cursor object instead of iterating over the result of fetchall().

Interesting Facts

mysql.connector is Oracle's own pure-Python driver for MySQL, requiring no separate C client library to be installed.

The connect-cursor-execute-fetch pattern follows Python's DB-API 2.0 specification (PEP 249), so the same code structure works with sqlite3, psycopg2 or any other DB-API compliant driver with minor changes.

fetchall() loads the entire result set into memory, so for very large tables fetchmany(size) or fetchone() in a loop is preferred in production code.

Spotted a mistake or something unclear?

Tell us — we fix reported answers fast.

Frequently Asked Questions

Why do we use a cursor object instead of the connection object directly?

The connection object only manages the link to the database server; all SQL commands (execute, fetchall, fetchone) are issued through a cursor, which also tracks the result set position row by row.

What happens if the database name is omitted from connect()?

The connection succeeds but no database is selected, so any query on a table will raise an error; you would then need to run `USE SCHOOL` via cursor.execute() before querying Account.

Is fetchall() the only way to retrieve rows?

No — fetchone() returns a single row at a time and fetchmany(n) returns n rows; fetchall() is simplest for small result sets like this one.