Re: [sqlalchemy] Session management for general functions within a class

[Andrew Martin]

Hi Simon, thank you for your thoughts. Sorry about the incomplete code. 
This project has gotten out of control, and I'm tired and a little burned 
out and have a launch deadline for Monday, and I was hoping this would be 
enough to uncover some basic stupidity in my approach. As long as I don't 
care about code being duplicated in lots of places, it all works. But 
refactoring to clean things up is uncovering some fundamental lack of 
understanding about how this stuff works.

The session is about as basic as it gets from a utility function.

from sqlalchemy import create_engine
def get_db_session() -> Session:
engine = create_engine(
f"postgresql://{settings.PG_USER}:{settings.PG_PASS}@{settings.PG_DSN}:{settings.PG_PORT}/{settings.DATABLENDER_DB}"
 
# noqa: E501
)
session = Session(engine)
return session

So in my Mixin class there's just 

from app.utils import get_db_session

and self.db_session = get_db_session()

Everything else is working from that. I'll get a complete working example 
up tonight or tomorrow. It's gotten complex because some of the logic is 
distributed and in Airflow DAGs and tasks and stuff. It's not super easy to 
pull out a class and some models and have something to demonstrate. And I 
sure as hell didn't want to just dump the whole thing on people here and be 
like, "Hey can you fix this for me?" lol!

On Friday, April 29, 2022 at 5:51:26 AM UTC-5 Simon King wrote:

> It's difficult to debug this without a script that we can run to reproduce 
> the problem. What kind of object is self.db_session? You use it as a 
> context manager without calling it, so I don't think it can be a 
> sessionmaker or a session.
>
> You're nesting calls to the context manager:
>
> # in load_new_data
> with self.db_session as outersession:
> # add new_obj to outersession
> # call move_to_error
> with self.db_session as innersession:
> # add new_obj to innersession
>
> Are innersession and outersession supposed to be the same object? If they 
> are different sessions, you're trying to add new_obj to both of them, which 
> is going to be a problem.
>
> If it were me, I would explicitly pass the session to the move_to_error 
> method. If you don't like that, you can also use 
> sqlalchemy.orm.object_session to get the session that new_obj already 
> belongs to.
>
> Hope that helps,
>
> Simon
>
> On Fri, Apr 29, 2022 at 5:10 AM Andrew Martin  wrote:
>
>> Hi all, I'm struggling a bit with best practices for my ETL application.
>>
>> Each part of the ETL app is completely separate from the others, but I 
>> have a MixIn for some common functions that each of them need to do, like 
>> move this record to error if there's a data integrity problem. Or move this 
>> record to manual review if there's insufficient data to move it along to 
>> the next stage of the ETL.
>>
>> The problem I'm having is that I don't understand the correct way to pass 
>> an object to a function, update it, and eventually commit it.
>>
>> I have for example:
>>
>> class DataMoverMixin:
>> def __init__(self) -> None:
>> self.db_session = get_db_session()
>> 
>>
>> self.move_to_error(obj: Any, error_stage: str, traceback: Exception) 
>> -> bool:
>> logger.info("Moving object to error.")
>> json_data = json.dumps(obj, cls=AlchemyEncoder)
>> e = Error(
>> id=obj.id,
>> error_stage=error_stage,
>> error_message=repr(traceback),
>> error_data=json_data,
>> )
>> obj.status = "error"
>> with self.db_session as session:
>> session.add(e)
>> session.add(obj)
>> session.commit()
>> logger.info("Successfully moved object to error.")
>> return True
>>
>> class IngestDataManager(DataMoverMixin):
>> def __init__(self):
>> super().__init__()
>> 
>>
>>
>> def load_new_data(self, accounts: List[Dict]) -> bool:
>> for acc in accounts:
>> new_obj = NewObj(**acc)
>> with self.db_session as session:
>> session.add(new_obj)
>> session.commit()
>> # now the raw data is loaded, I need to check if it 
>> conforms and do some stuff  with the newly created id. 
>> session.refresh(new_obj)
>> if not new_obj.important_stuff:
>>  self.move_to_error(new_obj, 
>> "ingest_integrity_error", f"missing {important stuff} for account_id: {
>> new_obj.id}
>>
>>
>> This is the simplest example of what does and doesn't work. And I can 
>> tell from the errors that I must be doing something very anti pattern, but 
>> I can't quite figure out what.
>>
>> This pattern gives me a DetachedInstanceError.
>>
>> So if I change Mixin.move_to_error like so:
>>
>> . . . 
>> with self.db_session as session:
>> session.refresh(obj)
>> obj.status = "error"
>> session.add(e)
>> session.add(obj)
>> 

[sqlalchemy] Problem with PG ARRAY's ANY() and SA 1.4.36 (maybe related resolution of #6515 ?)

[Lele Gaifax]

Hi all,

I'm hitting what is either a regression or a "deprecated" usage of PG
ARRAY's any() method, trying to upgrade an app of mine from SA 1.4.35 to
SA 1.4.36.

The issue is within a quite complex query, with a filter expression like
the following:

  query = query.filter(((CC.languages == None) | 
CC.languages.any(sa.bindparam('language'

where CC.languages is a sqlalchemy.dialects.postgresql.ARRAY(UnicodeText()) 
column,
and the language bindparam is the two-letter code of a language, say
"it" or "en".

That query works great since years in production using SA 1.3, and also
in my work-in-progress dev branch using 1.4.x, producing something like

  ...
  AND (cc.languages IS NULL OR %(language)s = ANY (cc.languages))
  ...

Today I tried to upgrade the dev branch to SA 1.4.36 and several tests
failed, because that filter is rendered as

  ...
  AND (cc.languages IS NULL OR %(language)s::TEXT[] = ANY (cc.languages))
  ...

In the note that follows
https://docs.sqlalchemy.org/en/14/core/type_basics.html#sqlalchemy.types.ARRAY.Comparator.any
I read that the any() method "is superceded by" the any_() function, so
maybe that's what I should do... but given that the above idiom has
worked well for me for so long, this may very well be an unintended
side effect of 
https://github.com/sqlalchemy/sqlalchemy/commit/63191fbef63ebfbf57e7b66bd6529305fc62c605.

Thank you for any hint,
ciao, lele.
-- 
nickname: Lele Gaifax | Dire che Emacs è "conveniente" è come
real: Emanuele Gaifas | etichettare l'ossigeno come "utile"
l...@etour.tn.it  |   -- Rens Troost

-- 
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/87bkwjpzl0.fsf%40metapensiero.it.

Re: [sqlalchemy] Session within a session

[Jason Hoppes]

Sorry I got diverted on another project. I will try this later and let you 
know. Thanks for your help.

- Jason

On Wednesday, April 20, 2022 at 8:45:39 PM UTC-4 Mike Bayer wrote:

> you can work with multiple sessions and their objects simultaneously, with 
> the provision that you don't add() an object from one session directly into 
> the other one, without detaching it from the original session first.  to 
> transfer the state of an object from one session to another without 
> detaching it, use the session.merge() method.
>
> more important is that when you work with these sessions, the sessions are 
> not being simultaneously accessed by other concurrent tasks such as those 
> in other threads, greenlets or async coroutines.  if the two sessions are 
> local to the current task/thread/whatever, just keep track of which objects 
> are from which and it will be fine.
>
> On Wed, Apr 20, 2022, at 11:28 AM, Jason Hoppes wrote:
>
> I have an object that adds a user. I have another Object that gets cipher 
> information for that user's password. Each one having their own session. 
> Could I call the object that gets the cipher with a separate session with 
> the session that I add the user? Effectively it does a lookup using an 
> inner session.
>
> Thanks in advance for your help.
>
> - Jason
>
>
> -- 
> 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/dea3c02c-1dce-4496-9b6d-11b6a7b0bafbn%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/a6b0d488-cbcc-42e8-80a5-19ec67cc4f88n%40googlegroups.com.

Re: [sqlalchemy] query many-many with asssociation table

[Jason Hoppes]

Thanks Simon this worked.

- Jason

On Wednesday, April 6, 2022 at 6:26:51 AM UTC-4 Simon King wrote:

> I think it should work if you join to the *relationship* explicitly
>
> ie.
>
> session.query(User).join(User.user_groups).filter(...)
>
> Hope that helps,
>
> Simon
>
>
> On Tue, Apr 5, 2022 at 9:48 PM Jason Hoppes  
> wrote:
>
>> I want to select all users in a particular group. I have a users table, 
>> user_groups table, and a users_group_users_asc table to associate the two. 
>> Note this is not a self referencing relationship there are three different 
>> tables involved not two like the example in the documentation. I have the 
>> following configuration:
>>
>> user_groups_users = Table('user_groups_users_asc', Base.metadata,
>>   Column('user_group_id', ForeignKey('
>> user_groups.id', ondelete='CASCADE')),
>>   Column('user_id', ForeignKey('users.id', 
>> ondelete='CASCADE'))
>> )
>>
>> class User(Base):
>> __tablename__ = 'users'
>> id_ = Column('id', BigInteger, primary_key=True)
>> username = Column('username', String(255))
>> user_groups = relationship('UserGroup',
>>secondary=user_groups_users,
>>back_populates='users)
>>
>> class UserGroup(Base):
>> __tablename__ = 'user_groups'
>> id_ = Column('id', BigInteger, primary_key=True)
>> group_name = Column('group_name', String(255), nullable=False)
>> description = Column('description', Text)
>> users = relationship('User',
>>  secondary=user_groups_users,
>>  back_populates='user_groups',
>>  passive_deletes=True)
>>
>> As I suspected the following query gives me an error:
>>
>> session.query(User).join(UserGroup).filter(UserGroup.group_name == 
>> grp_name).all()
>>
>> Don't know how to join to . Please 
>> use the .select_from() method to establish an explicit left side, as well 
>> as providing an explicit ON clause if not present already to help resolve 
>> the ambiguity.
>>
>> Thank you in advance for your help.
>>
>> - Jason
>>
>> -- 
>> 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/43edd22c-7eca-427b-907e-57e20d665f6en%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/f9804fdc-2e9e-42b2-8de0-529d7b9ff7f7n%40googlegroups.com.

Re: [sqlalchemy] query many-many with asssociation table

[Hoppes, Jason]

Thank you Simon you solved my problem


On Wed, Apr 6, 2022 at 6:26 AM Simon King  wrote:

> I think it should work if you join to the *relationship* explicitly
>
> ie.
>
> session.query(User).join(User.user_groups).filter(...)
>
> Hope that helps,
>
> Simon
>
>
> On Tue, Apr 5, 2022 at 9:48 PM Jason Hoppes <
> jason.hop...@stormfish-sci.com> wrote:
>
>> I want to select all users in a particular group. I have a users table,
>> user_groups table, and a users_group_users_asc table to associate the two.
>> Note this is not a self referencing relationship there are three different
>> tables involved not two like the example in the documentation. I have the
>> following configuration:
>>
>> user_groups_users = Table('user_groups_users_asc', Base.metadata,
>>   Column('user_group_id', ForeignKey('
>> user_groups.id', ondelete='CASCADE')),
>>   Column('user_id', ForeignKey('users.id',
>> ondelete='CASCADE'))
>> )
>>
>> class User(Base):
>> __tablename__ = 'users'
>> id_ = Column('id', BigInteger, primary_key=True)
>> username = Column('username', String(255))
>> user_groups = relationship('UserGroup',
>>secondary=user_groups_users,
>>back_populates='users)
>>
>> class UserGroup(Base):
>> __tablename__ = 'user_groups'
>> id_ = Column('id', BigInteger, primary_key=True)
>> group_name = Column('group_name', String(255), nullable=False)
>> description = Column('description', Text)
>> users = relationship('User',
>>  secondary=user_groups_users,
>>  back_populates='user_groups',
>>  passive_deletes=True)
>>
>> As I suspected the following query gives me an error:
>>
>> session.query(User).join(UserGroup).filter(UserGroup.group_name ==
>> grp_name).all()
>>
>> Don't know how to join to . Please
>> use the .select_from() method to establish an explicit left side, as well
>> as providing an explicit ON clause if not present already to help resolve
>> the ambiguity.
>>
>> Thank you in advance for your help.
>>
>> - Jason
>>
>> --
>> 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/43edd22c-7eca-427b-907e-57e20d665f6en%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 a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/iMWS9o8vcmw/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CAFHwexdfe3cz%2BFxiLLzAHhAswb%2BY1WEKf8ACxqYoDYU5qExK5g%40mail.gmail.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/CAEkvBWURKWWdEg%2BoREdfxMAXWAf0dhJt67YQev%2BQ6BngQrdawQ%40mail.gmail.com.

Re: [sqlalchemy] Session management for general functions within a class

[Simon King]

It's difficult to debug this without a script that we can run to reproduce
the problem. What kind of object is self.db_session? You use it as a
context manager without calling it, so I don't think it can be a
sessionmaker or a session.

You're nesting calls to the context manager:

# in load_new_data
with self.db_session as outersession:
# add new_obj to outersession
# call move_to_error
with self.db_session as innersession:
# add new_obj to innersession

Are innersession and outersession supposed to be the same object? If they
are different sessions, you're trying to add new_obj to both of them, which
is going to be a problem.

If it were me, I would explicitly pass the session to the move_to_error
method. If you don't like that, you can also use
sqlalchemy.orm.object_session to get the session that new_obj already
belongs to.

Hope that helps,

Simon

On Fri, Apr 29, 2022 at 5:10 AM Andrew Martin  wrote:

> Hi all, I'm struggling a bit with best practices for my ETL application.
>
> Each part of the ETL app is completely separate from the others, but I
> have a MixIn for some common functions that each of them need to do, like
> move this record to error if there's a data integrity problem. Or move this
> record to manual review if there's insufficient data to move it along to
> the next stage of the ETL.
>
> The problem I'm having is that I don't understand the correct way to pass
> an object to a function, update it, and eventually commit it.
>
> I have for example:
>
> class DataMoverMixin:
> def __init__(self) -> None:
> self.db_session = get_db_session()
> 
>
> self.move_to_error(obj: Any, error_stage: str, traceback: Exception)
> -> bool:
> logger.info("Moving object to error.")
> json_data = json.dumps(obj, cls=AlchemyEncoder)
> e = Error(
> id=obj.id,
> error_stage=error_stage,
> error_message=repr(traceback),
> error_data=json_data,
> )
> obj.status = "error"
> with self.db_session as session:
> session.add(e)
> session.add(obj)
> session.commit()
> logger.info("Successfully moved object to error.")
> return True
>
> class IngestDataManager(DataMoverMixin):
> def __init__(self):
> super().__init__()
> 
>
>
> def load_new_data(self, accounts: List[Dict]) -> bool:
> for acc in accounts:
> new_obj = NewObj(**acc)
> with self.db_session as session:
> session.add(new_obj)
> session.commit()
> # now the raw data is loaded, I need to check if it
> conforms and do some stuff  with the newly created id.
> session.refresh(new_obj)
> if not new_obj.important_stuff:
>  self.move_to_error(new_obj, "ingest_integrity_error",
> f"missing {important stuff} for account_id: {new_obj.id}
>
>
> This is the simplest example of what does and doesn't work. And I can tell
> from the errors that I must be doing something very anti pattern, but I
> can't quite figure out what.
>
> This pattern gives me a DetachedInstanceError.
>
> So if I change Mixin.move_to_error like so:
>
> . . .
> with self.db_session as session:
> session.refresh(obj)
> obj.status = "error"
> session.add(e)
> session.add(obj)
> session.commit()
> . . .
>
> I get no error. But also the changes to the obj are not actually committed
> to the DB.
> The new record for error is committed.
>
> My expectation was that by attaching the session to the class that any
> method on the class would reference the same session, and that using the
> context manager was just a good practice to open and close it. But that
> doesn't seem to be the case.
>
> I might certainly be wrong, but it appears that when you pass an
> SQLAlchemy object to a function inside of a session context manager, it
> does not carry the session with it?
>
> And also reopening what I think is the session in a context manager fixes
> that but also then doesn't allow me to update the object?
>
> I guess I'm just kinda confused, and I'm sure there's a better way to do
> this.
>
> I've searched around a lot to try and understand this problem, but for
> whatever reason, nothing has clicked for me about what I'm doing wrong.
>
> Appreciate any help from people.
>
> -andrew
>
>
> --
> 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
>