Hi. There seems to be a problem using backreferences to fill in two
required fields that both have a foreign key to the same table.
I'm creating an app to manage "lessons." A trimmed-down version
presented here consists of two tables, users and lessons. Lessons have
two foreign keys to user - organizer_id and owner_id, each property
having a backref "lessons_as_organizer" and "lessons_as_teacher". This
is causing problems when I try to assign both the organizer and the
teacher through backrefs, as follows:
<Setup as seen below>
>>> bob = User()
>>> bob.firstname = "Robert"
>>> pete = User()
>>> pete.firstname = "Peter"
>>> session.add_all([bob, pete])
>>> session.flush()
>>>
>>> spy = Lesson()
>>> spy.name = "Spying in the digital age"
>>> bob.lessons_as_organizer.append(spy)
>>> pete.lessons_as_teacher.append(spy)
Traceback (most recent call last):
...
sqlalchemy.exc.IntegrityError: (IntegrityError) ORA-01400: cannot
insert NULL into ("FOO"."LESSONS"."TEACHER_ID")
'INSERT INTO foo."LESSONS" (id, teacher_id, organizer_id, name,
description) VALUES
(:id, :teacher_id, :organizer_id, :name, :description)' {'teacher_id':
None, 'organizer_id': 1.0, 'description': None, 'id': 1, 'name':
'Spying in the digital age'}
If I reverse the order, the error is still thrown when I try and set
the second attribute. Is this a bug? Do I have to turn off autoflush?
Is that even the problem? Thanks for any help. What follows is the SQL
generating the db (Oracle) and the code setting up the tables/mappers/
etc.
-Richard
--------------------------------------------------------------
SQL
--------------------------------------------------------------
CREATE TABLE USERS
(
ID NUMBER NOT NULL,
FIRSTNAME VARCHAR2(50),
LASTNAME VARCHAR2(50)
, CONSTRAINT USERS_PK PRIMARY KEY
(
ID
)
ENABLE
)
;
CREATE TABLE LESSONS
(
ID NUMBER NOT NULL,
TEACHER_ID NUMBER NOT NULL,
ORGANIZER_ID NUMBER NOT NULL,
NAME VARCHAR2(100) NOT NULL,
DESCRIPTION CLOB
, CONSTRAINT LESSONS_PK PRIMARY KEY
(
ID
)
ENABLE
)
;
ALTER TABLE LESSONS
ADD CONSTRAINT LESSONS_ORGANIZ_FK FOREIGN KEY
(
ORGANIZER_ID
)
REFERENCES USERS
(
ID
)
ENABLE
;
ALTER TABLE LESSONS
ADD CONSTRAINT LESSONS_TEACH_FK FOREIGN KEY
(
TEACHER_ID
)
REFERENCES USERS
(
ID
)
ENABLE
;
CREATE SEQUENCE USERS_SEQ INCREMENT BY 1 START WITH 1 MINVALUE 1;
CREATE SEQUENCE LESSONS_SEQ INCREMENT BY 1 START WITH 1 MINVALUE 1;
--------------------------------------------------------------
PYTHON SETUP
--------------------------------------------------------------
from sqlalchemy import Table, types, Column, Sequence,\
create_engine, MetaData
from sqlalchemy.orm import sessionmaker, mapper, relation, backref
ENGINE = create_engine('oracle://foo:b...@baz')
METADATA = MetaData(bind=ENGINE)
SESSION = sessionmaker(bind=ENGINE)
session = SESSION()
SCHEMA = 'foo'
OWNER = 'bar'
LESSONS_TN = "LESSONS"
USERS_TN = "USERS"
LESSONS_SEQ = Sequence("LESSONS_SEQ")
USERS_SEQ = Sequence("USERS_SEQ")
LESSONS_TABLE = Table(LESSONS_TN,
METADATA,
Column('id', types.Integer,
LESSONS_SEQ,
primary_key=True),
autoload=True, owner=OWNER, schema=SCHEMA)
USERS_TABLE = Table(USERS_TN,
METADATA,
Column('id', types.Integer,
USERS_SEQ,
primary_key=True),
autoload=True, owner=OWNER, schema=SCHEMA)
class Lesson(object):
pass
class User(object):
pass
mapper(Lesson, LESSONS_TABLE,
properties={'organizer':
relation(User, primaryjoin=
LESSONS_TABLE.c.organizer_id==USERS_TABLE.c.id,
foreign_keys=
[LESSONS_TABLE.c.organizer_id],
backref=backref
('lessons_as_organizer',foreign_keys=
[LESSONS_TABLE.c.organizer_id])
),
'teacher':
relation(User, primaryjoin=
LESSONS_TABLE.c.teacher_id==USERS_TABLE.c.id,
foreign_keys=[LESSONS_TABLE.c.teacher_id],
backref=backref('lessons_as_teacher',
foreign_keys=
[LESSONS_TABLE.c.teacher_id])
)
}
)
mapper(User, USERS_TABLE)
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
