Updating another table in "before-insert" event

[ai.rese...@gmail.com]

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.

[Mike Bayer]

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.

[ai.rese...@gmail.com]

Thank you very much. Perfectly worked.

[ai.rese...@gmail.com]

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:

[Mike Bayer]

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/a2e372b3-2c98-4699-810a-046009d6f3c8n%40googlegroups.com.