Re: [sqlalchemy] OperationalError with SQLite with simple update query
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
--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] OperationalError with SQLite with simple update query
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
--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] OperationalError with SQLite with simple update query
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 -- 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 https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] OperationalError with SQLite with simple update query
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
--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] OperationalError with SQLite with simple update query
On Wed, Mar 16, 2016 at 3:23 PM, Piotr Dobrogost <
p...@2016.groups.google.dobrogost.net> wrote:
> 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.
>
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
--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] OperationalError with SQLite with simple update query
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')]
>
> Does SA construct query which is not valid in SQLite?
> How can I solve this problem?
>
> Regards,
> Piotr Dobrogost
>
>
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
--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
