版本:1.1.0b2 |发布日期:2016年7月1日

SQLAlchemy 1.1文档

自动地图¶ T0>

定义对sqlalchemy.ext.declarative系统的扩展,该系统自动从数据库模式生成映射的类和关系,但通常不一定反映出来。

版本0.9.1新增:添加了sqlalchemy.ext.automap

It is hoped that the AutomapBase system provides a quick and modernized solution to the problem that the very famous SQLSoup also tries to solve, that of generating a quick and rudimentary object model from an existing database on the fly. By addressing the issue strictly at the mapper configuration level, and integrating fully with existing Declarative class techniques, AutomapBase seeks to provide a well-integrated approach to the issue of expediently auto-generating ad-hoc mappings.

基本使用

最简单的用法是将现有的数据库反映到新的模型中。我们使用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()对象。类和关系遵循我们可以定制的默认命名方案。此时,由相关的UserAddress类组成的基本映射就可以以传统方式使用了。

注意

通过可行,我们的意思是对于要映射的表,它必须指定一个主键。此外,如果表被检测为两个其他表之间的纯关联表,则不会将其直接映射,而是将其配置为两个引用表的映射之间的多对多表。

从现有元数据生成映射

We can pass a pre-declared MetaData object to 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. 如果我们的模式包含table useraddress,我们可以定义一个或两个要使用的类:

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
    )

从上面的映射,我们现在将有类UserAddress,从UserAddress称为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. As the ForeignKeyConstraint we are examining corresponds to a reference from the immediate mapped class, the relationship will be set up as a many-to-one referring to the referred class; a corresponding one-to-many backref will be created on the referred class referring to this class.

  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中的新功能: - 当生成一对多关系并建立所有的默认级联时,automap将检测不可空的外键约束。 t2 > delete-orphan如果是的话;另外,如果约束指定CASCADEForeignKeyConstraint.ondelete不可为空或SET tt> NULL / t9>表示可空列,还添加passive_deletes=True选项。

  5. 关系的名称是使用AutomapBase.prepare.name_for_scalar_relationshipAutomapBase.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.

自定义关系参数

可以使用AutomapBase.prepare.generate_relationship挂钩向关系添加参数。在大多数情况下,我们可以使用现有的automap.generate_relationship()函数在用给定的关键字字典扩充自己的参数之后返回对象。

下面是如何将relationship.cascaderelationship.passive_deletes选项发送到所有一对多关系的说明:

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)

多对多关系

sqlalchemy.ext.automap will generate many-to-many relationships, e.g. those which contain a secondary argument. 生产这些过程如下:

  1. 在给任何映射类分配给它之前,给Table给出一个给定的ForeignKeyConstraint对象。
  2. If the table contains two and exactly two ForeignKeyConstraint objects, and all columns within this table are members of these two ForeignKeyConstraint objects, the table is assumed to be a “secondary” table, and will not be mapped directly.
  3. 如果有的话,Table指向的两个(或一个,用于自引用)外部表与它们将要映射到的类相匹配。
  4. 如果两侧的映射类已经定位,则在这两个类之间创建一个多对多的双向relationship() / backref()对。
  5. The override logic for many-to-many works the same as that of one-to-many/ many-to-one; the generate_relationship() function is called upon to generate the strucures and existing attributes will be maintained.

与继承的关系

sqlalchemy.ext.automap will not generate any relationships between two classes that are in an inheritance 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',
    }

EngineerEmployee的外键不是用于关系,而是用于在两个类之间建立连接的继承。

Note that this means automap will not generate any relationships for foreign keys that link from a subclass to a superclass. 如果映射具有从子类到超类的实际关系,那么这些关系需要是明确的。下面,我们有两个独立的从EngineerEmployee的外键,我们需要设置我们想要的关系以及inherit_condition ,因为这些不是SQLAlchemy可以猜测的东西:

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
    }

处理简单的命名冲突

在映射过程中命名冲突的情况下,根据需要覆盖classname_for_table()name_for_scalar_relationship()name_for_collection_relationship()中的任何一个。例如,如果automap试图命名与现有列相同的多对一关系,则可以有条件地选择替代约定。给定一个模式:

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)
);

上面的模式将首先将table_a表自动映射为名为table_a的类;它会自动将关系映射到与这个相关类同名的table_b类, table_a该关系名称与映射列table_b.table_a冲突,并在映射时发出错误。

我们可以通过使用下划线来解决这个冲突:

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)

或者,我们可以更改列的名称。被映射的列可以使用Naming Columns Distinctly from Attribute Names区别命名列中描述的技术,通过明确地将列分配给一个新名称来修改:

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()函数的关键字参数。

参数:
class sqlalchemy.ext.automap。 AutomapBase

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

可以将AutomapBase类与由declarative.declarative_base()函数生成的“declarative base”类进行比较。In practice, the AutomapBase class is always used as a mixin along with an actual declarative base.

一个新的可分类的AutomapBase通常使用automap_base()函数实现。

也可以看看

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中提取映射的类和关系并执行映射。

参数:
sqlalchemy.ext.automap.classname_for_table(base, tablename, table)

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

默认的实现是:

return str(tablename)

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

参数:
返回:

一个字符串类的名字。

注意

在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.generate_relationship(base, direction, return_fn, attrname, local_cls, referred_cls, **kw)

代表两个映射类生成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()The backref() function’s result will be used to produce a new relationship() in a second step, so it is critical that user-defined implementations correctly differentiate between the two functions, if a custom relationship function is being used.
  • 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 – all additional keyword arguments are passed along to the function.
Attrname:

该关系被分配到的属性名称。如果generate_relationship.return_fn的值是backref()函数,那么这个名字就是被分配给backref的名字。

返回:

a relationship() or backref() construct, as dictated by the generate_relationship.return_fn parameter.