Re: [sqlalchemy] Updating another table in "before-insert" event

2020-11-03 Thread 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.





> 
> 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 == 
>> ).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
>>>  
>>> .
>> 
> 

> -- 
> 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

Re: [sqlalchemy] Updating another table in "before-insert" event

2020-11-02 Thread 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:

>
>
>
>
> 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 == 
> ).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
>  
> 
> .
>
>
>

-- 
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.

Re: [sqlalchemy] Updating another table in "before-insert" event

2020-10-25 Thread ai.rese...@gmail.com 
Thank you very much. Perfectly worked.

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 == 
> ).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
>  
> 
> .
>
>
>

-- 
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/91372b8c-e4e0-40bd-b8bc-593fb6d5053cn%40googlegroups.com.

Re: [sqlalchemy] Updating another table in "before-insert" event

2020-10-24 Thread Mike Bayer 




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 == 
).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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/a83bf57a-0c87-479d-89e9-fc009daa7c92n%40googlegroups.com
>  
> .

-- 
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/94751719-67f1-4164-96ec-f8c7b4d60daf%40www.fastmail.com.

[sqlalchemy] Updating another table in "before-insert" event

2020-10-23 Thread 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.

-- 
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/a83bf57a-0c87-479d-89e9-fc009daa7c92n%40googlegroups.com.