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.
Author(s): Rick Copeland
Publisher: O'Reilly Media
Year: 2008
Language: English
Pages: 240
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
