I am having some trouble getting the code below to work... running the
code below will give this error message when trying to create a Session
object (last line):
sqlalchemy.exc.ArgumentError: Could not determine relationship direction
for primaryjoin condition 'ChildArea.session_id==ParentArea.session_id
AND ChildArea.parent_area_id==ParentArea.id', on relationship
ChildArea.parent_area. Ensure that the referencing Column objects have a
ForeignKey present, or are otherwise part of a ForeignKeyConstraint on
their parent Table, or specify the foreign_keys parameter to this
relationship.
In other words, no error messages after the create_all statement.
Bit of background, the database is an existing database, hence the
explicit naming of database columns used every now and then. I am using
SQLA 0.7.3 on a Oracle XE 11.2 database (using the most recent version
of cx_Oracle).
Data is divided in sessions, each session (date) contains a topology
which consists of parent areas which themselves consist of child areas.
The set of parent areas differs over the sessions. The primary key for a
parent area consists of both the session id and the parent area id
column. Hence the foreignkey relation from child to parent id consists
of these two columns.
This is what is giving me issues at the moment, and a few questions.
First of course is how to make the code below work, but I am also
wondering what the best practice is with regards to using column names
and/or object and property names when defining foreign keys and
relationships. As the two namings are not in line for my example.
The ForeignKeyConstraint requires column names, but the primaryjoin
seems to be able to handle various options. Have been skimming through
the documentation to find a declarative example with a compound
foreignkey but have not been able to find one. What would be a best
practice for the primaryjoin?
Of course any other comments/recommendations with regards to the code
below is appreciated :)
Thijs
from sqlalchemy import Column
from sqlalchemy import ForeignKey
from sqlalchemy import ForeignKeyConstraint
from sqlalchemy import and_
from sqlalchemy import Integer
from sqlalchemy import Date
from sqlalchemy import String
from sqlalchemy.orm import relationship
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.declarative import declarative_base
from datetime import date
# Initiate connection to Oracle database
engine=create_engine('oracle://scott:tiger@localhost:1521/XE',
echo=True)
Base = declarative_base(engine)
class Session(Base):
__tablename__ = 'sessions'
id = Column(Integer, primary_key=True)
date = Column(Date, nullable=False)
def __init__(self, id, date):
self.id = id
self.date = date
def __repr__(self):
return '<Session(date="{1:%Y-%m-%d}")>'.format(self.id,
self.date)
class ParentArea(Base):
__tablename__ = 'session_parentareas'
session_id = Column(Integer, ForeignKey('sessions.id'),
primary_key=True)
session = relationship(
"Session",
primaryjoin="ParentArea.session_id==Session.id",
backref="parent_areas"
)
id = Column(Integer, primary_key=True)
name = Column(String(50))
def __init__(self, session, id, name):
self.session = session
self.id = id
self.name = name
def __repr__(self):
return '<ParentArea(date="{0:%Y-%m-%d}", name="{1}")>'.format(
self.session.delivery_date,
self.name
)
class ChildArea(Base):
__tablename__ = 'session_childareas'
__table_args__ = (
ForeignKeyConstraint(
['session_id', 'parentarea_id'],
['session_parentareas.session_id', 'session_parentareas.id']
),
)
session_id = Column(Integer, ForeignKey('sessions.id'),
primary_key=True)
session = relationship(
"Session",
primaryjoin="ChildArea.session_id==Session.id"
)
id = Column(Integer, primary_key=True)
parent_area_id = Column('parentarea_id', Integer, nullable=False)
parent_area = relationship(
"ParentArea",
primaryjoin=and_(
"ChildArea.session_id==ParentArea.session_id",
"ChildArea.parent_area_id==ParentArea.id"
),
backref="child_areas"
)
name = Column(String(50))
def __init__(self, session, id, parent_area, name):
self.session = session
self.id = id
self.parent_area = parent_area
self.name = name
def __repr__(self):
return '<ChildArea(date="{0:%Y-%m-%d}", name="{1}")>'.format(
self.session.delivery_date,
self.name
)
Base.metadata.drop_all()
Base.metadata.create_all()
# Add a session
c = Session(1, date.today())
For completeness sake the full traceback:
Traceback (most recent call last):
File "example.py", line 107, in <module>
c = Session(1, date.today())
File "<string>", line 2, in __init__
File
"C:\Python27\lib\site-packages\sqlalchemy-0.7.3-py2.7-win32.egg\sqlalchemy\orm\instrumentation.py",
line 309, in _new_state_if_none
state = self._state_constructor(instance, self)
File
"C:\Python27\lib\site-packages\sqlalchemy-0.7.3-py2.7-win32.egg\sqlalchemy\util\langhelpers.py",
line 484, in __get__
obj.__dict__[self.__name__] = result = self.fget(obj)
File
"C:\Python27\lib\site-packages\sqlalchemy-0.7.3-py2.7-win32.egg\sqlalchemy\orm\instrumentation.py",
line 157, in _state_constructor
self.dispatch.first_init(self, self.class_)
File
"C:\Python27\lib\site-packages\sqlalchemy-0.7.3-py2.7-win32.egg\sqlalchemy\event.py",
line 274, in __call__
fn(*args, **kw)
File
"C:\Python27\lib\site-packages\sqlalchemy-0.7.3-py2.7-win32.egg\sqlalchemy\orm\mapper.py",
line 2865, in _event_on_first_init
configure_mappers()
File
"C:\Python27\lib\site-packages\sqlalchemy-0.7.3-py2.7-win32.egg\sqlalchemy\orm\mapper.py",
line 2790, in configure_mappers
mapper._post_configure_properties()
File
"C:\Python27\lib\site-packages\sqlalchemy-0.7.3-py2.7-win32.egg\sqlalchemy\orm\mapper.py",
line 1095, in _post_configure_properties
prop.init()
File
"C:\Python27\lib\site-packages\sqlalchemy-0.7.3-py2.7-win32.egg\sqlalchemy\orm\interfaces.py",
line 128, in init
self.do_init()
File
"C:\Python27\lib\site-packages\sqlalchemy-0.7.3-py2.7-win32.egg\sqlalchemy\orm\properties.py",
line 910, in do_init
self._determine_synchronize_pairs()
File
"C:\Python27\lib\site-packages\sqlalchemy-0.7.3-py2.7-win32.egg\sqlalchemy\orm\properties.py",
line 1197, in _determine_synchronize_pairs
True)
File
"C:\Python27\lib\site-packages\sqlalchemy-0.7.3-py2.7-win32.egg\sqlalchemy\orm\properties.py",
line 1175, in _sync_pairs_from_join
self
sqlalchemy.exc.ArgumentError: Could not determine relationship direction
for primaryjoin condition 'ChildArea.session_id==ParentArea.session_id
AND ChildArea.parent_area_id==ParentArea.id', on relationship
ChildArea.parent_area. Ensure that the referencing Column objects have a
ForeignKey present, or are otherwise part of a ForeignKeyConstraint on
their parent Table, or specify the foreign_keys parameter to this
relationship.
--
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.
