Database Programming in Python

Database programming allows Python applications to store, retrieve, update, and delete information in a structured and permanent way. Databases are used in banking systems, e-commerce websites, mobile apps, inventory systems, and almost all modern software. Python provides simple tools for interacting with databases, making it beginner-friendly.

What Is a Database?

A database is a system that stores data in an organized format so it can be easily accessed later. Most applications use databases to store user information, orders, products, messages, and more. Python can work with both small local databases and large enterprise systems.

Types of Databases

Databases fall into two major categories:

• SQL Databases → Structured, table-based (MySQL, PostgreSQL, SQLite).
• NoSQL Databases → Flexible, document-based (MongoDB, Firebase, DynamoDB).

Beginners usually start with SQL databases because they are simple and widely supported. Python includes built-in support for SQLite, which requires no setup.

Using SQLite in Python

SQLite is a lightweight database stored in a single file. It is perfect for learning, small projects, and local applications. Python includes the sqlite3 module, so no installation is needed.

import sqlite3

# Connect to or create a database
conn = sqlite3.connect("students.db")

# Create a cursor to run commands
cursor = conn.cursor()

Creating a Table

A table stores data in rows and columns, similar to a spreadsheet. Before inserting data, the table must be created. A cursor object helps you execute SQL commands in Python.

cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER
)
""")

conn.commit()

Inserting Data

Data can be added to a table using an INSERT statement. Each new row becomes a record stored in the database file. Python will handle storing these values safely and consistently.

cursor.execute("""
INSERT INTO students (name, age)
VALUES (?, ?)
""", ("Emma", 21))

conn.commit()

Retrieving Data

To read data from a table, we use the SELECT statement. This allows programs to display information, generate reports, and perform calculations. The fetched data can be iterated like a Python list.

cursor.execute("SELECT * FROM students")
rows = cursor.fetchall()

for row in rows:
    print(row)

Updating Records

Updating allows modifying existing entries in the database. Applications often use updates to change profile information, adjust stock, or correct data. Only the rows matching the condition will be updated.

cursor.execute("""
UPDATE students
SET age = 22
WHERE name = "Emma"
""")

conn.commit()

Deleting Records

Records can be deleted from a table using a DELETE statement. This is useful for removing old data or cleaning up unwanted entries. Always be careful when deleting, because it is permanent.

cursor.execute("""
DELETE FROM students
WHERE name = "Emma"
""")

conn.commit()

Closing the Database

Once all operations are complete, the connection should be closed. This releases system resources and ensures all data is saved properly. It is a good habit to close every database connection when finished.

conn.close()

Real-World Uses of Database Programming

Python database programming powers important systems like inventory apps, financial software, attendance systems, and customer dashboards. It allows data to be stored permanently so applications can continue functioning even after restart. Even large enterprise systems depend on similar concepts with bigger tools.

📝 Practice Exercises

Exercise 1

Create a database named library.db and a table books with title and author columns.

Exercise 2

Insert three book records into the books table.

Exercise 3

Write a query to select all books and print them.

Exercise 4

Update the author name for one of the books.

Exercise 5

Delete one book from the table.

✅ Practice Answers

Answer 1

import sqlite3
conn = sqlite3.connect("library.db")
cursor = conn.cursor()

cursor.execute("""
CREATE TABLE IF NOT EXISTS books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT
)
""")

conn.commit()

Answer 2

cursor.execute("INSERT INTO books (title, author) VALUES (?, ?)", ("Invisible Cities", "Calvino"))
cursor.execute("INSERT INTO books (title, author) VALUES (?, ?)", ("Norwegian Wood", "Murakami"))
cursor.execute("INSERT INTO books (title, author) VALUES (?, ?)", ("The Alchemist", "Coelho"))
conn.commit()

Answer 3

cursor.execute("SELECT * FROM books")
for row in cursor.fetchall():
    print(row)

Answer 4

cursor.execute("""
UPDATE books
SET author = "Updated Author"
WHERE title = "Invisible Cities"
""")
conn.commit()

Answer 5

cursor.execute("""
DELETE FROM books
WHERE title = "The Alchemist"
""")
conn.commit()