Mapping Table Columns
The default behavior of mapper()
is to assemble all the columns inthe mapped Table
into mapped object attributes, each of which arenamed according to the name of the column itself (specifically, the key
attribute of Column
). This behavior can bemodified in several ways.
Naming Columns Distinctly from Attribute Names
A mapping by default shares the same name for aColumn
as that of the mapped attribute - specificallyit matches the Column.key
attribute on Column
, whichby default is the same as the Column.name
.
The name assigned to the Python attribute which maps toColumn
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
objectcan be referenced directly:
- class User(Base):
- __table__ = user_table
- id = user_table.c.user_id
- name = user_table.c.user_name
Or in a classical mapping, placed in the properties
dictionarywith the desired key:
- mapper(User, user_table, properties={
- 'id': user_table.c.user_id,
- 'name': user_table.c.user_name,
- })
In the next section we’ll examine the usage of .key
more closely.
Automating Column Naming Schemes from Reflected Tables
In the previous section Naming Columns Distinctly from Attribute Names, we showed howa Column
explicitly mapped to a class can have a different attributename 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 (e.g. as described in Reflecting Database Objects)?In this case we can make use of the DDLEvents.column_reflect()
eventto intercept the production of Column
objects and provide themwith the Column.key
of our choice:
- @event.listens_for(Table, "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()</pre>
With the above event, the reflection of
Column
objects will be interceptedwith 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=True, autoload_with=some_engine)If we want to qualify our event to only react for the specific
MetaData
object above, we can check for it in our event: @event.listens_for(Table, "column_reflect")def column_reflect(inspector, table, column_info): if table.metadata is Base.metadata:# set column.key = "attr_<lower_case_name>"
column_info['key'] = "attr_%s" % column_info['name'].lower()</pre>
Naming All Columns with a Prefix
A quick approach to prefix column names, typically when mappingto an existing
Table
object, is to usecolumn_prefix
: class User(Base): __table__ = user_table __mapper_args__ = {'column_prefix':'_'}The above will place attribute names such as
_user_id
,_user_name
,_password
etc. on the mappedUser
class.This approach is uncommon in modern usage. For dealing with reflectedtables, a more flexible approach is to use that described inAutomating Column Naming Schemes from Reflected Tables.
Using column_property for column level options
Options can be specified when mapping a
Column
using thecolumn_property()
function. This functionexplicitly creates theColumnProperty
used by themapper()
to keep track of theColumn
; normally, themapper()
creates this automatically. Usingcolumn_property()
,we can pass additional arguments about how we’d like theColumn
to be mapped. Below, we pass an optionactive_history
,which specifies that a change to this column’s value shouldresult 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 tomultiple columns. This use case arises when mapping to ajoin()
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 asmapped attributes, such as below where we create an attributefullname
that is the string concatenation of thefirstname
andlastname
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.
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 withinthe mapper’s selectable; examples include SQL expressions, functions,and scalar SELECT queries.
Columns that aren’t present in the mapper’s selectable won’t bepersisted by the mapper and are effectively “read-only” attributes.
- Parameters
active_history=False – When
True
, indicates that the “previous” value for ascalar attribute should be loaded when replaced, if notalready loaded. Normally, history tracking logic forsimple non-primary-key scalar values only needs to beaware of the “new” value in order to perform a flush. Thisflag is available for applications that make use ofattributes.get_history()
orSession.is_modified()
which also need to knowthe “previous” value of the attribute.
comparator_factory – a class which extends
ColumnProperty.Comparator
which provides custom SQLclause generation for comparison operations.
deferred – when True, the column property is “deferred”, meaning thatit does not load immediately, and is instead loaded when theattribute is first accessed on an instance. See also
deferred()
.
expire_on_flush=True – Disable expiry on flush. A column_property() which refersto a SQL expression (and not a single table-bound column)is considered to be a “read only” property; populating ithas no effect on the state of data, and it can only returndatabase state. For this reason a column_property()’s valueis expired whenever the parent object is involved in aflush, that is, has any kind of “dirty” state within a flush.Setting this parameter to
False
will have the effect ofleaving any existing value present after the flush proceeds.Note however that theSession
with default expirationsettings still expiresall attributes after aSession.commit()
call, however.
info – Optional data dictionary which will be populated into the
MapperProperty.info
attribute of this object.
an
AttributeExtension
instance, or list of extensions,which will be prepended to the list of attribute listeners for theresulting descriptor placed on the class.
Deprecated since version 0.7:
AttributeExtension
is deprecated in favor of theAttributeEvents
listener interface. Thecolumn_property.extension
parameter will be removed in a future release.
Mapping a Subset of Table Columns
Sometimes, a
Table
object was made available using thereflection process described at Reflecting Database Objects to loadthe table’s structure from the database.For such a table that has lots of columns that don’t need to be referencedin the application, theinclude_properties
orexclude_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 theuser_table
table, only includingtheuser_id
anduser_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 theaddress_table
table, includingall columns present exceptstreet
,city
,state
, andzip
.
When this mapping is used, the columns that are not included will not bereferenced in any SELECT statements emitted by
Query
, nor will therebe any mapped attribute on the mapped class which represents the column;assigning an attribute of that name will have no effect beyond that ofa normal Python attribute assignment.
In some cases, multiple columns may have the same name, such as whenmapping to a join of two or more tables that share some column name.
include_properties
andexclude_properties
can also accommodateColumn
objects to more accurately describe which columnsshould 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 thoseconfigured by theColumn.default
,Column.onupdate
,Column.server_default
andColumn.server_onupdate
parameters, will continue to functionnormally even if thoseColumn
objects are not mapped. This isbecause in the case ofColumn.default
andColumn.onupdate
, theColumn
object is still presenton the underlyingTable
, thus allowing the default functions totake place when the ORM emits an INSERT or UPDATE, and in the case ofColumn.server_default
andColumn.server_onupdate
,the relational database itself emits these defaults as a server sidebehavior.