Hello,
I am new to sqlalchemy and I have some questions.
After going through the tutorial I started experimenting with the
"Multiple Foreign Key Relationships Between Two Tables" recipe in the
wiki:
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/MultiFkRelations
I started from there and added some code (full source in attach).
First the table definitions:
info_table = Table('infos', meta,
Column('pk', Integer, primary_key=True),
Column('info', String))
rel_table = Table('rels', meta,
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))
As you can see this table has two foreign keys to the infos table.
In my example program the info table has people's names in it, and the
rels table has relationships between them, like "Uncle" or "Father".
Now let's see the mappers (the classes were defined but not shown because
their are trivial):
properties = { 'relation_direct' : relation(Relationship,
primaryjoin=(info_table.c.pk == rel_table.c.info_fk_one)),
'relation_inverse' : relation(Relationship,
primaryjoin=(info_table.c.pk == rel_table.c.info_fk_two))}
Relationship.mapper = mapper(Relationship, rel_table)
Information.mapper = mapper(Information, info_table, properties = properties)
Information.mapper.add_property('relations',
relation(Information,
primaryjoin=(info_table.c.pk ==
rel_table.c.info_fk_one),
secondaryjoin=(rel_table.c.info_fk_two
== info_table.c.pk),
secondary=rel_table))
This mappers seem to be working but I'm not sure their are correct.
Note that Information has a relation to itself.
If I create 4 Information objects (i1, i2, i3 and i4) there are at least 3
ways to specify relations between i1 and the others:
1. rel1 = Relationship(i1, i2, "Uncle")
session.save(rel1)
2. rel2 = Relationship(i1, i3, "Uncle")
i1.relation_direct.append(rel2)
3. i1.relations.append(i4)
The third one has just created a Relationship object automatically but
rel_data is not defined. If I could somehow receive this object I could
easily specify the relationship data between i1 and i4. Is this possible?
I could do something like: rel_obj.rel_data = "Father"
My second question is if it is possible to do a query on Information based
on rel_data (attribute of Relationship), like a query that returns all of
John's nephews (rel_data = "Uncle")?
I think the SQL output of the ORM should be something like:
SELECT infos2.pk, infos2.info FROM infos
JOIN rels ON infos.pk = rels.info_fk_one
JOIN infos AS infos2 ON rels.info_fk_two = infos2.pk
WHERE infos.pk = ? AND rels.rel_data = ?
Note that I could do a query on Relationship and then get to the Information
objects but I want to get a list of Information objects in one shot.
Can anyone help with this two questions?
Thanks!
--
Rúben Leote Mendes - [EMAIL PROTECTED]
from sqlalchemy import *
db = create_engine('sqlite:////tmp/test.db')
meta = BoundMetaData(db)
meta.engine.echo = True
info_table = Table('infos', meta,
Column('pk', Integer, primary_key=True),
Column('info', String))
#This table has two foreign keys to the infos table
rel_table = Table('rels', meta,
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))
meta.create_all()
class Information(object):
def __init__(self, info):
self.info = info
class Relationship(object):
def __init__(self, info_one, info_two, rel_data):
self.info_fk_one = info_one.pk
self.info_fk_two = info_two.pk
self.rel_data = rel_data
#Not sure if the mappers are correct!
properties = { 'relation_direct' : relation(Relationship, primaryjoin=(info_table.c.pk == rel_table.c.info_fk_one)),
'relation_inverse' : relation(Relationship, primaryjoin=(info_table.c.pk == rel_table.c.info_fk_two))}
Relationship.mapper = mapper(Relationship, rel_table)
Information.mapper = mapper(Information, info_table, properties = properties)
Information.mapper.add_property('relations',
relation(Information,
primaryjoin=(info_table.c.pk == rel_table.c.info_fk_one),
secondaryjoin=(rel_table.c.info_fk_two == info_table.c.pk),
secondary=rel_table))
session = create_session()
#Lets create 4 information objects and give them peoples names
i1 = Information("John")
i2 = Information("Paul")
i3 = Information("George")
i4 = Information("Ringo")
session.save(i1)
session.save(i2)
session.save(i3)
session.save(i4)
session.flush()
#Now lets define some relationships between them
#i1 is uncle of i2
rel1 = Relationship(i1, i2, "Uncle")
session.save(rel1)
#i1 is also uncle of i3
rel2 = Relationship(i1, i3, "Uncle")
#Instead of saving the session we can append via "relation_direct"
i1.relation_direct.append(rel2)
#This time, to add a new relation we append via "relations"
#to specify that i1 is somehow related to i4.
i1.relations.append(i4)
#This has just created a Relationship object automatically but rel_data
#is not defined. If we could somehow receive this object we could easily
#specify the relationship data between i1 and i4. Is this possible?
#rel_obj.rel_data = "Father"
session.flush()
#We don't know, so let's do it some other way
r=session.query(Relationship).get_by(info_fk_one=i1.pk, info_fk_two=i4.pk)
r.rel_data = "Father"
session.flush()
#Now let's do some queries.
session.clear()
#Let's get John's information
iq1=session.query(Information).get_by(info="John")
#We can easily access all Information objects that are related to John.
for n in range(len(iq1.relations)):
print i1.info + " is " + iq1.relations[n].info + "s " + iq1.relation_direct[n].rel_data
#But what if we would like to specify a query based on rel_data,
#like a query that returns all of John's nephews (rel_data = "Uncle")?
#How can it be done?
#I think the ORM should output something like:
#
#SELECT infos2.pk, infos2.info FROM infos
# JOIN rels ON infos.pk = rels.info_fk_one
# JOIN infos AS infos2 ON rels.info_fk_two = infos2.pk
# WHERE infos.pk = ? AND rels.rel_data = ?
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
