Many applications need to store information permanently. An online shopping website stores customer details, a school system stores student records, and a banking application stores account information. If this data were kept only in program variables, it would disappear when the program closes.

This is where databases become important. A database stores information safely so it can be accessed, updated, and managed whenever needed. Python provides built-in and third-party libraries that make it easy to connect to databases, execute SQL queries, and manage data.

In this lesson, you will learn the basics of database programming using Python, with examples based on SQLite, which is included with Python by default.

What is a Database?

A database is a structured collection of data that can be stored, searched, updated, and deleted efficiently.

• SQLite
• MySQL
• PostgreSQL
• Microsoft SQL Server
• Oracle Database

For beginners, SQLite is the easiest choice because it requires no separate installation or server.

Why Use a Database?

Databases offer many advantages over storing data in ordinary files.

• Permanent data storage
• Faster searching and filtering
• Better organization of information
• Easy data updates
• Support for multiple users in larger systems
• Secure data management

Most real-world Python applications use a database.

Connecting to a Database

Python includes the built-in sqlite3 module for working with SQLite databases. First, import the module and create a connection.

import sqlite3

connection = sqlite3.connect("school.db")

If the database file does not exist, SQLite creates it automatically.

Creating a Table

A database stores information in tables. The following example creates a table named students.

import sqlite3

connection = sqlite3.connect("school.db")
cursor = connection.cursor()

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

connection.commit()
connection.close()

• cursor() is used to execute SQL commands.
• commit() saves the changes.
• close() closes the database connection.

Inserting Data

Add records to a table using the SQL INSERT statement.

import sqlite3

connection = sqlite3.connect("school.db")
cursor = connection.cursor()

cursor.execute(
    "INSERT INTO students (name, age) VALUES (?, ?)",
    ("Alice", 20)
)

connection.commit()
connection.close()

The question-mark placeholders safely pass values to the query.

Reading Data

Retrieve data using the SQL SELECT statement.

import sqlite3

connection = sqlite3.connect("school.db")
cursor = connection.cursor()

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

for row in rows:
    print(row)

connection.close()

Sample Output

(1, 'Alice', 20)

The fetchall() method returns all matching rows.

Updating Data

The SQL UPDATE statement modifies existing records.

cursor.execute(
    "UPDATE students SET age = ? WHERE name = ?",
    (21, "Alice")
)

connection.commit()

This updates Alice's age to 21.

Deleting Data

The SQL DELETE statement removes records from a table.

cursor.execute(
    "DELETE FROM students WHERE name = ?",
    ("Alice",)
)

connection.commit()

Be careful when deleting data because removed records cannot be recovered unless you have a backup.

Parameterized Queries

When accepting user input, never build SQL queries by joining strings. Avoid code like this:

query = "SELECT * FROM students WHERE name = '" + name + "'"

Use a parameterized query instead:

cursor.execute(
    "SELECT * FROM students WHERE name = ?",
    (name,)
)

Parameterized queries help protect your application from SQL injection, a common security risk.

Transactions

A transaction is a group of database operations treated as a single unit. After making changes, commit them to the database.

connection.commit()

If something goes wrong, roll back the pending changes.

connection.rollback()

Transactions help keep your data accurate and consistent.

Working with MySQL

Although SQLite is excellent for learning, many real-world applications use MySQL. To connect to MySQL, install a connector library and provide the connection details.

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="school"
)

After connecting, the process of executing SQL queries is similar to SQLite. Store real credentials securely instead of placing them directly in source code.

Best Practices

• Always close database connections after use.
• Use parameterized queries instead of string concatenation.
• Commit changes after inserting, updating, or deleting data.
• Handle database errors using exception handling.
• Keep database credentials secure.
• Back up important data regularly.

These practices help you build secure and reliable applications.

Real-World Applications

• Student management systems
• Banking applications
• Online shopping websites
• Hospital management systems
• Inventory tracking
• Employee management systems
• Social media platforms

Almost every modern application uses a database to store and manage information.

Key Takeaways

• A database stores information permanently in an organized way.
• Python provides the built-in sqlite3 module for working with SQLite databases.
• A database connection is required before executing SQL commands.
• Tables organize related data into rows and columns.
• CREATE, INSERT, SELECT, UPDATE, and DELETE statements manage data.
• Parameterized queries improve security and help prevent SQL injection attacks.
• Transactions ensure database operations are completed safely and consistently.
• SQLite is ideal for beginners, while MySQL and PostgreSQL are commonly used in larger applications.
• Following best practices makes database applications more secure, reliable, and maintainable.
• Database programming is an essential skill for building real-world Python applications.