SQLAlchemy错误查询跨数据库联接 [英] SQLAlchemy error query join across database

问题描述

我有多个sqlite数据库.

I have multiple sqlite databases.

我尝试了以下代码，以从"Owner1"获取构造数据:

I tried this below code to get a Construction data from 'Owner1':

sess.query(Construction).select_from(join_(Owner)).filter(Owner.name == 'Owner 1').all()

和

sess.query(Construction).select_from(join_(Owner, Construction)).filter(Owner.name == 'Owner 1').all()

但是这给了我下面的错误:

but and this gave me below error :

sqlalchemy.exc.OperationalError: (OperationalError) no such table: owner

任何人都可以帮助我解决此问题.提前致谢 我正在使用Python 3.4和sqlalchemy 0.9.4

can anyone help me to solve this problem. Thanks in advance I'm using Python 3.4 and sqlalchemy 0.9.4

此处是来源:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base, declared_attr
from datetime import date

engines = {'o': create_engine('sqlite:///master.db', echo=True),
    'c': create_engine('sqlite:///struct.db', echo=True),
    't': create_engine('sqlite:///trade.db', echo=True)}

class Base(object):

    @declared_attr
    def __tablename__(cls):
        return cls.__name__.lower()

    id = Column(Integer, primary_key=True)

Base = declarative_base(cls=Base)

class OBase(Base):
    __abstract__ = True

    metadata = MetaData(bind=engines.get('o'))

class CBase(Base):
    __abstract__ = True

    metadata = MetaData(bind=engines.get('c'))

class TBase(Base):
    __abstract__ = True

    metadata = MetaData(bind=engines.get('t'))

class Owner(OBase):

    name = Column(String)
    address = Column(String)

class Construction(CBase):

    description = Column(String)
    value = Column(Integer)

    owner_id = Column(Integer, ForeignKey(Owner.id))
    owner = relationship(Owner)

class Trading(TBase):

    number = Column(String)
    date = Column(Date)
    value = Column(Integer)

    owner_id = Column(Integer, ForeignKey(Owner.id))
    owner = relationship(Owner)

OBase.metadata.create_all()
CBase.metadata.create_all()
TBase.metadata.create_all()

Session = scoped_session(sessionmaker())

sess = Session()

o1 = Owner(name='Owner 1', address='123 anywhere street')
o2 = Owner(name='Owner 2', address='40 main street')

sess.add_all([Construction(description='Flooring', value=1000, owner=o1),
    Construction(description='Flooring', value=1500, owner=o2),
    Construction(description='Roof', value=900, owner=o1)])
sess.commit()

推荐答案

问题是结果查询不是跨数据库"，它使用引擎为Construction执行，并且其数据库确实没有表用于Owner.

The problem is that the resulting query is not "cross-database", it executes using engine for Construction, and its database indeed does not have a table for Owner.

您可以分两个步骤进行操作:获取所有者的id，然后搜索Construction:

You can do that in two steps: get id of the Owner(s), and then search Construction:

如果只有一个所有者:

owner = sess.query(Owner).filter(Owner.name = 'Owner 1').one()
q = sess.query(Construction).filter(Construction.owner_id == owner.id)

如果有多个所有者:

owners = sess.query(Owner).filter(Owner.name.like('Owner 1%')).all()
ids = (_.id for _ in owners)
q = sess.query(Construction).filter(Construction.owner_id.in_(ids))

这篇关于SQLAlchemy错误查询跨数据库联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！