Essential SQLAlchemy

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.

Using Code Examples

This book is here to help you get your job done. In general, you may use the code in this book in your programs and documentation. You do not need to contact us for permission unless you’re reproducing a significant portion of the code. For example, writing a program that uses several chunks of code from this book does not require permission. Selling or distributing a CD-ROM of examples from O’Reilly books does require permission. Answering a question by citing this book and quoting example code does not require permission. Incorporating a significant amount of example code from this book into your product’s documentation does require permission.

We appreciate, but do not require, attribution. An attribution usually includes the title, author, publisher, and ISBN. For example: “Essential SQLAlchemy by Rick Copeland. Copyright 2008 Richard D. Copeland, Jr., 978-0-596-51614-7.”

If you feel your use of code examples falls outside fair use or the permission given here, feel free to contact us at [e-mail].

How to Contact Us

Please address comments and questions concerning this book to the publisher:

O’Reilly Media, Inc.

1005 Gravenstein Highway North

Sebastopol, CA 95472

800-998-9938 (in the United States or Canada)

707-829-0515 (international or local)

707-829-0104 (fax)

We have a web page for this book, where we list errata, examples, and any additional information. You can access this page at:

http://www.oreilly.com/catalog/9780596516147

To comment or ask technical questions about this book, send email to:

http://[e-mail]

For more information about our books, conferences, Resource Centers, and the O’Reilly Network, see our web site at:

http://www.oreilly.com

Acknowledgments

Many thanks go to Tatiana Apandi, Barry Hart, Grig Gheorghiu, and Catherine Devlin for their critical pre-publication feedback, without whom this book would have undoubtedly had many technical snafus.

My appreciation goes out to Noah Gift, whose recommendation led to this book being written in the first place. I still remember how his phone call started: “You know SQLAlchemy, right?...”

Thanks to my employer, Predictix, for allowing me the time and energy to finish the book, and to my coworkers for being unwitting guinea pigs for many of the ideas and techniques in this book.

Finally, my heartfelt gratitude goes to my beloved wife Nancy, whose support in the presence of a husband glued to the computer was truly the fuel that allowed this book to be written at all.