Working with Databases in Python

In this module, we will learn how to interact with databases in Python. Databases are essential for storing, retrieving, and managing data in a structured way. Python provides libraries and frameworks to connect with databases such as SQLite and MySQL, as well as Object-Relational Mappers (ORMs) like SQLAlchemy and Peewee to make database interactions more Pythonic.

Subtopic 1: Introduction to Databases

A database is a structured collection of data. It allows users to store and manage data efficiently. There are different types of databases, but relational databases (RDBMS) such as MySQL, SQLite, and PostgreSQL are widely used.

Key Concepts:

Tables: Data is stored in tables, with rows and columns.
SQL (Structured Query Language): A language used to query and manipulate data in relational databases.
Primary Keys: A unique identifier for each record in a table.
Foreign Keys: A field in one table that links to the primary key in another table, establishing relationships.

Subtopic 2: Connecting to SQLite and MySQL

Python has built-in support for SQLite through the sqlite3 module, which allows you to create and interact with SQLite databases. For MySQL, we use the mysql-connector or PyMySQL package to connect and execute SQL commands.

SQLite Connection Example:

SQLite is a lightweight, file-based database, ideal for small to medium-sized applications.

import sqlite3

# Connect to the SQLite database (it will create the database if it doesn't exist)
connection = sqlite3.connect('example.db')

# Create a cursor object to interact with the database
cursor = connection.cursor()

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

# Insert a record into the table
cursor.execute("INSERT INTO users (name, age) VALUES ('Alice', 30)")

# Commit the changes
connection.commit()

# Fetch and print all records
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)

# Close the connection
connection.close()

MySQL Connection Example:

MySQL is a popular, high-performance database server used in larger-scale applications.

Install MySQL Connector:

pip install mysql-connector

Connecting and Executing SQL Commands:

import mysql.connector

# Connect to the MySQL database
connection = mysql.connector.connect(
    host="localhost",  # Database host
    user="root",       # Database username
    password="password",  # Database password
    database="mydatabase"  # Database name
)

cursor = connection.cursor()

# Create a table
cursor.execute('''CREATE TABLE IF NOT EXISTS users (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    name VARCHAR(100) NOT NULL,
                    age INT NOT NULL)''')

# Insert data
cursor.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ("Bob", 25))

# Commit the changes
connection.commit()

# Fetch and print all records
cursor.execute("SELECT * FROM users")
for row in cursor.fetchall():
    print(row)

# Close the connection
connection.close()

Subtopic 3: Executing SQL Commands

Executing SQL commands in Python involves sending queries to the database using a cursor object. SQL commands include:

SELECT: Retrieve data from one or more tables.
INSERT: Add new records to a table.
UPDATE: Modify existing records in a table.
DELETE: Remove records from a table.

Example of SQL Commands in Python:

SELECT Query:

cursor.execute("SELECT * FROM users WHERE age > 20")
rows = cursor.fetchall()
for row in rows:
    print(row)

INSERT Query:

cursor.execute("INSERT INTO users (name, age) VALUES ('Charlie', 40)")
connection.commit()  # Commit the changes to the database

UPDATE Query:

cursor.execute("UPDATE users SET age = 35 WHERE name = 'Bob'")
connection.commit()

DELETE Query:

cursor.execute("DELETE FROM users WHERE name = 'Charlie'")
connection.commit()

Subtopic 4: Using Python ORMs (SQLAlchemy, Peewee)

Object-Relational Mappers (ORMs) are Python libraries that allow developers to interact with databases using Python objects instead of writing raw SQL queries. ORMs translate Python class definitions to SQL queries and provide a more Pythonic interface to work with databases.

SQLAlchemy Example:

Install SQLAlchemy:

pip install sqlalchemy

Basic SQLAlchemy Example:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# Create an engine and a session
engine = create_engine('sqlite:///example.db', echo=True)
Base = declarative_base()

# Define the User class (mapped to a database table)
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)

# Create the table
Base.metadata.create_all(engine)

# Create a session
Session = sessionmaker(bind=engine)
session = Session()

# Insert a new user
new_user = User(name="Eve", age=28)
session.add(new_user)
session.commit()

# Query the database
user = session.query(User).filter_by(name="Eve").first()
print(user.name, user.age)

Peewee Example:

Install Peewee:

pip install peewee

Basic Peewee Example:

from peewee import *

# Connect to SQLite database
db = SqliteDatabase('example.db')

# Define the User model
class User(Model):
    name = CharField()
    age = IntegerField()

    class Meta:
        database = db  # Use the "example.db" database

# Create the table
db.connect()
db.create_tables([User])

# Insert a new user
new_user = User.create(name="Eve", age=28)

# Query the database
user = User.get(User.name == "Eve")
print(user.name, user.age)

Tasks

Task 1: Create a Database and Table
- Write a Python program to create a SQLite or MySQL database and a table called employees with the fields id, name, and salary.
Task 2: Insert Multiple Records
- Write a Python program to insert multiple records into the employees table.
Task 3: Retrieve Data
- Write a Python program to retrieve all records from the employees table and print them.
Task 4: Update Records
- Write a Python program to update the salary of a specific employee in the employees table.
Task 5: Delete Records
- Write a Python program to delete an employee's record from the employees table.
Task 6: Use SQLAlchemy to Define and Query a Database
- Write a Python program using SQLAlchemy to define a table students and perform a query to fetch all students with an age greater than 18.