Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Activity
    PalmaAnderson
    @PalmaAnderson
    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
    @NominaSumpta_twitter
    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
    @CaselIT
    are you looking for isinstance?
    William Edwards
    @NominaSumpta_twitter
    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
    @CaselIT
    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
    felixfarquharson
    @felixfarquharson
    Hello, does anyone know how to set the cache to 1 second on tornado_sqlalchemy?
    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).