You are previewing Essential SQLAlchemy, 2nd Edition.
O'Reilly logo
Essential SQLAlchemy, 2nd Edition

Book Description

Dive into SQLAlchemy, the popular, open-source code library that helps Python programmers work with relational databases such as Oracle, MySQL, PostgresSQL, and SQLite. Using real-world examples, this practical guide shows you how to build a simple database application with SQLAlchemy, and how to connect to multiple databases simultaneously with the same metadata. If you’re an intermediate Python developer with knowledge of basic SQL syntax and relational theory, this book serves as both a learning tool and a handy reference.

Table of Contents

  1. Preface
    1. Who This Book Is For
    2. How to Use the Examples
    3. Assumptions This Book Makes
    4. Conventions Used in This Book
    5. Using Code Examples
    6. Safari® Books Online
    7. How to Contact Us
    8. Acknowledgments
  2. Introduction to SQLAlchemy
    1. Why Use SQLAlchemy?
      1. SQLAlchemy Core and the SQL Expression Language
      2. ORM
    2. Choosing Between SQLAlchemy Core and ORM
    3. Installing SQLAlchemy and Connecting to a Database
      1. Installing Database Drivers
      2. Connecting to a Database
  3. I. SQLAlchemy Core
  4. 1. Schema and Types
    1. Types
    2. Metadata
    3. Tables
      1. Columns
      2. Keys and Constraints
      3. Indexes
      4. Relationships and ForeignKeyConstraints
    4. Persisting the Tables
  5. 2. Working with Data via SQLAlchemy Core
    1. Inserting Data
    2. Querying Data
      1. ResultProxy
      2. Controlling the Columns in the Query
      3. Ordering
      4. Limiting
      5. Built-In SQL Functions and Labels
      6. Filtering
      7. ClauseElements
      8. Operators
      9. Boolean Operators
      10. Conjunctions
    3. Updating Data
    4. Deleting Data
    5. Joins
    6. Aliases
    7. Grouping
    8. Chaining
    9. Raw Queries
  6. 3. Exceptions and Transactions
    1. Exceptions
      1. AttributeError
      2. IntegrityError
      3. Handling Errors
    2. Transactions
  7. 4. Testing
    1. Testing with a Test Database
    2. Using Mocks
  8. 5. Reflection
    1. Reflecting Individual Tables
    2. Reflecting a Whole Database
    3. Query Building with Reflected Objects
  9. II. SQLAlchemy ORM
  10. 6. Defining Schema with SQLAlchemy ORM
    1. Defining Tables via ORM Classes
      1. Keys, Constraints, and Indexes
    2. Relationships
    3. Persisting the Schema
  11. 7. Working with Data via SQLAlchemy ORM
    1. The Session
    2. Inserting Data
    3. Querying Data
      1. Controlling the Columns in the Query
      2. Ordering
      3. Limiting
      4. Built-In SQL Functions and Labels
      5. Filtering
      6. Operators
      7. Boolean Operators
      8. Conjunctions
    4. Updating Data
    5. Deleting Data
    6. Joins
    7. Grouping
    8. Chaining
    9. Raw Queries
  12. 8. Understanding the Session and Exceptions
    1. The SQLAlchemy Session
      1. Session States
    2. Exceptions
      1. MultipleResultsFound Exception
      2. DetachedInstanceError
    3. Transactions
  13. 9. Testing with SQLAlchemy ORM
    1. Testing with a Test Database
    2. Using Mocks
  14. 10. Reflection with SQLAlchemy ORM and Automap
    1. Reflecting a Database with Automap
    2. Reflected Relationships
  15. III. Alembic
  16. 11. Getting Started with Alembic
    1. Creating the Migration Environment
    2. Configuring the Migration Environment
  17. 12. Building Migrations
    1. Generating a Base Empty Migration
    2. Autogenerating a Migration
    3. Building a Migration Manually
  18. 13. Controlling Alembic
    1. Determining a Database’s Migration Level
    2. Downgrading Migrations
    3. Marking the Database Migration Level
    4. Generating SQL
  19. 14. Cookbook
    1. Hybrid Attributes
    2. Association Proxy
    3. Integrating SQLAlchemy with Flask
    4. SQLAcodegen
  20. 15. Where to Go from Here
  21. Index