Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Activity
    Federico Caselli
    @CaselIT
    right after db.execute or in a context manager?
    if it's in a context manager it may be due to this fastapi issue: tiangolo/fastapi#5707
    Davide Riccardo Caliendo
    @drc0
    the order is
    db.execute(sql)
    db.commit()
    db is retrieved exactly with this first method (yield) https://fastapi.tiangolo.com/tutorial/dependencies/dependencies-with-yield/
    the fact is that we don't see any exception, data 'silently' not updating
    Federico Caselli
    @CaselIT
    so you don't have commit in the context manager?
    Davide Riccardo Caliendo
    @drc0
    does that try: finally: creates a context manager?
    Federico Caselli
    @CaselIT
    yes
    Davide Riccardo Caliendo
    @drc0

    the flow is

    @controller.post("") 
    def something(db: Session = Depends(get_db)): 
        try:
            callsomething(db) 
        except Exception:
            raise HTTPException(
                status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                detail="server_error",
            )
    
    def callsomething(db): 
       res = db.execute(f"select mytable") 
      db.execute("update mytable .... {res[0]} .... update mytable2...") 
      db.commit() 
      return True

    where get_db is:

    SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine) 
    def get_db(): 
      db = SessionLocal() 
      try: 
        yield 
        db 
      finally: 
        db.close()

    yes

    oh so that could probably be the case of that bug you linked?

    but isn't that strange I get no exception?
    Federico Caselli
    @CaselIT
    Looking at the code, I don't think so, since you are calling commit right after execute, so db is not something else
    Davide Riccardo Caliendo
    @drc0
    what do you mean by db is not something else?
    Federico Caselli
    @CaselIT
    that the commit() is issued on the same connection that did the update

    Looking at the code, I don't think so, since you are calling commit right after execute, so db is not something else

    than again issue like this were never reported to sqlalchemy, and a missed commit would be something that gets reported, so I'm inclined to believe that fastapi is at least part of the cause

    could you create a discussion with the example above (runnable would be better) so we can try to better debug it?
    Davide Riccardo Caliendo
    @drc0
    do you mean on the fastapi channel? the problem is that I don't have a reproducible bug...
    Federico Caselli
    @CaselIT
    no on the sqlalchemy github discussion
    Davide Riccardo Caliendo
    @drc0
    ok
    question: does db.execute respects sessionmaker(autocommit=False,... ) ?
    Federico Caselli
    @CaselIT
    yes
    Davide Riccardo Caliendo
    @drc0
    ok thanks
    An Yu
    @an_yu_twitter
    Is there a way to have the results of an INTERSECT return with field names?
    Federico Caselli
    @CaselIT
    Davide Riccardo Caliendo
    @drc0
    Davide Riccardo Caliendo
    @drc0

    I noticed that we have a middleware that does

    db = SessionLocal()
    try:
       response = await call_next(request)
       ...
    except:
        if something_based_on_current_route: 
          try:
          finally:
             db.close()
    return response

    and call_next (the route) has the previous get_db as dependency, could this lead to some problem?

    Davide Riccardo Caliendo
    @drc0
    that db.close worries me, expecially because it is in a if that is not always executed, but I'm not sure how that could impact sqlalchemy connection pool, with SessionLocal being SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
    Eric Weaver
    @weevaa

    hey all I'm trying to find out how to load relationships in order in which they were related.

    For example, I have a user who has multiple phones,
    I have a one-to-many relationship with the phone_table,

    so phone1 has an id of 1 and phone 2 has an id of say 15. Whenever I add both phones to user 1, the order will be phone1 then phone2.

    If i wanted to switch the order to be phone 2 then phone 1 how can I do it?

    to be fair this is not the "default" behaviour, some users will want the first phone to be first and additional phones listed after which is why I'm trying to figure out the best way to accomplish a set order for each user.

    bringing this one back up any ideas?

    Federico Caselli
    @CaselIT
    using the order_by field of the relationship is not an option?
    Eric Weaver
    @weevaa
    not every user will want it to function the same way
    which is why i was trying to see if it could be sorted based on when it was added... like a standard list..
    Federico Caselli
    @CaselIT
    If you use an autoincrement primary key you can order on that and it will be in insert order
    But if users need to use custom ordering than I don't think it's something that's supported
    At least not using the standard loading options. If you do a manual join with contains eager that should work
    Eric Weaver
    @weevaa
    ty
    Davide Riccardo Caliendo
    @drc0
    what are the drawbacks of using pool_pre_ping=True and could it solve some specific problems?
    Eric Vanular
    @ericvanular

    Hi all, I'm trying to use an Association Proxy to access subclasses from the other side of a many-to-many relationship.

    From a User table, I have an association object User_ContactMethods with foreign keys to User and ContactMethods. The ContactMethods Base is then inherited by Email, Phone, etc as a joined table inheritance. Basically what I'd like is to access User.emails, User.phones, etc

    I'm very close but when I try to access these relationships currently I get null values in User.emails, User.phones, etc corresponding to where there are User_ContactMethods records that might be an Email ContactMethod but not a Phone ContactMethod.

    Any ideas? Is this even possible? Ideally I could create, read, update, delete contact methods directly from the User object

    Eric Vanular
    @ericvanular

    class User(Base):
    ...

    user_contact_methods = relationship('UserContactMethod', back_populates='user', cascade='all, delete-orphan')
    emails = association_proxy('user_contact_methods', 'email', cascade_scalar_deletes=True)
    phones = association_proxy('user_contact_methods', 'phone', cascade_scalar_deletes=True)

    class UserContactMethod(Base):
    ...

    user = relationship('User', back_populates='user_contact_methods')
    address = relationship('Address', back_populates='user_contact_methods')
    email = relationship('Email', back_populates='user_contact_methods')

    class ContactMethod(Base):
    ...

    __mapper_args__ = {'polymorphic_identity': 'contact_method', 'polymorphic_on': type}

    class Address(ContactMethod):
    ...

    user_contact_methods = relationship('UserContactMethod', back_populates='address', cascade='all, delete')
    parties = association_proxy('user_contact_methods', 'party', cascade_scalar_deletes=True)

    class email(ContactMethod):
    ...

    user_contact_methods = relationship('UserContactMethod', back_populates='email', cascade='all, delete')
    parties = association_proxy('user_contact_methods', 'party', cascade_scalar_deletes=True)

    An Yu
    @an_yu_twitter
    This may be pretty basic, but for some reason I can't find an example of this in docs or tutorials..
    When using sqlalchemy 2.0 syntax with (session.execute(query)), how might I select from a subquery?
    eg. pseudocode:
    sub_query = select(MyTable).where(MyTable.someField > 1).subquery()
    query = select(MyTable).where(MyTable.author_id in sub_query)
    ...
    An Yu
    @an_yu_twitter
    ^ The issue I hit with that is
    argument of type 'Subquery' is not iterable
    Federico Caselli
    @CaselIT
    you can't use column in subquery
    1 reply
    you need to use column.in_(subquery)
    Nicolas Erlijman
    @nerlijma
    is sql alchemy 1.4 asyncio recommended for production?
    lowercase00
    @lowercase00

    Hey, quick question on executing raw queries with the session (and not the engine).
    Shouldn't this work? The session.execute method also receives *kw, I though it was the same interface as the engine.connection()?

    result = db.session.execute(text("SELECT * FROM user WHERE user_id=:user_id"), user_id=10)`

    It gives me a StatementError saying that a value ir required for bind parameter, it is as if it doesn't understand the combination of :param and param=value
    I'm I doing something wrong?

    The docs mention this using the connection:
    t = text("SELECT * FROM users WHERE id=:user_id")
    result = connection.execute(t, user_id=12)
    Federico Caselli
    @CaselIT
    that's deprecated and IIRC only available on the connection. pass a dict {'user_id':10}
    lowercase00
    @lowercase00
    thanks, will do
    lowercase00
    @lowercase00
    Also, MyPy has been complaining about the lastrowid attribute of a Result object, after executing an INSERT statement (currently using PyMySQL). I couldn't find the attribute in the source code indeed, but the code works (I can get the row id), wondering if this is just missing from the stubs2
    Nicolas Erlijman
    @nerlijma

    is sql alchemy 1.4 asyncio recommended for production?

    anyone?

    Federico Caselli
    @CaselIT
    there haven't been any serious reports on the async part, and it's undoubtedly used a lot. the main issue is that outsude postgres the async drivers are a bit spotty
    2 replies