Python SQL Interface Notes
Uploaded by
Ramanathan MeiyappanPython SQL Interface Notes
Uploaded by
Ramanathan MeiyappanInterface of Python with MySQL – Class
12 Important Q&A
1. Theory / Concept Questions
Q: Which library is used to connect Python with MySQL?
A: The library used is `mysql.connector`. It allows Python programs to connect and interact
with MySQL databases.
Example:
import mysql.connector
Q: Which function is used to open a connection?
A: The `connect()` function is used.
Syntax:
con = mysql.connector.connect(host='localhost', user='root', passwd='password',
database='test')
Q: How to check if the connection is established?
A: Use the `is_connected()` method:
if con.is_connected():
print('Connected successfully')
Q: What is a cursor? How is it created?
A: A cursor is a pointer that helps execute SQL commands and fetch results.
Example:
cur = con.cursor()
Q: Which function is used to execute queries?
A: The `execute()` function of a cursor is used to run queries.
cur.execute('SELECT * FROM students')
Q: Which functions fetch data from a result set?
A: - `fetchall()` – retrieves all rows
- `fetchone()` – retrieves one row
- `fetchmany(n)` – retrieves n rows
2. Code-Based Questions and Examples
Q: Delete a record from THEATRE table
1. Solution:
import mysql.connector as sql
con = sql.connect(host='localhost', user='root', password='Ex2025', database='CINEMA')
cur = con.cursor()
Tid = input('Enter Theatre ID: ')
cur.execute(f"DELETE FROM THEATRE WHERE Th_ID = {Tid}")
con.commit()
con.close()
Q: Insert a new record into Bank_Account table
2. Solution:
import mysql.connector as pm
con = pm.connect(host='localhost', user='admin', password='root', database='Bank')
cur = con.cursor()
Accno = int(input('Accno: '))
Cname = input('Cname: ')
Atype = input('Atype: ')
Amount = float(input('Amount: '))
cur.execute(f"INSERT INTO BANK_ACCOUNT VALUES ({Accno}, '{Cname}', '{Atype}',
{Amount})")
con.commit()
con.close()
Q: Update quantity in shop table
3. Solution:
import mysql.connector as pm
con = pm.connect(host='localhost', user='admin', password='Shopping', database='Keeper')
cur = con.cursor()
cur.execute("UPDATE SHOP SET QTY=20 WHERE ITEM_CODE=111")
con.commit()
con.close()
Q: Display all passenger details from Flight table
4. Solution:
import mysql.connector as pm
con = pm.connect(host='localhost', user='root', password='airplane', database='Travel')
cur = con.cursor()
cur.execute("SELECT * FROM Flight")
for row in cur.fetchall():
print(row)
con.close()
Q: Fetch employees from city 'Delhi'
5. Solution:
import mysql.connector as mysql
con = mysql.connect(host='localhost', user='root', passwd='root', database='emp')
cur = con.cursor()
cur.execute("SELECT * FROM employee WHERE City='Delhi'")
for row in cur.fetchall():
print(row)
📊 1. fetchone()
Return type: tuple (or None if no more rows are available)
Description:
o Fetches only one row from the result set.
o Each column value of that row is stored as an element of the tuple.
✅ Example:
cur.execute("SELECT name, age FROM students")
row = cur.fetchone()
print(row)
Output (example):
('Riya', 17)
row is a tuple containing one row.
📊 2. fetchmany(n)
Return type: list of tuples
Description:
o Fetches n rows from the result set.
o Each row is a tuple, and the collection of rows is returned as a list.
✅ Example:
cur.execute("SELECT name, age FROM students")
rows = cur.fetchmany(3)
print(rows)
Output (example):
[('Riya', 17), ('Arjun', 18), ('Meera', 17)]
rows is a list of tuples.
📊 3. fetchall()
Return type: list of tuples
Description:
o Fetches all remaining rows from the result set.
o Same structure as fetchmany(), but for all rows.
✅ Example:
cur.execute("SELECT name, age FROM students")
rows = cur.fetchall()
print(rows)
Output (example):
[('Riya', 17), ('Arjun', 18), ('Meera', 17), ('Rahul', 19)]
rows is a list of tuples.
📌 Summary Table:
