Accessing Table and Metadata¶

A declaratively mapped class will always include an attribute called __table__; when the above configuration using __tablename__ is complete, the declarative process makes the Table available via the __table__ attribute:

# access the Table
user_table = User.__table__

The above table is ultimately the same one that corresponds to the Mapper.local_table attribute, which we can see through the runtime inspection system:

from sqlalchemy import inspect

user_table = inspect(User).local_table

The MetaData collection associated with both the declarative registry as well as the base class is frequently necessary in order to run DDL operations such as CREATE, as well as in use with migration tools such as Alembic. This object is available via the .metadata attribute of registry as well as the declarative base class. Below, for a small script we may wish to emit a CREATE for all tables against a SQLite database:

engine = create_engine("sqlite://")

Base.metadata.create_all(engine)

Declarative Table Configuration¶

When using Declarative Table configuration with the __tablename__ declarative class attribute, additional arguments to be supplied to the Table constructor should be provided using the __table_args__ declarative class attribute.

This attribute accommodates both positional as well as keyword arguments that are normally sent to the Table constructor. The attribute can be specified in one of two forms. One is as a dictionary:

class MyClass(Base):
    __tablename__ = 'sometable'
    __table_args__ = {'mysql_engine':'InnoDB'}

The other, a tuple, where each argument is positional (usually constraints):

class MyClass(Base):
    __tablename__ = 'sometable'
    __table_args__ = (
            ForeignKeyConstraint(['id'], ['remote_table.id']),
            UniqueConstraint('foo'),
            )

Keyword arguments can be specified with the above form by specifying the last argument as a dictionary:

class MyClass(Base):
    __tablename__ = 'sometable'
    __table_args__ = (
            ForeignKeyConstraint(['id'], ['remote_table.id']),
            UniqueConstraint('foo'),
            {'autoload':True}
            )

A class may also specify the __table_args__ declarative attribute, as well as the __tablename__ attribute, in a dynamic style using the declared_attr() method decorator. See the section Mixin and Custom Base Classes for examples on how this is often used.

Explicit Schema Name with Declarative Table¶

The schema name for a Table as documented at Specifying the Schema Name is applied to an individual Table using the Table.schema argument. When using Declarative tables, this option is passed like any other to the __table_args__ dictionary:

class MyClass(Base):
    __tablename__ = 'sometable'
    __table_args__ = {'schema': 'some_schema'}

The schema name can also be applied to all Table objects globally by using the MetaData.schema parameter documented at Specifying a Default Schema Name with MetaData. The MetaData object may be constructed separately and passed either to registry() or declarative_base():

from sqlalchemy import MetaData
metadata = MetaData(schema="some_schema")

Base = declarative_base(metadata = metadata)


class MyClass(Base):
    # will use "some_schema" by default
    __tablename__ = 'sometable'

Adding New Columns¶

The declarative table configuration allows the addition of new Column objects under two scenarios. The most basic is that of simply assigning new Column objects to the class:

MyClass.some_new_column = Column('data', Unicode)

The above operation performed against a declarative class that has been mapped using the declarative base (note, not the decorator form of declarative) will add the above Column to the Table using the Table.append_column() method and will also add the column to the Mapper to be fully mapped.

The other scenario where a Column is added on the fly is when an inheriting subclass that has no table of its own indicates additional columns; these columns will be added to the superclass table. The section Single Table Inheritance discusses single table inheritance.

Using DeferredReflection¶

To accommodate this case, a simple extension called the DeferredReflection mixin is available, which alters the declarative mapping process to be delayed until a special class-level DeferredReflection.prepare() method is called, which will perform the reflection process against a target database, and will integrate the results with the declarative table mapping process, that is, classes which use the __tablename__ attribute:

from sqlalchemy.orm import declarative_base
from sqlalchemy.ext.declarative import DeferredReflection

Base = declarative_base()

class Reflected(DeferredReflection):
    __abstract__ = True

class Foo(Reflected, Base):
    __tablename__ = 'foo'
    bars = relationship("Bar")

class Bar(Reflected, Base):
    __tablename__ = 'bar'

    foo_id = Column(Integer, ForeignKey('foo.id'))

Above, we create a mixin class Reflected that will serve as a base for classes in our declarative hierarchy that should become mapped when the Reflected.prepare method is called. The above mapping is not complete until we do so, given an Engine:

engine = create_engine("postgresql://user:pass@hostname/my_existing_database")
Reflected.prepare(engine)

The purpose of the Reflected class is to define the scope at which classes should be reflectively mapped. The plugin will search among the subclass tree of the target against which .prepare() is called and reflect all tables.

Using Automap¶

A more automated solution to mapping against an existing database where table reflection is to be used is to use the Automap extension. This extension will generate entire mapped classes from a database schema, and allows several hooks for customization including the ability to explicitly map some or all classes while still making use of reflection to fill in the remaining columns.