Hi,
Le lundi 18 juin 2012 12:07:46 UTC+2, Thierry Florac a écrit :
>
>
> Hi Michael,
>
> Le samedi 16 juin 2012 17:54:21 UTC+2, Michael Bayer a écrit :
>>
>>
>> On Jun 16, 2012, at 11:41 AM, Michael Bayer wrote:
>>
>> > It's not a basic question at all as this is a rare edge case, and it's
>> not a "foreign key" by definition. You need to relate the two tables
>> based on a SQL function, in this case a concatenation. In some cases this
>> can be tricky, and there's improvements in 0.8 to address that, though in
>> this case it seems to work without too much difficulty:
>>
>> ha ha, except that example was running in 0.8 :). Prior to 0.8 you
>> need to use an undocumented attribute _local_remote_pairs. Undocumented
>> because, it was never the best way to do this and in 0.8 it isn't needed
>> anymore. But for now:
>>
>> from sqlalchemy import *
>> from sqlalchemy.orm import *
>> from sqlalchemy.ext.declarative import declarative_base
>>
>> Base= declarative_base()
>>
>> class A(Base):
>> __tablename__ = "a"
>>
>> id1 = Column(String, primary_key=True)
>> id2 = Column(String, primary_key=True)
>>
>> class B(Base):
>> __tablename__ = "b"
>> id = Column(Integer, primary_key=True)
>> a_id = Column(String)
>>
>> A.bs = relationship("B",
>> primaryjoin="B.a_id == A.id1 + A.id2",
>> foreign_keys="B.a_id",
>> _local_remote_pairs=[(A.__table__.c.id1, B.__table__.c.a_id),
>> (A.__table__.c.id2, B.__table__.c.a_id)],
>> viewonly=True)
>>
>> e = create_engine("sqlite://", echo=True)
>> Base.metadata.create_all(e)
>> s = Session(e)
>>
>> s.add_all([
>> A(id1="x", id2="y", bs=[
>> B(a_id="xy"),
>> B(a_id="xy")
>> ]),
>> A(id1="q", id2="p", bs=[
>> B(a_id="qp")
>> ])
>> ])
>>
>> s.commit()
>>
>> print s.query(A).first().bs
>>
>> for a in s.query(A).options(joinedload(A.bs)):
>> print a.bs
>
>
> I tried your code this morning and, as is, it works !
> But I still have a problem to define a back reference. If creating the
> relation as:
>
> Commune.regions = relationship(Region,
> primaryjoin="Region.cheflieu == Commune.dep
> + Commune.com",
> foreign_keys="Region.cheflieu",
>
> _local_remote_pairs=[(Commune.__table__.c.dep,
> Region.__table__.c.cheflieu),
> (Commune.__table__.
> c.com, Region.__table__.c.cheflieu)],
> viewonly=True,
> backref=backref('commune', uselist=False))
>
>
> I get an error if trying to access the entity:
>
> reg = session.query(Region).get(7)
> Traceback (most recent call last):
> File "<console>", line 1, in <module>
> File
> "/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/session.py",
>
> line 969, in query
> return self._query_cls(entities, self, **kwargs)
> File
> "/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py",
>
> line 107, in __init__
> self._set_entities(entities)
> File
> "/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py",
>
> line 116, in _set_entities
> self._setup_aliasizers(self._entities)
> File
> "/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py",
>
> line 131, in _setup_aliasizers
> _entity_info(entity)
> File
> "/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/util.py",
>
> line 550, in _entity_info
> mapperlib.configure_mappers()
> File
> "/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/mapper.py",
>
> line 2861, in configure_mappers
> mapper._post_configure_properties()
> File
> "/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/mapper.py",
>
> line 1166, in _post_configure_properties
> prop.init()
> File
> "/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/interfaces.py",
>
> line 128, in init
> self.do_init()
> File
> "/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/properties.py",
>
> line 919, in do_init
> self._generate_backref()
> File
> "/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/properties.py",
>
> line 1418, in _generate_backref
> mapper._configure_property(backref_key, relationship)
> File
> "/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/mapper.py",
>
> line 1145, in _configure_property
> prop.init()
> File
> "/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/interfaces.py",
>
> line 128, in init
> self.do_init()
> File
> "/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/properties.py",
>
> line 916, in do_init
> self._determine_direction()
> File
> "/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/properties.py",
>
> line 1228, in _determine_direction
> elif self._refers_to_parent_table():
> File
> "/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/properties.py",
>
> line 1458, in _refers_to_parent_table
> pt.is_derived_from(c.table) and \
> AttributeError: '_BinaryExpression' object has no attribute 'table'
>
>
> So what parameters should I use to create the back reference ?
>
I finally managed to create the reverse relation, not by using a back
reference parameter but by creating a new complete relationship.
Anyway, is there a better way to create it with a single instruction ?
Best regards,
Thierry
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/VkewOlmTIy0J.
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.
