On Mon, Jun 17, 2019, at 12:02 PM, João Miguel Neves wrote: > Hi, > > Ok, versioning adds a different requirement level, as it fails if the version > being updated has changed. I was looking for a situation where updating 2 > keys inside a JSONB field wouldn't lose one of them. Using versioning it > raises an exception when writing one of them (which is better than the > previous situation where it silently lost one of the updates, but slightly > worst than the update with synchronize_session if it would work with > inheritance). I'm assuming there's no other alternative? > > > Thanks in advance, > João > > On Fri, Jun 14, 2019 at 5:52 PM João Miguel Neves > <joao.silva.ne...@gmail.com> wrote: >> Cool, wasn't aware of that feature! Thanks! >> >> On Fri, Jun 14, 2019 at 5:50 PM Mike Bayer <mike...@zzzcomputing.com> wrote: >>> __ >>> >>> >>> 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 >>> >>> <https://groups.google.com/d/msgid/sqlalchemy/0a138c5b-992a-4410-876b-32351a57d2fb%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/CAJGrhWayTL0b8kHW_S3tm_bT%2BQARepP4-z%2BdC02ijLPg-LYsWQ%40mail.gmail.com > > <https://groups.google.com/d/msgid/sqlalchemy/CAJGrhWayTL0b8kHW_S3tm_bT%2BQARepP4-z%2BdC02ijLPg-LYsWQ%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/826b0700-d674-4c9a-be58-550266601629%40www.fastmail.com. For more options, visit https://groups.google.com/d/optout.
