OperationalError with SQLite with simple update query

[Piotr Dobrogost]

Hi!

When executing below code

DBSession.query(TextValue).\
        filter(Node.id.in_(ids)).\
        update({TextValue.value: appstruct['text_value']},
                     synchronize_session=False)

I get this error:
OperationalError: (sqlite3.OperationalError) near "FROM": syntax error [SQL: u'UPDATE text_values SET value=? FROM nodes WHERE nodes.id IN (?, ?, ?, ?)'] [parameters: (u'zzz', u'1685', u'175', u'1688', u'180')]

Does SA construct query which is not valid in SQLite?

How can I solve this problem?

Regards,
Piotr Dobrogost

[Simon King]

I'm not sure if sqlite supports multi-table updates. Do you know what sort of SQL you are expecting to generate here?

(Note that your query appears at least to be missing a join condition between the TextValue and Node classes)

Simon

[Piotr Dobrogost]

On Wednesday, March 16, 2016 at 3:51:16 PM UTC+1, Simon King wrote:

On Wed, Mar 16, 2016 at 1:43 PM, Piotr Dobrogost <p...@2016.groups.google.dobrogost.net> wrote:

Hi!

When executing below code

DBSession.query(TextValue).\
        filter(Node.id.in_(ids)).\
        update({TextValue.value: appstruct['text_value']},
                     synchronize_session=False)

I get this error:
OperationalError: (sqlite3.OperationalError) near "FROM": syntax error [SQL: u'UPDATE text_values SET value=? FROM nodes WHERE nodes.id IN (?, ?, ?, ?)'] [parameters: (u'zzz', u'1685', u'175', u'1688', u'180')]

 

I'm not sure if sqlite supports multi-table updates. Do you know what sort of SQL you are expecting to generate here?

I would expect "normal" UPDATE with WHERE clause. I'm not sure where does FROM come from here as the new value is given explicitly and not to be read from existing rows.

 

(Note that your query appears at least to be missing a join condition between the TextValue and Node classes)

TextValue is a subclass of Content which is a subclass of Node.

Content declares this:

@classproperty
def __mapper_args__(cls):
    return dict(polymorphic_identity=camel_case_to_name(cls.__name__))

so TextValue and Node should be implicitly joined according to rules for joined table polymorphism in SA.

 

Regards,

Piotr

[Simon King]

Hmm, ok. In that case, does it work if you use "TextValue.id.in_(ids)" rather than Node? I can't tell from your description if the "id" and "value" columns are both present on the TextValue table, or if you actually need to join to the Node class.

It does seem like a bug that SA is generating this SQL.

Simon

[Piotr Dobrogost]

On Wednesday, March 16, 2016 at 2:43:06 PM UTC+1, Piotr Dobrogost wrote:

Hi!

When executing below code

DBSession.query(TextValue).\
        filter(Node.id.in_(ids)).\
        update({TextValue.value: appstruct['text_value']},
                     synchronize_session=False)

I get this error:
OperationalError: (sqlite3.OperationalError) near "FROM": syntax error [SQL: u'UPDATE text_values SET value=? FROM nodes WHERE nodes.id IN (?, ?, ?, ?)'] [parameters: (u'zzz', u'1685', u'175', u'1688', u'180')]

Changing Node.id.in_(ids) to TextValue.id.in_(ids) results in expected and much simpler SQL which is executed without problems:

UPDATE text_values SET value=? WHERE text_values.id IN (?, ?, ?, ?)

(u'zzz', u'1685', u'175', u'1688', u'180')

I used Node.id here as I'm using this for simple selects like this:

first_text = DBSession.query(TextValue).filter(Node.id == ids[0]).one()

without any problems so I thought I can use the same in update. Apparently I can't and I would like to know what makes it invalid when used in update.

Regards,

Piotr Dobrogost

[Piotr Dobrogost]

On Wednesday, March 16, 2016 at 4:41:38 PM UTC+1, Simon King wrote:

Hmm, ok. In that case, does it work if you use "TextValue.id.in_(ids)" rather than Node? I can't tell from your

Yes, this works – see my last post in this thread.

 

description if the "id" and "value" columns are both present on the TextValue table, or if you actually need to join to the Node class.

Both TextValue and Node have "id" column and "value" column is only in TextValue table. To get TextValue object all tables for superclasses of TextValue have to be joined which is how joined table inheritance works.

Abbreviated models below:

class TextValue(Content):
    id = Column(Integer, ForeignKey('contents.id'), primary_key=True)
    value = Column(Unicode)

class Content(Node):

    @classproperty
    def __mapper_args__(cls):
        return dict(polymorphic_identity=camel_case_to_name(cls.__name__))

    id = Column(Integer, ForeignKey('nodes.id'), primary_key=True)
    (...)

class Node(Base, ContainerMixin, PersistentACLMixin):
    __table_args__ = (UniqueConstraint('parent_id', 'name'))

    __mapper_args__ = dict(
        polymorphic_on='type',
        polymorphic_identity='node',
        with_polymorphic='*',
    )

    id = Column(Integer(), primary_key=True)
    type = Column(String(30), nullable=False)
    parent_id = Column(ForeignKey('nodes.id'), index=True)

    (....)

It does seem like a bug that SA is generating this SQL.

I would thought so but I don't know SA to be sure.

Regards,

Piotr 

[Mike Bayer]

On 03/16/2016 11:23 AM, Piotr Dobrogost wrote:
>
> so TextValue and Node should be implicitly joined according to rules for
> joined table polymorphism in SA.

this is not supported - please read the caveats at


http://docs.sqlalchemy.org/en/rel_1_0/orm/query.html?highlight=query.update#sqlalchemy.orm.query.Query.update