On Mon, Nov 2, 2020, at 10:55 PM, ai.rese...@gmail.com wrote: > 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.
IIUC you are saying that you are still using the after_insert() event and you are emitting a new Insert within the after_insert() event. if it "doesn't work" you would need to use SQL logging or echoing (see https://docs.sqlalchemy.org/en/13/core/engines.html#configuring-logging ) to more carefully observe the statements that are being emitted. You'd want to note that the original INSERT is proceeding as well as the additional INSERT you've added. > > > > 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. this would only make sense if the inactivity occurs while a database connection is checked out from the connection pool. Such as, you have a Session object, you've emitted some SQL on it, and it has not been closed with .close(). attempting to use the connection after 8 hours would raise an error. if the application has been constructed to catch this exception and silently reconnecting, you could observe issues with parts of the "transaction" being lost. that all sounds fairly unlikely though. if pool_recycle is in use, and the new activity at the end of the 8 hour period is that the pool is accessed, the DB connection is recycled and a new one returned, then the new connection has two INSERT statements emitted, there should certainly not be any loss of consistency for that. you'd need to log the SQL and look at what's transpired. > > On Saturday, October 24, 2020 at 10:17:10 PM UTC+8 Mike Bayer wrote: >> >> >> >> >> On Fri, Oct 23, 2020, at 8:30 PM, ai.rese...@gmail.com 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 sqlalchemy+...@googlegroups.com. >>> 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 sqlalchemy+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/a2e372b3-2c98-4699-810a-046009d6f3c8n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/a2e372b3-2c98-4699-810a-046009d6f3c8n%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 sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/d2c42929-6d15-45ee-898a-58a4b2a59a3d%40www.fastmail.com.
