Essential SQLAlchemy, 2nd Edition (2010)

Preface

If you’re an application programmer, you’ve probably run into a relational database at some point in your professional career. Whether you’re writing enterprise client-server applications or building the next killer Web 2.0 application, you need someplace to put the persistent data for your application. Relational databases, accessed via SQL, are some of the most common places to put that data.

SQL is a powerful language for querying and manipulating data in a database, but sometimes it’s tough to integrate it with the rest of your application. You may have used some language that tries to merge SQL syntax into your application’s programming language, such as Oracle’s Pro*C/C++ precompiler, or you may have used string manipulation to generate queries to run over an ODBC interface. If you’re a Python programmer, you may have used a DB-API module. But there is a better way.

This book is about a very powerful and flexible Python library named SQLAlchemy that bridges the gap between relational databases and traditional object-oriented programming. While SQLAlchemy allows you to “drop down” into raw SQL to execute your queries, it encourages higher-level thinking through a “pythonic” approach to database queries and updates. It supplies the tools that let you map your application’s classes and objects onto database tables once and then to “forget about it,” or to return to your model again and again to fine-tune performance.

SQLAlchemy is powerful and flexible, but it can also be a little daunting. SQLAlchemy tutorials expose only a fraction of what’s available in this excellent library, and though the online documentation is extensive, it is often better as a reference than as a way to learn the library initially. This book is meant as a learning tool and a handy reference for when you’re in “implementation mode” and need an answer fast.

This book covers the 0.4 release series of conservatively versioned SQLAlchemy.

Audience

First of all, this book is intended for those who want to learn more about how to use relational databases with their Python programs, or have heard about SQLAlchemy and want more information on it. Having said that, to get the most out of this book, the reader should have intermediate-to-advanced Python skills and at least moderate exposure to SQL databases. SQLAlchemy provides support for many advanced SQL constructs, so the experienced DBA will also find plenty of information here.

The beginning Python or database programmer would probably be best served by reading a Python book such as Learning Python by Mark Lutz (O’Reilly) and/or a SQL book such as Learning SQL by Alan Beaulieu (O’Reilly), either prior to this book or as a reference to read in parallel with this book.

Assumptions This Book Makes

This book assumes basic knowledge about Python syntax and semantics, particularly versions 2.4 and later. In particular, the reader should be familiar with object-oriented programming in Python, as a large component of SQLAlchemy is devoted entirely to supporting this programming style. The reader should also know basic SQL syntax and relational theory, as this book assumes familiarity with the SQL concepts of defining schemas, tables, SELECTs, INSERTs, UPDATEs, and DELETEs.

Contents of This Book

Chapter 1, Introduction to SQLAlchemy

This chapter takes you on a whirlwind tour through the main components of SQLAlchemy. It demonstrates connecting to the database, building up SQL statements, and mapping simple objects to the database. It also describes SQLAlchemy’s philosophy of letting tables be tables and letting classes be classes.

Chapter 2, Getting Started

This chapter walks you through installing SQLAlchemy using easy_install. It shows you how to create a simple database using SQLite, and walks though some simple queries against a sample database to to illustrate the use of the Engine and the SQL expression language.

Chapter 3, Engines and MetaData

This chapter describes the various engines (methods of connecting to database servers) available for use with SQLAlchemy, including the connection parameters they support. It then describes the MetaData object, which is where SQLAlchemy stores information about your database’s schema, and how to manipulate MetaData objects.

Chapter 4, SQLAlchemy Type Engines

This chapter describes the way that SQLAlchemy uses its built-in types. It also shows you how to create custom types to be used in your schema. You will learn the requirements for creating custom types as well as the cases where it is useful to use custom rather than built-in types.

Chapter 5, Running Queries and Updates

This chapter tells you how to perform INSERTs, UPDATEs, and DELETEs. It covers result set objects, retrieving partial results, and using SQL functions to aggregate and sort data in the database server.

Chapter 6, Building an Object Mapper

This chapter describes the object-relational mapper (ORM) used in SQLAlchemy. It describes the differences between the object mapper pattern (used in SQLAlchemy) and the active record pattern used in other ORMs. It then describes how to set up a mapper, and how the mapper maps your tables by default. You will also learn how to override the default mapping and how to specify various relationships between tables.

Chapter 7, Querying and Updating at the ORM Level

This chapter shows you how to create objects, save them to a session, and flush them to the database. You will learn about how Session and Query objects are defined, their methods, and how to use them to insert, update, retrieve, and delete data from the database at the ORM level. You will learn how to use result set mapping to populate objects from a non-ORM query and when it should be used.

Chapter 8, Inheritance Mapping

This chapter describes how to use SQLAlchemy to model object-oriented inheritance. The various ways of modeling inheritance in the relational model are described, as well as the support SQLAlchemy provides for each.

Chapter 9, Elixir: A Declarative Extension to SQLAlchemy

This chapter describes the Elixir extension to SQLAlchemy, which provides a declarative, active record pattern for use with SQLAlchemy. You will learn how to use Elixir extensions such as acts_as_versioned to create auxiliary tables automatically, and when Elixir is appropriate instead of “bare” SQLAlchemy.

Chapter 10, SqlSoup: An Automatic Mapper for SQLAlchemy

This chapter introduces the SQLSoup extension, which provides an automatic metadata and object model based on database reflection. You will learn how to use SQLSoup to query the database with a minimum of setup, and learn the pros and cons of such an approach.

Chapter 11, Other SQLAlchemy Extensions

This chapter covers other, less comprehensive extensions to SQLAlchemy. It describes the extensions that are currently used in the 0.4 release series of SQLAlchemy, as well as briefly describing deprecated extensions and the functionality in SQLAlchemy that supplants them.

Conventions Used in This Book

The following typographical conventions are used in this book:

Italic

Indicates new terms, URLs, email addresses, filenames, file extensions, pathnames, directories, and Unix utilities.

Constant width

Indicates commands, options, switches, variables, attributes, keys, functions, types, classes, namespaces, methods, modules, properties, parameters, values, objects, events, event handlers, the contents of files, or the output from commands.

Constant width italic

Shows text that should be replaced with user-supplied values.

ALL CAPS

Shows SQL keywords and queries.

Note

This icon signifies a tip, suggestion, or general note.

Warning

This icon indicates a warning or caution.