Columns Columns define the fields that exists in our tables, and they provide the primary means by which we define other constraints through their keyword arguments. Dif‐ ferent types of columns have different primary arguments. For example, String type columns have length as their primary argument, while numbers with a fractional component will have precision and length. Most other types have no primary argu‐ ments. Sometimes you will see examples that just show String columns without a length, which is the primary argument. This behavior is not universally supported—for example, MySQL and several other database backends do not allow for it. Columns can also have some additional keyword arguments that help shape their behavior even further. We can mark columns as required and/or force them to be unique. We can also set default initial values and change values when the record is updated. A common use case for this is fields that indicate when a record was created or updated for logging or auditing purposes. Let’s take a look at these keyword argu‐ ments in action in Example 1-2

from datetime import datetime
from sqlalchemy import Table, Column, Integer, Numeric, String, ForeignKey
from sqlalchemy import DateTime

users = Table('users', metadata,
Column('user_id', Integer(), primary_key=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)
)

Here we are making this column required (nullable=False) and also requiring a unique value. The default sets this column to the current time if a date isn’t specified. Using onupdate here will reset this column to the current time every time any part of the record is updated.

You’ll notice that we set default and onupdate to the callable date time.now instead of the function call itself, datetime.now(). If we had used the function call itself, it would have set the default to the time when the table was first instantiated. By using the callable, we get the time that each individual record is instantiated and updated.