Thanks Michael.
On Saturday, 21 November 2015 22:35:14 UTC, Michael Bayer wrote:
>
>
>
> On 11/20/2015 12:20 PM, Martin Pengelly-Phillips wrote:
> > Hi there,
> >
> > Using SQLAlchemy 1.0.9
> >
> > I am dealing with some legacy code and came across the following issue.
> > It appears that in a polymorphic relationship SQLAlchemy is not able to
> > correctly determine what to load for a relationship when using a
> > subselect (due to limit on query) and child class has a different field
> > name for the primary key.
> >
> > Here is a reproducible test case to show the issue better:
> >
> > |
> > fromuuid importuuid1 asuuid
> >
> > importsqlalchemy
> > importsqlalchemy.orm
> > fromsqlalchemy import(
> > Column,Unicode,ForeignKey,CHAR,Boolean,UniqueConstraint
> > )
> > fromsqlalchemy.orm importrelationship,backref,synonym
> > fromsqlalchemy.ext.declarative importdeclarative_base,declared_attr
> >
> >
> > Base=declarative_base()
> >
> >
> > classContext(Base):
> > '''Represent a context.'''
> >
> > __tablename__ ='context'
> >
> > context_type =Column(Unicode(32),nullable=False)
> >
> > __mapper_args__ ={
> > 'polymorphic_on':context_type,
> > 'polymorphic_identity':'context'
> > }
> >
> > id =Column(CHAR(36),primary_key=True,default=lambda:str(uuid()))
> >
> >
> > classTask(Context):
> > '''Represent a task.'''
> >
> > __tablename__ ='task'
> >
> > __mapper_args__ ={
> > 'polymorphic_identity':'task'
> > }
> >
> > # Change this and references to it to 'id' to fix issue.
> > task_id =Column(
> > CHAR(36),
> > ForeignKey('context.id'),
> > primary_key=True
> > )
> >
> > scopes =relationship('Scope',secondary='task_scope')
> >
> >
> > classScope(Base):
> > '''Represent a Scope.'''
> >
> > __tablename__ ='scope'
> >
> > id =Column(CHAR(36),primary_key=True,default=lambda:str(uuid()))
> >
> >
> > classTaskScope(Base):
> > '''Represent a relation between a scope and a task.'''
> >
> > __tablename__ ='task_scope'
> >
> > id =Column(CHAR(36),primary_key=True,default=lambda:str(uuid()))
> >
> > scope_id =Column(CHAR(36),ForeignKey(Scope.id),nullable=False)
> >
> > task_id =Column(CHAR(36),ForeignKey(Task.task_id),nullable=False)
> >
> >
> > defmain():
> > '''Execute test.'''
> > engine =sqlalchemy.create_engine('sqlite://')
> > Base.metadata.create_all(engine)
> > Session=sqlalchemy.orm.sessionmaker(bind=engine)
> >
> > session =Session()
> >
> > task =Task()
> > scope =Scope()
> >
> > session.add(task)
> > session.add(scope)
> > session.commit()
> >
> > link =TaskScope(scope_id=scope.id,task_id=task.task_id)
> > session.add(link)
> > session.commit()
> >
> > query =session.query(Task).options(
> > sqlalchemy.orm.load_only('context_type'),
> > sqlalchemy.orm.joinedload('scopes').load_only()
> > ).limit(10)
> > printquery
> >
> > results =query.all()
> > printresults
> >
> >
> > if__name__ =='__main__':
> > main()
> > |
> >
> > Running the above gives:
> >
> > |
> > sqlalchemy.exc.OperationalError:(sqlite3.OperationalError)nosuch
> > column:task.task_id [SQL:u'SELECT anon_1.context_context_type AS
> > anon_1_context_context_type, anon_1.context_id AS anon_1_context_id,
> > anon_2.scope_1_id AS scope_1_id \nFROM (SELECT context.context_type AS
> > context_context_type, context.id AS context_id \nFROM context JOIN task
> > ON context.id = task.task_id\n LIMIT ? OFFSET ?) AS anon_1 LEFT OUTER
> > JOIN (SELECT task_scope_1.id AS task_scope_1_id, task_scope_1.scope_id
> > AS task_scope_1_scope_id, task_scope_1.task_id AS task_scope_1_task_id,
> > scope_1.id AS scope_1_id \nFROM task_scope AS task_scope_1 JOIN scope
> AS
> > scope_1 ON scope_1.id = task_scope_1.scope_id) AS anon_2 ON
> task.task_id
> > = anon_2.task_scope_1_task_id'][parameters:(10,0)]
> > |
> >
> >
> > If you change 'task_id' to 'id' (and references to it) then the query
> > will now work correctly.
>
> this is a bug and requires five separate conditions to all be in place
> at the same time in order to occur. This is issue
>
> https://bitbucket.org/zzzeek/sqlalchemy/issues/3592/logic-to-ensure-parent-cols-present-in
>
> fixed in 60c36ca8418cec18073 and 7998f15b1687a0bd0b3 for rel_1_0. For
> workaround, don't exclude the attribute that's needed for the
> joinedload(), eg:
>
> query = session.query(Task).options(
> sqlalchemy.orm.load_only('context_type', 'task_id'),
> sqlalchemy.orm.joinedload('scopes').load_only()
> ).limit(10)
>
>
>
>
>
> >
> > As I mentioned this is legacy code so there are many references to
> > task_id throughout the code base unfortunately. I have found a
> > workaround by using a synonym, but wanted to report the issue to see if
> > the failure is expected and there is a requirement for the primary key
> > field names to match or not.
> >
> > cheers,
> >
> >
> > Martin
> >
> > --
> > You received this message because you are subscribed to the Google
> > Groups "sqlalchemy" group.
> > To unsubscribe from this group and stop receiving emails from it, send
> > an email to sqlalchemy+...@googlegroups.com <javascript:>
> > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > Visit this group at http://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/d/optout.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
