Rick Copeland - Essential SQLAlchemy

Essential SQLAlchemy

Rick Copeland

Editor

Mary E. Treseler

Copyright 2010 Richard Copeland

OReilly books may be purchased for educational, business, or sales promotional use. Online editions are also available for most titles (.

Nutshell Handbook, the Nutshell Handbook logo, and the OReilly logo are registered trademarks of OReilly Media, Inc. , the image of largescale flying fish, and related trade dress are trademarks of OReilly Media, Inc.

Many of the designations uses by manufacturers and sellers to distinguish their products are claimed as trademarks. Where those designations appear in this book, and OReilly Media, Inc. was aware of a trademark claim, the designations have been printed in caps or initial caps

While every precaution has been taken in the preparation of this book, the publisher and author assume no responsibility for errors or omissions, or for damages resulting from the use of the information contained herein.

---

Preface

If youre an application programmer, youve probably run into a relational database at some point in your professional career. Whether youre 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 its tough to integrate it with the rest of your application. You may have used some language that tries to merge SQL syntax into your applications programming language, such as Oracles Pro*C/C++ precompiler, or you may have used string manipulation to generate queries to run over an ODBC interface. If youre 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 applications 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 whats 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 youre 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 (OReilly) and/or a SQL book such as Learning SQL by Alan Beaulieu (OReilly), 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

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 SQLAlchemys philosophy of letting tables be tables and letting classes be classes.This chapter walks you through installing SQLAlchemy using . 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.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 databases schema, and how to manipulate MetaData objects.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.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.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.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.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.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.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.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.Next page