Introduction

In this lesson, we will dive into integrating databases with Python to create a complete database application. This project-oriented lesson builds on your previous foundational knowledge of database concepts, focusing on how to programmatically interact with databases using Python's SQLite and SQLAlchemy libraries. You'll gain hands-on experience with executing CRUD (Create, Read, Update, Delete) operations, managing database transactions and exceptions, and designing efficient database schemas and relationships.

Understanding Database Connection in Python

To start integrating with databases, we must establish a connection from Python to the database. Python offers several libraries to facilitate this, with SQLite being one of the simplest and most accessible.

Using SQLite Library

SQLite is a lightweight database engine that's built into Python, making it perfect for local development and prototyping.

Here's how to establish a connection:

import sqlite3

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

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

Connections must always be closed after operations to maintain database integrity.

Using SQLAlchemy

SQLAlchemy provides a high-level, more flexible ORM (Object Relational Mapper) for Python. It allows for complex schema creators and object manipulation through its ORM capabilities:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# Create an SQLite connection using SQLAlchemy
engine = create_engine('sqlite:///example.db')
Session = sessionmaker(bind=engine)
session = Session()

CRUD Operations in Python

CRUD represents the four essential functions of persistent storage. Let's perform these operations using both SQLite and SQLAlchemy.

SQLite CRUD Operations

# Create a new table
cur.execute('''CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)''')
conn.commit()

# Insert a record
cur.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Alice', 25))
conn.commit()

# Read a record
cur.execute('SELECT * FROM users WHERE name=?', ('Alice',))
row = cur.fetchone()
print(row)

# Update a record
cur.execute('UPDATE users SET age=? WHERE name=?', (26, 'Alice'))
conn.commit()

# Delete a record
cur.execute('DELETE FROM users WHERE name=?', ('Alice',))
conn.commit()

SQLAlchemy CRUD Operations

Defining a model:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)

Base.metadata.create_all(engine)

Performing CRUD:

# Create
new_user = User(name='Alice', age=25)
session.add(new_user)
session.commit()

# Read
user = session.query(User).filter_by(name='Alice').first()
print(user)

# Update
user.age = 26
session.commit()

# Delete
session.delete(user)
session.commit()

Handling Database Transactions and Exceptions

Proper transaction management and exception handling are crucial in maintaining data integrity when performing database operations in Python.

Transactions

Transactions should be explicitly committed, or if an error occurs, rolled back to ensure partial changes do not corrupt the database.

try:
    cur.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Bob', 30))
    conn.commit()
except sqlite3.Error as e:
    conn.rollback()
    print('Transaction failed:', e)

Exceptions

Using SQLAlchemy, exceptions like IntegrityError can be caught to manage errors gracefully.

from sqlalchemy.exc import IntegrityError

try:
    session.add(User(name='Bob', age=30))
    session.commit()
except IntegrityError:
    session.rollback()
    print('Insertion failed due to integrity constraints')

Designing Database Schemas and Relationships

Schematic design is essential for efficient data storage and retrieval. With SQLAlchemy, defining relationships is straightforward.

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

class Address(Base):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    email_address = Column(String, nullable=False)
    user = relationship('User', back_populates='addresses')

User.addresses = relationship('Address', order_by=Address.id, back_populates='user')

Summary / Key Takeaways

In this lesson, Project: Complete Database Application in Python, you have learned:

• How to set up database connections using SQLite and SQLAlchemy
• Performing CRUD operations in both SQLite and SQLAlchemy
• Managing database transactions and handling exceptions effectively
• Designing complex schemas with relationships using SQLAlchemy
• Ensuring data integrity through correct transaction processing