Chapter 10: Working with Databases

Databases allow you to store, retrieve, and manipulate structured data efficiently. Python provides built-in support for SQLite, a lightweight, serverless database, using the sqlite3 module. This chapter will cover basic database operations, also known as CRUD: Create, Read, Update, and Delete.

Setting Up SQLite

SQLite is included with Python, so there is no need for additional installation.

Connecting to a Database:

Use sqlite3.connect() to connect to a database. If the database file does not exist, SQLite will create it.

Example:

import sqlite3

# Connect to a database (or create one if it doesn't exist)
connection = sqlite3.connect("example.db")

# Create a cursor object
cursor = connection.cursor()

# Close the connection
connection.close()

Creating Tables

Tables are where data is stored in rows and columns.

Example:

import sqlite3

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

# Create a table
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    email TEXT UNIQUE
)
''')

connection.commit()
connection.close()

Inserting Data

To add data to a table, use the INSERT INTO statement.

Example:

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

# Insert data into the table
cursor.execute('''
INSERT INTO users (name, age, email)
VALUES (?, ?, ?)
''', ("Alice", 25, "[email protected]"))

connection.commit()
connection.close()

Reading Data

To retrieve data from a table, use the SELECT statement.

Example:

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

# Fetch all rows from the users table
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()

for row in rows:
    print(row)

connection.close()

Updating Data

To modify existing data, use the UPDATE statement.

Example:

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

# Update a user's age
cursor.execute('''
UPDATE users
SET age = ?
WHERE name = ?
''', (30, "Alice"))

connection.commit()
connection.close()

Deleting Data

To remove data from a table, use the DELETE statement.

Example:

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

# Delete a user by name
cursor.execute('''
DELETE FROM users
WHERE name = ?
''', ("Alice",))

connection.commit()
connection.close()

Using Parameters to Prevent SQL Injection

Always use parameterized queries (?) instead of string formatting to prevent SQL injection attacks.

Example:

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

Best Practices for Database Handling

1. Use with statements for managing database connections to ensure they are closed properly.

   with sqlite3.connect("example.db") as connection:
       cursor = connection.cursor()
       cursor.execute("SELECT * FROM users")

2. Validate and sanitize user input.

3. Use transactions (connection.commit()) for data integrity.

4. Index frequently searched columns for faster query execution.

Exercises

Exercise 1:

Create a database with a table products having columns id, name, price, and quantity. Insert at least three products and retrieve all data.

Solution:

import sqlite3

with sqlite3.connect("store.db") as connection:
    cursor = connection.cursor()
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        price REAL NOT NULL,
        quantity INTEGER NOT NULL
    )
    ''')
    
    # Insert data
    products = [
        ("Laptop", 1200.50, 5),
        ("Mouse", 25.00, 50),
        ("Keyboard", 45.00, 30)
    ]
    cursor.executemany('''
    INSERT INTO products (name, price, quantity)
    VALUES (?, ?, ?)
    ''', products)
    
    # Fetch data
    cursor.execute("SELECT * FROM products")
    for row in cursor.fetchall():
        print(row)

Exercise 2:

Write a program to update the price of a product based on its name.

Solution:

with sqlite3.connect("store.db") as connection:
    cursor = connection.cursor()
    cursor.execute('''
    UPDATE products
    SET price = ?
    WHERE name = ?
    ''', (50.00, "Mouse"))

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

Exercise 3:

Write a program to delete all products with a quantity of zero.

Solution:

with sqlite3.connect("store.db") as connection:
    cursor = connection.cursor()
    cursor.execute('''
    DELETE FROM products
    WHERE quantity = 0
    ''')

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

In the next chapter, we will explore advanced Python topics, including iterators, generators, and the yield keyword.