Python can be used to connect with databases, store data, retrieve records, update information, and delete records. Database programming is an important part of building real-world applications.
A database is an organized collection of data. It allows applications to store and manage information efficiently.
For example, a student management system may store student names, mobile numbers, courses and fees in a database.
Student Database
ID Name Course
1 Rahul Python
2 Priya Java
3 Amit SQLDatabases are useful when an application needs to permanently store large amounts of information.
Python can work with several database systems:
The database you choose depends on the requirements of your application.
SQLite is a lightweight database that is included with Python through the
built-in sqlite3 module.
It stores the complete database in a single file and does not require a separate database server.
import sqlite3
connection = sqlite3.connect("school.db")
print("Database connected")Database connected
The sqlite3.connect() function is used to connect to an SQLite
database.
import sqlite3
conn = sqlite3.connect("students.db")
print("Connected successfully")sqlite3 imports the SQLite module.connect() creates or opens the database.students.db is the database file.A cursor is used to execute SQL statements and retrieve results from a database.
import sqlite3
conn = sqlite3.connect("students.db")
cursor = conn.cursor()
print("Cursor created")
The SQL CREATE TABLE statement can be executed using the
cursor.
import sqlite3
conn = sqlite3.connect("students.db")
cursor = conn.cursor()
cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
id INTEGER PRIMARY KEY,
name TEXT,
course TEXT,
fee INTEGER
)
""")
conn.commit()
print("Table created")Table created
The SQL INSERT statement is used to add records.
cursor.execute(
"INSERT INTO students (name, course, fee) VALUES (?, ?, ?)",
("Rahul", "Python", 15000)
)
conn.commit()The question marks are parameter placeholders used by SQLite.
The executemany() method can be used to insert multiple records.
students = [
("Rahul", "Python", 15000),
("Priya", "Java", 18000),
("Amit", "SQL", 12000)
]
cursor.executemany(
"INSERT INTO students (name, course, fee) VALUES (?, ?, ?)",
students
)
conn.commit()
The SELECT statement is used to retrieve records from a table.
cursor.execute("SELECT * FROM students")
rows = cursor.fetchall()
for row in rows:
print(row)(1, 'Rahul', 'Python', 15000) (2, 'Priya', 'Java', 18000) (3, 'Amit', 'SQL', 12000)
The fetchone() method returns one record from the query result.
cursor.execute("SELECT * FROM students")
row = cursor.fetchone()
print(row)(1, 'Rahul', 'Python', 15000)
The fetchmany() method retrieves a specified number of records.
cursor.execute("SELECT * FROM students")
rows = cursor.fetchmany(2)
for row in rows:
print(row)
The fetchall() method retrieves all remaining records from the
query result.
cursor.execute("SELECT * FROM students")
rows = cursor.fetchall()
for row in rows:
print(row)
The WHERE clause is used to retrieve records that satisfy a
condition.
cursor.execute(
"SELECT * FROM students WHERE course = ?",
("Python",)
)
rows = cursor.fetchall()
for row in rows:
print(row)
The SQL UPDATE statement is used to modify existing records.
cursor.execute(
"UPDATE students SET fee = ? WHERE id = ?",
(18000, 1)
)
conn.commit()
The fee of the student whose ID is 1 is changed to
18000.
The SQL DELETE statement is used to remove records.
cursor.execute(
"DELETE FROM students WHERE id = ?",
(3,)
)
conn.commit()
This deletes the student whose ID is 3.
Parameterized queries allow values to be passed separately from SQL statements. They are safer than building SQL statements by concatenating user input.
name = "Rahul"
cursor.execute(
"SELECT * FROM students WHERE name = ?",
(name,)
)
rows = cursor.fetchall()Changes such as INSERT, UPDATE and DELETE normally need to be committed before they are permanently saved.
cursor.execute(
"UPDATE students SET fee = ? WHERE id = ?",
(20000, 1)
)
conn.commit()
The commit() method saves the transaction.
If an error occurs before a transaction is committed, rollback()
can be used to undo the pending changes.
try:
cursor.execute(
"UPDATE students SET fee = ? WHERE id = ?",
(25000, 1)
)
conn.commit()
except Exception:
conn.rollback()
print("Transaction rolled back")Database operations can fail because of invalid SQL, missing tables, incorrect data or other database errors. Exception handling can be used to handle such problems.
import sqlite3
try:
conn = sqlite3.connect("students.db")
cursor = conn.cursor()
cursor.execute("SELECT * FROM students")
rows = cursor.fetchall()
for row in rows:
print(row)
except sqlite3.Error as error:
print("Database error:", error)
finally:
conn.close()After completing database operations, the connection should be closed when it is no longer needed.
conn.close()Closing connections helps release resources properly.
import sqlite3
conn = sqlite3.connect("school.db")
cursor = conn.cursor()
cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
id INTEGER PRIMARY KEY,
name TEXT,
course TEXT,
fee INTEGER
)
""")
cursor.execute(
"INSERT INTO students (name, course, fee) VALUES (?, ?, ?)",
("Rahul", "Python", 15000)
)
conn.commit()
cursor.execute("SELECT * FROM students")
rows = cursor.fetchall()
for row in rows:
print(row)
conn.close()
Python's context manager can help manage database transactions and resources.
For SQLite, using with around the connection can automatically
commit a successful transaction or roll it back if an exception occurs.
import sqlite3
with sqlite3.connect("school.db") as conn:
cursor = conn.cursor()
cursor.execute(
"UPDATE students SET fee = ? WHERE id = ?",
(18000, 1)
)
Python can also connect to MySQL using a MySQL driver such as
mysql-connector-python.
import mysql.connector
conn = mysql.connector.connect(
host="localhost",
user="root",
password="",
database="school"
)
print("MySQL connected")For detailed MySQL programming, see the previous lesson: MySQL with Python.
CRUD is a common term used in database programming.
| Operation | SQL Command | Purpose |
|---|---|---|
| Create | INSERT | Add new data |
| Read | SELECT | Retrieve data |
| Update | UPDATE | Modify existing data |
| Delete | DELETE | Remove data |
A typical database application follows these steps:
commit() after changes.Python database programming is commonly used in applications such as:
sqlite3 module.SELECT retrieves data.INSERT adds data.UPDATE modifies data.DELETE removes data.commit() saves transaction changes.rollback() can undo pending transaction changes.Question: Which SQL command is used to retrieve data from a database?