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/CAJGrhWbmMb1PPyLSkp7AjDDFkZmEnPG%2BejmLoui_urtzZKOhrw%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
