SQLAlchemy 1.4 Documentation
SQLAlchemy ORM
- ORM Quick Start
- Object Relational Tutorial (1.x API)
- ORM Mapped Class Configuration
- ORM Mapped Class Overview
- Mapping Classes with Declarative
- Integration with dataclasses and attrs
- Mapping Columns and Expressions
- Mapping Class Inheritance Hierarchies
- Non-Traditional Mappings
- Configuring a Version Counter
- Class Mapping API
- Relationship Configuration
- Querying Data, Loading Objects
- Using the Session
- Events and Internals
- ORM Extensions
- ORM Examples
Project Versions
Table Configuration with Declarative¶
As introduced at Declarative Mapping, the Declarative style
includes the ability to generate a mapped Table
object
at the same time, or to accommodate a Table
or other
FromClause
object directly.
The following examples assume a declarative base class as:
from sqlalchemy.orm import declarative_base
Base = declarative_base()
All of the examples that follow illustrate a class inheriting from the above
Base
. The decorator style introduced at Declarative Mapping using a Decorator (no declarative base)
is fully supported with all the following examples as well.
Declarative Table¶
With the declarative base class, the typical form of mapping includes an
attribute __tablename__
that indicates the name of a Table
that should be generated along with the mapping:
from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.orm import declarative_base
Base = declarative_base()
class User(Base):
__tablename__ = "user"
id = Column(Integer, primary_key=True)
name = Column(String)
fullname = Column(String)
nickname = Column(String)
Above, Column
objects are placed inline with the class
definition. The declarative mapping process will generate a new
Table
object against the MetaData
collection
associated with the declarative base, and each specified
Column
object will become part of the Table.columns
collection of this Table
object. The Column
objects can omit their “name” field, which is usually the first positional
argument to the Column
constructor; the declarative system
will assign the key associated with each Column
as the name,
to produce a Table
that is equivalent to:
# equivalent Table object produced
user_table = Table(
"user",
Base.metadata,
Column("id", Integer, primary_key=True),
Column("name", String),
Column("fullname", String),
Column("nickname", String),
)
See also
Mapping Table Columns - contains additional notes on affecting
how Mapper
interprets incoming Column
objects.
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_obj = MetaData(schema="some_schema")
Base = declarative_base(metadata=metadata_obj)
class MyClass(Base):
# will use "some_schema" by default
__tablename__ = "sometable"
See also
Specifying the Schema Name - in the Describing Databases with MetaData documentation.
Appending additional columns to an existing Declarative mapped class¶
A declarative table configuration allows the addition of new
Column
objects to an existing mapping after the Table
metadata has already been generated.
For a declarative class that is declared using a declarative base class,
the underlying metaclass DeclarativeMeta
includes a __setattr__()
method that will intercept additional Column
objects and
add them to both the Table
using Table.append_column()
as well as to the existing Mapper
using Mapper.add_property()
:
MyClass.some_new_column = Column("data", Unicode)
Additional Column
objects may also be added to a mapping
in the specific circumstance of using single table inheritance, where
additional columns are present on mapped subclasses that have
no Table
of their own. This is illustrated in the section
Single Table Inheritance.
Declarative with Imperative Table (a.k.a. Hybrid Declarative)¶
Declarative mappings may also be provided with a pre-existing
Table
object, or otherwise a Table
or other
arbitrary FromClause
construct (such as a Join
or Subquery
) that is constructed separately.
This is referred to as a “hybrid declarative”
mapping, as the class is mapped using the declarative style for everything
involving the mapper configuration, however the mapped Table
object is produced separately and passed to the declarative process
directly:
from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.orm import declarative_base
Base = declarative_base()
# construct a Table directly. The Base.metadata collection is
# usually a good choice for MetaData but any MetaData
# collection may be used.
user_table = Table(
"user",
Base.metadata,
Column("id", Integer, primary_key=True),
Column("name", String),
Column("fullname", String),
Column("nickname", String),
)
# construct the User class using this table.
class User(Base):
__table__ = user_table
Above, a Table
object is constructed using the approach
described at Describing Databases with MetaData. It can then be applied directly
to a class that is declaratively mapped. The __tablename__
and
__table_args__
declarative class attributes are not used in this form.
The above configuration is often more readable as an inline definition:
class User(Base):
__table__ = Table(
"user",
Base.metadata,
Column("id", Integer, primary_key=True),
Column("name", String),
Column("fullname", String),
Column("nickname", String),
)
A natural effect of the above style is that the __table__
attribute is
itself defined within the class definition block. As such it may be
immediately referred towards within subsequent attributes, such as the example
below which illustrates referring to the type
column in a polymorphic
mapper configuration:
class Person(Base):
__table__ = Table(
"person",
Base.metadata,
Column("id", Integer, primary_key=True),
Column("name", String(50)),
Column("type", String(50)),
)
__mapper_args__ = {
"polymorphic_on": __table__.c.type,
"polymorhpic_identity": "person",
}
The “imperative table” form is also used when a non-Table
construct, such as a Join
or Subquery
object,
is to be mapped. An example below:
from sqlalchemy import func, select
subq = (
select(
func.count(orders.c.id).label("order_count"),
func.max(orders.c.price).label("highest_order"),
orders.c.customer_id,
)
.group_by(orders.c.customer_id)
.subquery()
)
customer_select = (
select(customers, subq)
.join_from(customers, subq, customers.c.id == subq.c.customer_id)
.subquery()
)
class Customer(Base):
__table__ = customer_select
For background on mapping to non-Table
constructs see
the sections Mapping a Class against Multiple Tables and Mapping a Class against Arbitrary Subqueries.
The “imperative table” form is of particular use when the class itself is using an alternative form of attribute declaration, such as Python dataclasses. See the section Applying ORM Mappings to an existing dataclass for detail.
Mapping Declaratively with Reflected Tables¶
There are several patterns available which provide for producing mapped
classes against a series of Table
objects that were
introspected from the database, using the reflection process described at
Reflecting Database Objects.
A very simple way to map a class to a table reflected from the database is to
use a declarative hybrid mapping, passing the
Table.autoload_with
parameter to the
Table
:
from sqlalchemy import create_engine
from sqlalchemy import Table
from sqlalchemy.orm import declarative_base
engine = create_engine("postgresql+psycopg2://user:pass@hostname/my_existing_database")
Base = declarative_base()
class MyClass(Base):
__table__ = Table(
"mytable",
Base.metadata,
autoload_with=engine,
)
A variant on the above pattern that scales much better is to use the
MetaData.reflect()
method to reflect a full set of Table
objects at once, then refer to them from the MetaData
:
from sqlalchemy import create_engine
from sqlalchemy import Table
from sqlalchemy.orm import declarative_base
engine = create_engine("postgresql+psycopg2://user:pass@hostname/my_existing_database")
Base = declarative_base()
Base.metadata.reflect(engine)
class MyClass(Base):
__table__ = Base.metadata.tables["mytable"]
See also
Automating Column Naming Schemes from Reflected Tables - further notes on using table reflection with mapped classes
A major downside to the above approach is that the mapped classes cannot be declared until the tables have been reflected, which requires the database connectivity source to be present while the application classes are being declared; it’s typical that classes are declared as the modules of an application are being imported, but database connectivity isn’t available until the application starts running code so that it can consume configuration information and create an engine. There are currently two approaches to working around this.
Using DeferredReflection¶
To accommodate the use case of declaring mapped classes where reflection of
table metadata can occur afterwards, 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.ext.declarative import DeferredReflection
from sqlalchemy.orm import declarative_base
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+psycopg2://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 which are named by declared classes; tables in the target database
that are not part of mappings and are not related to the target tables
via foreign key constraint will not be reflected.
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, including relationships between classes based on observed foreign key constraints. While it includes hooks for customization, such as hooks that allow custom class naming and relationship naming schemes, automap is oriented towards an expedient zero-configuration style of working. If an application wishes to have a fully explicit model that makes use of table reflection, the Using DeferredReflection may be preferable.
See also
flambé! the dragon and The Alchemist image designs created and generously donated by Rotem Yaari.
Created using Sphinx 7.2.6. Documentation last generated: Wed 30 Oct 2024 02:18:58 PM EDT