Next / Previous / Contents / TCC Help System / NM Tech homepage


Summary of common functions in the SQLAlchemy object-relational database system.

This publication is available in Web form and also as a PDF document. Please forward any comments to

Table of Contents

1. Introduction
2. Overall structure of SQLAlchemy
3. Connecting to database engines
3.1. DSN: The Data Source Name
4. Metadata: Describing the database schema
4.1. Reflection: finding out the structure of an existing SQL database
4.2. Building your own schema
4.3. Interface to class MetaData
4.4. class Table: Metadata for one table
4.5. class ForeignKeyConstraint: Specifying a relation to another table
4.6. class PrimaryKeyConstraint: Describing a table's primary key
4.7. class UniqueConstraint: Describing a uniqueness constraint on a table
4.8. class Index: Specifying an index on a table
4.9. class Column: Metadata for one table column
4.10. Column types
4.11. class ForeignKey: Specifying a column's foreign key relation
4.12. class Sequence: Specifying an auto-incrementing column
5. The object-relational mapper
5.1. Defining a class to be mapped
5.2. Using orm.mapper()
5.3. Properties of a mapping
6. Session operations
6.1. Operations on a session
7. Queries
7.1. Slicing a Query
7.2. Query.all()
7.3. Query.count()
7.4. Query.delete()
7.5. Query.filter()
7.6. Query.filter_by()
7.7. Query.first()
7.8. Query.get()
7.9. Query.join()
7.11. Query.order_by()
7.12. Result tuples from a Query
8. Exceptions
9. reflector: Script to show an existing database's structure
9.1. Prologue
9.2. main(): reflector's main program
9.3. checkArguments(): Process the command line arguments
9.4. usage(): Usage message
9.5. fatal(): Fatal error message
9.6. showTable(): Report on one table
9.7. showColumn(): Report on one column
9.8. Epilogue

1. Introduction

This document summarizes some of the more commonly used features of the SQLAlchemy system. The purpose of the SQLAlchemy system is to provide a connection between your Python program and any of several classic database systems built on top of SQL (Structured Query Language) such as MySQL, Postgres, or Oracle.


You should have general knowledge of relational databases, and the additional problems of object-relational mappers, or the current document will not make much sense to you.

Assuming you understand how a classical SQL database works, SQLAlchemy can make life much easier for these applications and many more:

  • You can use SQLAlchemy to connect to applications in other languages, using classical SQL database engines to interchange data.

  • You can use SQLAlchemy to provide a persistence layer, so that some of the objects within your Python application can be stored in external databases for later retrieval and translation back into live objects.

This document is not a complete reference. It is intended for daily reference to functions necessary to put up fairly simple applications. SQLAlchemy is a full-featured and highly tuneable system, and we will not cover here many of its useful features for improved performance and simplified programming. Please refer to the SQLAlchemy home page for tutorials and detailed reference information.