[sqlalchemy] Re: Relation spanning multiple tables
This looks like an association proxy. Have you tried that?
http://www.sqlalchemy.org/docs/05/reference/ext/associationproxy.html
--
Mike Conley
On Mon, Jun 1, 2009 at 11:46 PM, George Sakkis george.sak...@gmail.comwrote:
I can't for the life of me figure out how to specify a relation
spanning 3 tables. I think I've tried all combinations of
primaryjoin, secondaryjoin, viewonly, foreign_keys, remote_dest and
all that jazz, to no avail so far. If this is possible at all, there
should be a sample usage at the docs or at least a recipe on the wiki
since apparently it's not obvious at all. Below are some of my failed
attempts; any help will be very much appreciated.
George
#
from sqlalchemy.orm import relation, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, String,
ForeignKey, and_
TEST = 5
def main():
c = Company()
u = User()
a = Address()
c.employees.append(u)
u.addresses.append(a)
session = sessionmaker()()
session.add(c)
session.flush()
print a.company
Base = declarative_base(bind=create_engine('sqlite:///:memory:',
echo=True))
class Company(Base):
__tablename__ = 'company'
id = Column(Integer, primary_key=True)
name = Column(String(50))
employees = relation('User')
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String(50))
company_id = Column(Integer, ForeignKey(Company.id))
addresses = relation('Address')
class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key=True)
email = Column(String(50))
user_id = Column(Integer, ForeignKey(User.id))
# 1st attempt: just a primary join. Fails with
# sqlalchemy.exc.ArgumentError: Could not locate any equated,
locally
# mapped column pairs for primaryjoin condition 'addresses.user_id
= users.id
# AND users.company_id = company.id' on relation Address.company.
For more
# relaxed rules on join conditions, the relation may be marked as
viewonly=True.
if TEST == 1:
company = relation(Company,
primaryjoin=and_(user_id==User.id,
User.company_id==Company.id))
# 2nd attempt: add viewonly=True. Fails with
# sqlalchemy.exc.ArgumentError: Could not determine relation
direction for
# primaryjoin condition 'addresses.user_id = users.id AND
users.company_id =
# company.id', on relation Address.company. Specify the
'foreign_keys'
# argument to indicate which columns on the relation are foreign.
if TEST == 2:
company = relation(Company,
primaryjoin=and_(user_id==User.id,
User.company_id==Company.id),
viewonly=True)
# 3rd attempt: add foreign_keys=[user_id]. Fails with
#sqlalchemy.exc.ArgumentError: Remote column 'users.id' is not
part of
# mapping Mapper|Company|company. Specify remote_side argument to
indicate
# which column lazy join condition should bind.
if TEST == 3:
company = relation(Company,
primaryjoin=and_(user_id==User.id,
User.company_id==Company.id),
viewonly=True,
foreign_keys=[user_id])
# 4th attempt: add remote_side=[Company.id]. Fails with
# sqlalchemy.orm.exc.UnmappedColumnError: No column
users.company_id is
# configured on mapper Mapper|Address|addresses...
if TEST == 4:
company = relation(Company,
primaryjoin=and_(user_id==User.id,
User.company_id==Company.id),
viewonly=True,
foreign_keys=[user_id],
remote_side=[Company.id])
# 5th attempt: try secondary table/join. Fails with:
# sqlalchemy.orm.exc.UnmappedColumnError: Column 'users.id' is not
available,
# due to conflicting property
'id':sqlalchemy.orm.properties.ColumnProperty
# object at 0x8f73bac
if TEST == 5:
company = relation(Company, User.__table__,
primaryjoin=user_id==User.id,
secondaryjoin=User.company_id==Company.id)
# give up :/
if __name__ == '__main__':
Base.metadata.create_all()
main()
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Relation spanning multiple tables
On Jun 2, 7:00 am, Mike Conley mconl...@gmail.com wrote:
This looks like an association proxy. Have you tried that?
http://www.sqlalchemy.org/docs/05/reference/ext/associationproxy.html
I don't see how this is related. From a quick read, it seems that's a
simplified way to handle many-to-many relations by hiding the
association object and it requires a regular relation to be defined
anyway. In my example there are two independent one-to-many
relationships and the intermediate table (User) is not just an
association object, it's a standalone object.
George
On Mon, Jun 1, 2009 at 11:46 PM, George Sakkis george.sak...@gmail.comwrote:
I can't for the life of me figure out how to specify a relation
spanning 3 tables. I think I've tried all combinations of
primaryjoin, secondaryjoin, viewonly, foreign_keys, remote_dest and
all that jazz, to no avail so far. If this is possible at all, there
should be a sample usage at the docs or at least a recipe on the wiki
since apparently it's not obvious at all. Below are some of my failed
attempts; any help will be very much appreciated.
George
#
from sqlalchemy.orm import relation, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, String,
ForeignKey, and_
TEST = 5
def main():
c = Company()
u = User()
a = Address()
c.employees.append(u)
u.addresses.append(a)
session = sessionmaker()()
session.add(c)
session.flush()
print a.company
Base = declarative_base(bind=create_engine('sqlite:///:memory:',
echo=True))
class Company(Base):
__tablename__ = 'company'
id = Column(Integer, primary_key=True)
name = Column(String(50))
employees = relation('User')
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String(50))
company_id = Column(Integer, ForeignKey(Company.id))
addresses = relation('Address')
class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key=True)
email = Column(String(50))
user_id = Column(Integer, ForeignKey(User.id))
# 1st attempt: just a primary join. Fails with
# sqlalchemy.exc.ArgumentError: Could not locate any equated,
locally
# mapped column pairs for primaryjoin condition 'addresses.user_id
= users.id
# AND users.company_id = company.id' on relation Address.company.
For more
# relaxed rules on join conditions, the relation may be marked as
viewonly=True.
if TEST == 1:
company = relation(Company,
primaryjoin=and_(user_id==User.id,
User.company_id==Company.id))
# 2nd attempt: add viewonly=True. Fails with
# sqlalchemy.exc.ArgumentError: Could not determine relation
direction for
# primaryjoin condition 'addresses.user_id = users.id AND
users.company_id =
# company.id', on relation Address.company. Specify the
'foreign_keys'
# argument to indicate which columns on the relation are foreign.
if TEST == 2:
company = relation(Company,
primaryjoin=and_(user_id==User.id,
User.company_id==Company.id),
viewonly=True)
# 3rd attempt: add foreign_keys=[user_id]. Fails with
#sqlalchemy.exc.ArgumentError: Remote column 'users.id' is not
part of
# mapping Mapper|Company|company. Specify remote_side argument to
indicate
# which column lazy join condition should bind.
if TEST == 3:
company = relation(Company,
primaryjoin=and_(user_id==User.id,
User.company_id==Company.id),
viewonly=True,
foreign_keys=[user_id])
# 4th attempt: add remote_side=[Company.id]. Fails with
# sqlalchemy.orm.exc.UnmappedColumnError: No column
users.company_id is
# configured on mapper Mapper|Address|addresses...
if TEST == 4:
company = relation(Company,
primaryjoin=and_(user_id==User.id,
User.company_id==Company.id),
viewonly=True,
foreign_keys=[user_id],
remote_side=[Company.id])
# 5th attempt: try secondary table/join. Fails with:
# sqlalchemy.orm.exc.UnmappedColumnError: Column 'users.id' is not
available,
# due to conflicting property
'id':sqlalchemy.orm.properties.ColumnProperty
# object at 0x8f73bac
if TEST == 5:
company = relation(Company, User.__table__,
primaryjoin=user_id==User.id,
secondaryjoin=User.company_id==Company.id)
# give up :/
if __name__ == '__main__':
Base.metadata.create_all()
main()
--~--~-~--~~~---~--~~
You received this
