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

问题描述

从具有外键的现有（SQLite）数据库开始，SQLAlchemy可以自动构建 __ table_args__ = {'autoload'：True}自动创建的 code $。

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

解决方案

[更新] 从SQLAlchemy 0.9.1开始，有 Automap扩展为此。

对于SQLAlchemy< 0.9.0有可能使用sqlalchemy反射。

SQLAlchemy反射加载表之间的外键/主键关系。但是不会在映射类之间创建关系。其实反射不会为你创建映射类 - 你必须指定映射类名。

实际上我认为反射支持加载外键是一个很好的帮手和时间节约工具。使用它，你可以使用连接来建立一个查询，而不需要指定哪一列用于连接。

  from sqlalchemy import * $来自sqlalchemy的b $ b从sqlalchemy.ext.declarative导入create_engine，orm 
从sqlalchemy.orm导入declarative_base 
导入关系
 
 
 
元数据= MetaData （）
 Base = declarative_base（）
 Base.metadata =元数据
 $ b $ db = create_engine（'< db connection URL>'，echo = False）
元数据.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）
 $ bq = session.query（ndticket_table，cause_code_table）.join （cause_code_table）
 for q.limit（10）：
 print r 
  

<另外，当我使用反射来查询现有的数据库e - 我只能定义映射类名，表绑定，关系，但是没有必要为这些关系定义表列。

 <$ c $ b $ class bb_tablename__ =ndticket
 cause_code = relationship（$ b $ （10）：
打印r.ticket_id（）
 
 $ bq = session.query（NDTicket）
 ，r.cause_code.cause_code 
  

总体而言，SQLAlchemy反射已经是强大的工具，节省了时间，如果我将不得不使用现有的外键开发映射对象之间的关系的功能，我会开始使用反思与督察。使用get_foreign_keys（）方法提供了建立关系所需的所有信息 - 在目标表中引用的表名，引用的列名和列名。并将使用此信息添加属性与关系映射类。

$ $ p $ insp = reflection.Inspector.from_engine（db）
print insp.get_table_names（）
print insp.get_foreign_keys（NDTicket .__ tablename__）
>>> [{'referenced_table'：u'cause_code'，'referenced_columns'：[u'cause_code ']，'referenced_schema'：None，'name'：u'SYS_C00135367'，'constrained_columns'：[u'cause_code_id']}]


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

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

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

解决方案

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

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

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

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

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屋！