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 Table Columns¶
- SQL Expressions as Mapped Attributes
- Changing Attribute Behavior
- Composite Column Types
- 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
- Previous: Mapping Columns and Expressions
- Next: SQL Expressions as Mapped Attributes
- Up: Home
- On this page:
Mapping Table Columns¶
Introductory background on mapping to columns falls under the subject of
Table
configuration; the general form falls under one of three
forms:
Declarative Table -
Column
objects are associated with aTable
as well as with an ORM mapping in one step by declaring them inline as class attributes.Declarative with Imperative Table (a.k.a. Hybrid Declarative) -
Column
objects are associated directly with theirTable
object, as detailed at Describing Databases with MetaData; the columns are then mapped by the Declarative process by associating theTable
with the class to be mapped via the__table__
attribute.Imperative Mapping - like “Imperative Table”,
Column
objects are associated directly with theirTable
object; the columns are then mapped by the Imperative process usingregistry.map_imperatively()
.
In all of the above cases, the mapper
constructor is ultimately
invoked with a completed Table
object passed as the selectable unit
to be mapped. The behavior of mapper
then is to assemble all the
columns in the mapped Table
into mapped object attributes,
each of which are named according to the name of the column itself
(specifically, the key
attribute of Column
). This behavior
can be modified in several ways.
Naming Columns Distinctly from Attribute Names¶
A mapping by default shares the same name for a
Column
as that of the mapped attribute - specifically
it matches the Column.key
attribute on Column
, which
by default is the same as the Column.name
.
The name assigned to the Python attribute which maps to
Column
can be different from either
Column.name
or Column.key
just by assigning
it that way, as we illustrate here in a Declarative mapping:
class User(Base):
__tablename__ = "user"
id = Column("user_id", Integer, primary_key=True)
name = Column("user_name", String(50))
Where above User.id
resolves to a column named user_id
and User.name
resolves to a column named user_name
.
When mapping to an existing table, the Column
object
can be referenced directly:
class User(Base):
__table__ = user_table
id = user_table.c.user_id
name = user_table.c.user_name
The corresponding technique for an imperative mapping is
to place the desired key in the mapper.properties
dictionary with the desired key:
mapper_registry.map_imperatively(
User,
user_table,
properties={
"id": user_table.c.user_id,
"name": user_table.c.user_name,
},
)
Automating Column Naming Schemes from Reflected Tables¶
In the previous section Naming Columns Distinctly from Attribute Names, we showed how
a Column
explicitly mapped to a class can have a different attribute
name than the column. But what if we aren’t listing out Column
objects explicitly, and instead are automating the production of Table
objects using reflection (i.e. as described in Reflecting Database Objects)?
In this case we can make use of the DDLEvents.column_reflect()
event
to intercept the production of Column
objects and provide them
with the Column.key
of our choice. The event is most easily
associated with the MetaData
object that’s in use,
such as below we use the one linked to the declarative_base
instance:
@event.listens_for(Base.metadata, "column_reflect")
def column_reflect(inspector, table, column_info):
# set column.key = "attr_<lower_case_name>"
column_info["key"] = "attr_%s" % column_info["name"].lower()
With the above event, the reflection of Column
objects will be intercepted
with our event that adds a new “.key” element, such as in a mapping as below:
class MyClass(Base):
__table__ = Table("some_table", Base.metadata, autoload_with=some_engine)
The approach also works with both the DeferredReflection
base class
as well as with the Automap extension. For automap
specifically, see the section Intercepting Column Definitions for
background.
See also
Mapping Declaratively with Reflected Tables
Intercepting Column Definitions - in the Automap documentation
Using column_property for column level options¶
Options can be specified when mapping a Column
using the
column_property()
function. This function
explicitly creates the ColumnProperty
used by the
mapper()
to keep track of the Column
; normally, the
mapper()
creates this automatically. Using column_property()
,
we can pass additional arguments about how we’d like the Column
to be mapped. Below, we pass an option active_history
,
which specifies that a change to this column’s value should
result in the former value being loaded first:
from sqlalchemy.orm import column_property
class User(Base):
__tablename__ = "user"
id = Column(Integer, primary_key=True)
name = column_property(Column(String(50)), active_history=True)
column_property()
is also used to map a single attribute to
multiple columns. This use case arises when mapping to a join()
which has attributes which are equated to each other:
class User(Base):
__table__ = user.join(address)
# assign "user.id", "address.user_id" to the
# "id" attribute
id = column_property(user_table.c.id, address_table.c.user_id)
For more examples featuring this usage, see Mapping a Class against Multiple Tables.
Another place where column_property()
is needed is to specify SQL expressions as
mapped attributes, such as below where we create an attribute fullname
that is the string concatenation of the firstname
and lastname
columns:
class User(Base):
__tablename__ = "user"
id = Column(Integer, primary_key=True)
firstname = Column(String(50))
lastname = Column(String(50))
fullname = column_property(firstname + " " + lastname)
See examples of this usage at SQL Expressions as Mapped Attributes.
Object Name | Description |
---|---|
column_property(*columns, **kwargs) |
Provide a column-level property for use with a mapping. |
- function sqlalchemy.orm.column_property(*columns, **kwargs)¶
Provide a column-level property for use with a mapping.
Column-based properties can normally be applied to the mapper’s
properties
dictionary using theColumn
element directly. Use this function when the given column is not directly present within the mapper’s selectable; examples include SQL expressions, functions, and scalar SELECT queries.The
column_property()
function returns an instance ofColumnProperty
.Columns that aren’t present in the mapper’s selectable won’t be persisted by the mapper and are effectively “read-only” attributes.
- Parameters:
*cols¶ – list of Column objects to be mapped.
active_history=False¶ – When
True
, indicates that the “previous” value for a scalar attribute should be loaded when replaced, if not already loaded. Normally, history tracking logic for simple non-primary-key scalar values only needs to be aware of the “new” value in order to perform a flush. This flag is available for applications that make use ofget_history()
orSession.is_modified()
which also need to know the “previous” value of the attribute.comparator_factory¶ – a class which extends
Comparator
which provides custom SQL clause generation for comparison operations.group¶ – a group name for this property when marked as deferred.
deferred¶ – when True, the column property is “deferred”, meaning that it does not load immediately, and is instead loaded when the attribute is first accessed on an instance. See also
deferred()
.doc¶ – optional string that will be applied as the doc on the class-bound descriptor.
expire_on_flush=True¶ – Disable expiry on flush. A column_property() which refers to a SQL expression (and not a single table-bound column) is considered to be a “read only” property; populating it has no effect on the state of data, and it can only return database state. For this reason a column_property()’s value is expired whenever the parent object is involved in a flush, that is, has any kind of “dirty” state within a flush. Setting this parameter to
False
will have the effect of leaving any existing value present after the flush proceeds. Note however that theSession
with default expiration settings still expires all attributes after aSession.commit()
call, however.info¶ – Optional data dictionary which will be populated into the
MapperProperty.info
attribute of this object.raiseload¶ –
if True, indicates the column should raise an error when undeferred, rather than loading the value. This can be altered at query time by using the
deferred()
option with raiseload=False.New in version 1.4.
See also
See also
Using column_property for column level options - to map columns while including mapping options
Using column_property - to map SQL expressions
Mapping to an Explicit Set of Primary Key Columns¶
The Mapper
construct in order to successfully map a table always
requires that at least one column be identified as the “primary key” for
that selectable. This is so that when an ORM object is loaded or persisted,
it can be placed in the identity map with an appropriate
identity key.
To support this use case, all FromClause
objects (where
FromClause
is the common base for objects such as Table
,
Join
, Subquery
, etc.) have an attribute
FromClause.primary_key
which returns a collection of those
Column
objects that indicate they are part of a “primary key”,
which is derived from each Column
object being a member of a
PrimaryKeyConstraint
collection that’s associated with the
Table
from which they ultimately derive.
In those cases where the selectable being mapped does not include columns
that are explicitly part of the primary key constraint on their parent table,
a user-defined set of primary key columns must be defined. The
mapper.primary_key
parameter is used for this purpose.
Given the following example of a Imperative Table
mapping against an existing Table
object, as would occur in a scenario
such as when the Table
were reflected from an existing
database, where the table does not have any declared primary key, we may
map such a table as in the following example:
from sqlalchemy import Column
from sqlalchemy import MetaData
from sqlalchemy import String
from sqlalchemy import Table
from sqlalchemy import UniqueConstraint
from sqlalchemy.orm import declarative_base
metadata = MetaData()
group_users = Table(
"group_users",
metadata,
Column("user_id", String(40), nullable=False),
Column("group_id", String(40), nullable=False),
UniqueConstraint("user_id", "group_id"),
)
Base = declarative_base()
class GroupUsers(Base):
__table__ = group_users
__mapper_args__ = {"primary_key": [group_users.c.user_id, group_users.c.group_id]}
Above, the group_users
table is an association table of some kind
with string columns user_id
and group_id
, but no primary key is set up;
instead, there is only a UniqueConstraint
establishing that the
two columns represent a unique key. The Mapper
does not automatically
inspect unique constraints for primary keys; instead, we make use of the
mapper.primary_key
parameter, passing a collection of
[group_users.c.user_id, group_users.c.group_id]
, indicating that these two
columns should be used in order to construct the identity key for instances
of the GroupUsers
class.
Mapping a Subset of Table Columns¶
Sometimes, a Table
object was made available using the
reflection process described at Reflecting Database Objects to load
the table’s structure from the database.
For such a table that has lots of columns that don’t need to be referenced
in the application, the include_properties
or exclude_properties
arguments can specify that only a subset of columns should be mapped.
For example:
class User(Base):
__table__ = user_table
__mapper_args__ = {"include_properties": ["user_id", "user_name"]}
…will map the User
class to the user_table
table, only including
the user_id
and user_name
columns - the rest are not referenced.
Similarly:
class Address(Base):
__table__ = address_table
__mapper_args__ = {"exclude_properties": ["street", "city", "state", "zip"]}
…will map the Address
class to the address_table
table, including
all columns present except street
, city
, state
, and zip
.
When this mapping is used, the columns that are not included will not be
referenced in any SELECT statements emitted by Query
, nor will there
be any mapped attribute on the mapped class which represents the column;
assigning an attribute of that name will have no effect beyond that of
a normal Python attribute assignment.
In some cases, multiple columns may have the same name, such as when
mapping to a join of two or more tables that share some column name.
include_properties
and exclude_properties
can also accommodate
Column
objects to more accurately describe which columns
should be included or excluded:
class UserAddress(Base):
__table__ = user_table.join(addresses_table)
__mapper_args__ = {
"exclude_properties": [address_table.c.id],
"primary_key": [user_table.c.id],
}
Note
insert and update defaults configured on individual Column
objects, i.e. those described at Column INSERT/UPDATE Defaults including those
configured by the Column.default
,
Column.onupdate
, Column.server_default
and
Column.server_onupdate
parameters, will continue to function
normally even if those Column
objects are not mapped. This is
because in the case of Column.default
and
Column.onupdate
, the Column
object is still present
on the underlying Table
, thus allowing the default functions to
take place when the ORM emits an INSERT or UPDATE, and in the case of
Column.server_default
and Column.server_onupdate
,
the relational database itself emits these defaults as a server side
behavior.
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