Essential SQLAlchemy, 2nd Edition

Chapter 1. Schema and Types

The first thing we must do is define what data our tables hold, how that data is interrelated, and any constraints on that data.

In order to provide access to the underlying database, SQLAlchemy needs a representation of the tables that should be present in the database. We can do this in one of three ways:

• Using user-defined Table objects

• Using declarative classes that represent your tables

• Inferring them from the database

This chapter focuses on the first of these, as that is the approach used with SQLAlchemy Core; we’ll cover the other two options in later chapters after we have a grasp of the fundamentals. The Table objects contain a list of typed columns and their attributes, which are associated with a common metadata container. We’ll begin our exploration of schema definitions by taking a look at the types that are available to build tables in SQLAlchemy.

Types

There are four categories of types we can use inside of SQLAlchemy:

• Generic

• SQL standard

• Vendor specific

• User defined

SQLAlchemy defines a large number of generic types that are abstracted away from the actual SQL types supported by each backend database. These types are all available in the sqlalchemy.types module, and for convenience they are also available in the sqlalchemy module. So let’s think about how these generic types are useful.

The Boolean generic type typically uses the BOOLEAN SQL type, and on the Python side deals in true or false; however, it also uses ...