SQLAlchemy 101: Exploring Object-Relational Mapping (ORM) and Core API for Queries

SQLAlchemy in Action

SQLAlchemy offers two main approaches for querying tables:

1. Object Relational Mapping (ORM): This method treats your database tables as Python classes. You interact with the database through these classes, making the process more intuitive.
2. Core API: This approach deals directly with SQL statements, providing more granular control over queries.

Using SQLAlchemy ORM

• Import necessary libraries:

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

• Define your table structure:

Base = declarative_base()

class User(Base):
    # Define table columns and data types here
    pass

• Connect to your database:

engine = create_engine('your_database_url')
Base.metadata.create_all(engine)  # Create tables if they don't exist

# Create a session object to interact with the database
Session = sessionmaker(bind=engine)
session = Session()

• Query the table:

# Get all users
users = session.query(User).all()  # all() fetches all results

# Filter users by name
name = 'Alice'
filtered_users = session.query(User).filter(User.name == name).all()

# Select specific columns
from sqlalchemy import select

user_names = session.execute(select(User.name)).fetchall()  # fetches all names

# Remember to close the session
session.close()

Using SQLAlchemy Core API

• Import libraries:

from sqlalchemy import create_engine, select

• Connect to the database:

engine = create_engine('your_database_url')

• Build the query:

# Select all users from the 'users' table
query = select('*').from_('users')

# Execute the query and fetch results
result = engine.execute(query)
users = result.fetchall()

Choosing the Right Approach

• Use ORM for a more Pythonic and object-oriented way to interact with databases, especially for complex queries involving relationships between tables.
• Use Core API for greater control over raw SQL statements or for simpler queries.

Using SQLAlchemy ORM

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

# Define the database URL (replace with your actual connection details)
DATABASE_URL = 'sqlite:///users.db'  # Example using a SQLite database

# Create the base class for ORM models
Base = declarative_base()

# Define the User model (replace data types as needed)
class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    email = Column(String, unique=True)  # Assuming emails are unique

# Connect to the database
engine = create_engine(DATABASE_URL)

# Create tables if they don't exist (optional, can be done separately)
Base.metadata.create_all(engine)

# Create a session to interact with the database
Session = sessionmaker(bind=engine)
session = Session()

# Example queries:

# Get all users
users = session.query(User).all()
print("All users:", users)  # Prints a list of User objects

# Filter users by name
name_to_find = 'Alice'
filtered_users = session.query(User).filter(User.name == name_to_find).all()
print(f"Users named {name_to_find}:", filtered_users)  # Prints matching users

# Select specific columns (using Core API within ORM context)
from sqlalchemy import select

user_names = session.execute(select(User.name)).fetchall()
print("User names:", user_names)  # Prints a list of tuples containing names

# Remember to close the session
session.close()

Using SQLAlchemy Core API

from sqlalchemy import create_engine, select

# Define the database URL (replace with your actual connection details)
DATABASE_URL = 'sqlite:///users.db'  # Example using a SQLite database

# Connect to the database
engine = create_engine(DATABASE_URL)

# Build the query
table_name = 'users'  # Replace with your actual table name
query = select('*').from_(table_name)

# Execute the query and fetch results
result = engine.execute(query)
users = result.fetchall()
print("All users:", users)  # Prints a list of tuples containing all columns

# Close the engine connection (optional, handled automatically in some scenarios)
engine.dispose()

These examples demonstrate both ORM and Core API approaches, including:

• Clearer variable names and comments
• Example data types for the User model
• Using f-strings for formatted printing
• Highlighting the use of Core API within the ORM context for specific column selection
• Reminders for closing the session/engine connection

Remember to replace placeholders like DATABASE_URL and table_name with your actual database details.

Alternate Methods for Querying Tables in SQLAlchemy

Raw SQL Strings (Core API):

In limited cases, you can construct raw SQL strings directly. However, this is generally discouraged due to potential for SQL injection vulnerabilities and reduced readability. Use it with caution and proper parameterization:

from sqlalchemy import create_engine

engine = create_engine('your_database_url')

# Example (not recommended for most cases)
name_to_find = 'Alice'
query_string = f"SELECT * FROM users WHERE name = '{name_to_find}'"
result = engine.execute(query_string)
users = result.fetchall()

SQLAlchemy Alembic:

For database schema migrations, consider using Alembic, an official SQLAlchemy add-on for version control of database schema changes. It allows for writing migration scripts that define how the database schema evolves over time.

Other ORM Libraries:

While SQLAlchemy is a popular choice, other Python ORM libraries exist, such as Pony or Django ORM. These might offer different features or syntax depending on your project's needs.

Choosing the Right Method:

• For most cases, SQLAlchemy's ORM offers a good balance of readability and power.
• Use the Core API when you need more granular control over the SQL statements.
• Avoid raw SQL strings unless absolutely necessary due to security concerns.
• Consider Alembic for managing database schema changes.
• Explore other ORM libraries if SQLAlchemy doesn't fit your specific requirements.