SQLAlchemy 1.3 Documentation
SQLAlchemy ORM
- Object Relational Tutorial
- Mapper Configuration
- Relationship Configuration
- Loading Objects
- Using the Session
- Events and Internals
- ORM Extensions
- ORM Examples
Project Versions
Table Configuration¶
See also
This section describes specifics about how the Declarative system
defines Table
objects that are to be mapped with the
SQLAlchemy ORM. For general information on Table
objects
see Describing Databases with MetaData.
Table arguments other than the name, metadata, and mapped Column
arguments are specified using the __table_args__
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}
)
Using a Hybrid Approach with __table__¶
As an alternative to __tablename__
, a direct
Table
construct may be used. The
Column
objects, which in this case require
their names, will be added to the mapping just like a regular mapping
to a table:
class MyClass(Base):
__table__ = Table('my_table', Base.metadata,
Column('id', Integer, primary_key=True),
Column('name', String(50))
)
__table__
provides a more focused point of control for establishing
table metadata, while still getting most of the benefits of using declarative.
An application that uses reflection might want to load table metadata elsewhere
and pass it to declarative classes:
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
Base.metadata.reflect(some_engine)
class User(Base):
__table__ = metadata.tables['user']
class Address(Base):
__table__ = metadata.tables['address']
Some configuration schemes may find it more appropriate to use __table__
,
such as those which already take advantage of the data-driven nature of
Table
to customize and/or automate schema definition.
Note that when the __table__
approach is used, the object is immediately
usable as a plain Table
within the class declaration body itself,
as a Python class is only another syntactical block. Below this is illustrated
by using the id
column in the primaryjoin
condition of a
relationship()
:
class MyClass(Base):
__table__ = Table('my_table', Base.metadata,
Column('id', Integer, primary_key=True),
Column('name', String(50))
)
widgets = relationship(Widget,
primaryjoin=Widget.myclass_id==__table__.c.id)
Similarly, mapped attributes which refer to __table__
can be placed inline,
as below where we assign the name
column to the attribute _name
,
generating a synonym for name
:
from sqlalchemy.ext.declarative import synonym_for
class MyClass(Base):
__table__ = Table('my_table', Base.metadata,
Column('id', Integer, primary_key=True),
Column('name', String(50))
)
_name = __table__.c.name
@synonym_for("_name")
def name(self):
return "Name: %s" % _name
Using Reflection with Declarative¶
It’s easy to set up a Table
that uses autoload=True
in conjunction with a mapped class:
class MyClass(Base):
__table__ = Table('mytable', Base.metadata,
autoload=True, autoload_with=some_engine)
However, one improvement that can be made here is to not
require the Engine
to be available when classes are
being first declared. To achieve this, use the
DeferredReflection
mixin, which sets up mappings
only after a special prepare(engine)
step is called:
from sqlalchemy.ext.declarative import declarative_base, DeferredReflection
Base = declarative_base(cls=DeferredReflection)
class Foo(Base):
__tablename__ = 'foo'
bars = relationship("Bar")
class Bar(Base):
__tablename__ = 'bar'
# illustrate overriding of "bar.foo_id" to have
# a foreign key constraint otherwise not
# reflected, such as when using MySQL
foo_id = Column(Integer, ForeignKey('foo.id'))
Base.prepare(e)
flambé! the dragon and The Alchemist image designs created and generously donated by Rotem Yaari.
Created using Sphinx 7.2.6. Documentation last generated: Sat 06 Jan 2024 12:16:15 PM