hybrid expression setter system in query.update() ?

[Jean-Sébastien Suzanne]

Hi Mike,

I have a second request with the report #3228

this is the example:

from sqlalchemy import Column, Integer, String, create_engine, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.hybrid import hybrid_property


engine = create_engine('sqlite:///memory')
Base = declarative_base()


class Person(Base):
    __tablename__ = 'person'

    id = Column(Integer, primary_key=True)
    first_name = Column(String(64))
    last_name = Column(String(64))

    @hybrid_property
    def name(self):
        return self.first_name + ' ' + self.last_name

    @name.setter
    def name(self, value):
        self.first_name, self.last_name = value.split(' ', 1)

    @name.expression
    def name(cls):
        return func.concat(cls.first_name, ' ', cls.last_name)


Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)

session = Session()
t = Person(name='James BOND')
session.add(t)
session.query(Person).update({Person.name: 'Dr. No'})

And the traceback:

Traceback (most recent call last):
  File "/Users/jssuzanne/erpblok/anyblok/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1022, in _execute_context
    context)
  File "/Users/jssuzanne/erpblok/anyblok/sqlalchemy/lib/sqlalchemy/engine/default.py", line 436, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: near "(": syntax error

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "./bin/python", line 63, in <module>
    exec(compile(__file__f.read(), __file__, "exec"))
  File "hybrid_property.py", line 37, in <module>
    session.query(Person).update({Person.name: 'Dr. No'})
  File "/Users/jssuzanne/erpblok/anyblok/sqlalchemy/lib/sqlalchemy/orm/query.py", line 2848, in update
    update_op.exec_()
  File "/Users/jssuzanne/erpblok/anyblok/sqlalchemy/lib/sqlalchemy/orm/persistence.py", line 906, in exec_
    self._do_exec()
  File "/Users/jssuzanne/erpblok/anyblok/sqlalchemy/lib/sqlalchemy/orm/persistence.py", line 1036, in _do_exec
    update_stmt, params=self.query._params)
  File "/Users/jssuzanne/erpblok/anyblok/sqlalchemy/lib/sqlalchemy/orm/session.py", line 982, in execute
    bind, close_with_result=True).execute(clause, params or {})
  File "/Users/jssuzanne/erpblok/anyblok/sqlalchemy/lib/sqlalchemy/engine/base.py", line 800, in execute
    return meth(self, multiparams, params)
  File "/Users/jssuzanne/erpblok/anyblok/sqlalchemy/lib/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/Users/jssuzanne/erpblok/anyblok/sqlalchemy/lib/sqlalchemy/engine/base.py", line 897, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/Users/jssuzanne/erpblok/anyblok/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1029, in _execute_context
    context)
  File "/Users/jssuzanne/erpblok/anyblok/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1230, in _handle_dbapi_exception
    exc_info
  File "/Users/jssuzanne/erpblok/anyblok/sqlalchemy/lib/sqlalchemy/util/compat.py", line 188, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
  File "/Users/jssuzanne/erpblok/anyblok/sqlalchemy/lib/sqlalchemy/util/compat.py", line 181, in reraise
    raise value.with_traceback(tb)
  File "/Users/jssuzanne/erpblok/anyblok/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1022, in _execute_context
    context)
  File "/Users/jssuzanne/erpblok/anyblok/sqlalchemy/lib/sqlalchemy/engine/default.py", line 436, in do_execute
    cursor.execute(statement, parameters)
  sqlalchemy.exc.OperationalError: (OperationalError) near "(": syntax error 'UPDATE person SET concat(first_name, ?, last_name)=?' ('Dr. No', ' ')

I think the better way is adding new callback in hybrid_property like

@name.update_expression
def name(cls, value):
    first_name, last_name = value.split(' ', 1)
    return cls.first_name = first_name, cls.last_name = last_name

Regard,

[Mike Bayer]

I was sort of waiting for you to ask about that, as I found it very unusual that a hybrid is used in the first place as the target of an update; hybrids are supposed to be composite expressions. I only fixed #3228 as opposed to closing it as "wontfix" because I saw that the interpretation of string attributes was wrong across the board, not just for hybrids.

as it stands, this is pure feature request for hybrids and I don't have an API in mind at the moment, we can't just hardcode "hybrid" logic into persistence.py, as hybrids are an extension (and there was even a time when the whole point of them were that they were "simple accessors").

a feature like this would need to be addressed in a much more fundamental way, at the Core level as we are really seeking to implement behavior for the SQL assignment operator.

[Mike Bayer]

well let me walk back the last paragraph; as this is composite behavior, we still handle composite column behaviors at the ORM level. so this stays as ORM.

[Jean-Sébastien Suzanne]

Hi, I understand that my ask is very unusual. I use use the hybrid property as pur functional column. I know it is not really a column but i would like see as a column.

[Mike Bayer]

well no, this could be core. If we did in fact implement an assignment operator, it would be hit right here:

diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index a6c30b7..88826b7 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -1888,10 +1888,11 @@ class SQLCompiler(Compiled):
         text += ' SET '
         include_table = extra_froms and \
             self.render_table_with_column_in_update_from
+
         text += ', '.join(
-            c[0]._compiler_dispatch(self,
-                                    include_table=include_table) +
-            '=' + c[1] for c in crud_params
+            set_clause._compiler_dispatch(self,
+                                    include_table=include_table, **kw)
+            for set_clause in crud_set_clauses
         )

         if self.returning or update_stmt._returning:

that is, we'd need to produce something like a SetClause object, which would be delivered via a new operator (this would be in default_operators really but the idea is):

diff --git a/lib/sqlalchemy/sql/operators.py b/lib/sqlalchemy/sql/operators.py
index 9453563..260a003 100644
--- a/lib/sqlalchemy/sql/operators.py
+++ b/lib/sqlalchemy/sql/operators.py
@@ -560,6 +560,9 @@ class ColumnOperators(Operators):
         the parent object, given the collation string."""
         return self.operate(collate, collation)

+    def set(self, value):
+        return SetClause(self, value)
+
     def __radd__(self, other):
         """Implement the ``+`` operator in reverse.

hybrids can deliver this using the existing hybrid_property.comparator hook.

we'd then have to unwrap these SetClauses somehow for expiration:

diff --git a/lib/sqlalchemy/orm/persistence.py b/lib/sqlalchemy/orm/persistence.py
index 114b79e..67d817f 100644
--- a/lib/sqlalchemy/orm/persistence.py
+++ b/lib/sqlalchemy/orm/persistence.py
@@ -1076,9 +1076,8 @@ class BulkUpdateEvaluate(BulkEvaluate, BulkUpdate):
     def _additional_evaluators(self, evaluator_compiler):
         self.value_evaluators = {}
         for key, value in self.values.items():
-            key = self._resolve_key_to_attrname(key)
-            if key is not None:
-                self.value_evaluators[key] = evaluator_compiler.process(
+            for subkey in self._resolve_key_to_attrnames(key):
+                self.value_evaluators[subkey] = evaluator_compiler.process(
                     expression._literal_as_binds(value))

[Mike Bayer]

this would be a big shift in behavior so keep it on a major version. if we have time after 1.0's other issues are in we can consider moving this to 1.0.

[Jean-Sébastien Suzanne]

Think, your implementation seem better than mine.

I understand and I agree to wait after the 1.0

[Mike Bayer]

the word "composite" here had me thinking this was related to orm.composite(), this is hybrids.

[Mike Bayer]

it's in both for hybrids and composites.

[Mike Bayer]

Support hybrids/composites with bulk updates

The :meth:.Query.update method can now accommodate both hybrid attributes as well as composite attributes as a source of the key to be placed in the SET clause. For hybrids, an additional decorator :meth:.hybrid_property.update_expression is supplied for which the user supplies a tuple-returning function.

Change-Id: I15e97b02381d553f30b3301308155e19128d2cfb Fixes: #3229

→ <<cset 1fcbc17b7dd5>>