Essential SQLAlchemy: Mapping Python to Databases

Essential SQLAlchemy introduces a high-level open-source code library that makes it easier for Python programmers to access relational databases such as Oracle, DB2, MySQL, PostgreSQL, and SQLite. SQLAlchemy has become increasingly popular since its release, but it still lacks good offline documentation. This practical book fills the gap, and because a developer wrote it, you get an objective look at SQLAlchemy's tools rather than an advocate's description of all the "cool" features. SQLAlchemy includes both a database server-independent SQL expression language and an object-relational mapper (ORM) that lets you map "plain old Python objects" (POPOs) to database tables without substantially changing your existing Python code. Essential SQLAlchemy demonstrates how to use the library to create a simple database application, walks you through simple queries, and explains how to use SQLAlchemy to connect to multiple databases simultaneously with the same Metadata. You also learn how to: Create custom types to be used in your schema, and when it's useful to use custom rather than built-in types Run queries, updates, and deletes with SQLAlchemy's SQL expression language Build an object mapper with SQLAlchemy, and understand the differences between this and active record patterns used in other ORMs Create objects, save them to a session, and flush them to the database Use SQLAlchemy to model object oriented inheritance Provide a declarative, active record pattern for use with SQLAlchemy using the Elixir extension Use the SQLSoup extension to provide an automatic metadata and object model based on database reflection In addition, you'll learn how and when to use other extensions to SQLAlchemy, including AssociationProxy, OrderingList, and more. Essential SQLAlchemy is the much-needed guide for every Python developer using this code library. Instead of a feature-by-feature documentation, this book takes an "essentials" approach that gives you exactly what you need to become productive with SQLAlchemy right away. Table of Contents Preface Audience Assumptions This Book Makes Contents of this Book Conventions Used in This Book Using Code Examples How to Contact Us Acknowledgments Chapter 1. Introduction to SQLAlchemy What Is SQLAlch The Object/Relational “Impedance Mismatch” SQLAlchemy Philosophy SQLAlchemy Architecture Engine Connection Pooling SQL dialect management MetaData Management Types System SQL Expression Language Object Relational Mapper (ORM) Chapter 2. Getting Started Installing SQLAlchemy Installing the SQLAlchemy Package Installing setup tools Installing SQLAlchemy with easy_install Testing the install Installing Some Database Drivers Installing the SQLite driver on Python versions before 2.5 Other supported drivers SQLAlchemy Tutorial Connecting to the Database and Creating Some Tables Performing Queries and Updates Mapping Objects to Tables Chapter 3. Engines and MetaData Engines and Connectables Configuring SQLAlchemy Logging Database Connections and ResultProxys Connection Pooling MetaData Getting Started with MetaData Defining Tables Table reflection Column Definitions Constraints Primary keys Foreign keys UNIQUE constraints CHECK constraints Defaults Active defaults Passive defaults Defining Indexes The Index object Creating Explicit Sequences MetaData Operations Binding MetaData Create/drop MetaData and schema objects Adapt Tables from one MetaData to another Chapter 4. SQLAlchemy Type Engines Type System Overview Built-in Types Generic Types Dialect-Specific Types Application-Specific Custom Types Implementing a TypeDecorator Creating a New TypeEngine Chapter 5. Running Queries and Updates Inserts, Updates, and Deletes Insert Statements Update Statements Delete Statements Queries Basic Query Construction The select⁠(⁠⁠) function versus the select⁠(⁠⁠) method Result set objects Operators and functions in WHERE clauses Using custom bind parameters Using literal text in queries Ordering and grouping results, returning distinct values Limiting results returned Using the “generative” query interface Joins and Set Operations Joining selectables Set operations (UNION, INTERSECT, EXCEPT) Using aliases Subqueries Embedding subqueries in the column list Correlated versus uncorrelated subqueries Embedding subqueries in an IN clause Embedding subqueries in the FROM clause Chapter 6. Building an Object Mapper Introduction to ORMs Design Concepts in the ORM The data mapper pattern The unit of work pattern Declaring Object Mappers Basic Object Mapping Customizing Property Mapping Using include_properties and exclude_properties Customizing the name of the mapped column Using synonyms Mapping subqueries Mapping composite values Eager versus deferred loading Mapping Arbitrary Selectables Other mapper⁠(⁠⁠) Parameters Declaring Relationships Between Mappers Basic Relationships 1:N relations M:N relations 1:1 relations Using BackRefs Using a Self-Referential Mapper Cascading Changes to Related Objects Other relation⁠(⁠⁠) and backref⁠(⁠⁠) Parameters Using custom collections in relations Extending Mappers ORM Partitioning Strategies Vertical Partitioning Horizontal Partitioning Chapter 7. Querying and Updating at the ORM Level The SQLAlchemy ORM Session Object Creating a Session Saving Objects to the Session Updating Objects in the Session Embedding SQL expressions in a flush Deleting Objects from the Session Flushing, Committing, and Rolling Back Session Changes Other Session Methods Extending Sessions Querying at the ORM Level ORM Querying with Joins Customizing the Select Statement in ORM Queries Other Query Methods Contextual or Thread-Local Sessions Using Contextual Sessions with Mappers and Classes Chapter 8. Inheritance Mapping Overview of Inheritance Mapping Single Table Inheritance Mapping Concrete Table Inheritance Mapping Joined Table Inheritance Mapping Optimizing Performance with Joined Table Inheritance Mapping Using deferred loading Using select_table Relations and Inheritance Chapter 9. Elixir: A Declarative Extension to SQLAlchemy Introduction to Elixir Installing Elixir Using Elixir Fields and Properties Elixir deferred properties Relations Attribute-based syntax DSL syntax Inheritance Querying Using Elixir Elixir Extensions Associable Extension Encrypted Extension Versioned Extension Chapter 10. SqlSoup: An Automatic Mapper for SQLAlchemy Introduction to SqlSoup Using SqlSoup for ORM-Style Queries and Updates Joins with SqlSoup Mapping Arbitrary Selectables Directly Accessing the Session Using SqlSoup for SQL-Level Inserts, Updates, and Deletes When to Use SqlSoup Versus Elixir Versus “Bare” SQLAlchemy SqlSoup Pros and Cons Elixir Pros and Cons Chapter 11. Other SQLAlchemy Extensions Association Proxy Ordering List Deprecated Extensions Index "Now Python developers can easily access relational databases such as Oracle, DB2, and MySQL. Essential SQLAlchemy walks you through simple queries, demonstrates how to create database applications, explains how to connect to multiple databases simultaneously with the same metadata, and more. With this guide, you'll learn how the SQLAlchemy open source code library lets you map objects to database tables without substantially changing your existing Python code." "This book gives you an objective look at SQLAlchemy from a developer's viewpoint rather than from an advocate's description. It's exactly what you need to quickly get up to speed with this code library."--Jacket Essential SQLAlchemy introduces a high-level open-source code library that makes it easier for Python programmers to access relational databases such as Oracle, DB2, MySQL, PostgreSQL, and SQLite. SQLAlchemy has become increasingly popular since its release, but it still lacks good documentation. This practical book fills the gap, and because a developer wrote it, the reader gets an objective look at SQLAlchemy's tools rather than an advocate's description of all the "cool" features This text presents a detailed introduction to SQLAlchemy, the code library for Python users that makes it easier to access popular databases such as Oracle and MySQL. It demonstrates how to use the library to create simple database applications, and walks readers through simple queries