You are missing a foreign key column in the "people" table that
corresponds to your Person->Company relation. As a result, SQLAlchemy
tries to use person.id as the foreign key column (because that column
happens to be a foreign key to a base table of Company) and everything
blows up.
So:
* Add a foreign key column to Person that refers to companies.id.
* Add a primaryjoin argument to your Persion.company relation, because
SQLAlchemy will now see two potential ways to get from Person to
Company (people.id -> bizentities.bizentity_id and people.company_id -
> companies.id) and refuse to guess which path to take. Try
"primaryjoin=lambda: Person.company_id == Company.__table__.c.id". I'm
using Company.__table__.c.id instead of Company.id because Company.id
maps to the "bizentities.bizentity_id" column and not the
"companies.id" column.
-Conor
On Sep 23, 11:16 am, Kevin Horn <kevin.h...@gmail.com> wrote:
> On Wed, Sep 23, 2009 at 10:57 AM, Kevin Horn <kevin.h...@gmail.com> wrote:
>
> > On Wed, Sep 23, 2009 at 12:27 AM, Michael Bayer
> > <mike...@zzzcomputing.com>wrote:
>
> >> On Sep 22, 2009, at 11:59 AM, Kevin H wrote:
>
> >> > I'm having some trouble developing my model, and was hoping someone on
> >> > this list could help...
>
> >> > Here's what I want:
> >> > A BizEntity object
> >> > A Person and Company object (both descended from BizEntity, using
> >> > joined table inheritance)
> >> > A Company.employees attribute, which points to a list of Persons who
> >> > work for the company
> >> > A Person.company attribute, which points back to the company that
> >> > person works for
>
> >> > Whenever I try to combine inheritance with this sort of pseudo-
> >> > adjacency-list, I get really odd things happening when I try to query
> >> > from the tables...like getting the wrong company back when I query by
> >> > id.
>
> >> > Any ideas out there? Anyone done something like this?
>
> >> I'm doing this.
>
> > Howdy, Michael! Knowing that I'm not trying to do something impossible is
> > definitely a relief.
>
> >> The first thing to do is to definitely be on 0.5.6
> >> at the least.
>
> > OK, I'm on 0.5.5, so that's the first thing to fix, I guess.
>
> >> the next thing is to define the employees/company thing only once, as
> >> a relation/backref pair on just one of your mapped classes. doing it
> >> twice will mess things up for sure.
>
> > Good to know, thanks.
>
> >> your example also mentions a table called "nodes" which from
> >> everything else mentioned below would be erroneous. you don't need
> >> remote_side when mapping between Company and Person.
>
> > Wow, that's from something _really_ old. Been commented out for a
> > while...I didn't even notice that.
>
> >> None of this would cause the wrong "Company" to come back from a
> >> simple query by id, though. If that is really the effect you're
> >> seeing then something more fundamental might be amiss.
>
> > Looking at it again, it looks like this was caused by a problem in my
> > tests. I was assuming something I shouldn't have been about the order of
> > the data I was testing.
>
> > Thanks for the pointers, I'll post back later with results.
>
> > Kevin Horn
>
> Still having problems...
>
> Here's my new model:
>
> # START OF MODEL
>
> Base = declarative_base()
>
> class BizEntity(Base):
> __tablename__ = 'biz_entities'
> id = Column('bizentity_id', Integer, primary_key=True)
> type = Column('bizentity_type', String(30), nullable=False)
> __mapper_args__ = {'polymorphic_on': type}
>
> class Company(BizEntity):
> __tablename__ = 'companies'
> id = Column(Integer, ForeignKey('biz_entities.bizentity_id'),
> primary_key=True)
> name = Column('company_name', String(50))
> __mapper_args__ = {'polymorphic_identity': 'company'}
>
> def __init__(self, company_name):
> self.name = company_name
>
> def __repr__(self):
> return "<Company('%s')>" % (self.name)
>
> class Person(BizEntity):
> __tablename__ = 'people'
> id = Column('bizentity_id', Integer,
> ForeignKey('biz_entities.bizentity_id'), primary_key=True)
> first_name = Column('first_name', String(50))
> middle_init = Column('middle_init', String(1))
> last_name = Column('last_name', String(50))
>
> company = relation(Company, backref=backref('employees', order_by=id))
>
> __mapper_args__ = {'polymorphic_identity':'person'}
>
> def __init__(self, first_name, middle_init, last_name):
> self.first_name = first_name
> self.middle_init = middle_init
> self.last_name = last_name
>
> def __repr__(self):
> return "<Person('%s %s. %s')>" % (self.first_name, self.middle_init,
> self.last_name)
>
> # END OF MODEL
>
> now when I try to use it like this:
>
> # START SAMPLE CODE
> comp1 = Company('Test Company')
> #~ self.session.add(comp1)
> #~ self.session.commit()
>
> joe = Person('Joe', 'Q', 'Public')
> joe.company = comp1
> self.session.add(joe)
>
> self.session.commit()
> # END SAMPLE CODE
>
> I get a traceback like this:
>
> Traceback (most recent call last):
> File "ta_sa_test.py", line 98, in testCreateCompanyWithEmployees
> self.session.commit()
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\session.py",
> line 673, in commit
> self.transaction.commit()
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\session.py",
> line 378, in commit
> self._prepare_impl()
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\session.py",
> line 362, in _prepare_impl
> self.session.flush()
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\session.py",
> line 1356, in flush
> self._flush(objects)
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\session.py",
> line 1434, in _flush
> flush_context.execute()
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\unitofwork.py",
> line 261, in execute
> UOWExecutor().execute(self, tasks)
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\unitofwork.py",
> line 753, in execute
> self.execute_save_steps(trans, task)
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\unitofwork.py",
> line 773, in execute_save_steps
> self.execute_cyclical_dependencies(trans, task, False)
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\unitofwork.py",
> line 789, in execute_cyclical_dependencies
> self.execute(trans, [t], isdelete)
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\unitofwork.py",
> line 753, in execute
> self.execute_save_steps(trans, task)
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\unitofwork.py",
> line 773, in execute_save_steps
> self.execute_cyclical_dependencies(trans, task, False)
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\unitofwork.py",
> line 789, in execute_cyclical_dependencies
> self.execute(trans, [t], isdelete)
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\unitofwork.py",
> line 753, in execute
> self.execute_save_steps(trans, task)
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\unitofwork.py",
> line 768, in execute_save_steps
> self.save_objects(trans, task)
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\unitofwork.py",
> line 759, in save_objects
> task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\mapper.py",
> line 1406, in _save_obj
> c = connection.execute(statement.values(value_params), params)
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\engine\base.py",
> line 824, in execute
> return Connection.executors[c](self, object, multiparams, params)
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\engine\base.py",
> line 874, in _execute_clauseelement
> return self.__execute_context(context)
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\engine\base.py",
> line 896, in __execute_context
> self._cursor_execute(context.cursor, context.statement,
> context.parameters[0], context=context)
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\engine\base.py",
> line 950, in _cursor_execute
> self._handle_dbapi_exception(e, statement, parameters, cursor, context)
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\engine\base.py",
> line 931, in _handle_dbapi_exception
> raise exc.DBAPIError.instance(statement, parameters, e,
> connection_invalidated=is_disconnect)
> IntegrityError: (IntegrityError) PRIMARY KEY must be unique u'INSERT INTO
> biz_entities (bizentity_id, bizentity_type) VALUES (?, ?)' [1, 'person']
>
> and if I enable the commented-out lines in my sample code, I get a traceback
> like this:
>
> Traceback (most recent call last):
> File "ta_sa_test.py", line 98, in testCreateCompanyWithEmployees
> self.session.commit()
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\session.py",
> line 673, in commit
> self.transaction.commit()
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\session.py",
> line 378, in commit
> self._prepare_impl()
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\session.py",
> line 362, in _prepare_impl
> self.session.flush()
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\session.py",
> line 1356, in flush
> self._flush(objects)
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\session.py",
> line 1434, in _flush
> flush_context.execute()
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\unitofwork.py",
> line 261, in execute
> UOWExecutor().execute(self, tasks)
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\unitofwork.py",
> line 753, in execute
> self.execute_save_steps(trans, task)
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\unitofwork.py",
> line 773, in execute_save_steps
> self.execute_cyclical_dependencies(trans, task, False)
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\unitofwork.py",
> line 789, in execute_cyclical_dependencies
> self.execute(trans, [t], isdelete)
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\unitofwork.py",
> line 753, in execute
> self.execute_save_steps(trans, task)
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\unitofwork.py",
> line 773, in execute_save_steps
> self.execute_cyclical_dependencies(trans, task, False)
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\unitofwork.py",
> line 789, in...
>
> read more »
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
