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, >> <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. For more options, visit https://groups.google.com/d/optout.
