Hi, I have a situation where an update tries to update the wrong table on when a column comes from the parent table and is not on the current table. I'll grant I didn't quite understand all the caveats in https://docs.sqlalchemy.org/en/13/orm/query.html#sqlalchemy.orm.query.Query.update so let me apologise if it's written there - then I just need a pointer in the right direction. Here is some sample code (mostly adapted from https://stackoverflow.com/questions/44183500/problems-with-update-and-table-inheritance-with-sqlalchemy - the code won't work on sqlite).
import os import sys from sqlalchemy import Column, create_engine, ForeignKey, Integer, String, DateTime from sqlalchemy.orm import sessionmaker from sqlalchemy.sql import func, update from sqlalchemy.ext.declarative import declarative_base try: os.remove('test.db') except FileNotFoundError: pass engine = create_engine('postgresql+psycopg2://user:pass@server/database', echo=True) Session = sessionmaker(engine) Base = declarative_base() class People(Base): __tablename__ = 'people' discriminator = Column('type', String(50)) __mapper_args__ = {'polymorphic_on': discriminator} id = Column(Integer, primary_key=True) name = Column(String(50)) updated = Column(DateTime, server_default=func.now(), onupdate=func.now()) class Engineer(People): __tablename__ = 'engineer' __mapper_args__ = {'polymorphic_identity': 'engineer'} id = Column(Integer, ForeignKey('people.id'), primary_key=True) kind = Column(String(100), nullable=True) Base.metadata.create_all(engine) session = Session() e = Engineer() e.name = 'Mike' session.add(e) session.flush() session.commit() # works when updating the object e.name = "Doug" session.add(e) session.commit() # works using the base class for the query count = session.query(People).filter( People.name == 'Doug').update({People.name: 'James'}) # fails when using the derived class count = session.query(Engineer).filter(Engineer.id == People.id, Engineer.name == 'James', ).update({Engineer.name: 'Mary'}) session.commit() print("Count: {}".format(count)) ---- The error message is: Traceback (most recent call last): File "/home/joao/testes/sa-bugs/.ve/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context cursor, statement, parameters, context File "/home/joao/testes/sa-bugs/.ve/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 550, in do_execute cursor.execute(statement, parameters) psycopg2.errors.UndefinedColumn: column "name" of relation "engineer" does not exist LINE 1: UPDATE engineer SET name='Mary', updated=now() FROM people W... ^ The above exception was the direct cause of the following exception: Traceback (most recent call last): File "update-inheritance.py", line 63, in <module> Engineer.name == 'James', ).update({Engineer.name: 'Mary'}) File "/home/joao/testes/sa-bugs/.ve/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3824, in update update_op.exec_() File "/home/joao/testes/sa-bugs/.ve/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line 1673, in exec_ self._do_exec() File "/home/joao/testes/sa-bugs/.ve/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line 1866, in _do_exec self._execute_stmt(update_stmt) File "/home/joao/testes/sa-bugs/.ve/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line 1678, in _execute_stmt self.result = self.query._execute_crud(stmt, self.mapper) File "/home/joao/testes/sa-bugs/.ve/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3356, in _execute_crud return conn.execute(stmt, self._params) File "/home/joao/testes/sa-bugs/.ve/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 988, in execute return meth(self, multiparams, params) File "/home/joao/testes/sa-bugs/.ve/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File "/home/joao/testes/sa-bugs/.ve/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement distilled_params, File "/home/joao/testes/sa-bugs/.ve/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context e, statement, parameters, cursor, context File "/home/joao/testes/sa-bugs/.ve/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception util.raise_from_cause(sqlalchemy_exception, exc_info) File "/home/joao/testes/sa-bugs/.ve/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 383, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "/home/joao/testes/sa-bugs/.ve/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 128, in reraise raise value.with_traceback(tb) File "/home/joao/testes/sa-bugs/.ve/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context cursor, statement, parameters, context File "/home/joao/testes/sa-bugs/.ve/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 550, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column "name" of relation "engineer" does not exist LINE 1: UPDATE engineer SET name='Mary', updated=now() FROM people W... ^ [SQL: UPDATE engineer SET name=%(people_name)s, updated=now() FROM people WHERE engineer.id = people.id AND people.name = %(name_1)s] [parameters: {'people_name': 'Mary', 'name_1': 'James'}] (Background on this error at: http://sqlalche.me/e/f405) I can get the correct table to update through: table = Engineer.name.property.columns[0].table And the following update works (albeit with a slightly different semantics): update(table).where(table.c.name == 'James').values({table.c.name : 'Mary'}) >From what I've read, adding the People.id == Engineer.id should be enough, but it seems like I missed some detail. Can you help? Thanks in advance for any help/pointers, João -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/4b8377e7-3fff-4750-aebf-aaa5eaac35da%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.