Hi Mike, Thank you very much for the quick response!
Is there any other way to find the right table from the model other than somthing like Engineer.name.property.columns[0].table? (I'm trying to do an update in a codepath that can have several different models passed to it) TIA, João On Fri, Jun 14, 2019 at 4:04 PM Mike Bayer <mike...@zzzcomputing.com> wrote: > > > On Fri, Jun 14, 2019, at 10:30 AM, João Miguel Neves wrote: > > 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). > > > > Hi there - > > yes the caveats refer to the joined inheritance situation as where there > are intricacies here. > > Your UPDATE statement refers to the "name" column which is part of People, > so you need to format your UPDATE statement in terms of the "people" table, > which means the second query is not supported. It at least has to be > against "People" otherwise the UPDATE is only targeting the "engineer" > table. > > if you want to limit the "name" updates to only those rows that are > Engineer, use the discriminator column to filter to People.type == > "engineer". > > > > > zi > ---- > > 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 > <https://groups.google.com/d/msgid/sqlalchemy/4b8377e7-3fff-4750-aebf-aaa5eaac35da%40googlegroups.com?utm_medium=email&utm_source=footer> > . > For more options, visit https://groups.google.com/d/optout. > > > -- > 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/085a157e-cda0-478a-b4a8-04903d69b780%40www.fastmail.com > <https://groups.google.com/d/msgid/sqlalchemy/085a157e-cda0-478a-b4a8-04903d69b780%40www.fastmail.com?utm_medium=email&utm_source=footer> > . > For more options, visit https://groups.google.com/d/optout. > -- 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/CAJGrhWYeQFCpOiwbtToqnVTq8qy%2Bya3V7NUmmotY7%2Ber-0xYvg%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
