“Integrate SQL with Python by importing a suitable module” is a standard Class 12 computer science practical. The answer is to import mysql.connector, connect to the database, make a cursor, run SQL commands with execute(), read the rows with fetchall() or fetchone(), and save changes with commit(). In this post you will get complete programs to insert, display, search, update and delete records in a student table, a picture of how the data travels between Python and the database, a version with the built-in sqlite3 module that runs without a server, common mistakes with real error messages, practice questions and an FAQ. Use the interactive tool below to follow every step.
See how a Python program talks to a database: connect, cursor, execute, fetch and commit.
Graphical view
Python code
Output
What You Will Learn
- The problem statement
- How Python and a database work together
- Set up the database and the table
- The programs: insert, display, update and delete
- The same programs with sqlite3
- Common mistakes, practice questions and FAQ
Problem Statement
Integrate SQL with Python by importing a suitable module.
A Python program on its own forgets its data when it ends. A database keeps the data. To use both, the program must connect to the database and send SQL commands to it. In this post the database is called school and it has a table student with the columns roll_no, name and marks. The programs insert new records, display records, update the marks of a roll number and delete a record.
How Python and a Database Work Together
Three things take part, and the interactive tool above shows all three:
- The Python program holds the values in variables and decides what to do.
- The connection and the cursor. The connection (
mydb) is the open line to the database. The cursor (cur) sends the SQL text to the database and holds the rows that come back. - The database stores the table on the disk.
| Step | Statement | What happens |
|---|---|---|
| 1 | import mysql.connector | Loads the module |
| 2 | mydb = mysql.connector.connect(...) | Opens the connection |
| 3 | cur = mydb.cursor() | Makes the cursor |
| 4 | cur.execute(sql, values) | Sends the SQL command |
| 5a | cur.fetchall() or cur.fetchone() | Brings the rows of a SELECT into Python as tuples |
| 5b | mydb.commit() | Saves the changes of INSERT, UPDATE or DELETE |
| 6 | mydb.close() | Closes the connection |
A row comes into Python as a tuple, for example (1, 'Amit', 78). A list of such tuples is what fetchall() gives. Reading and changing are different: a SELECT only reads, so it needs no commit. INSERT, UPDATE and DELETE change the table, and the change is saved for good only after commit(). Before the commit, the change belongs to the connection and can still be lost.
Set Up the Database and the Table
First install the module (once) and create the database and the table in MySQL. Run this in the MySQL command line or in MySQL Workbench:
pip install mysql-connector-pythonCREATE DATABASE school;
USE school;
CREATE TABLE student (
roll_no INT PRIMARY KEY,
name VARCHAR(20),
marks INT
);
INSERT INTO student VALUES (1, 'Amit', 78);
INSERT INTO student VALUES (2, 'Riya', 85);
INSERT INTO student VALUES (3, 'Karan', 64);
INSERT INTO student VALUES (4, 'Neha', 91);To check that Python can reach the database, this short program prints a message when the connection works. Change the user and password to your own:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="1234",
database="school")
if mydb.is_connected():
print("Connected to MySQL")
mydb.close()
The Programs
Each program below starts from the table with the four rows above. The tool at the top lets you try the same programs.
Program 1: Insert records
Logic:
- Connect and make the cursor.
- Read
n, the number of records. For each record read the roll number, name and marks and run an INSERT withexecute(). The values go in a tuple, in the place of%s. - Call
commit()once to save all the records. Print a message and close the connection.
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="1234",
database="school")
cur = mydb.cursor()
n = int(input("Enter number of records: "))
for i in range(n):
roll = int(input("Enter roll number: "))
name = input("Enter name: ")
marks = int(input("Enter marks: "))
sql = "INSERT INTO student VALUES (%s, %s, %s)"
cur.execute(sql, (roll, name, marks))
mydb.commit()
print(n, "record(s) inserted")
mydb.close()Output:
Enter number of records: 2
Enter roll number: 5
Enter name: Sonia
Enter marks: 82
Enter roll number: 6
Enter name: Ravi
Enter marks: 70
2 record(s) inserted
Program 2: Display the records (SELECT)
Run the SELECT, take all the rows with fetchall() and print them one by one:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="1234",
database="school")
cur = mydb.cursor()
cur.execute("SELECT * FROM student")
rows = cur.fetchall()
for row in rows:
print(row)
mydb.close()Output:
(1, 'Amit', 78)
(2, 'Riya', 85)
(3, 'Karan', 64)
(4, 'Neha', 91)
A tuple can be unpacked into variables, which gives a neater output. Replace the loop with this:
for roll, name, marks in rows:
print(roll, name, marks)1 Amit 78
2 Riya 85
3 Karan 64
4 Neha 91
Program 3: Fetch one record, search and count
fetchone() returns only the next row:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="1234",
database="school")
cur = mydb.cursor()
cur.execute("SELECT * FROM student")
row = cur.fetchone()
print(row)
mydb.close()Output:
(1, 'Amit', 78)
A SELECT with WHERE and a value from the user. The value is given as a tuple with a comma, (n,). Here the students with marks above 80:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="1234",
database="school")
cur = mydb.cursor()
n = int(input("Enter marks: "))
cur.execute("SELECT * FROM student WHERE marks > %s", (n,))
rows = cur.fetchall()
if len(rows) == 0:
print("No record found")
for row in rows:
print(row)
mydb.close()Output (the user typed 80):
Enter marks: 80
(2, 'Riya', 85)
(4, 'Neha', 91)
If nobody matches (the user typed 95), fetchall() gives an empty list:
Enter marks: 95
No record found
To count the rows, use COUNT(*). The answer is a tuple with one value, so we print row[0]:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="1234",
database="school")
cur = mydb.cursor()
cur.execute("SELECT COUNT(*) FROM student")
row = cur.fetchone()
print("Total records:", row[0])
mydb.close()Total records: 4
Program 4: Update the marks of a roll number
Logic: read the roll number and the new marks. First run a SELECT and call fetchone() to check that the roll number exists. If row is None, print a message and change nothing. Otherwise run the UPDATE, call commit() and print a message.
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="1234",
database="school")
cur = mydb.cursor()
roll = int(input("Enter roll number: "))
marks = int(input("Enter new marks: "))
cur.execute("SELECT * FROM student WHERE roll_no = %s", (roll,))
row = cur.fetchone()
if row is None:
print("Roll number not found")
else:
sql = "UPDATE student SET marks = %s WHERE roll_no = %s"
cur.execute(sql, (marks, roll))
mydb.commit()
print("Record updated")
mydb.close()Output:
Enter roll number: 2
Enter new marks: 95
Record updated
Output when the roll number is not in the table (the user typed 9):
Enter roll number: 9
Enter new marks: 50
Roll number not found
Program 5: Delete a record
The same plan as the update program, with DELETE:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="1234",
database="school")
cur = mydb.cursor()
roll = int(input("Enter roll number: "))
cur.execute("SELECT * FROM student WHERE roll_no = %s", (roll,))
row = cur.fetchone()
if row is None:
print("Roll number not found")
else:
sql = "DELETE FROM student WHERE roll_no = %s"
cur.execute(sql, (roll,))
mydb.commit()
print("Record deleted")
mydb.close()Output:
Enter roll number: 3
Record deleted
Output when the roll number is not in the table (the user typed 9):
Enter roll number: 9
Roll number not found
The Same Programs with sqlite3
If you do not have MySQL, use the sqlite3 module. It is part of Python and keeps the database in a file, so no server and no password are needed. Only three things change:
| mysql.connector | sqlite3 | |
|---|---|---|
| Import | import mysql.connector | import sqlite3 |
| Connect | mysql.connector.connect(host=..., user=..., password=..., database=...) | sqlite3.connect("school.db") |
| Placeholder | %s | ? |
Program 1 with sqlite3 looks like this. The table must exist in school.db first. We ran all the five programs in this form on the four-row table, and the outputs and the final table were the same as shown above.
import sqlite3
mydb = sqlite3.connect("school.db")
cur = mydb.cursor()
n = int(input("Enter number of records: "))
for i in range(n):
roll = int(input("Enter roll number: "))
name = input("Enter name: ")
marks = int(input("Enter marks: "))
sql = "INSERT INTO student VALUES (?, ?, ?)"
cur.execute(sql, (roll, name, marks))
mydb.commit()
print(n, "record(s) inserted")
mydb.close()
Common Mistakes
- Forgetting commit(): the changes are lost. We inserted a row without commit and opened the database again: the table still had 4 rows.
- Using fetchall() twice: the second call returns
[], because the first call has already taken every row. Store the result in a variable. - Building the SQL text with + or an f-string: a user can type SQL into the input. When we built the DELETE command from the text
0 OR 1=1, all 4 rows were deleted. With a placeholder, nothing was deleted. Always pass the values as the second argument ofexecute(). - Writing (n) instead of (n,): the values must be a tuple.
(n)gaveProgrammingError: parameters are of unsupported type. - Using row[0] on None: when fetchone() finds nothing, row is None, and
row[0]givesTypeError: 'NoneType' object is not subscriptable. Testif row is Nonefirst. - Inserting a roll number that already exists: the primary key must be unique. The database refuses the row with an IntegrityError (sqlite3 said
UNIQUE constraint failed: student.roll_no; MySQL reports a duplicate entry). - Using the cursor after close(): the program stops with an error. Close the connection last.
- Wrong password or database name in connect(): the connection fails. Check the user, the password and that the database school exists.
Practice Questions
- Write a program that displays the students who have marks between 70 and 90.
- Write a program that shows the name of the student who has the highest marks (use
MAXorORDER BY ... DESCwithfetchone()). - Write a menu-driven program with the choices insert, display, update, delete and exit.
- Write a program that searches a student by name and prints the roll number and marks.
- Write a program that prints the average marks of all students.
Frequently Asked Questions
How do you connect Python with MySQL?
Install the package with pip install mysql-connector-python, then import mysql.connector and call mysql.connector.connect(host="localhost", user="root", password="your password", database="school"). The call returns a connection object, which you usually store in a variable such as mydb.
Which module is used to connect Python with SQL?
For MySQL, the module is mysql.connector (package mysql-connector-python), which is the one used in the CBSE syllabus. Python also has the built-in sqlite3 module for SQLite, which needs no server. Other databases have their own modules, for example psycopg2 for PostgreSQL.
What is a cursor in Python database programs?
A cursor is an object made with mydb.cursor(). It sends SQL commands to the database with execute() and holds the rows returned by a SELECT until you fetch them.
What is the difference between fetchone() and fetchall()?
fetchone() returns the next row as a tuple, or None when there are no more rows. fetchall() returns all the remaining rows as a list of tuples. In our test, a second fetchall() on the same result returned an empty list, because the first call had used up all the rows.
Why do we call commit()?
INSERT, UPDATE and DELETE change the table, but the changes are saved permanently only after mydb.commit(). In our test, a row inserted without commit was gone when the database was opened again: the table still had 4 rows. SELECT does not need commit.
What is the difference between %s and ? in the SQL command?
They are placeholders for the values. mysql.connector uses %s and sqlite3 uses ?. The values are given as a tuple in the second argument of execute(), so the module puts them into the command safely.
Why should we not build the SQL command with + or an f-string?
Because a user can type SQL into the input. In our test, a program that built DELETE FROM student WHERE roll_no = from the typed text 0 OR 1=1 deleted all 4 rows. The same input with a placeholder deleted nothing. This attack is called SQL injection.
Why does (n,) have a comma?
The values must be a tuple. (n) is just the number n in brackets, but (n,) is a tuple with one value. In our test, (n) stopped the program with ProgrammingError: parameters are of unsupported type.
What does fetchone() return when no row matches?
It returns None. That is why the update and delete programs test if row is None before they change anything. Writing row[0] on None stops the program with TypeError: 'NoneType' object is not subscriptable.
How do you count the rows of a table from Python?
Run SELECT COUNT(*) FROM student and call fetchone(). The result is a tuple with one value, such as (4,), so the count is row[0].
Do I need to close the connection?
Yes. mydb.close() ends the connection and frees the resources. After close, the cursor cannot be used. Our test of a SELECT on a closed database stopped with ProgrammingError: Cannot operate on a closed database. (the message is for sqlite3).
Can I practise this without installing MySQL?
Yes. Use the built-in sqlite3 module. Only the import line, the connect line and the placeholders change, as shown in the post, and the rest of the program is the same.