Hi all :)
I'm having a problem. I've table with two foreign keys to another
table. When I
define relation from child table to parent table, indicating that I
want to
check if one of the foreign keys match parent table, it doesn't work.
Let's take a look at my example database and code.
create table specialities(id int primary key, name varchar(30));
create table employees(id int primary key, fullname varchar(70),
first_speciality_id int references specialities,
second_speciality_id int references specialities);
insert into specialities values(101, 'Programmer');
insert into specialities values(102, 'Database adminstrator');
insert into specialities values(103, 'Manager');
insert into specialities values(104, 'Consultant');
-- John is a programmer.
insert into employees values(201,'John',101,null);
-- Adam is an administrator.
insert into employees values(202,'Adam',102,null);
-- Anna is a manager, but she can also work as a consultant.
insert into employees values(203,'Anna',103,104);
And here's my python.
import sqlalchemy.orm as orm
import sqlalchemy as sa
import sqlalchemy.databases.postgres as pg
class Employee(object):
def __repr__(self): return self.fullname
class Speciality(object):
def __repr__(self): return self.name
metadata = sa.MetaData()
table_employees = sa.Table('employees', metadata,
sa.Column('id', pg.PGInteger(), primary_key=True),
sa.Column('fullname', pg.PGInteger()),
sa.Column('first_speciality_id', pg.PGInteger(),
sa.ForeignKey('specialities.id')),
sa.Column('second_speciality_id', pg.PGInteger(),
sa.ForeignKey('specialities.id')),
)
table_specialities = sa.Table('specialities', metadata,
sa.Column('id', pg.PGInteger(), primary_key=True),
sa.Column('name', pg.PGString(255)),
)
orm.mapper(Employee, table_employees, properties={
"what_can_be":orm.relation(Speciality,
primaryjoin=sa.or_(
table_employees.c.first_speciality_id==table_specialities.c.id,
table_employees.c.second_speciality_id==table_specialities.c.id
))
})
orm.mapper(Speciality, table_specialities)
# Let's do some queries.
def connect():
import psycopg2
return psycopg2.connect("host='localhost' dbname='test7' \
user='postgres' password='misiu'")
myengine = sa.engine.create_engine('postgres://', creator=connect)
Session = orm.sessionmaker(bind=myengine, autoflush=True,
transactional=True)
session = Session()
# This works fine:
anna = session.query(Employee).filter_by(fullname='Anna').first()
if anna:
print anna.fullname, 'can be:'
print [spec for spec in session.query(Speciality).filter(sa.or_(
table_specialities.c.id==anna.first_speciality_id,
table_specialities.c.id==anna.second_speciality_id
))] # That's ok - prints a list: [Manager, Consultant]
print
# And this is not fine:
anna = session.query(Employee).filter_by(fullname='Anna').first()
if anna:
print anna.fullname, 'can be:'
print anna.what_can_be # Prints only: Manager (not list, just a
string!)
I found that in the last line, where I want to print, what Anna can
be, SA generates this query:
2008-04-04 17:42:56,266 INFO sqlalchemy.engine.base.Engine.0x..cL
SELECT specialities.id AS specialities_id, specialities.name AS
specialities_name
FROM specialities
WHERE %(param_1)s = specialities.id OR %(param_2)s = specialities.id
ORDER BY specialities.id
2008-04-04 17:42:56,267 INFO sqlalchemy.engine.base.Engine.0x..cL
{'param_1': 103, 'param_2': 104}
The SQL looks OK and when I paste it into SQL console, I get correct
results, but SA give me bad results.
Do I do something wrong or is it a bug?
Regards and huge greetings for devs of superb SA :)
Michał.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
