Essential SQLAlchemy, 2nd Edition

Preface

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 it’s 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 application’s 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. 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 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.

Who This Book Is For

This book is intended for those who want to learn more about how to use relational databases in their Python programs, or have heard about SQLAlchemy and want more information on it. To get the most out of this book, the reader should have intermediate Python skills and at least moderate exposure to SQL databases. While we have worked hard to make the material accessible, if you are just getting started with Python, we recommend reading Introducing Python by Bill Lubanovic or watching the “Introduction to Python” videos by Jessica McKellar as they are both fantastic resources. If you are new to SQL and databases, check out Learning SQL by Alan Beaulieu. These will fill in any missing gaps as you work through this book.

How to Use the Examples

Most of the examples in this book are built to be run in a read-eval-print loop (REPL). You can use the built-in Python REPL by typing python at the command prompt. The examples also work well in an ipython notebook. There are a few parts of the book, such as Chapter 4, that will direct you to create and use files instead of a REPL. The supplied example code is provided in IPython notebooks for most examples, and Python files for the chapters that specify to use them. You can learn more about IPython at its website.

Assumptions This Book Makes

This book assumes basic knowledge about Python syntax and semantics, particularly versions 2.7 and later. In particular, the reader should be familiar with iteration and working with objects in Python, as these are used frequently throughout the book. The second part of the book deals extensively with object-oriented programming and the SQLAlchemy ORM. The reader should also know basic SQL syntax and relational theory, as this book assumes familiarity with the SQL concepts of defining schema and tables along with creating SELECT, INSERT, UPDATE, and DELETE statements.

Conventions Used in This Book

The following typographical conventions are used in this book:

Italic

Indicates new terms, URLs, email addresses, filenames, and file extensions.

Constant width

Used for program listings, as well as within paragraphs to refer to program elements such as variable or function names, databases, data types, environment variables, statements, and keywords.

Constant width bold

Shows commands or other text that should be typed literally by the user.

Constant width italic

Shows text that should be replaced with user-supplied values or by values determined by context.

Tip

This element signifies a tip or suggestion.

Note

This element signifies a general note.

Warning

This element indicates a warning or caution.

Using Code Examples

Supplemental material (code examples, exercises, etc.) is available for download at link: https://github.com/oreillymedia/essential-sqlalchemy-2e.

This book is here to help you get your job done. In general, if example code is offered with this book, you may use it 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, Second Edition, by Jason Myers and Rick Copeland (O’Reilly). Copyright 2016 Jason Myers and Rick Copeland, 978-1-4919-1646-9.”

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

Safari® Books Online

Note

Safari Books Online is an on-demand digital library that delivers expert content in both book and video form from the world’s leading authors in technology and business.

Technology professionals, software developers, web designers, and business and creative professionals use Safari Books Online as their primary resource for research, problem solving, learning, and certification training.

Safari Books Online offers a range of plans and pricing for enterprise, government, education, and individuals.

Members have access to thousands of books, training videos, and prepublication manuscripts in one fully searchable database from publishers like O’Reilly Media, Prentice Hall Professional, Addison-Wesley Professional, Microsoft Press, Sams, Que, Peachpit Press, Focal Press, Cisco Press, John Wiley & Sons, Syngress, Morgan Kaufmann, IBM Redbooks, Packt, Adobe Press, FT Press, Apress, Manning, New Riders, McGraw-Hill, Jones & Bartlett, Course Technology, and hundreds more. For more information about Safari Books Online, please visit us online.

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 the following link: http://oreil.ly/1lwEdiw.

To comment or ask technical questions about this book, send email to [e-mail].

For more information about our books, courses, conferences, and news, see our website at http://www.oreilly.com.

Find us on Facebook: http://facebook.com/oreilly

Follow us on Twitter: http://twitter.com/oreillymedia

Watch us on YouTube: http://www.youtube.com/oreillymedia

Acknowledgments

Many thanks go to Patrick Altman, Eric Floehr, and Alex Grönholm for their critical prepublication feedback. Without them, this book would have undoubtedly had many technical issues and been much harder to read.

My appreciation goes out to Mike Bayer, whose recommendation led to this book being written in the first place. I’m grateful to Meghan Blanchette and Dawn Schanafelt for pushing me to complete the book, making me a better writer, and putting up with me. I also would like to thank Brian Dailey for reading some of the roughest cuts of the book, providing great feedback, and laughing with me about it.

I want to thank the Nashville development community for supporting me, especially Cal Evans, Jacques Woodcock, Luke Stokes, and William Golden.

Thanks to my employer, Cisco Systems, for allowing me the time and providing support to finish the book. Thanks also to Justin at Mechanical Keyboards for keeping me supplied with everything I needed to keep my fingers typing.

Most importantly I want to thank my wife for putting up with me reading aloud to myself, disappearing to go write, and being my constant source of support and hope. I love you, Denise.