SQLAlchemy 可以自动从数据库模式创建关系吗? [英] Can SQLAlchemy automatically create relationships from a database schema?

问题描述

使用外键从现有(SQLite)数据库开始，SQLAlchemy 是否可以自动构建 关系?

Starting from an existing (SQLite) database with foreign keys, can SQLAlchemy automatically build relationships?

SQLAlchemy 类是通过 __table_args__ = {'autoload': True} 自动创建的.

SQLAlchemy classes are automatically created via __table_args__ = {'autoload': True}.

目标是轻松访问相关表中的数据，而无需手动添加所有关系(即不使用 sqlalchemy.orm.relationship() 和 sqlalchemy.orm.backref).

The goal would be to easily access data from related tables without having to add all the relationships one by one by hand (i.e. without using sqlalchemy.orm.relationship() and sqlalchemy.orm.backref).

推荐答案

[更新] 从 SQLAlchemy 0.9.1 开始有 Automap 扩展 这样做.

[Update] As of SQLAlchemy 0.9.1 there is Automap extension for doing that.

对于 SQLAlchemy

0.9.0 可以使用 sqlalchemy 反射.

For SQLAlchemy < 0.9.0 it is possible to use sqlalchemy reflection.

SQLAlchemy 反射加载表之间的外键/主键关系.但不会在映射类之间创建关系.实际上反射不会为您创建映射类 - 您必须指定映射类名称.

SQLAlchemy reflection loads foreign/primary keys relations between tables. But doesn't create relations between mapped classes. Actually reflection doesn't create mapped classes for you - you have to specify mapped class name.

其实我认为加载外键的反射支持是一个很好的帮手和节省时间的工具.使用它，您可以使用联接构建查询，而无需指定要用于联接的列.

Actually I think that reflection support for loading foreign keys is a great helper and time saving tool. Using it you can build a query using joins without need to specify which columns to use for a join.

from sqlalchemy import *
from sqlalchemy import create_engine, orm
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship



metadata = MetaData()
Base = declarative_base()
Base.metadata = metadata

db = create_engine('<db connection URL>',echo=False)
metadata.reflect(bind=db)

cause_code_table = metadata.tables['cause_code']
ndticket_table = metadata.tables['ndticket']

sm = orm.sessionmaker(bind=db, autoflush=True, autocommit=True, expire_on_commit=True)
session = orm.scoped_session(sm)

q = session.query(ndticket_table,cause_code_table).join(cause_code_table)
for r in q.limit(10):
    print r

此外，当我使用反射对现有数据库运行查询时 - 我只需要定义映射类名称、表绑定、关系，但不需要为这些关系定义表列.

Also when I was using reflection to run queries to existing database - I had to define only mapped classes names, table bindings, relations, BUT there were no need to define table columns for these relations.

class CauseCode(Base):
    __tablename__ = "cause_code"

class NDTicket(Base):
    __tablename__ = "ndticket"
    cause_code = relationship("CauseCode", backref = "ndticket")


q = session.query(NDTicket)
for r in q.limit(10):
    print r.ticket_id, r.cause_code.cause_code

整体 SQLAlchemy 反射已经是强大的工具，可以节省我的时间，因此手动添加关系对我来说是一个很小的开销.

Overall SQLAlchemy reflection is already powerful tool and save me time, so adding relations manually is a small overhead for me.

如果我必须开发使用现有外键添加映射对象之间关系的功能，我会从使用 用检查器反射.使用 get_foreign_keys() 方法提供了建立关系所需的所有信息——引用的表名、引用的列名和目标表中的列名.并将使用此信息将具有关系的属性添加到映射类中.

If I would have to develop functionality that will add relations between mapped objects using existing foreign keys, I would start from using reflection with inspector. Using get_foreign_keys() method gives all information required to build relations - referred table name, referred column name and column name in target table. And would use this information for adding property with relationship into mapped class.

insp = reflection.Inspector.from_engine(db)
print insp.get_table_names()
print insp.get_foreign_keys(NDTicket.__tablename__)
>>>[{'referred_table': u'cause_code', 'referred_columns': [u'cause_code'], 'referred_schema': None, 'name': u'SYS_C00135367', 'constrained_columns': [u'cause_code_id']}]

这篇关于SQLAlchemy 可以自动从数据库模式创建关系吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！