Integrate SQL with Python by importing suitable module

“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.

🔗 Python and SQL: Connect to a Database

See how a Python program talks to a database: connect, cursor, execute, fetch and commit.

Graphical view

Python code

Output

What You Will Learn

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:

  1. The Python program holds the values in variables and decides what to do.
  2. 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.
  3. The database stores the table on the disk.
StepStatementWhat happens
1import mysql.connectorLoads the module
2mydb = mysql.connector.connect(...)Opens the connection
3cur = mydb.cursor()Makes the cursor
4cur.execute(sql, values)Sends the SQL command
5acur.fetchall() or cur.fetchone()Brings the rows of a SELECT into Python as tuples
5bmydb.commit()Saves the changes of INSERT, UPDATE or DELETE
6mydb.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-python
CREATE 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 with execute(). 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.connectorsqlite3
Importimport mysql.connectorimport sqlite3
Connectmysql.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 of execute().
  • Writing (n) instead of (n,): the values must be a tuple. (n) gave ProgrammingError: parameters are of unsupported type.
  • Using row[0] on None: when fetchone() finds nothing, row is None, and row[0] gives TypeError: 'NoneType' object is not subscriptable. Test if row is None first.
  • 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

  1. Write a program that displays the students who have marks between 70 and 90.
  2. Write a program that shows the name of the student who has the highest marks (use MAX or ORDER BY ... DESC with fetchone()).
  3. Write a menu-driven program with the choices insert, display, update, delete and exit.
  4. Write a program that searches a student by name and prints the roll number and marks.
  5. 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.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top