Jason Myers - Essential SQLAlchemy: Mapping Python to databases

by Jason Myers and Rick Copeland

Copyright 2016 Jason Myers and Rick Copeland. All rights reserved.

Printed in the United States of America.

Published by OReilly Media, Inc. , 1005 Gravenstein Highway North, Sebastopol, CA 95472.

OReilly books may be purchased for educational, business, or sales promotional use. Online editions are also available for most titles (http://safaribooksonline.com). For more information, contact our corporate/institutional sales department: 800-998-9938 or corporate@oreilly.com .

• Editors: Dawn Schanafelt and Meghan Blanchette
• Production Editor: Shiny Kalapurakkel
• Copyeditor: Charles Roumeliotis
• Proofreader: Jasmine Kwityn
• Indexer: Angela Howard
• Interior Designer: David Futato
• Cover Designer: Karen Montgomery
• Illustrator: Rebecca Demarest

• June 2008: First Edition
• December 2015: Second Edition

See http://oreilly.com/catalog/errata.csp?isbn=9781491916469 for release details.

The OReilly logo is a registered trademark of OReilly Media, Inc. Essential SQLAlchemy, the cover image, and related trade dress are trademarks of OReilly Media, Inc.

While the publisher and the authors have used good faith efforts to ensure that the information and instructions contained in this work are accurate, the publisher and the authors disclaim all responsibility for errors or omissions, including without limitation responsibility for damages resulting from the use of or reliance on this work. Use of the information and instructions contained in this work is at your own risk. If any code samples or other technology this work contains or describes is subject to open source licenses or the intellectual property rights of others, it is your responsibility to ensure that your use thereof complies with such licenses and/or rights.

978-1-491-91646-9

[LSI]

We are surrounded by data everywhere, and your ability to store, update, and report on that data is critical to every application you build. Whether you are developing for the Web, the desktop, or other applications, you need fast and secure access to data. Relational databases are still one 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 string manipulation to generate queries to run over an ODBC interface, or used a DB API as a Python programmer. While those can be effective ways to handle data, they can make security and database changes very difficult.

This book is about a very powerful and flexible Python library named SQLAlchemy that bridges the gap between relational databases and traditional programming. While SQLAlchemy allows you to drop down into raw SQL to execute your queries, it encourages higher-level thinking through a more Pythonic and friendly approach to database queries and updates. It supplies the tools that let you map your applications classes and objects to 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. SQL Alchemy 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 focuses on the 1.0 release of SQLAlchemy; however, much of what we will cover has been available for many of the previous versions. It certainly works from 0.8 forward with minor tweaking, and most of it from 0.5.

This book has been written in three major parts: SQLAlchemy Core, SQLAlchemy ORM, and an Alembic section. The first two parts are meant to mirror each other as closely as possible. We have taken care to perform the same examples in each part so that you can compare and contrast the two main ways of using SQLAlchemy. The book is also written so that you can read both the SQLAlchemy Core and ORM parts or just the one suits your needs at the moment.