All of our tables and additional schema definitions are associated with an instance of metadata. Persisting the schema to the database is simply a matter of calling the create_all() method on our metadata instance with the engine where it should cre‐ ate those tables:

metadata.create_all(engine)

By default, create_all will not attempt to re-create tables that already exist in the database, and it is safe to run multiple times. It’s wiser to use a database migration tool like Alembic to handle any changes to existing tables or additional schema than to try to handcode changes directly in your application code (we’ll explore this more fully in Chapter 11). Now that we have persisted the tables in the database, let’s take a look at Example 1-4, which shows the complete code for the tables we’ve been work‐ ing on in this chapter.

from datetime import datetime
from sqlalchemy import (MetaData, Table, Column, Integer, Numeric, String, DateTime, ForeignKey, create_engine)


metadata = MetaData()
cookies = Table('cookies', metadata,
Column('cookie_id', Integer(), primary_key=True),
Column('cookie_name', String(50), index=True),
Column('cookie_recipe_url', String(255)),
Column('cookie_sku', String(55)),
Column('quantity', Integer()),
Column('unit_cost', Numeric(12, 2))
)
users = Table('users', metadata,
Column('user_id', Integer(), primary_key=True),
Column('customer_number', Integer(), autoincrement=True),
Column('username', String(15), nullable=False, unique=True),
Column('email_address', String(255), nullable=False),
Column('phone', String(20), nullable=False),
Column('password', String(25), nullable=False),
Column('created_on', DateTime(), default=datetime.now),
Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now)
)
orders = Table('orders', metadata,
Column('order_id', Integer(), primary_key=True),
Column('user_id', ForeignKey('users.user_id'))
)
line_items = Table('line_items', metadata,
Column('line_items_id', Integer(), primary_key=True),
Column('order_id', ForeignKey('orders.order_id')),
Column('cookie_id', ForeignKey('cookies.cookie_id')),
Column('quantity', Integer()),
Column('extended_cost', Numeric(12, 2))
)
engine = create_engine('sqlite:///:memory:')
metadata.create_all(engine)

In this chapter, we took a look at how metadata is used as a catalog by SQLAlchemy to store table schemas along with other miscellaneous data. We also can define a table with multiple columns and constraints. We explored the types of constraints and how to explicitly construct them outside of a column object to match an existing schema or naming scheme. Then we covered how to set default values and onupdate values for auditing. Finally, we now know how to persist or save our schema into the database for reuse. The next step is to learn how to work with data within our schema via the SQL Expression Language.