Section 1.1 What is SQLAlchemy?

SQLAlchemy is an Object-Relational Mapping (ORM) library designed for Python. It offers a high-level API that facilitates communication with relational databases. Its main objective is to create a seamless connection between Python's object-oriented programming approach and the structured environment of relational databases. By utilizing SQLAlchemy, developers can perform database operations using Python objects, making the process more intuitive and aligned with Python's syntax.

Section 1.2 Setting Up SQLAlchemy

Before we dive into examples, we need to set up SQLAlchemy. You can install it with the following command:

pip install sqlalchemy

Section 1.3 Connecting to a Database

Let’s start by establishing a connection to a database. SQLAlchemy supports various database engines; for this demonstration, we will use SQLite.

# File: app.py

from sqlalchemy import create_engine

# Connect to an SQLite database

engine = create_engine('sqlite:///example.db', echo=True)

In this snippet, we create an SQLite database called 'example.db.' The echo=True parameter enables logging, which provides visibility into the SQL commands being executed.

Section 1.4 Defining a Model

In SQLAlchemy, a model is represented by a Python class that corresponds to a table in the database. Let’s create a simple model for a 'User' table.

# File: app.py

from sqlalchemy import Column, Integer, String, Sequence

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):

__tablename__ = 'users'

id = Column(Integer, Sequence('user_id_seq'), primary_key=True)

username = Column(String(50), unique=True)

email = Column(String(50), unique=True)

In this example, we define a 'User' class that inherits from the declarative base class provided by SQLAlchemy. Class attributes are used to define columns in the 'users' table, including an auto-incrementing primary key.

Section 1.5 Creating Tables

Now that we have our model established, we can create the corresponding table in the database.

# File: app.py

Base.metadata.create_all(engine)

This line of code creates all tables defined in our models. Make sure to execute it after defining all models and before any database operations.

Section 1.6 Adding Data to the Database

Next, let’s add a new user to the 'users' table.

# File: app.py

from sqlalchemy.orm import Session

# Create a session to interact with the database

session = Session(engine)

# Add a new user

new_user = User(username='john_doe', email='[email protected]')

session.add(new_user)

session.commit()

In this block, we create a session to interact with the database and add a new user. The commit() method saves the changes to the database.

Section 1.7 Querying Data

Now, let's retrieve data from the 'users' table.

# File: app.py

# Query all users

users = session.query(User).all()

for user in users:

print(f"User ID: {user.id}, Username: {user.username}, Email: {user.email}")

This code fetches all users from the 'users' table and prints their details.

Section 1.8 Updating Data

Let's update the email address of the user we just added.

# File: app.py

# Update user email

user_to_update = session.query(User).filter_by(username='john_doe').first()

user_to_update.email = '[email protected]'

session.commit()

This snippet locates the user with the username 'john_doe' and updates their email address.

Section 1.9 Deleting Data

Finally, let’s remove a user from the database.

# File: app.py

# Delete a user

user_to_delete = session.query(User).filter_by(username='john_doe').first()

session.delete(user_to_delete)

session.commit()

This code finds the user with the username 'john_doe' and deletes them from the database.

Section 1.10 Handling Relationships

One of SQLAlchemy's standout features is its ability to manage relationships between tables. Let’s extend our model to include a 'Post' table and establish a one-to-many relationship with the 'User' table.

# File: app.py

from sqlalchemy import ForeignKey

from sqlalchemy.orm import relationship

class Post(Base):

__tablename__ = 'posts'

id = Column(Integer, Sequence('post_id_seq'), primary_key=True)

title = Column(String(100))

content = Column(String(500))

user_id = Column(Integer, ForeignKey('users.id'))

user = relationship('User', back_populates='posts')

User.posts = relationship('Post', order_by=Post.id, back_populates='user')

In this example, we define a 'Post' class that includes a foreign key relationship to the 'User' table, creating a bidirectional relationship.

Section 1.11 Querying Related Data

Now, let’s query a user along with their posts.

# File: app.py

# Query a user and their posts

user_with_posts = session.query(User).filter_by(username='john_doe').first()

print(f"User: {user_with_posts.username}")

for post in user_with_posts.posts:

print(f"Post Title: {post.title}, Content: {post.content}")

This code retrieves a user and displays their posts.