On Fri, Jun 14, 2019, at 12:46 PM, Mike Bayer wrote: > > > On Fri, Jun 14, 2019, at 12:22 PM, João Miguel Neves wrote: >> Not performance, actually to avoid a race condition with key/values written >> to JSONB fields. Ended up with the following function that we use for when >> multiple updates at the same time can occur (from the frontend mostly). It's >> based on your recommendations from >> https://groups.google.com/forum/#!topic/sqlalchemy/hjjIyEC8KHQ >> >> When we got 2 requests in parallel (one updating key1 with value1 and >> another updating key2 with value2), the first would write value1, but as the >> second had loaded the JSONB field before, it would write to key2 and then >> save the whole content of the JSONB field without the update to key1 >> effectively losing data in our case. >> >> def atomic_jsonb_set(context, dynamic_column_name, column_id, value): >> id_key = [col.name for col in inspect(context.__class__).primary_key] >> identifier = {key: getattr(context, key) for key in id_key} >> >> DBSession.query(context.__class__).filter_by(**identifier).update( >> { >> dynamic_column_name: func.jsonb_set( >> getattr(context.__class__, dynamic_column_name), >> f"{{{column_id}}}", >> config["sqlalchemy.json_serializer"](value), >> ) >> }, >> synchronize_session="fetch", >> ) > > > OK so the patterns you can use with that are the versioning column, or if you > want the UPDATE to be atomic there might be complications if you are updating > columns in both "person" and "engineer". I might use SELECT..FOR UPDATE for a > pessimistic approach.
oh and by versioning column i mean https://docs.sqlalchemy.org/en/13/orm/versioning.html?highlight=versioning, which is also using regular Session.add() types of patterns, not query.update(). > > > >> >> On Fri, Jun 14, 2019 at 5:07 PM Mike Bayer <mike...@zzzcomputing.com> wrote: >>> __ >>> >>> >>> 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 >>> >>> <https://groups.google.com/d/msgid/sqlalchemy/27f24134-661e-4c16-982a-6983a82609a4%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/CAJGrhWZ_REpV09w5YVz-oY4BoTQjuB2DqTUirwwd8sAweKuHMg%40mail.gmail.com >> >> <https://groups.google.com/d/msgid/sqlalchemy/CAJGrhWZ_REpV09w5YVz-oY4BoTQjuB2DqTUirwwd8sAweKuHMg%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/b743f771-12db-4635-bc2e-5d44ec140ac5%40www.fastmail.com > > <https://groups.google.com/d/msgid/sqlalchemy/b743f771-12db-4635-bc2e-5d44ec140ac5%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/0a138c5b-992a-4410-876b-32351a57d2fb%40www.fastmail.com. For more options, visit https://groups.google.com/d/optout.
