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.
Chapter 1 Introduction to SQLAlchemy
What Is SQLAlchemy
The Object/Relational “Impedance Mismatch”
Chapter 2 Getting Started
Chapter 3 Engines and MetaData
Engines and Connectables
Chapter 4 SQLAlchemy Type Engines
Type System Overview
Application-Specific Custom Types
Chapter 5 Running Queries and Updates
Inserts, Updates, and Deletes
Chapter 6 Building an Object Mapper
Introduction to ORMs
Declaring Object Mappers
Declaring Relationships Between Mappers
ORM Partitioning Strategies
Chapter 7 Querying and Updating at the ORM Level
The SQLAlchemy ORM Session Object
Querying at the ORM Level
Contextual or Thread-Local Sessions
Chapter 8 Inheritance Mapping
Overview of Inheritance Mapping
Single Table Inheritance Mapping
Concrete Table Inheritance Mapping
Joined Table Inheritance Mapping
Relations and Inheritance
Chapter 9 Elixir: A Declarative Extension to SQLAlchemy
Introduction to Elixir
Chapter 10 SqlSoup: An Automatic Mapper for SQLAlchemy
Introduction to SqlSoup
Using SqlSoup for ORM-Style Queries and Updates
Using SqlSoup for SQL-Level Inserts, Updates, and Deletes
When to Use SqlSoup Versus Elixir Versus “Bare” SQLAlchemy
Rick Copeland is a senior software engineer with retail analytics firm Predictix, LLC, where he uses SQLAlchemy extensively, primarily for web application development. He has been using Python full-time for development since 2005, in projects as diverse as demand forecasting, business web applications, compilers, and hardware synthesis.
The animal on the cover of Essential SQLAlchemy is a largescale flying fish (Cypselurus oligolepis). Flying fish is the more common name for members of the Exocoetidae family, which comprises roughly 40 species that inhabit the warm tropical and subtropical waters of the Atlantic, Pacific, and Indian oceans. Flying fish range from 7 to12 inches in length and are characterized by their unusually large, winglike pectoral fins. Some species also have enlarged pelvic fins and are thus known as four-winged flying fish.
As their name suggests, flying fish have the unique ability to leap from the water and glide through the air for distances of up to a quarter of a mile. Their torpedo-like bodies help them gather the speed necessary to propel themselves from the ocean (about 37 miles per hour), and their distinctive pectoral fins and forked tailfins keep them airborne. Biologists believe this remarkable trait may have evolved as a way for flying fish to escape their many predators, which include tuna, mackerel, swordfish, marlin, and other larger fish. However, flying fish sometimes have a more difficult time evading their human predators. Attracted by a luring light that fishermen attach to their canoes at night, the fish leap in and are unable to vault themselves back out.
Dried flying fish are a dietary staple for the Tao people of Orchid Island, located off the coast of Taiwan, and flying fish roe is common in Japanese cuisine. They are also a coveted delicacy in Barbados, known as "Land of the flying fish" before shipping pollution and overfishing depleted their numbers. The flying fish retains a prominent cultural status there, however; it's the main ingredient in the national dish (cou cou and flying fish) and it is featured on coins, artwork, and even in the Barbados Tourism Authority's logo.
The cover image is from Dover's Animals. The cover font is Adobe ITC Garamond. Thetext font is Linotype Birka, the heading font is Adobe Myriad Condensed, and the codefont is LucasFont's TheSansMonoCondensed.
In and of itself this is a pretty good reference, but it appears to have been written for version 0.4 or thereabouts and SQLAlchemy has undergone a LOT of changes since then, enough to render much of this book obsolete and only marginally useful. (Oddly, my product page shows it as having been updated in May 2013.) At any rate, it's in desperate need of revision.
Bottom Line No, I would not recommend this to a friend
Essential SQLAlchemy by Rick Copeland is a great book describing how to use SQLAlchemy to connect Python programs to databases. In fact, at the moment (mid-summer 2008), it is the book, since there are no other books on the subject, yet. Athough I am not (yet) a SQLAlchemy user, this book seems to cover all of the core topics in SQLAlchemy. The text includes many straightforward examples of how to use various facilities in SQLAlchemy and how to map various database programming problems into Python code via SQLAlchemy. Copeland also provides a whirlwind tour of some extensions to SQLAlchemy.
I heard about SQLAlchemy project on the This Week in Django podcast. Django doesn't use SQLAlchmey, but it does use a similar object-relational mapper (ORM). As I mentioned, I haven't used SQLAlchemy so I came into this book with a somewhat blank slate. I have, however, been programming in Python since before 1.0, and I've worked with database APIs and ORMs since the early 90s in C++, Java, and Python. So, I was familiar with the basic landscape of database programming, even if I hadn't used SQLAlchemy. And, I'm currently working on a large Python project that is coded using the Python database API directly, which is very tedious. So, the whole time I was reading this book, I was looking at how to fit SQLAlchemy into this existing code base.
To be honest, the first chapter (the proverbial introduction) almost turned me off. The author starts out slowly enough, but then he starts touching on a huge number details, which were glazing my eyes over. However, the second chapter (getting started) started back at ground zero and stepped through everything in a nice clear fashion, and the rest of the book continued in that vein. He covers all the topics you would expect in a database programming book: queries, updates, joins, the built-in types, and how to hook in to provide support for your own types.
Something I didn't realize about SQLAlchemy coming into this is that SQLAlchemy is both an ORM (what I expected) as well as a high-level, database-independent API. Which is to say, you can just access the database as tables, columns and rows rather than as classes, attributes, and object instances. Although I'd personally prefer to use the ORM, I can imagine cases where it might not be the right tool for the job, and it's good to have a choice.
I was also surprised to see the ORM supports two styles of object-relational access: the data mapper pattern (which I had seen in Django and Hibernate) and the active record (used in Ruby). The author does a good job of explaining both of these and how to use them. He even devotes a whole chapter to Exlir, which is an extension that implements the active record pattern.
One thing that many people might consider odd is the fact that although SQLAlchemy is an ORM, the author waits until chapter eight to discuss how to map object inheritance hierarchies onto relational databases. Most books I've read on ORMs discuss this topic early, but I applaud Copeland's decisions to hold off on discussing it. When books bring this up early (e.g., in chapter three), the discussion often gets bogged down in details, which glaze the reader's eyes. I've dealt with the issue of inheritance mapping enough in ORMs I've used and those I've written enough that I'm not that interested in the topic (assuming the tool provides the typical, reasonable solutions), and was grateful that he held off on it.
One issue I had with the overall structure of the book is that I'm hard pressed to pigeon-hole the book. Books about a single technology such as SQLAlchemy usually occupy one end or the other of a spectrum. Either they're hard-core references, often times copying-and-pasting API documentation from a web site (I really hate that), or they're largish tutorials that may or may not contain enough technical meat. This is a short (roughly 200 page) book that contains plenty of technical meat, but it also includes some simple tutorial motivations for using various capabilities of the tool. Although this mix felt odd to me, I'm sure it will be perfect when I go to apply SQLAlchemy to my existing database projects since I don't really want a hand-holding tutorial, but a pure reference wouldn't quite work for me either when I'm just starting out.
In conclusion, Essential SQLAlchemy provides a thorough presentation of the SQLAlchemy tool for interfacing Python code to SQL databases. The author covers a number of different methods in which SQLAlchemy can be used to access databases from Python, and he provides plenty of details of the various APIs available to the programmer.