基本使用¶

最简单的用法是将现有数据库反映到新模型中。我们使用automap_base()创建一个新的AutomapBase类，类似于我们如何创建声明性基类。然后，我们在生成的基类上调用AutomapBase.prepare()，要求它反映模式并生成映射：

from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import create_engine

Base = automap_base()

# engine, suppose it has two tables 'user' and 'address' set up
engine = create_engine("sqlite:///mydatabase.db")

# reflect the tables
Base.prepare(engine, reflect=True)

# mapped classes are now created with names by default
# matching that of the table name.
User = Base.classes.user
Address = Base.classes.address

session = Session(engine)

# rudimentary relationships are produced
session.add(Address(email_address="foo@bar.com", user=User(name="foo")))
session.commit()

# collection-based relationships are by default named
# "<classname>_collection"
print (u1.address_collection)

Above, calling AutomapBase.prepare() while passing along the AutomapBase.prepare.reflect parameter indicates that the MetaData.reflect() method will be called on this declarative base classes’ MetaData collection; then, each viable Table within the MetaData will get a new mapped class generated automatically. 将各种表链接在一起的ForeignKeyConstraint对象将用于在类之间生成新的，双向的relationship()对象。类和关系沿着我们可以定制的默认命名方案进行。此时，由相关的User和Address类组成的基本映射已准备好以传统方式使用。

从现有元数据生成映射¶

我们可以将预先声明的MetaData对象传递给automap_base()。这个对象可以以任何方式构造，包括以编程方式，从序列化的文件中，或从使用MetaData.reflect()反映的本身。下面我们举例说明反射和显式表声明的组合：

from sqlalchemy import create_engine, MetaData, Table, Column, ForeignKey
engine = create_engine("sqlite:///mydatabase.db")

# produce our own MetaData object
metadata = MetaData()

# we can reflect it ourselves from a database, using options
# such as 'only' to limit what tables we look at...
metadata.reflect(engine, only=['user', 'address'])

# ... or just define our own Table objects with it (or combine both)
Table('user_order', metadata,
                Column('id', Integer, primary_key=True),
                Column('user_id', ForeignKey('user.id'))
            )

# we can then produce a set of mappings from this MetaData.
Base = automap_base(metadata=metadata)

# calling prepare() just sets up mapped classes and relationships.
Base.prepare()

# mapped classes are ready
User, Address, Order = Base.classes.user, Base.classes.address,        Base.classes.user_order

明确指定类¶

sqlalchemy.ext.automap扩展允许类以类似于DeferredReflection类的方式显式定义。从AutomapBase扩展的类与常规声明类相似，但在构建之后不会立即映射，而是在调用AutomapBase.prepare()时映射。The AutomapBase.prepare() method will make use of the classes we’ve established based on the table name we use. 如果我们的模式包含表user和address，我们可以定义一个或两个要使用的类：

from sqlalchemy.ext.automap import automap_base
from sqlalchemy import create_engine

# automap base
Base = automap_base()

# pre-declare User for the 'user' table
class User(Base):
    __tablename__ = 'user'

    # override schema elements like Columns
    user_name = Column('name', String)

    # override relationships too, if desired.
    # we must use the same name that automap would use for the
    # relationship, and also must refer to the class name that automap will
    # generate for "address"
    address_collection = relationship("address", collection_class=set)

# reflect
engine = create_engine("sqlite:///mydatabase.db")
Base.prepare(engine, reflect=True)

# we still have Address generated from the tablename "address",
# but User is the same as Base.classes.User now

Address = Base.classes.address

u1 = session.query(User).first()
print (u1.address_collection)

# the backref is still there:
a1 = session.query(Address).first()
print (a1.user)

以上，其中一个更复杂的细节是，我们说明了覆盖automap将创建的relationship()对象之一。为此，我们需要确保名称与automap通常会生成的名称相匹配，因为从automap的角度来看，关系名称将是User.address_collection和引用的类名称，被称为address，即使我们在我们使用这个类时将它称为Address。

重写命名方案¶

sqlalchemy.ext.automap is tasked with producing mapped classes and relationship names based on a schema, which means it has decision points in how these names are determined. These three decision points are provided using functions which can be passed to the AutomapBase.prepare() method, and are known as classname_for_table(), name_for_scalar_relationship(), and name_for_collection_relationship(). 这些函数中的任何一个或所有函数都是在下面的例子中提供的，我们使用Inflect包使用类名的“camel case”方案和集合名称的“pluralizer”：

import re
import inflect

def camelize_classname(base, tablename, table):
    "Produce a 'camelized' class name, e.g. "
    "'words_and_underscores' -> 'WordsAndUnderscores'"

    return str(tablename[0].upper() + \
            re.sub(r'_([a-z])', lambda m: m.group(1).upper(), tablename[1:]))

_pluralizer = inflect.engine()
def pluralize_collection(base, local_cls, referred_cls, constraint):
    "Produce an 'uncamelized', 'pluralized' class name, e.g. "
    "'SomeTerm' -> 'some_terms'"

    referred_name = referred_cls.__name__
    uncamelized = re.sub(r'[A-Z]',
                         lambda m: "_%s" % m.group(0).lower(),
                         referred_name)[1:]
    pluralized = _pluralizer.plural(uncamelized)
    return pluralized

from sqlalchemy.ext.automap import automap_base

Base = automap_base()

engine = create_engine("sqlite:///mydatabase.db")

Base.prepare(engine, reflect=True,
            classname_for_table=camelize_classname,
            name_for_collection_relationship=pluralize_collection
    )

从上面的映射中，我们现在将有User和Address的类，其中User到Address的集合是称为User.addresses：

User, Address = Base.classes.User, Base.classes.Address

u1 = User(addresses=[Address(email="foo@bar.com")])

关系检测¶

绝大多数automap完成的是基于外键的relationship()结构的生成。这对于多对一和一对多关系有效的机制如下：

1. 已知映射到特定类的给定Table会针对ForeignKeyConstraint对象进行检查。

2. 从每个ForeignKeyConstraint，存在的远程Table对象与其映射到的类匹配（如果有的话），否则它将被跳过。

3. 正如我们正在检查的ForeignKeyConstraint对应于来自直接映射类的引用，该关系将被设置为指向所引用的类的多对一；将在涉及这个类的引用类上创建相应的一对多后向参考。

4. If any of the columns that are part of the ForeignKeyConstraint are not nullable (e.g. nullable=False), a cascade keyword argument of all, delete-orphan will be added to the keyword arguments to be passed to the relationship or backref. If the ForeignKeyConstraint reports that ForeignKeyConstraint.ondelete is set to CASCADE for a not null or SET NULL for a nullable set of columns, the option passive_deletes flag is set to True in the set of relationship keyword arguments. 请注意，并非所有后端都支持ON DELETE的反射。

   版本1.0.0中的新功能： - 当生成一对多关系并建立默认级联all时，automap将检测不可为空的外键约束， t2 > delete-orphan如果是的话；此外，如果约束指定CASCADE的ForeignKeyConstraint.ondelete为非空或SET tt> NULL / t9>表示可空列，还添加passive_deletes=True选项。

5. 关系的名称是使用AutomapBase.prepare.name_for_scalar_relationship和AutomapBase.prepare.name_for_collection_relationship可调用函数确定的。需要注意的是，默认关系命名从实际类名称派生名称。如果您通过声明某个特定类的明确名称，或者指定了一个备用类命名方案，那么这就是从中派生关系名称的名称。

6. 检查这些类是否存在与这些名称匹配的现有映射属性。If one is detected on one side, but none on the other side, AutomapBase attempts to create a relationship on the missing side, then uses the relationship.back_populates parameter in order to point the new relationship to the other side.

7. In the usual case where no relationship is on either side, AutomapBase.prepare() produces a relationship() on the “many-to-one” side and matches it to the other using the relationship.backref parameter.

8. Production of the relationship() and optionally the backref() is handed off to the AutomapBase.prepare.generate_relationship function, which can be supplied by the end-user in order to augment the arguments passed to relationship() or backref() or to make use of custom implementations of these functions.

from sqlalchemy.ext.automap import generate_relationship

def _gen_relationship(base, direction, return_fn,
                                attrname, local_cls, referred_cls, **kw):
    if direction is interfaces.ONETOMANY:
        kw['cascade'] = 'all, delete-orphan'
        kw['passive_deletes'] = True
    # make use of the built-in function to actually return
    # the result.
    return generate_relationship(base, direction, return_fn,
                                 attrname, local_cls, referred_cls, **kw)

from sqlalchemy.ext.automap import automap_base
from sqlalchemy import create_engine

# automap base
Base = automap_base()

engine = create_engine("sqlite:///mydatabase.db")
Base.prepare(engine, reflect=True,
            generate_relationship=_gen_relationship)

class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    type = Column(String(50))
    __mapper_args__ = {
         'polymorphic_identity':'employee', 'polymorphic_on': type
    }

class Engineer(Employee):
    __tablename__ = 'engineer'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    __mapper_args__ = {
        'polymorphic_identity':'engineer',
    }

class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    type = Column(String(50))

    __mapper_args__ = {
        'polymorphic_identity':'employee', 'polymorphic_on':type
    }

class Engineer(Employee):
    __tablename__ = 'engineer'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    favorite_employee_id = Column(Integer, ForeignKey('employee.id'))

    favorite_employee = relationship(Employee,
                                     foreign_keys=favorite_employee_id)

    __mapper_args__ = {
        'polymorphic_identity':'engineer',
        'inherit_condition': id == Employee.id
    }

CREATE TABLE table_a (
    id INTEGER PRIMARY KEY
);

CREATE TABLE table_b (
    id INTEGER PRIMARY KEY,
    table_a INTEGER,
    FOREIGN KEY(table_a) REFERENCES table_a(id)
);

def name_for_scalar_relationship(base, local_cls, referred_cls, constraint):
    name = referred_cls.__name__.lower()
    local_table = local_cls.__table__
    if name in local_table.columns:
        newname = name + "_"
        warnings.warn(
            "Already detected name %s present.  using %s" %
            (name, newname))
        return newname
    return name


Base.prepare(engine, reflect=True,
    name_for_scalar_relationship=name_for_scalar_relationship)

Base = automap_base()

class TableB(Base):
    __tablename__ = 'table_b'
    _table_a = Column('table_a', ForeignKey('table_a.id'))

Base.prepare(engine, reflect=True)

将Automap与显式声明一起使用¶

如前所述，automap不依赖于反射，并且可以使用MetaData集合中任何Table对象的集合。由此可见，automap也可以用于生成缺失的关系，给出一个完全定义表元数据的完整模型：

from sqlalchemy.ext.automap import automap_base
from sqlalchemy import Column, Integer, String, ForeignKey

Base = automap_base()

class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column(String)

class Address(Base):
    __tablename__ = 'address'

    id = Column(Integer, primary_key=True)
    email = Column(String)
    user_id = Column(ForeignKey('user.id'))

# produce relationships
Base.prepare()

# mapping is complete, with "address_collection" and
# "user" relationships
a1 = Address(email='u1')
a2 = Address(email='u2')
u1 = User(address_collection=[a1, a2])
assert a1.user is u1

Above, given mostly complete User and Address mappings, the ForeignKey which we defined on Address.user_id allowed a bidirectional relationship pair Address.user and User.address_collection to be generated on the mapped classes.

请注意，当继承AutomapBase时，需要使用AutomapBase.prepare()方法；如果未调用，则我们声明的类处于未映射状态。

API参考¶

sqlalchemy.ext.automap.automap_base(declarative_base=None, **kw)¶

生成声明式自动映射基础。

该函数生成一个新的基类，它是AutomapBase类的产物，也是declarative.declarative_base()生成的声明基。

除declarative_base以外的所有参数都是直接传递给declarative.declarative_base()函数的关键字参数。

参数：	declarative_base¶ – an existing class produced by declarative.declarative_base(). 当它被传递时，函数本身不再调用declarative.declarative_base()，所有其他关键字参数都被忽略。 **kw¶ – keyword arguments are passed along to declarative.declarative_base().

class sqlalchemy.ext.automap。 AutomapBase ¶

“自动映射”模式的基类。

可以将AutomapBase类与由declarative.declarative_base()函数生成的“声明性基本”类进行比较。在实践中，AutomapBase类总是作为一个mixin与一个实际的声明基础一起使用。

通常使用automap_base()函数即时创建一个新的子类化的AutomapBase。

也可以看看

Automap

类 =无 ¶

包含类的util.Properties的实例。

该对象的行为与表上的.c集合非常相似。类以它们的名称存在，例如：

Base = automap_base()
Base.prepare(engine=some_engine, reflect=True)

User, Address = Base.classes.User, Base.classes.Address

classmethod prepare(engine=None, reflect=False, schema=None, classname_for_table=<function classname_for_table>, collection_class=<type 'list'>, name_for_scalar_relationship=<function name_for_scalar_relationship>, name_for_collection_relationship=<function name_for_collection_relationship>, generate_relationship=<function generate_relationship>)¶

从MetaData中提取映射的类和关系并执行映射。

参数：

engine¶ – an Engine or Connection with which to perform schema reflection, if specified. 如果AutomapBase.prepare.reflect参数为False，则不使用此对象。 reflect¶ – if True, the MetaData.reflect() method is called on the MetaData associated with this AutomapBase. The Engine passed via AutomapBase.prepare.engine will be used to perform the reflection if present; else, the MetaData should already be bound to some engine else the operation will fail. classname_for_table¶ – callable function which will be used to produce new class names, given a table name. 默认为classname_for_table()。 name_for_scalar_relationship¶ – callable function which will be used to produce relationship names for scalar relationships. 默认为name_for_scalar_relationship()。 name_for_collection_relationship¶ – callable function which will be used to produce relationship names for collection-oriented relationships. 默认为name_for_collection_relationship()。 generate_relationship¶ – callable function which will be used to actually generate relationship() and backref() constructs. 默认为generate_relationship()。 collection_class¶ – the Python collection class that will be used when a new relationship() object is created that represents a collection. 默认为list。 模式 ¶ - 当与AutomapBase.prepare.reflect标志一起出现时，将传递给MetaData.reflect()以指示表应该从哪里反映出来的主模式。省略时，将使用由数据库连接使用的默认模式。 版本1.1中的新功能

sqlalchemy.ext.automap.classname_for_table(base, tablename, table)¶

给定表名称，返回应该使用的类名称。

默认的实现是：

return str(tablename)

可以使用AutomapBase.prepare.classname_for_table参数指定替代实现。

参数：	base¶ – the AutomapBase class doing the prepare. tablename¶ – string name of the Table. table¶ – the Table object itself.
返回：	一个字符串类的名字。 注意 在Python 2中，用于类名的字符串必须是非Unicode对象，例如一个str()对象。Table的.name属性通常是一个Python unicode子类，所以在计算后应该将str()函数应用于该名称任何非ASCII字符。

sqlalchemy.ext.automap.name_for_scalar_relationship(base, local_cls, referred_cls, constraint)¶

对于标量对象引用，返回应该用于从一个类引用到另一个类的属性名称。

默认的实现是：

return referred_cls.__name__.lower()

可以使用AutomapBase.prepare.name_for_scalar_relationship参数指定替代实现。

参数：	base¶ – the AutomapBase class doing the prepare. local_cls ¶ - 要在本地映射的类。 referenced_cls ¶ - 要在引用端映射的类。 constraint¶ – the ForeignKeyConstraint that is being inspected to produce this relationship.

sqlalchemy.ext.automap.name_for_collection_relationship(base, local_cls, referred_cls, constraint)¶

返回应该用于从一个类引用到另一个类的属性名称作为集合引用。

默认的实现是：

return referred_cls.__name__.lower() + "_collection"

可以使用AutomapBase.prepare.name_for_collection_relationship参数指定替代实现。

参数：	base¶ – the AutomapBase class doing the prepare. local_cls ¶ - 要在本地映射的类。 referenced_cls ¶ - 要在引用端映射的类。 constraint¶ – the ForeignKeyConstraint that is being inspected to produce this relationship.

sqlalchemy.ext.automap。 T0> generate_relationship T1> （ T2> 碱 T3>，方向 T4>， return_fn T5>， attrname T6>， local_cls T7>， referred_cls T8>， **千瓦 T9> ） T10 > ¶ T11>

代表两个映射类生成relationship()或backref()。

该函数的一个替代实现可以使用AutomapBase.prepare.generate_relationship参数指定。

这个函数的默认实现如下：

if return_fn is backref:
    return return_fn(attrname, **kw)
elif return_fn is relationship:
    return return_fn(referred_cls, **kw)
else:
    raise TypeError("Unknown relationship function: %s" % return_fn)

参数：	base¶ – the AutomapBase class doing the prepare. direction¶ – indicate the “direction” of the relationship; this will be one of ONETOMANY, MANYTOONE, MANYTOMANY. return_fn ¶ - 默认用于创建关系的功能。这将是relationship()或backref()。在第二步中，将使用backref()函数的结果产生新的relationship()，所以用户定义的实现正确区分这两个函数，如果使用自定义关系函数。 local_cls¶ – the “local” class to which this relationship or backref will be locally present. referred_cls¶ – the “referred” class to which the relationship or backref refers to. ** kw ¶ - 所有其他关键字参数都传递给该函数。
Attrname：	该关系被分配到的属性名称。如果generate_relationship.return_fn的值是backref()函数，那么这个名称是分配给后端参考的名称。
返回：	relationship()或backref()结构，如generate_relationship.return_fn参数指定的。