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 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 1281, in _save_obj
(state_str(state), instance_key, state_str(existing)))
FlushError: New instance <Person at 0x10dea30> with identity key (<class
'ta_sa_model_declarative.BizEntity'>, (1,)) conflicts with persistent
instance <Company at 0x10def50>
I've also attempted to define the relation from the Company side, with
basically the same results. I'm obviously doing something wrong here, but I
just don't know what. This is the first time I've really used SA 0.5, and I
haven't used 0.4 in a long while...so I may be trying to do something the
"0.4 way" without realizing it.
Is there a problem with my model? Am I using the model incorrectly?
Where am I going wrong?
NOTE: I did upgrade to 0.5.6 from 0.5.5
Kevin Horn
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
