sqlalchemy - dwilson2547/wiki_demo GitHub Wiki

SQLAlchemy is a Python SQL toolkit and Object-Relational Mapping (ORM) library that provides a high-level, Pythonic way to interact with databases. It allows developers to work with databases using Python classes and objects instead of writing raw SQL, while still providing the flexibility to use raw SQL when needed. SQLAlchemy supports multiple database backends, including PostgreSQL, MySQL, SQLite, Oracle, and Microsoft SQL Server.

• 1. Key Features of SQLAlchemy
  • 1.1. ORM (Object-Relational Mapping)
  • 1.2. Core vs. ORM
  • 1.3. Database Abstraction
  • 1.4. Session Management
  • 1.5. Relationships
  • 1.6. Querying Data
• 2. SQLAlchemy Workflow
  • 2.1. Setup and Configuration
  • 2.2. Define Models
  • 2.3. Create Tables
  • 2.4. Insert Data
  • 2.5. Query Data
  • 2.6. Update Data
  • 2.7. Delete Data
• 3. SQLAlchemy Core vs. ORM
• 4. Advanced Features
  • 4.1. Transactions
  • 4.2. Eager Loading
  • 4.3. Hybrid Properties
  • 4.4. Events
• 5. Example: Full CRUD Application
  • 5.1. Setup
  • 5.2. Define Model
  • 5.3. CRUD Operations
• 6. SQLAlchemy with Asyncio (SQLAlchemy 1.4+)
• 7. Strengths of SQLAlchemy
• 8. Weaknesses of SQLAlchemy
• 9. When to Use SQLAlchemy
• 10. Alternatives to SQLAlchemy
• 11. Learning Resources
• 12. Summary

• Map Python Classes to Database Tables: Define database tables as Python classes.
• Example:

  from sqlalchemy import Column, Integer, String
  from sqlalchemy.ext.declarative import declarative_base
  
  Base = declarative_base()
  
  class User(Base):
      __tablename__ = 'users'
      id = Column(Integer, primary_key=True)
      name = Column(String)
      age = Column(Integer)

• SQLAlchemy Core: Provides a low-level, SQL-expression language for writing database-agnostic SQL queries.

  from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData
  
  metadata = MetaData()
  users = Table('users', metadata,
                Column('id', Integer, primary_key=True),
                Column('name', String),
                Column('age', Integer))
  
  engine = create_engine('sqlite:///mydatabase.db')
  metadata.create_all(engine)  # Creates the table

• SQLAlchemy ORM: High-level abstraction for working with objects instead of tables and rows.

• Single API for Multiple Databases: Write code once and switch databases (e.g., SQLite → PostgreSQL) with minimal changes.
• Supported Databases: PostgreSQL, MySQL, SQLite, Oracle, MSSQL, and more.

• Unit of Work Pattern: The Session object tracks changes and commits them to the database.

  from sqlalchemy.orm import sessionmaker
  
  Session = sessionmaker(bind=engine)
  session = Session()
  
  # Add a new user
  new_user = User(name="Alice", age=30)
  session.add(new_user)
  session.commit()  # Saves to the database

• Define one-to-many, many-to-one, and many-to-many relationships between tables.

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

• Use the query interface to retrieve data.

  # Get all users
  users = session.query(User).all()
  
  # Filter users by age
  adults = session.query(User).filter(User.age >= 18).all()
  
  # Get a user by ID
  user = session.query(User).get(1)

1. Install SQLAlchemy:

   pip install sqlalchemy

2. Create an Engine:

   from sqlalchemy import create_engine
   engine = create_engine('sqlite:///mydatabase.db')

3. Define the Base Class:

   from sqlalchemy.ext.declarative import declarative_base
   Base = declarative_base()

from sqlalchemy import Column, Integer, String

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

Base.metadata.create_all(engine)  # Creates tables in the database

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)
session = Session()

# Add a user
user = User(name="Bob", age=25)
session.add(user)
session.commit()

# Get all users
users = session.query(User).all()
for user in users:
    print(user.name, user.age)

# Filter users
adults = session.query(User).filter(User.age >= 18).all()

user = session.query(User).get(1)
user.age = 26
session.commit()

user = session.query(User).get(1)
session.delete(user)
session.commit()

• Use session.begin() for explicit transactions.

  with session.begin():
      user = User(name="Charlie", age=35)
      session.add(user)

• Load related objects upfront to avoid the N+1 query problem.

  users = session.query(User).options(joinedload(User.addresses)).all()

• Combine Python logic with database columns.

  from sqlalchemy.ext.hybrid import hybrid_property
  
  class User(Base):
      __tablename__ = 'users'
      id = Column(Integer, primary_key=True)
      first_name = Column(String)
      last_name = Column(String)
  
      @hybrid_property
      def full_name(self):
          return f"{self.first_name} {self.last_name}"

• Use event listeners to execute code before/after database operations.

  from sqlalchemy import event
  
  @event.listens_for(User, 'before_insert')
  def before_insert(mapper, connection, target):
      print(f"Inserting {target.name}")

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

engine = create_engine('sqlite:///example.db')
Base = declarative_base()
Session = sessionmaker(bind=engine)
session = Session()

class Book(Base):
    __tablename__ = 'books'
    id = Column(Integer, primary_key=True)
    title = Column(String)
    author = Column(String)
    published_year = Column(Integer)

Base.metadata.create_all(engine)

# Create
new_book = Book(title="The Great Gatsby", author="F. Scott Fitzgerald", published_year=1925)
session.add(new_book)
session.commit()

# Read
books = session.query(Book).all()
for book in books:
    print(book.title, book.author)

# Update
book = session.query(Book).get(1)
book.published_year = 1926
session.commit()

# Delete
book = session.query(Book).get(1)
session.delete(book)
session.commit()

• Asynchronous Support: Use asyncio for non-blocking database operations.

  from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
  
  engine = create_async_engine('sqlite+aiosqlite:///example.db')
  async with engine.begin() as conn:
      await conn.run_sync(Base.metadata.create_all)
  
  async_session = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)
  
  async with async_session() as session:
      result = await session.execute(select(User))
      users = result.scalars().all()

✅ Flexibility: Use ORM for high-level operations or Core for fine-grained SQL control. ✅ Database Agnostic: Switch databases with minimal code changes. ✅ Performance: Optimized for both small and large-scale applications. ✅ Mature and Stable: Widely used in production for over a decade. ✅ Rich Query API: Powerful querying capabilities with a Pythonic interface. ✅ Integration: Works well with FastAPI, Flask, Django, and other frameworks.

❌ Learning Curve: ORM can be complex for beginners. ❌ Performance Overhead: ORM adds overhead compared to raw SQL (mitigated by Core). ❌ Debugging: Complex queries can be hard to debug. ❌ Boilerplate: Requires more setup than simpler ORMs (e.g., Django ORM).

• Complex Applications: When you need fine-grained control over database operations.
• Multi-Database Support: When your application might switch databases.
• Legacy Databases: When working with existing schemas or complex queries.
• Performance-Critical Apps: Use SQLAlchemy Core for optimized queries.
• Python Projects: When you want a Pythonic, object-oriented approach to databases.

• Official Documentation: sqlalchemy.org
• Tutorials:
  • SQLAlchemy ORM Tutorial
  • SQLAlchemy Core Tutorial
• Books:
  • Essential SQLAlchemy by Rick Copeland
• Courses:
  • SQLAlchemy on Udemy
  • Real Python: SQLAlchemy

• SQLAlchemy is a powerful, flexible ORM and SQL toolkit for Python.
• Key Features: ORM, Core, database abstraction, relationships, and session management.
• Strengths: Flexibility, performance, and database-agnostic design.
• Weaknesses: Learning curve and boilerplate.
• Use Cases: Ideal for complex applications, multi-database projects, and performance-critical apps.

SQLAlchemy is the go-to choice for Python developers who need both high-level ORM features and low-level SQL control. Whether you're building a small script or a large-scale application, SQLAlchemy provides the tools to interact with databases efficiently and elegantly.