1

I want to update the column c in table t with some function such as unicodedata.normalize 

session.query(t).update({t.c: unicodedata.normalize('NFKC',t.c)})

But in this case I get the error like argument 2 must be str, not InstrumentedAttribute. Which seems that I cannot map the function to the table directly.

I know I can use the examples in this question to update data by session.query and then session.commit. But I still wonder whether I can use some mapping function to realize that.

natsuapo
  • 585
  • 9
  • 22
  • 1
    You're mixing Python and SQL. `unicodedata.normalize()` is a Python function and is evaluated on the spot. It expects a string to normalize, but you've given it an instrumented attribute. In order to form a bulk update query you have to consult your database's documentation for an equivalent SQL function and use SQLAlchemy's [generic functions](http://docs.sqlalchemy.org/en/latest/core/functions.html) to form an SQL function expression. If you're using SQLite, you could perhaps create a user-defined function (see the docs). – Ilja Everilä Jul 24 '17 at 08:35
  • Thanks for your reply. I know Python and SQL are different, but it possible for `sqlalchemy` to map the python function to the data acquired from the database? – natsuapo Jul 24 '17 at 14:24
  • I'm sorry, but there's no data acquired in an UPDATE statement in the sense I think you mean. You send the query to the DB and it performs the operation on the data there. It should be again noted that your call to the Python function is evaluated right there and then, at least the way it is now. It would be helpful if you could include in your question what database you are using. Sqlite for example could actually do what you're after, in a way. – Ilja Everilä Jul 24 '17 at 15:04
  • Of course with the ORM you could fetch the objects, update each instance's c and commit. SQLAlchemy would issue required updates for you. – Ilja Everilä Jul 24 '17 at 18:46
  • Thanks a lot for your answering. I am using MySQL and I think there is no such kind of function according to https://dev.mysql.com/worklog/task/?id=2048 – natsuapo Jul 25 '17 at 00:14

0 Answers0