Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Activity
    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
    mikenerone
    @mikenerone:matrix.org
    [m]

    @CaselIT: So I came up with this:

    def association_hybrid_proxy(target: str, attr: str):
        prop = hybrid_property(lambda self: getattr(getattr(self, target), attr))
        return prop.expression(lambda cls: getattr(getattr(cls, target).mapper.class_, attr))

    You use it just like association_proxy, but it makes a hybrid_property under the covers that works in both filter() and order_by() (the latter requires that you ensure the join in some other way, so it's not 100% transparent, but at least that's easy and immediately discovered if you don't).

    mikenerone
    @mikenerone:matrix.org
    [m]
    I'll also be using this in some mixins, which I'm pretty sure will require also wrapping that as a declared_attr. I'll get around to it in a bit and know for sure.
    mikenerone
    @mikenerone:matrix.org
    [m]

    :point_up: Edit: @CaselIT: So I came up with this:

    def hybrid_association_proxy(target: str, attr: str):
        prop = hybrid_property(lambda self: getattr(getattr(self, target), attr))
        return prop.expression(lambda cls: getattr(getattr(cls, target).mapper.class_, attr))

    You use it just like association_proxy, but it makes a hybrid_property under the covers that works in both filter() and order_by() (the latter requires that you ensure the join in some other way, so it's not 100% transparent, but at least that's easy and immediately discovered if you don't).

    Nirmal Kumar Kosuru
    @nikkijyos:matrix.org
    [m]
    Hi All
    I am using Snowflake Connector and trying to load data into Snowflake table using Python code below

    import pandas as pd
    import snowflake.connector as snow
    from snowflake.connector.pandas_tools import write_pandas
    import logging
    SNOWFLAKE_ACCOUNT = 'jwa13025' # os.getenv('SF_ACOUNT') SF_ACOUNT
    SNOWFLAKE_USER = 'nkumar' # os.getenv('SF_USR') SF_USR
    SNOWFLAKE_PWD = 'Kuldeep@12' # os.getenv('SF_PWD') SF_PWD'
    conn = snow.connect(user=SNOWFLAKE_USER,
    password=SNOWFLAKE_PWD ,
    account=SNOWFLAKE_ACCOUNT,
    warehouse="WORLDPANTRYDW",
    database="SDS",
    schema="PUBLIC");

    try:
    logging.basicConfig(level=logging.INFO, filename='Load_SDS_Header.log', filemode='w', format='%(asctime)s %(message)s',datefmt='%m/%d/%Y %I:%M:%S %p')
    Result=conn.cursor().execute("use schema PUBLIC")
    print(Result)
    SDS_HEADERS = pd.read_csv("C:\BalaReports\SDS\Oracle_SDS_Datafiles_Nov21\Headers.txt",sep="\t",header=None,names=["Column_Id","Header_ColId","Header_ColDescription"])

    print(Header_Info)

    success, nchunks, nrows, _ = write_pandas(conn, SDS_HEADERS, "SDS.PUBLIC.SDS_HEADERS",schema="PUBLIC")
    
    print(success)

    except conn.error.DatabaseError as e:

    #raise e.errno
    logging.error("Exception occurred", exc_info=True)

    except exception as e:

    #raise e.errno
    #print('SQL Execution Error: {0}'.format(e.msg))
    #print('Snowflake Query Id: {0}'.format(e.sfqid))
    #print('Error Number: {0}'.format(e.errno))
    #print('SQL State: {0}'.format(e.sqlstate))

    finally:
    conn.close()
    logging.info('Trace Other Issue', exc_info=True)

    I get the below error:
    snowflake.connector.errors.ProgrammingError: 002043 (02000): 01a0b3c8-0500-e563-0029-ec830066c0ba: SQL compilation error:
    Object does not exist, or operation cannot be performed.
    I have all the objects required for loading
    Nirmal Kumar Kosuru
    @nikkijyos:matrix.org
    [m]
    Can any one let me know whats the Issue?
    Alex Grönholm
    @agronholm
    @nikkijyos:matrix.org would you mind properly formatting that code above?
    10 replies
    Nirmal Kumar Kosuru
    @nikkijyos:matrix.org
    [m]
    I am trying to load this file
    Nirmal Kumar Kosuru
    @nikkijyos:matrix.org
    [m]

    "'''"import pandas as pd"'''"
    "'''"import snowflake.connector as snow "'''"
    "'''"from snowflake.connector.pandas_tools import write_pandas"'''"
    "'''"import logging"'''"

    "'''"conn = snow.connect(user=SNOWFLAKE_USER,"'''"
    "'''" password=SNOWFLAKE_PWD ,"'''"
    "'''" account=SNOWFLAKE_ACCOUNT,"'''"
    "'''" warehouse="WORLDPANTRYDW", "'''"
    "'''" database="SDS", "'''"
    "'''" schema="PUBLIC"); "'''"

    "'''"try: "'''"
    "'''" logging.basicConfig(level=logging.INFO, filename='Load_SDS_Header.log', filemode='w', format='%(asctime)s %(message)s',datefmt='%m/%d/%Y %I:%M:%S %p') "'''"
    "'''" Result=conn.cursor().execute("use schema PUBLIC") "'''"
    "'''" print(Result) "'''"
    "'''" SDS_HEADERS = pd.read_csv("C:\BalaReports\SDS\Oracle_SDS_Datafiles_Nov21\Headers.txt",sep="\t",header=None,names=["Column_Id","Header_ColId","Header_ColDescription"]) "'''"
    "'''" #print(SDSHEADERS) "'''"
    "'''" success, nchunks, nrows,
    = write_pandas(conn, SDS_HEADERS, "SDS.PUBLIC.SDS_HEADERS",schema="PUBLIC") "'''"
    "'''" print(success) "'''"
    "'''"#except conn.error.DatabaseError as e: "'''"
    "'''" #raise e.errno "'''"
    "'''" logging.error("Exception occurred", exc_info=True) "'''"
    "'''"#except exception as e: "'''"
    "'''" #raise e.errno "'''"
    "'''" #print('SQL Execution Error: {0}'.format(e.msg)) "'''"
    "'''" #print('Snowflake Query Id: {0}'.format(e.sfqid)) "'''"
    "'''" #print('Error Number: {0}'.format(e.errno)) "'''"
    "'''" #print('SQL State: {0}'.format(e.sqlstate)) "'''"
    "'''"finally: "'''"
    "'''" conn.close() "'''"
    "'''" logging.info('Trace Other Issue', exc_info=True) "'''"

    Nirmal Kumar Kosuru
    @nikkijyos:matrix.org
    [m]
    I have attached the file I am trying to load
    Any help is much Appreciated.
    Federico Caselli
    @CaselIT

    snowflake.connector.errors.ProgrammingError: 002043 (02000): 01a0b3c8-0500-e563-0029-ec830066c0ba: SQL compilation error:
    Object does not exist, or operation cannot be performed.

    it seems the table is missing. just guessing, I've never used snowflake

    4 replies
    Alex Grönholm
    @agronholm
    @nikkijyos:matrix.org when I told you how to fix your formatting, this was not it. I meant to add ``` (three backticks) before the snippet and after it. You used single quotes instead of backticks, with the double quotes (which I never meant to be included there) plus you put them on every line
    4 replies
    Nirmal Kumar Kosuru
    @nikkijyos:matrix.org
    [m]
    Tables inside the database
    Nirmal Kumar Kosuru
    @nikkijyos:matrix.org
    [m]
    I have one question here, the WRITE_PANDAS create put and copy command during this process it stores the csv file into stage so do I need to give any permission for creating stage like grant usage, read, write on future stages in schema "PUBLIC" to role "SYSADMIN"