Essential SQLAlchemy, 2nd Edition

Essential SQLAlchemy, 2nd Edition

by Jason Myers, Rick Copeland
Released November 2015
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781491916551

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

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.

SQL is a powerful language for querying and manipulating data, but it’s tough to integrate it with your application. SQLAlchemy helps you map Python objects to database tables without substantially changing your existing Python code. 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.

Essential SQLAlchemy includes several sections:

  • SQLAlchemy Core: Provide database services to your applications in a Pythonic way with the SQL Expression Language
  • SQLAlchemy ORM: Use the object relational mapper to bind database schema and operations to data objects in your application
  • Alembic: Use this lightweight database migration tool to handle changes to the database as your application evolves
  • Cookbook: Learn how to use SQLAlchemy with web frameworks like Flask and libraries like SQLAcodegen

Publisher resources

View/Submit Errata

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

Product information

  • Title: Essential SQLAlchemy, 2nd Edition
  • Author(s): Jason Myers, Rick Copeland
  • Release date: November 2015
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781491916551