CODEWITHSUNDEEP
pythondatabasejoinsqlalchemy
Tok Soegiharto
Tok Soegiharto

Reputation: 329

SQLAlchemy error query join across database

I have multiple sqlite databases.

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

and

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

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

here the source:

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

Upvotes: 1

Views: 747

Answers (1)

van
van

Reputation: 76972

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.

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

In case of just one owner:

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

In case of multiple owners:

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

Upvotes: 1

Related Questions