Re: [sqlalchemy] Re: ObjectDeletedError in master/slave configuration

[Jonathan Vanasco]

On Thursday, March 26, 2020 at 2:53:08 PM UTC-4, Mike Bayer wrote:
>
> is the issue that your follower database is only updating 
> asynchronously?   I would likely organize the application to simply use two 
> different Session objects, one for master one for follower.   Trying to do 
> it on a per-query basis is needlessly implicit and complicated.
>

 In my applications, I have explicit Primary/Writer and Secondary/Reader 
sessions.  As a convenience, I will create a shortcut to proxy one of them 
onto a request(web)/context object as `.dbSession`.  Certain logic flows 
will only utilize the Primary/Writer, other logic flows will only utilize 
the Secondary/Reader.

-- 
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/5a92dc19-0d7a-41e0-bc6f-47b96ac1d32b%40googlegroups.com.

Re: [sqlalchemy] Re: ObjectDeletedError in master/slave configuration

[Mike Bayer]

On Thu, Mar 26, 2020, at 2:45 PM, Colton Allen wrote:
> Aside from disabling expire_on_commit, any thoughts on how I can prevent this 
> error? I guess I just need a method to force the attribute refresh to use the 
> master database. I'm just not sure where I should put that. Thoughts?

is the issue that your follower database is only updating asynchronously? I 
would likely organize the application to simply use two different Session 
objects, one for master one for follower. Trying to do it on a per-query basis 
is needlessly implicit and complicated. Individual service methods would 
declare whether they are "read only" or "writer" and they start out with one or 
the other session/engine. That's how we do it in Openstack. thinking in terms 
of logical service methods rather than individual queries is the way to go.


> 
> I think one of my previous comments got lost because of formatting. Quoting 
> it here for safety.
> 
> "I agree with your assessment. I think its because every time I call 
> "session". I'm actually saying "scoped_session(session_maker)()". So the 
> _flushing attribute will be reset because its a new session instance."
> 
> 
> 
> On Thursday, March 26, 2020 at 1:35:14 PM UTC-5, Mike Bayer wrote:
>> 
>> 
>> On Thu, Mar 26, 2020, at 2:18 PM, Colton Allen wrote:
>>> > You can adjust `expire_on_commit` if you're only doing short-term 
>>> > read-only actions.
>>> 
>>> Can you expand on this? Or link to docs/blog so I can do some research. 
>>> Google hasn't helped me so far. Why would I want to expire after every 
>>> commit?
>> 
>> because once the transaction is completed, there are other transactions 
>> going on in a database concurrently which can change the state of the 
>> objects as they are represented in the database. in order that when you next 
>> access these local objects so that they have the correct state, they are 
>> automatically expired. However this behavior is not desirable in many cases, 
>> so this flag is very commonly used to disable this behavior when you are not 
>> concerned about your objects having stale data relative to other 
>> transactions going on, when the new transaction begins.
>> 
>> this behavior is described at: 
>> https://docs.sqlalchemy.org/en/13/orm/session_basics.html#committing
>> 
>> 
>> 
>> 
>>> 
>>> ---
>>> 
>>> I agree with your assessment. I think its because every time I call 
>>> "session". I'm actually saying "session_maker()". So the _flushing 
>>> attribute will be reset because its a new session instance.
>>> 
>>> On Thursday, March 26, 2020 at 1:02:18 PM UTC-5, Jonathan Vanasco wrote:
 My first guess is two things are going on:
 
 1. This is a behavior of `expire_on_commit` on the session. Once you 
 commit on the Primary database, the object is stale.
 https://docs.sqlalchemy.org/en/13/orm/session_api.html
 
 2. The session is then trying to read off a Secondary database, but the 
 row has not yet synced.
 
 You can adjust `expire_on_commit` if you're only doing short-term 
 read-only actions. However, I would explore to ensure this is trying to 
 read off the other database and why.
>>> 

>>> --
>>> 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 sqlal...@googlegroups.com.
>>> To view this discussion on the web visit 
>>> https://groups.google.com/d/msgid/sqlalchemy/8185b96d-35cb-4973-a9cf-1e572ebd643b%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/dd5c4d05-5af3-451e-b965-52ac50b432ac%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  

Re: [sqlalchemy] Re: ObjectDeletedError in master/slave configuration

[Colton Allen]

Aside from disabling expire_on_commit, any thoughts on how I can prevent 
this error?  I guess I just need a method to force the attribute refresh to 
use the master database.  I'm just not sure where I should put that.  
Thoughts?

I think one of my previous comments got lost because of formatting.  
Quoting it here for safety.

"I agree with your assessment.  I think its because every time I call 
"session".  I'm actually saying "scoped_session(session_maker)()".  So the 
_flushing attribute will be reset because its a new session instance."

 

On Thursday, March 26, 2020 at 1:35:14 PM UTC-5, Mike Bayer wrote:
>
>
>
> On Thu, Mar 26, 2020, at 2:18 PM, Colton Allen wrote:
>
> > You can adjust `expire_on_commit` if you're only doing short-term 
> read-only actions.
>
> Can you expand on this?  Or link to docs/blog so I can do some research.  
> Google hasn't helped me so far.  Why would I want to expire after every 
> commit?
>
>
> because once the transaction is completed, there are other transactions 
> going on in a database concurrently which can change the state of the 
> objects as they are represented in the database.  in order that when you 
> next access these local objects so that they have the correct state, they 
> are automatically expired.   However this behavior is not desirable in many 
> cases, so this flag is very commonly used to disable this behavior when you 
> are not concerned about your objects having stale data relative to other 
> transactions going on, when the new transaction begins.
>
> this behavior is described at: 
> https://docs.sqlalchemy.org/en/13/orm/session_basics.html#committing
>
>
>
>
>
> ---
>
> I agree with your assessment.  I think its because every time I call 
> "session".  I'm actually saying "session_maker()".  So the _flushing 
> attribute will be reset because its a new session instance.
>
> On Thursday, March 26, 2020 at 1:02:18 PM UTC-5, Jonathan Vanasco wrote:
>
> My first guess is two things are going on:
>
> 1. This is a behavior of `expire_on_commit` on the session.  Once you 
> commit on the Primary database, the object is stale.
>https://docs.sqlalchemy.org/en/13/orm/session_api.html
>
> 2. The session is then trying to read off a Secondary database, but the 
> row has not yet synced.
>
> You can adjust `expire_on_commit` if you're only doing short-term 
> read-only actions. However,  I would explore to ensure this is trying to 
> read off the other database and why.
>
>
> --
> 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 sqlal...@googlegroups.com .
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/8185b96d-35cb-4973-a9cf-1e572ebd643b%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/dd5c4d05-5af3-451e-b965-52ac50b432ac%40googlegroups.com.

Re: [sqlalchemy] Re: ObjectDeletedError in master/slave configuration

[Mike Bayer]

On Thu, Mar 26, 2020, at 2:18 PM, Colton Allen wrote:
> > You can adjust `expire_on_commit` if you're only doing short-term read-only 
> > actions.
> 
> Can you expand on this? Or link to docs/blog so I can do some research. 
> Google hasn't helped me so far. Why would I want to expire after every commit?

because once the transaction is completed, there are other transactions going 
on in a database concurrently which can change the state of the objects as they 
are represented in the database. in order that when you next access these local 
objects so that they have the correct state, they are automatically expired. 
However this behavior is not desirable in many cases, so this flag is very 
commonly used to disable this behavior when you are not concerned about your 
objects having stale data relative to other transactions going on, when the new 
transaction begins.

this behavior is described at: 
https://docs.sqlalchemy.org/en/13/orm/session_basics.html#committing




> 
> ---
> 
> I agree with your assessment. I think its because every time I call 
> "session". I'm actually saying "session_maker()". So the _flushing attribute 
> will be reset because its a new session instance.
> 
> On Thursday, March 26, 2020 at 1:02:18 PM UTC-5, Jonathan Vanasco wrote:
>> My first guess is two things are going on:
>> 
>> 1. This is a behavior of `expire_on_commit` on the session. Once you commit 
>> on the Primary database, the object is stale.
>> https://docs.sqlalchemy.org/en/13/orm/session_api.html
>> 
>> 2. The session is then trying to read off a Secondary database, but the row 
>> has not yet synced.
>> 
>> You can adjust `expire_on_commit` if you're only doing short-term read-only 
>> actions. However, I would explore to ensure this is trying to read off the 
>> other database and why.
> 

> --
>  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/8185b96d-35cb-4973-a9cf-1e572ebd643b%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/2ae3206b-d8e6-43ef-a661-b52bcc88b6db%40www.fastmail.com.

Re: 'alembic current' crashes with stack trace

[Mike Bayer]

Somewhere in your model, likely inside of a Table() object, there is a keyword 
"index" being used which is invalid. Look for that keyword and remove it.





On Thu, Mar 26, 2020, at 1:53 PM, Rob Schottland wrote:
> Suddenly, I can't get alembic (1.3.3?). alembic history does work, but my 
> most recent migration crashes with an enormous stack-trace (as usual): here's 
> my current pretty-simple migration script:
> 
>> """Citation datasource optional
>> 
>> Revision ID: f5a6e316ee8d
>> Revises: 2a181e783d96
>> Create Date: 2020-03-26 08:54:59.547135
>> 
>> """
>> 
>> # revision identifiers, used by Alembic.
>> revision = 'f5a6e316ee8d'
>> down_revision = '2a181e783d96'
>> branch_labels = None
>> depends_on = None
>> 
>> from alembic import op
>> import sqlalchemy as sa
>> 
>> 
>> def upgrade():
>>  op.alter_column('citation', 'id_datasource',
>>  existing_Type=sa.INTEGER(),
>>  nullable=True)
>>  pass
>> 
>> 
>> def downgrade():
>>  op.alter_column('citation', 'id_datasource',
>>  existing_Type=sa.INTEGER(),
>>  nullable=False)
>>  pass
> 
> Here's the last stanza of the stack trace:
> 
>  File 
> "/home/rschottland/.local/share/virtualenvs/astorb_db-u7fxujye/lib/python3.7/site-packages/sqlalchemy/sql/base.py",
>  line 289, in _validate_dialect_kwargs
>  "named _, got '%s'" % k
> 
> 
> TypeError: Additional arguments should be named _, got 
> 'index'
> 
> The whole 69 line stack trace is attached.
> 
> This is the 30th migration for this database going back to 2015. Nothing like 
> this has occurred before. Could my alembic package be corrupted? The previous 
> (successful) igration was only 3 days ago.
> 
> Any thoughts on this would be appreciated.
> 
> 
> 
> 
> 

> --
>  You received this message because you are subscribed to the Google Groups 
> "sqlalchemy-alembic" group.
>  To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy-alembic+unsubscr...@googlegroups.com.
>  To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy-alembic/619800be-2eb5-4728-a695-5000c3668646%40googlegroups.com
>  
> .
> 
> 
> *Attachments:*
>  * alembic_stacktrace.txt

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy-alembic/97174b7c-874f-434e-be53-c4996a9045c0%40www.fastmail.com.

[sqlalchemy] Re: ObjectDeletedError in master/slave configuration

[Jonathan Vanasco]

By default, SqlAlchemy has `expire_on_commit=True`. 

I'm going to poorly restate most of what Mike Bayer has told me in the 
past:  the rationale behind this- an active Session is supposed to mirror 
the current state in the database; within a transaction we know the object 
values are equal to the table values.  Once the transaction ends via 
`commit`, the object is no longer in sync - another transaction may have 
modified that database row.  

In your example, note these 2 lines from the stacktrace:

File "sqlalchemy/orm/attributes.py", line 669, in get
  value = state._load_expired(state, passive)

when you invoked a `commit`, SqlAlchemy expired the object (after the 
commit).

when you invoked `print()`, SqlAclhemy recognized the object as expired and 
is now trying to load the expired attributes so it can print them for you.

-- 
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/bdb5dde6-ff15-472d-a3da-4aa5b980fdfc%40googlegroups.com.

[sqlalchemy] Re: ObjectDeletedError in master/slave configuration

[Colton Allen]

> You can adjust `expire_on_commit` if you're only doing short-term 
read-only actions.

Can you expand on this?  Or link to docs/blog so I can do some research.  
Google hasn't helped me so far.  Why would I want to expire after every 
commit?

---

I agree with your assessment.  I think its because every time I call 
"session".  I'm actually saying "session_maker()".  So the _flushing 
attribute will be reset because its a new session instance.

On Thursday, March 26, 2020 at 1:02:18 PM UTC-5, Jonathan Vanasco wrote:
>
> My first guess is two things are going on:
>
> 1. This is a behavior of `expire_on_commit` on the session.  Once you 
> commit on the Primary database, the object is stale.
>https://docs.sqlalchemy.org/en/13/orm/session_api.html
>
> 2. The session is then trying to read off a Secondary database, but the 
> row has not yet synced.
>
> You can adjust `expire_on_commit` if you're only doing short-term 
> read-only actions. However,  I would explore to ensure this is trying to 
> read off the other database and why.
>

-- 
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/8185b96d-35cb-4973-a9cf-1e572ebd643b%40googlegroups.com.

[sqlalchemy] Re: ObjectDeletedError in master/slave configuration

[Jonathan Vanasco]

My first guess is two things are going on:

1. This is a behavior of `expire_on_commit` on the session.  Once you 
commit on the Primary database, the object is stale.
   https://docs.sqlalchemy.org/en/13/orm/session_api.html

2. The session is then trying to read off a Secondary database, but the row 
has not yet synced.

You can adjust `expire_on_commit` if you're only doing short-term read-only 
actions. However,  I would explore to ensure this is trying to read off the 
other database and why.

-- 
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/7cdd3a62-63d3-4b3c-8366-4748fc2256cf%40googlegroups.com.

[sqlalchemy] ObjectDeletedError in master/slave configuration

[Colton Allen]

Hi,

I'm using a custom session class to route requests to different database 
engines based on the type of action being performed.  Master for writes; 
slave for reads.  It looks my attributes on my models expire immediately 
after creation.  Anyway to prevent this?  Or should I not worry about 
preventing it the expiration?

Session class:

class RoutedSession(Session):

def execute(self, *args, **kwargs):
return super().execute(*args, **kwargs, use_master=True)

def get_bind(self, mapper=None, clause=None, use_master=False):
if use_master or self._flushing:
return ENGINES['master']
return random.choice(ENGINES['slaves'])

Application code:

model = MyModel()
session.add(model)
session.commit()

print(model.id)  # ObjectDeletedError: Instance '' has been 
deleted, or its row is otherwise not present.

Traceback:

File "tests/mock.py", line 2414, in populate_db_with_mock_data
  contact_id=contact2.id, account_id=account.id,
File "sqlalchemy/orm/attributes.py", line 275, in __get__
  return self.impl.get(instance_state(instance), dict_)
File "sqlalchemy/orm/attributes.py", line 669, in get
  value = state._load_expired(state, passive)
File "sqlalchemy/orm/state.py", line 632, in _load_expired
  self.manager.deferred_scalar_loader(self, toload)
File "sqlalchemy/orm/loading.py", line 985, in load_scalar_attributes
  raise orm_exc.ObjectDeletedError(state)


-- 
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/7409fb50-53b3-4505-bb2c-e81d8002ecba%40googlegroups.com.

Re: [sqlalchemy] Cleaning metadata

[Javier Collado Jiménez]

Thank you Mike!
I'm going to try this different approach.

El miércoles, 25 de marzo de 2020, 13:30:47 (UTC+1), Mike Bayer escribió:
>
>
>
> On Wed, Mar 25, 2020, at 6:27 AM, Javier Collado Jiménez wrote:
>
> Hello, 
> I'm having a problem trying to cleanup sqlalchemy objects. My application 
> has a thread which handles DB connections. In some cases the thread dies 
> and I want to do a cleanup so, next time the thread is started it would be 
> able to reconnect again.
> The steps I tried are:
> self.metadata.clear()
> self.engine.dispose()
> self.session.close()
> self.conn.close()
> But when I start a new thread, errors like this appear:
> sqlalchemy.exc.ArgumentError: Column object 'column' already assigned to 
> Table 'table'
>
>
> you should not be calling metadata.clear() and it looks like you are doing 
> something where you are building Table objects on the fly, re-using the 
> same Column objects a second time, leading to the error you are seeing.  
> There should be a single MetaData object that is not part of an object, 
> e.g. it's a global variable in your program (e.g. not "self.metadata").
>
> Similarly for the engine, there should not be a "self.engine", there 
> should be a single Engine object that is also a global variable in your 
> program.  It is also threadsafe and can be shared among multiple threads.
>
>
>
> --
> 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 sqlal...@googlegroups.com .
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/74117457-9968-4aa2-afa3-8ccb91e86937%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/34e8c406-eb2e-45a2-bbfe-91e443da26d6%40googlegroups.com.