Chapter 10: Working with Databases

Working with Databases

In this blog post, we will discuss working with databases in Python. Python has several libraries to connect and work with various types of databases such as SQLite, MySQL, PostgreSQL, Oracle, and MongoDB. The standard library includes the sqlite3 module for SQLite databases. For other databases, you will need to install an appropriate third-party library.

Here’s a basic overview of how you would generally use these libraries:

• Connect to the database: This typically involves creating a connection object by calling a function such as connect() and passing the appropriate connection parameters, such as the database name, username, password, and host.

• Create a cursor object: A cursor is a database control structure that enables traversal over the records in a database. You can create a cursor object by calling the cursor() method on the connection object.

• Execute SQL commands: You can execute SQL commands using the execute() method of the cursor object.

• Commit changes and close the connection: Any changes you make to the database are not actually saved until you call the commit() method on the connection object. You should close the connection when you’re done using it to free up resources.

Here’s an example of how you would create a table, insert data, and query data in a SQLite database:

import sqlite3

# Connect to the database
conn = sqlite3.connect('example.db')

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

# Execute some SQL commands
cursor.execute('''CREATE TABLE stocks
                 (date text, trans text, symbol text, qty real, price real)''')
cursor.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")

# Commit the changes and close the connection
conn.commit()
conn.close()

To query data, you would use the execute() method and then call fetchone() or fetchall() on the cursor object:

import sqlite3

# Connect to the database and create a cursor object
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Execute a query
cursor.execute("SELECT * FROM stocks WHERE symbol = 'RHAT'")

# Fetch and print the results
print(cursor.fetchone())

# Close the connection
conn.close()

This example uses SQLite, but the process is similar for other databases. You just need to use the appropriate connection parameters and possibly install a third-party library. For example, for PostgreSQL, you would use the psycopg2 library, and for MySQL, you would use the mysql-connector-python library.

Connecting to databases

Connecting to databases in Python typically involves importing the appropriate database module, and then calling its connect() function with the appropriate arguments. Let’s look at a few examples with different types of databases:

SQLite

SQLite is a lightweight, file-based database included with Python. Here’s how you connect to it:

import sqlite3

conn = sqlite3.connect('example.db')  # Connects to a file-based SQLite database

MySQL or MariaDB

You can use the mysql-connector-python module to connect to MySQL or MariaDB. You will need to install it first with pip install mysql-connector-python.

import mysql.connector

conn = mysql.connector.connect(
    host='localhost',
    user='user',
    password='password',
    database='test'
)

PostgreSQL

The psycopg2 module is commonly used to connect to PostgreSQL databases. Install it with pip install psycopg2.

import psycopg2

conn = psycopg2.connect(
    host='localhost',
    user='user',
    password='password',
    dbname='test'
)

Oracle

You can use the cx_Oracle module to connect to Oracle databases. Install it with pip install cx_Oracle.

import cx_Oracle

conn = cx_Oracle.connect('user/password@localhost/test')

SQL Server

The pyodbc module can be used to connect to SQL Server databases. Install it with pip install pyodbc.

import pyodbc

conn = pyodbc.connect(
    'DRIVER={ODBC Driver 17 for SQL Server};'
    'SERVER=localhost;'
    'DATABASE=test;'
    'UID=user;'
    'PWD=password;'
)

In each case, replace the placeholders (like ‘localhost’, ‘user’, ‘password’, and ‘test’) with your actual database details. After you have a connection, you can create a cursor (cursor = conn.cursor()) and execute SQL queries (cursor.execute(“SELECT * FROM my_table”)). Always remember to close the connection when you’re done (conn.close()).

Executing SQL queries

For executing SQL queries in Python, we first need to establish a database connection. In this example, we’ll use SQLite which comes pre-packaged with Python. We’ll be using an imaginary automotive database where we maintain vehicle data and customer purchases.

Please note that we’re assuming the two tables Vehicle and CustomerPurchases already exist in the database. If they don’t, you need to create them first.

import sqlite3

# Connect to the SQLite database
conn = sqlite3.connect('automotive.db')
cursor = conn.cursor()

# Let's suppose we have two tables `Vehicle` and `CustomerPurchases` in our database.

# The `Vehicle` table has the following fields:
# VehicleID, Brand, Model, Year, Price

# The `CustomerPurchases` table has the following fields:
# PurchaseID, CustomerName, VehicleID, PurchaseDate, PurchasePrice

# SQL queries

# 1. Fetch all vehicles of the brand 'Toyota'
cursor.execute("SELECT * FROM Vehicle WHERE Brand = 'Toyota'")
toyota_vehicles = cursor.fetchall()
print("Toyota Vehicles: ", toyota_vehicles)

# 2. Fetch all customers who purchased a vehicle in 2023
cursor.execute("SELECT DISTINCT CustomerName FROM CustomerPurchases WHERE strftime('%Y', PurchaseDate) = '2023'")
customers_2023 = cursor.fetchall()
print("Customers who purchased in 2023: ", customers_2023)

# 3. Update vehicle price
cursor.execute("UPDATE Vehicle SET Price = 25000 WHERE Brand = 'Toyota' AND Model = 'Camry' AND Year = 2023")
conn.commit()

# 4. Delete records of purchases made before 2022
cursor.execute("DELETE FROM CustomerPurchases WHERE strftime('%Y', PurchaseDate) < '2022'")
conn.commit()

# Close the connection
conn.close()

In the example above, we performed several operations:

• SELECT – We selected all vehicles from the brand ‘Toyota’ and all unique customers who made a purchase in 2023.
• UPDATE – We updated the price of the 2023 Toyota Camry to 25000.
• DELETE – We deleted all purchase records made before 2022.

After executing any query that modifies the database (UPDATE, INSERT, DELETE), we need to commit the changes using conn.commit(). And finally, we always close the connection after we’re done with it to free up resources.

Fetching and manipulating data

Once you’ve connected to your database and executed your SQL queries, you’ll usually want to do something with the returned data. Let’s look at how you might fetch and manipulate data from an automotive database in Python. For this example, we’ll continue to use SQLite:

import sqlite3

# Connect to the SQLite database
conn = sqlite3.connect('automotive.db')
cursor = conn.cursor()

# 1. Fetch all vehicles of the brand 'Toyota'
cursor.execute("SELECT * FROM Vehicle WHERE Brand = 'Toyota'")
toyota_vehicles = cursor.fetchall()

for vehicle in toyota_vehicles:
    print(f"Vehicle ID: {vehicle[0]}, Brand: {vehicle[1]}, Model: {vehicle[2]}, Year: {vehicle[3]}, Price: {vehicle[4]}")

# 2. Calculate the average price of all vehicles in the database
cursor.execute("SELECT AVG(Price) FROM Vehicle")
average_price = cursor.fetchone()
print(f"The average vehicle price is: {average_price[0]}")

# 3. Fetch all purchases in 2023 and calculate total sales
cursor.execute("SELECT PurchasePrice FROM CustomerPurchases WHERE strftime('%Y', PurchaseDate) = '2023'")
sales_2023 = cursor.fetchall()

total_sales = sum(sale[0] for sale in sales_2023)
print(f"Total sales in 2023: {total_sales}")

# Close the connection
conn.close()

In this example, we used the fetchall() method to retrieve all rows returned by the SELECT queries. We then looped through the rows and printed out the data. We also used fetchone() to retrieve a single row (the average price). After fetching the data, we can manipulate it in any way we want, such as calculating sums, averages, or other statistics.

Real World Example – 1

For this task, we’ll use SQLite as our database management system since it’s lightweight, file-based, and it comes bundled with Python. The example will simulate an automotive software system where we will create a database for a car dealership, then insert, update, retrieve and delete records.

# Importing necessary libraries
import sqlite3
from sqlite3 import Error

def create_connection():
    conn = None;
    try:
        # This line creates a SQLite database in memory. 
        # Replace ':memory:' with a database file like 'db.sqlite' to persist the database
        conn = sqlite3.connect(':memory:') 
        print(f"Successfully Connected to SQLite Version: {sqlite3.version}")
    except Error as e:
        print(f"Error occurred: {e}")
        
    return conn

def close_connection(conn):
    conn.close()
    print("SQLite connection closed")

def execute_query(conn, query):
    try:
        cursor = conn.cursor()
        cursor.execute(query)
    except Error as e:
        print(f"Error occurred: {e}")

# Create Cars table
create_cars_table_query = """
CREATE TABLE IF NOT EXISTS Cars (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    make TEXT NOT NULL,
    model TEXT NOT NULL,
    year INTEGER,
    price REAL
);
"""

conn = create_connection()

# Execute the create table query
execute_query(conn, create_cars_table_query)

# Insert car records
insert_car_query = """
INSERT INTO Cars (make, model, year, price) VALUES 
    ('Toyota', 'Corolla', 2020, 18000),
    ('Ford', 'Mustang', 2019, 25000),
    ('Honda', 'Civic', 2021, 20000);
"""
execute_query(conn, insert_car_query)

# Retrieve records
select_all_query = "SELECT * FROM Cars"
cursor = conn.cursor()
cursor.execute(select_all_query)
rows = cursor.fetchall()
print("Printing all car records")
for row in rows:
    print(row)

# Update record
update_car_query = "UPDATE Cars SET price = 19000 WHERE make = 'Toyota'"
execute_query(conn, update_car_query)

# Verify Update
select_toyota_query = "SELECT * FROM Cars WHERE make = 'Toyota'"
cursor.execute(select_toyota_query)
rows = cursor.fetchall()
print("\nPrinting updated Toyota record")
for row in rows:
    print(row)

# Delete record
delete_car_query = "DELETE FROM Cars WHERE make = 'Ford'"
execute_query(conn, delete_car_query)

# Verify Deletion
cursor.execute(select_all_query)
rows = cursor.fetchall()
print("\nPrinting all car records after deletion")
for row in rows:
    print(row)

# Close connection
close_connection(conn)

In the code above, we first define helper functions to create, execute queries, and close the SQLite connection. We then create a table Cars with attributes id, make, model, year, and price. We proceed to insert a few records, retrieve and print all records, update a specific record, and finally delete a record. Each operation is followed by a retrieval to confirm the action.

Remember to replace :memory: with the path to your own database file if you want the data to be persistent. The :memory: keyword creates a temporary, in-memory database that’s deleted when the connection is closed.

Real World Example – 2

For this exercise, we will be using PostgreSQL as our database system and the psycopg2 module to connect Python with PostgreSQL. The psycopg2 module can be installed with pip: pip install psycopg2-binary.

In our scenario, let’s assume we are working with an aerospace software system where we maintain a database of all rockets launched. We will perform various database operations such as creating a table for rocket launches, inserting records, querying data, updating records and deleting data.

Please note that you need to replace your_username, your_password, your_host, your_port and your_database with your own PostgreSQL configuration details.

# Import the psycopg2 library
import psycopg2
from psycopg2 import sql, Error

# Database connection parameters
params = {
    "user": "your_username",
    "password": "your_password",
    "host": "your_host",
    "port": "your_port",
    "database": "your_database"
}

def create_connection(params):
    conn = None
    try:
        conn = psycopg2.connect(**params)
        print("Successfully connected to the database")
    except Error as e:
        print(f"Error occurred: {e}")

    return conn

def close_connection(conn):
    if conn:
        conn.close()
        print("Database connection closed")

def execute_query(conn, query):
    try:
        cursor = conn.cursor()
        cursor.execute(query)
        conn.commit()
    except Error as e:
        print(f"Error occurred: {e}")

# Create connection
conn = create_connection(params)

# Create a table for rocket launches
create_table_query = """
CREATE TABLE IF NOT EXISTS RocketLaunches (
    id SERIAL PRIMARY KEY,
    rocket_name VARCHAR(100),
    launch_date DATE,
    launch_site VARCHAR(100),
    mission_status VARCHAR(50)
);
"""
execute_query(conn, create_table_query)

# Insert records into the table
insert_data_query = """
INSERT INTO RocketLaunches (rocket_name, launch_date, launch_site, mission_status) VALUES 
    ('Falcon 9', '2020-05-30', 'Kennedy Space Center', 'Success'),
    ('Starship', '2021-03-30', 'Boca Chica Launch Site', 'Fail'),
    ('Astra Rocket', '2021-12-10', 'Pacific Spaceport Complex', 'Success');
"""
execute_query(conn, insert_data_query)

# Fetch all records from the table
def fetch_data(conn):
    select_query = "SELECT * FROM RocketLaunches"
    cursor = conn.cursor()
    cursor.execute(select_query)
    records = cursor.fetchall()
    print("\nRocket launch records:")
    for row in records:
        print(row)

fetch_data(conn)

# Update a record in the table
update_data_query = """
UPDATE RocketLaunches 
SET mission_status = 'Success' 
WHERE rocket_name = 'Starship';
"""
execute_query(conn, update_data_query)

# Fetch data to confirm update
fetch_data(conn)

# Delete a record from the table
delete_data_query = """
DELETE FROM RocketLaunches 
WHERE rocket_name = 'Astra Rocket';
"""
execute_query(conn, delete_data_query)

# Fetch data to confirm deletion
fetch_data(conn)

# Close connection
close_connection(conn)

This script starts by importing the necessary libraries and setting up the PostgreSQL connection parameters. We then define a function to establish a connection to the database, execute SQL queries, fetch data, and finally close the connection. We create a RocketLaunches table and perform various operations such as inserting records, querying the table, updating a record, and deleting a record. Each operation is followed by querying all records from the table to demonstrate the changes made to the data.

Remember to replace the connection parameters with your actual PostgreSQL connection details.

Finally, remember to always close your database connection when you’re done with it to free up resources.

Conclusion

In conclusion, working with databases in Python is a vital skill that enables developers to build more sophisticated and data-driven applications.

We also appreciated the convenience of Python’s approach to database management, emphasizing readability and simplicity without sacrificing power or flexibility.

It’s about understanding the data, designing appropriate schemas, ensuring data integrity, optimizing queries, and managing connections and resources.

By mastering the interaction between Python and databases, developers can unlock the full potential of their applications, turning raw data into valuable insights and actions. Python, with its simplicity and power, offers an excellent platform for this exciting journey into the world of databases.