Hi Thank you again for the solution. I have tried and it's working well. Just I noticed that when I update the second table (as mentioned in this thread), there is no issues. But if I try to insert a new row in the second table (in the same way using connection and not session), sometimes it works and sometime it doesn't work without raising any exceptions or complaints. May I have your advice on this? What is the difference between updating a row or inserting a new row in the second table.
As an additional piece of info, this happens only after a long period of inactivity. Let's say for more than 8 hours, there is no db transactions. Then I try to insert a row to table A, in before_insert listener I add another row to table B (using connection and not session). Surprisingly the row A is inserted and row B is not inserted. FYI I'm using MySQL and I pass pool_recycle=18000 while creating the engine. On Saturday, October 24, 2020 at 10:17:10 PM UTC+8 Mike Bayer wrote: > > > > > On Fri, Oct 23, 2020, at 8:30 PM, [email protected] wrote: > > > Hi all > > I want to update a table row once I create a new row in another table. > These 2 tables are in the same database, but no relationship between them > > I use "before_insert" listener for the first table. In this function, I > load the target object instance from the second table and try to update it. > After update, if I flush the session, I got this error that the session is > already flushing which is reasonable. On the other hand, if I don't flush > the session, the change on the second table is not applied. I appreciate > your kind advice on this issue. > > > So I would imagine you'd want to use the "after_insert" event here since > you want to update this row *after* you created the other new row. > > when you're in before_insert /after_insert etc., there's a Connection > there, emit Core update statements on that. you can't perform session > persistence operations inside of these functions. > > connection.execute(target_table.update().where(target_table.c.id == > <id>).values(foo='bar')) > > > > > > > > > > > > > > > > -- > 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 [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/a83bf57a-0c87-479d-89e9-fc009daa7c92n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/a83bf57a-0c87-479d-89e9-fc009daa7c92n%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > > -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/a2e372b3-2c98-4699-810a-046009d6f3c8n%40googlegroups.com.
