Next / Previous / Contents / Shipman's homepage

17.1. Tables and mapped classes

As class variables, we now define the table and the class that is mapped to it in SQLAlchemy. First, we will need an instance of schema.MetaData to hold the definitions associated with the database.

    # [ meta  :=  a new, empty MetaData instance ]
    meta = schema.MetaData()

Next we define the single table and its columns, also as class variables. The path column is the unique primary key.

    # [ schema  +:=  a new table as described
    #   path_table  :=  that new table ]
    path_table = schema.Table('path_hash', meta,
        schema.Column('path', types.VARCHAR, primary_key=True),
        schema.Column('hash', types.VARCHAR),
        schema.Column('size', types.Integer))

Because we need to be able to find all the paths that have the same hash, we define an external index on the concatenation of the hash and path columns, so that when we retrieve the set of duplicates, the path names will be in ascending order.

    # [ schema  +:=  a new index on path_table(hash, path) ]
    schema.Index('hash_x', path_table.c.hash, path_table.c.path)