Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Activity
    Jonathan Vanasco
    @jvanasco
    That sounds like a question for the tornado-sqlalchemy or tornado developers. sqlalchemy doesn't have a timed cache.
    felixfarquharson
    @felixfarquharson
    thanks anyway :\
    dzpt
    @dzpt
    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
    @agronholm
    @dzpt do your tables have primary keys? those are required for classes
    dzpt
    @dzpt
    @agronholm no, thank you, let me set pk for those tables
    ytwxy99
    @ytwxy99
    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
    @CaselIT
    How is order defined?
    kalaLokia
    @kalaLokia

    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)
     .filter(
         sa.and_(
             ProductionScan.date >= "2021-08-01 08:00:00",
             ProductionScan.date <= "2021-08-25 08:00:00",
             ProductionMaster.article.like("%3780%black%"),
         )
     )
     .group_by(ProductionMaster.article, Barcode.size)
     .all()

    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]
           ,[barcode].[dbo].[Tbl_barcode].[Size]
           ,COUNT([barcode].[dbo].[Tbl_barcode].[Size])
    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é
    @phaltoe
    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
    achraf
    @achko_gitlab

    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
    @CaselIT
    @achko_gitlab look for load_only in the documentation
    Anton Melser
    @AntonOfTheWoods

    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(
            DateTime(True),
            nullable=False,
            onupdate=utcnow(),
            server_default=utcnow(),
            index=True,
        )

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

    Federico Caselli
    @CaselIT
    when does it not updates?
    1 reply
    Aviram Hassan
    @aviramha
    How can I specify columns to obtain when using using selectinload?
    Federico Caselli
    @CaselIT
    you need to combine it with load_only
    Maxwell Taylor
    @maxwell9999
    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
    @agronholm
    QLDB doesn't look like a relational database
    KrzysztofNawara
    @KrzysztofNawara

    Hello,

    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
    @CaselIT
    I don't think it's implemented in sqlalchemy
    Federico Caselli
    @CaselIT
    I'm not sure if you can implement this yourself using events or similar. feel free to open a discussion
    KrzysztofNawara
    @KrzysztofNawara

    @CaselIT
    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
    @CaselIT
    The first one. It adapts the core statements so that they can be executed by cx_oracle
    KrzysztofNawara
    @KrzysztofNawara
    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
    @CaselIT
    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
    KrzysztofNawara
    @KrzysztofNawara

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

    https://stackoverflow.com/a/49816320/12640922

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

    id_val = 9
    lob_var = cursor.var(cx_Oracle.DB_TYPE_BLOB)
    cursor.execute("""
            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:
                break
            offset += len(data)
    connection.commit()

    https://cx-oracle.readthedocs.io/en/latest/user_guide/lob_data.html#streaming-lobs-write

    Federico Caselli
    @CaselIT
    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
    mikenerone
    @mikenerone:matrix.org
    [m]
    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'
    mikenerone
    @mikenerone:matrix.org
    [m]
    Oh, I think @hybrid_property might be what I'm looking for.
    Ah yes, found https://docs.sqlalchemy.org/en/14/orm/mapped_sql_expr.html now. Discusses two decent ways of doing that. :)
    mikenerone
    @mikenerone:matrix.org
    [m]
    I feel like category = column_property(box.category) should work, but apparently the box field isn't populated yet at that point, because I get an AttributeError on box.category.
    mikenerone
    @mikenerone:matrix.org
    [m]
    :point_up: Edit: I feel like category = column_property(box.category) should work, but apparently the box field isn't populated yet at that point, because I get an AttributeError on box.category, and wrapping it in @declared_attr doesn't work, either.
    mikenerone
    @mikenerone:matrix.org
    [m]
    But I did get it working with @hybrid_property. :)
    Federico Caselli
    @CaselIT
    You may also check association_proxy. You can also use colum_property but you need to provide a scalar select to it
    1 reply
    mikenerone
    @mikenerone:matrix.org
    [m]

    @CaselIT: So using association proxy with my earlier example, like

    class Folder(BaseModel):
        ...
        box_id = Column(Integer, ForeignKey('box.id'))
        box = relationship(Box, backref=backref("folders"))
        category = association_proxy("box", "category")

    Though referencing it works in some contexts (like .filter(Folder.category == "x"), it doesn't transparently work in other field contexts. Specifically:

    session.query(Folder.category).all()
    -> InvalidRequestError: SQL expression, column, or mapped entity expected - got 'ColumnAssociationProxyInstance(AssociationProxy('box', 'category'))'

    Any way to make that work? What I want is for callers not to have to know that this isn't a real column.

    Federico Caselli
    @CaselIT
    yes association proxy only works for wheres. I think your best bet it to use column_property or hybrid property like you did above then
    mikenerone
    @mikenerone:matrix.org
    [m]
    kk, thanks!
    mikenerone
    @mikenerone:matrix.org
    [m]
    It seems like it principle it should be able to work pretty easily for SELECT, though, right? Wouldn't it just compile as SELECT associated_table.associated_col_name AS col_name ...?
    Federico Caselli
    @CaselIT
    I guess that logic was never added. if you use select you get a better error iirc
    mikenerone
    @mikenerone:matrix.org
    [m]
    Sorry, I didn't mean select specifically - I just meant the SELECT expressions in the compiled SQL resulting from session.query(Model.an_association_proxy_attr).
    Federico Caselli
    @CaselIT
    yes, I meant that with select() you get a better error. Not sure why it's not supported. I guess there was a good reason, not sure if there is still
    you should be able to create a column_property to do that though
    mikenerone
    @mikenerone:matrix.org
    [m]
    The problem I ran into with column_property() is that in my actual usage, I have like a dozen such mapped fields, and the result is that the generated query has like a dozen subselects to the same related table. I would kinda like the eager loading, but not at that expense. :P
    mikenerone
    @mikenerone:matrix.org
    [m]
    Oh, association_proxy doesn't work with order_by(), either. While using it in select expressions was a nice-to-have, order_by actually is a show-stopper for me. sigh
    Federico Caselli
    @CaselIT
    I'm not actually sure why it just does not do that. it has to write basically the same thing to the where anyway.
    Maybe open an use case issue on the repo, so we can get also mike's thinking on this