On Fri, Jun 14, 2019, at 11:49 AM, João Miguel Neves wrote: > 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)
you're doing query.update() for performance reasons, right? yeah you probably have to do something like that, or just use regular session patterns to update data. > > 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 > > <https://groups.google.com/d/msgid/sqlalchemy/CAJGrhWYeQFCpOiwbtToqnVTq8qy%2Bya3V7NUmmotY7%2Ber-0xYvg%40mail.gmail.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/27f24134-661e-4c16-982a-6983a82609a4%40www.fastmail.com. For more options, visit https://groups.google.com/d/optout.
