hybrid expression setter system in query.update() ?
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,
Comments (12)
-
repo owner -
repo owner 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.
-
repo owner - changed milestone to 1.x.xx
- changed component to orm
- changed title to composite setter system in query.update() ?
- marked as proposal
-
reporter 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.
-
repo owner 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))
-
repo owner - changed milestone to 1.1
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.
-
reporter Think, your implementation seem better than mine.
I understand and I agree to wait after the 1.0
-
repo owner - changed milestone to 1.2
-
repo owner - changed title to hybrid expression setter system in query.update() ?
the word "composite" here had me thinking this was related to orm.composite(), this is hybrids.
-
repo owner it's in both for hybrids and composites.
-
repo owner - changed status to resolved
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>>
-
repo owner - marked as enhancement
- Log in to comment
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
#3228as 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.