Where communities thrive

  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
    Jonathan Vanasco
    That's a third party library that hasn't been updated in over 4 years
    Tomas Wilhelmsson
    hey! Im trying to build a table with some properties from two tables, ive got this now and it does just what i want .. only problem is that its dog slow ... any better way to do the same thing ? https://paste.ofcode.org/VVtKByEvTkuBHwGDXY9757
    Federico Caselli
    you may create a select and map that to __table__

    I've been a relatively casual user of SQLALchemy until now, but I'm trying to really get a grip on Session behavior. I'm having trouble understanding a description of Session identity map behavior in the "Querying (1.x Style)" section of the documentation here: https://docs.sqlalchemy.org/en/14/orm/session_basics.html

    The description I'm struggling with is this:

    When ORM objects are returned in results, they are also stored in the identity map. When an incoming database row has a primary key that matches an object which is already present, the same object is returned, and those attributes of the object which already have a value are not re-populated.

    I understand that first sentence - but I am struggling with the second one. It seems like its describing the behavior when a returned row has a database identity that matches an object that is already in the identity map...and maybe how it resolves conflicts if their attributes do not have matching values - but I can't quite understand it.

    Can somebody reword that - or even better - suggest a code snippet that I can run to better understand what is going on? Thanks

    Gord Thompson
    @alovicious72 - Does this help?
    engine1 = sa.create_engine(connection_uri)
    engine2 = sa.create_engine(connection_uri)
    class Thing(Base):
        __tablename__ = "thing"
        id = Column(Integer, primary_key=True, autoincrement=False)
        txt = Column(String(50))
    with Session(engine1) as session1:
        session1.add(Thing(id=1, txt="from session1"))
        thing1 = session1.get(Thing, 1)  # re-retrieve object into identity map
        with engine2.begin() as conn2:
            # simulate update from external process
            conn2.exec_driver_sql("UPDATE thing SET txt = '***updated***' WHERE id = 1")
        x = session1.execute(select(Thing).where(Thing.id == 1)).scalar()
        print(f"x and thing1 {'ARE' if x == thing1 else 'are NOT'} the same object")
        # x and thing1 ARE the same object
        print(f"thing1.txt is '{thing1.txt}'")
        # thing1.txt is 'from session1'
        session1.expire(thing1)  # force reload on subsequent access
        print(f"thing1.txt is '{thing1.txt}'")
        # thing1.txt is '***updated***'
    Hi @gordthompson Thanks for posting this! So - would "thing1.txt == x.txt" evaluate to "True" just before the "session1.expire(thing1)" executes? I expect so (given that "x" and "thing1" are the same object)...but I am going to fire up my IDE and run this to find out. I think I understand what is happening here, but what to run that particular test to be sure.

    @gordthompson Yes, thankfully (though not unexpectedly...as "thing1" and "x" are the same object) - their "txt" attributes have the same value. OK, so I think I understand what's going on here...and here is how I know have rewritten that 2nd sentence in my own mind:

    When an incoming database row has a primary key that matches an object which is already present, the same object is returned, and those attributes of the object which already have a value will continue to return that value - even if the value returned by the row is different.

    (In effect, the returned row does not overwrite the existing attribute value - should one already be set - on the object.

    Have I got it right?

    Gord Thompson
    @alovicious72 - Sounds right to me.
    Great. Thank you, @gordthompson
    Hello everyone
    i'm new here, first time creating a REST API
    and it seens to be working fine when I use postman(GET/POST/DELETE), but i get into CORS problem when my website (another python flask bootstrap) tries to run it
    William Edwards
    Hi. I have a model (inherited from declarative_base), and I'd like to check if I'm working with the model I expect. I currently have 'if str(model) == "app.models.Cluster":' where model is a model derived from declarative_base - is there a more elegant way to check equality?
    Federico Caselli
    are you looking for isinstance?
    William Edwards
    Probably; but it doesn't work for me. print(model,Cluster,type(model),type(Cluster),isinstance(model, Cluster),str(model)) yields <class 'app.models.Cluster'> <class 'app.models.Cluster'> <class 'sqlalchemy.ext.declarative.api.DeclarativeMeta'> <class 'sqlalchemy.ext.declarative.api.DeclarativeMeta'> False <class 'app.models.Cluster'> - not sure why isinstance evaluates to False as the type and string representation are the same
    1 reply
    Federico Caselli
    well model there is a class, just an alias of Cluster, not an instance of it. you can just use ==. model == Cluster. you should probably not call it model, maybe something like modelCls or something
    2 replies
    Hello, does anyone know how to set the cache to 1 second on tornado_sqlalchemy?
    Jonathan Vanasco
    That sounds like a question for the tornado-sqlalchemy or tornado developers. sqlalchemy doesn't have a timed cache.
    thanks anyway :\
    Hi all, i use sqlacodegen to gen oracle tables
    but it generates Table() instead of class
    even after using sqlacodegen --generator dataclasses
    Alex Grönholm
    @dzpt do your tables have primary keys? those are required for classes
    @agronholm no, thank you, let me set pk for those tables
    hello,does anyone know how to fix this issue which is 'AttributeError: 'Order' object has no attribute '_sa_instance_state''?
    It's just a query segments.
    session.query(models.Order).filter(models.Order.direction == direction).first()
    Federico Caselli
    How is order defined?

    Hi, I am trying to run a SQL raw query in ORM method. But the SQL Alchemy returns error and that query isn't I am expecting. Only issue with the FROM part in the query, My raw query has only one table in FROM keyword and other two tables are in JOIN where as in sqlalchemy the JOIN is okey but two tables coming with FROM keyword.
    SQL Alchemy query:

     result = (
     session.query(ProductionMaster.article, Barcode.size, sa.func.count(Barcode.size))
     .join(Barcode, Barcode.serial_no == ProductionScan.serial_no)
     .join(ProductionMaster, ProductionMaster.prod_code == Barcode.prod_code)
             ProductionScan.date >= "2021-08-01 08:00:00",
             ProductionScan.date <= "2021-08-25 08:00:00",
     .group_by(ProductionMaster.article, Barcode.size)

    Error message I am getting: The multi-part identifier "tbl_ProductionScan.serial_no" could not be bound. (4104) (SQLExecDirectW)')
    returned QUERY with the error message:

    SELECT [Tbl_ProductionMaster].[ProductionName] AS [Tbl_ProductionMaster_ProductionName], [Tbl_barcode].[Size] AS [Tbl_barcode_Size], count([Tbl_barcode].[Size]) AS count_1
    FROM [tbl_ProductionScan], [Tbl_ProductionMaster] 
    JOIN [Tbl_barcode] ON [Tbl_barcode].serial_no = [tbl_ProductionScan].serial_no 
    JOIN [Tbl_ProductionMaster] ON [Tbl_ProductionMaster].[ProductionCode] = [Tbl_barcode].[Product_Code]
    WHERE [tbl_ProductionScan].prod_date >= ? AND [tbl_ProductionScan].prod_date <= ? AND [Tbl_ProductionMaster].[ProductionName] LIKE ? 
    GROUP BY [Tbl_ProductionMaster].[ProductionName], [Tbl_barcode].[Size]

    The actual query I am expecting: (This works fine)

    SELECT [barcode].[dbo].[Tbl_ProductionMaster].[ProductionName]
    FROM [barcode].[dbo].[tbl_ProductionScan]
    INNER JOIN [barcode].[dbo].[Tbl_barcode] ON [barcode].[dbo].[Tbl_barcode].[Serial_no] = [barcode].[dbo].[tbl_ProductionScan].[serial_no]
    INNER JOIN [barcode].[dbo].[Tbl_ProductionMaster] ON [barcode].[dbo].[Tbl_ProductionMaster].[ProductionCode] = [barcode].[dbo].[Tbl_barcode].[Product_code]
    WHERE [barcode].[dbo].[tbl_ProductionScan].[prod_date] BETWEEN '2021-08-01 08:00:00' AND '2021-08-25 08:00:00' AND [barcode].[dbo].[Tbl_ProductionMaster].[ProductionName] Like '%3780%black%'
    GROUP BY [barcode].[dbo].[Tbl_ProductionMaster].[ProductionName], [barcode].[dbo].[Tbl_barcode].[Size]
    9 replies
    Pedro Henrique Altoé
    Hi everyone! Is there a way to change the default db folder on automap from public to something else? My tables are under v2 and when I am trying to map it's being mapped to public therefore I can't access them and am getting an AttributeError
    1 reply

    Hi everyone, i have a question about selecting only few columns from the Parent relationship,

    I have this two tables:

    class Parent(Base):
    tablename = 'parent'
    id = Column(Integer, primary_key=True)
    label = Column(String)
    children = relationship("Child", back_populates="parent")

    class Child(Base):
    tablename = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('public.parent.id'))
    parent = relationship("Parent", back_populates="children")

    when i use

    db.query( models.Parent).first()

    i get the parent object with the list of Children as i was expecting, but what i would like to do is to select only few columns like that:

    db.query( models.Parent.id, models.Parent.children)

    Federico Caselli
    @achko_gitlab look for load_only in the documentation
    Anton Melser

    Hi all. I have a column (postgresql 14) that I thought would update on every row update (1.4.27), declared as follows:

    class utcnow(expression.FunctionElement):  # pylint: disable=W0223
        type = DateTime()
        updated_at = Column(

    This was what I came up with. I had the impression it works... sometimes. What am I missing?

    Federico Caselli
    when does it not updates?
    1 reply
    Aviram Hassan
    How can I specify columns to obtain when using using selectinload?
    Federico Caselli
    you need to combine it with load_only
    Maxwell Taylor
    Hi all, I am interested in using SQLAlchemy with AWS QLDB, if it's possible. Does anyone have experience with this? Thanks :)
    1 reply
    Alex Grönholm
    QLDB doesn't look like a relational database


    I have a problem but I'm not sure whether it belongs here or with sqlalchemy. Once that determined I'll provide the required details.

    Using sqlalchemy's PickleType I'm loading large files BLOBs into Oracle. Unfortunatelly they are too large and I get DPI-1057: buffer size too large.

    From oracle/python-cx_Oracle#480 I learned that streaming writes must be used in order to write LOBs larger than 1073741822B.

    As far as I know cx-Oracle provides DB-API implementation that's used by sqlalchemy, but I'm uncertain on exactly where is the boundary.

    This brings me to my question:
    Does support for streaming writes (to support large LOBs) is something to be implemented in python-cx-Oracle or in sqlalchemy?

    Federico Caselli
    I don't think it's implemented in sqlalchemy
    Federico Caselli
    I'm not sure if you can implement this yourself using events or similar. feel free to open a discussion

    I think I'm stuck because of not understanding the difference between different abstractions:

    • cx-Oracle
    • DBAPI
    • sqlalchemy dialects
    • sqlalchemy core

    My current understanding is that cx-Oracle is Python wrapper to Oracle Client libraries. It exposes a DBAPI (PEP249) compatibile interface

    I'm unclear about "Oracle dialect" - https://docs.sqlalchemy.org/en/14/dialects/.
    Is that a separate thing that sits between core and the DBAPI driver? Or it's part of either cx-Oracle or SQLAlchemy Core?

    Federico Caselli
    The first one. It adapts the core statements so that they can be executed by cx_oracle
    Okay. In that case I'd be looking at DialectEvents since they give me the option to suppress the default behaviour? (don't want to submit the LOBs twice)
    Federico Caselli
    An option if the query are simple is to avoid using sqlalchemy in that case by getting a cursor from a connection and using it directly

    That sounds like a reasonable approach. We're using SQLAlchemy across the whole application, so it'd be nice to be able to interface is somehow.

    Would it go something like this?

    connection = engine.raw_connection()
    cursor = connection.cursor()


    And then that's the same cursor & connection they are using here:

    id_val = 9
    lob_var = cursor.var(cx_Oracle.DB_TYPE_BLOB)
            insert into lob_tbl (id, b)
            values (:1, empty_blob())
            returning b into :2""", [id_val, lob_var])
    blob, = lobVar.getvalue()
    offset = 1
    num_bytes_in_chunk = 65536
    with open("image.png", "rb") as f:
        while True:
            data = f.read(num_bytes_in_chunk)
            if data:
                blob.write(data, offset)
            if len(data) < num_bytes_in_chunk:
            offset += len(data)


    Federico Caselli
    yes, that's for sure an option. or it you already have a sqlalchemy connection you can get the cxoracle one by using conn.connection.dbapi_connection (dbapi_connection is recent. before it was named connection)
    6 replies
    Sorry if my doc-searching skills are failing me - I can't think of how what I'm thinking might be phrased: Is it possible to make a "virtual" column such that when used in expressions, it results in a SQL expression that references a related object? Simplified example (that doesn't really illustrate why I want to do it this way - please just trust me on that part):
    class Box(BaseModel):
        __tablename__ = "box"
        id = Column(Integer, primary_key=True)
        category = Column(String)
    class Folder(BaseModel):
        __tablename__ = "folder"
        id = Column(Integer, primary_key=True)
        box_id = Column(Integer, ForeignKey('box.id'))
        box = relationship(Box, backref=backref("folders"))
        category = ???  # In particular, I don't want this column to ACTUALLY exist in this table
    # I'd like:
    session.query(Folder).filter(Folder.category == "Mid-17th-Century Pocket Lint Collectors")
    # to implicitly result in SQL with the effect of:
    SELECT * FROM folder, box WHERE folder.box_id = box.id and box.category = 'Mid-17th-Century Pocket Lint Collectors'