Hi guys -
I'm not sure this is a bug or not, but if not it might be worth
adding a comment to the documentation.
Using the following schema:
table A:
pk
B_fk_1
B_fk_2
...
table B:
pk
info
After setting up A and B correctly (see attachment), and performing
cascade_mapper(A, B), looking at an instance of class A does not show
any B instances (nor is it unambiguous what they should be
called). There is, I note, a A.B entry, but with the value of
None. Looking at the engine log, it is obvious what is happening -
it is trying to find the B object which has a pk matching both B_fk_1
AND B_fk_2.
The documentation for cascade_mapper should be updated to either
state that it's not the right tool for this situation, or perhaps a
bug/feature request filed against cascade_mapper.
Incidentally, I am trying, as you can see from the attached larger
sample, to build up to a correctly specified mapper for this
testcase. This information may be in the docs already (if anyone
wants to submit chapter and verse...?).
Cheers!
--G
import sys
from sqlalchemy import *
engine = create_engine('sqlite', {'filename':':memory:'}, echo_uow=True)
info_table = Table('infos', engine,
Column('pk', Integer, primary_key=True),
Column('info', String))
rel_table = Table('rels', engine,
Column('pk', Integer, primary_key=True),
Column('info_fk_one', Integer, ForeignKey(info_table.c.pk),
nullable=False),
Column('info_fk_two', Integer, ForeignKey(info_table.c.pk),
nullable=False),
Column('rel_data', String))
info_table.create()
rel_table.create()
info_table.insert().execute(
{'pk':1, 'info':'pk_1_data'},
{'pk':2, 'info':'pk_2_data'},
{'pk':3, 'info':'pk_3_data'},
{'pk':4, 'info':'pk_4_data'},
{'pk':5, 'info':'pk_5_data'})
rel_table.insert().execute(
{'pk':1, 'info_fk_one':1, 'info_fk_two':3, 'rel_data':'1-1:3'},
{'pk':2, 'info_fk_one':2, 'info_fk_two':4, 'rel_data':'2-2:4'},
{'pk':3, 'info_fk_one':3, 'info_fk_two':5, 'rel_data':'3-3:5'})
# The raw SQL query we are trying to reproduce in SQLAlchemy
sql_result = engine.text("""
SELECT rels_1.pk, infos_1.info, infos_2.info, rels_1.rel_data
FROM rels AS rels_1, rels AS rels_2, infos AS infos_1, infos AS infos_2
WHERE rels_1.info_fk_one = infos_1.pk
AND rels_2.info_fk_two = infos_2.pk
AND rels_1.pk = rels_2.pk
""").execute().fetchall()
# Outputs:
# [(1, u'pk_1_data', u'pk_3_data', u'1-1:3'),
# (2, u'pk_2_data', u'pk_4_data', u'2-2:4'),
# (3, u'pk_3_data', u'pk_5_data', u'3-3:5')]
print 'The result from the SQL Query:\n' + repr(sql_result)
# Here's the same query, but as using SQLAlchemy tokens
info_t_2 = info_table.alias()
sas_result = select(
# First select the columns we wish to see, which are the same as the
ones in the above query.
[rel_table.c.pk, info_table.c.info, info_t_2.c.info,
rel_table.c.rel_data],
# Logically AND the following arguments together...
and_(
# Set the first relationship on our normal info_table
rel_table.c.info_fk_one == info_table.c.pk,
# Set the second relationship using the alias to tie together
the second foreign key relation
rel_table.c.info_fk_two == info_t_2.c.pk)
).execute().fetchall()
# Outputs:
# [(1, u'pk_1_data', u'pk_3_data', u'1-1:3'),
# (2, u'pk_2_data', u'pk_4_data', u'2-2:4'),
# (3, u'pk_3_data', u'pk_5_data', u'3-3:5')]
print 'The result from the SA SELECT Tokenized query:\n' + repr(sas_result)
assert repr(sql_result) == repr(sas_result)
print
# Cleanup a bit:
info_t_2 = None
# Here's the same query, but using SQLALchemy mappers:
# Create the classes to integrate the mappers with:
class Information(object):
pass
class Relationship(object):
pass
# Create the basic mappers, with no frills or modifications
Information.mapper = mapper(Information, info_table)
Relationship.mapper = mapper(Relationship, rel_table)
# Add the necessary properties
# Cascade mappers doesn't create any corresponding entries for the foreign keys
cascade_mappers(Relationship.mapper, Information.mapper)
