Python and Databases

This time, we’ll explore how to work with databases in Python.

We’ll be using SQLite for a bunch of reasons, but mainly because it’s lightweight, serverless (I’ll explain, shortly) and it’s self-contained. It’s also included in the Python standard library, so we won’t have to install any additional software.

By the end of this lesson, you’ll be able to create databases, interact with them, and execute common database operations such as creating tables, inserting data, querying, updating, and deleting records.

What is SQLite?

SQLite is an embedded relational database management system (RDBMS) that stores data in a single file on disk – perfect for small to medium-sized applications.

Unlike larger database systems like MySQL or PostgreSQL, SQLite does not require a separate server process, making it lightweight and easy to set up. It is perfect for applications that don’t need the complexity of a full-scale database system.

Why Use SQLite?

• Lightweight: No server setup is required; everything is stored in a single file.
• Portable: The entire database is a single file, making it easy to share and deploy.
• Built-In: SQLite is included in the Python standard library, so no installation is needed.

Setting Up SQLite in Python

Since SQLite is part of Python’s standard library, you can start using it by importing the sqlite3 module.

import sqlite3

This module provides the necessary functions to connect to an SQLite database and execute SQL queries.

Creating a Database

To create a new SQLite database or connect to an existing one, use the sqlite3.connect() method. This method takes the name of the database file as an argument.

Example:

import sqlite3

# Connect to a database (creates the file if it doesn't exist)
conn = sqlite3.connect('example.db')

# Create a cursor object to execute SQL queries
cursor = conn.cursor()

If the file example.db does not exist, SQLite will create it. The cursor object is used to execute SQL commands.

Creating Tables

Once connected to a database, you can create tables using the SQL CREATE TABLE statement. Tables are structures that hold your data, and they consist of rows and columns, where each column represents a specific field.

Example: Create a table for storing user information.

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

# Commit the changes to the database
conn.commit()

Explanation:

• CREATE TABLE: This SQL statement creates a new table.
• IF NOT EXISTS: Ensures that the table is only created if it doesn’t already exist.
• PRIMARY KEY: The id column is set as the primary key, meaning it uniquely identifies each row.
• TEXT, INTEGER: These are the data types for the fields (TEXT for strings and INTEGER for numbers).
• NOT NULL: Specifies that the name field cannot be empty.
• UNIQUE: Ensures that the email field is unique across all rows.

Inserting Data

You can insert data into a table using the INSERT INTO SQL statement.

Example: Inserting a new user into the users table.

# Insert a new record into the "users" table
cursor.execute('''
INSERT INTO users (name, age, email)
VALUES (?, ?, ?)
''', ('Alice', 30, 'alice@example.com'))

# Commit the changes
conn.commit()

Explanation:

• The ? placeholders are used to insert values into the SQL query safely. This helps prevent SQL injection attacks.
• The tuple ('Alice', 30, 'alice@example.com') provides the values for the placeholders.
• conn.commit() saves the changes to the database.

You can insert multiple records in one go by using executemany().

Example:

users = [
    ('Bob', 25, 'bob@example.com'),
    ('Carol', 22, 'carol@example.com'),
    ('Dave', 35, 'dave@example.com')
]

cursor.executemany('''
INSERT INTO users (name, age, email)
VALUES (?, ?, ?)
''', users)

conn.commit()

Querying Data (Reading Records)

To retrieve data from a table, use the SELECT SQL statement. This allows you to read specific records based on your query.

Example: Selecting all records from the users table.

cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()

for row in rows:
    print(row)

Explanation:

• SELECT * retrieves all columns for all rows in the users table.
• cursor.fetchall() fetches all the results as a list of tuples.
• Each tuple represents a row from the table.

You can also filter records using WHERE clauses.

Example: Retrieve users who are older than 25.

cursor.execute('SELECT * FROM users WHERE age > 25')
rows = cursor.fetchall()

for row in rows:
    print(row)

Updating Data

To update existing records in a table, use the UPDATE SQL statement.

Example: Update the email of the user with id = 1.

cursor.execute('''
UPDATE users
SET email = ?
WHERE id = ?
''', ('newemail@example.com', 1))

conn.commit()

Explanation:

• The UPDATE statement modifies the email column for the user where id is 1.
• The placeholders ? are used to safely pass in the new email and the user ID.

Deleting Data

You can delete records from a table using the DELETE SQL statement.

Example: Delete a user with id = 2.

cursor.execute('DELETE FROM users WHERE id = ?', (2,))
conn.commit()

Explanation:

• The DELETE FROM statement removes the row where id = 2.
• Always be cautious when using DELETE, especially without a WHERE clause, as it can remove all records.

Using Placeholders for Safe Queries

When inserting or querying data, it’s essential to use placeholders (? in SQLite) instead of directly embedding user input in SQL queries. This prevents SQL injection, a common attack vector where malicious input can be executed as SQL commands.

Safe Query Example:

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

Avoid writing unsafe queries like this:

# Unsafe! Don't concatenate user input directly into queries.
cursor.execute(f'SELECT * FROM users WHERE name = "{name}"')

Handling Transactions

SQLite automatically handles transactions when you call commit(), ensuring that your changes are saved. If you want to roll back changes in case of an error, you can use rollback().

Example: Using a transaction to ensure data consistency.

try:
    cursor.execute('INSERT INTO users (name, age, email) VALUES (?, ?, ?)', ('Eve', 29, 'eve@example.com'))
    cursor.execute('UPDATE users SET age = ? WHERE id = ?', (30, 3))
    conn.commit()
except Exception as e:
    print("Error occurred:", e)
    conn.rollback()

In this example, if any query fails, the changes are rolled back, preventing partial updates to the database.

Closing the Connection

After finishing your operations, it’s important to close the connection to the database to free up resources.

conn.close()

Working with SQLite in Memory

If you don’t want to create a persistent database file, you can create an in-memory database. This is useful for testing or temporary data storage.

Example:

conn = sqlite3.connect(':memory:')  # Creates an in-memory database
cursor = conn.cursor()

# Create tables, insert data, and query as usual

Data stored in an in-memory database is lost when the connection is closed.

Key Concepts Recap

In this lesson, we covered:

• How to connect to an SQLite database and create tables.
• How to insert, query, update, and delete data using SQL commands in Python.
• The importance of using placeholders to avoid SQL injection.
• How to handle transactions to ensure data consistency.
• How to work with in-memory databases for temporary data storage.

SQLite provides an easy-to-use database solution that integrates seamlessly with Python, making it a powerful tool for managing application data.

Exercises

1. Create a database for storing books, with columns for title, author, and year_published. Insert five books and retrieve all books published after the year 2000.
2. Update the email of a user in the users table and display the updated record.
3. Write a Python script that deletes all users from the users table who are older than 40.
4. Create an in-memory database, insert some test data, and query it. After the program finishes, verify that the data is lost (since the database is in memory).

FAQ

Q1: What is the difference between SQLite and other database systems like MySQL or PostgreSQL?

A1:

• SQLite is a serverless, file-based database that stores everything in a single file. It’s ideal for small to medium-sized applications where simplicity and portability are important. It requires no configuration or separate server process.
• MySQL and PostgreSQL are client-server databases, meaning they require a server to run and manage multiple connections. These systems are better suited for large-scale applications that need high performance, multi-user support, and complex querying capabilities.

SQLite is a great choice for lightweight applications, prototyping, or projects that don’t require the overhead of a full-fledged database server.

Q2: What does PRIMARY KEY mean in a table, and why should I use it?

A2: A PRIMARY KEY is a column (or a combination of columns) in a table that uniquely identifies each row. Every table should have a primary key to ensure that each record can be uniquely identified. In SQLite, defining a column as PRIMARY KEY ensures that:

• Each value in the column is unique.
• The column cannot contain NULL values.

Most commonly, the id column is used as a primary key, and SQLite will automatically generate unique values for this column if it’s declared as INTEGER PRIMARY KEY.

Q3: How do I check if a table exists before creating it?

A3: To avoid creating a table that already exists, you can use the IF NOT EXISTS clause in the CREATE TABLE statement.

Example:

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

This will only create the users table if it doesn’t already exist, preventing an error if the table is already there.

Q4: How do I handle NULL values in SQLite?

A4: You can insert NULL values into a table if the column allows it. Columns defined with NOT NULL cannot accept NULL values. To insert a NULL value into a column, simply provide None in your Python code.

Example:

cursor.execute('INSERT INTO users (name, age, email) VALUES (?, ?, ?)', ('John Doe', None, 'john@example.com'))
conn.commit()

In this case, the age column will be set to NULL because None in Python is equivalent to NULL in SQL.

Q5: How do I prevent duplicate entries in the database?

A5: To prevent duplicate entries, you can use the UNIQUE constraint on a column. For example, if you want to ensure that no two users have the same email, you can define the email column as UNIQUE.

Example:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER,
    email TEXT UNIQUE
)

If you try to insert a duplicate value into the email column, SQLite will raise an IntegrityError.

Q6: How can I perform case-insensitive queries in SQLite?

A6: SQLite is case-insensitive by default when comparing text with the = operator. However, if you need to explicitly enforce case-insensitive queries, you can use the COLLATE NOCASE clause.

Example:

cursor.execute('SELECT * FROM users WHERE name = ? COLLATE NOCASE', ('alice',))

This query will return records with the name Alice, ALICE, or any other case variation.

Q7: How do I update multiple records at once?

A7: You can update multiple records by specifying the condition in the WHERE clause. For example, to update the age of all users older than 30:

cursor.execute('UPDATE users SET age = age + 1 WHERE age > 30')
conn.commit()

This query increments the age for all users whose current age is greater than 30.

Q8: How do I handle errors in SQLite, such as when trying to insert duplicate entries?

A8: You can handle SQLite errors using a try-except block in Python. For example, to catch an error when inserting a duplicate email:

import sqlite3

try:
    cursor.execute('INSERT INTO users (name, age, email) VALUES (?, ?, ?)', ('Alice', 30, 'alice@example.com'))
    conn.commit()
except sqlite3.IntegrityError as e:
    print("Error occurred:", e)

In this case, IntegrityError will be raised if a duplicate email is inserted or if any other constraint (e.g., NOT NULL or UNIQUE) is violated.

Q9: Can I use foreign keys in SQLite?

A9: Yes, SQLite supports foreign keys, but you must enable foreign key support explicitly because it is turned off by default. To enable foreign keys, execute the following command right after connecting to the database:

conn.execute('PRAGMA foreign_keys = ON')

Then, you can create a table with a foreign key constraint.

Example:

cursor.execute('''
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    user_id INTEGER,
    amount REAL,
    FOREIGN KEY (user_id) REFERENCES users (id)
)
''')

In this example, the user_id column in the orders table is a foreign key that references the id column in the users table.

Q10: How do I delete all records from a table but keep the table structure?

A10: To delete all records in a table without dropping the table itself, use the DELETE statement without a WHERE clause:

cursor.execute('DELETE FROM users')
conn.commit()

This will remove all rows from the users table but leave the table structure intact.

Q11: How can I export data from an SQLite database to a CSV file?

A11: You can export the results of a query to a CSV file using Python’s csv module.

Example:

import csv

cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()

with open('users.csv', 'w', newline='') as file:
    writer = csv.writer(file)
    writer.writerow(['ID', 'Name', 'Age', 'Email'])  # Header row
    writer.writerows(rows)

This script retrieves all records from the users table and writes them to a users.csv file.

Q12: How do I create a temporary in-memory database?

A12: You can create an in-memory database by passing ':memory:' to the sqlite3.connect() method. This database exists only in memory, and all data will be lost once the connection is closed.

Example:

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

# Create tables, insert data, and query as usual

This type of database is useful for testing or temporary data storage during a program’s execution.

Q13: Can I execute multiple SQL commands in one go?

A13: Yes, you can execute multiple SQL statements in one call by using the executescript() method. This method allows you to execute multiple SQL commands separated by semicolons.

Example:

cursor.executescript('''
    INSERT INTO users (name, age, email) VALUES ('John', 40, 'john@example.com');
    INSERT INTO users (name, age, email) VALUES ('Jane', 28, 'jane@example.com');
    UPDATE users SET age = 41 WHERE name = 'John';
''')
conn.commit()

This will execute all three SQL commands in a single batch.

Q14: How can I improve the performance of my SQLite queries?

A14: To optimize performance when working with SQLite:

1. Use Indexes: Creating indexes on frequently queried columns (especially those used in WHERE clauses) can speed up data retrieval.
2. Limit Transactions: Group multiple operations into a single transaction instead of committing each operation individually. This reduces the overhead of writing to disk.
3. Avoid SELECT *: Only retrieve the columns you need in your queries to reduce the amount of data being processed.
4. VACUUM: Use the VACUUM command periodically to clean up and optimize the database file.

Thanks for the questions – don’t forget to checkout the official SQLite documentation for more insights!