Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Activity
    Uttam Velani
    @uttamss11_gitlab
    We are using sqlalchemy with flask [not flask-sqlalchemy but core sqlalchemy] as ORM. Our database is MariaDB. We want to generate UUID at database level [it will not be a primary key]. How we can do it. Any idea?
    Michael Bourke
    @bourke
    1 reply
    fraschm1998
    @fraschm1998
    anyone know how to use in_ for enums in sqlalchemy1.4?
        if country in [TDCountry.america, TDCountry.us_america]:
              query = query.where(
                   TwelveStocks.country=(TDCountry.america.value | TDCountry.us_america.value))     ■■ Operator "|" not supported for types "Literal['United States']" and "Literal['United States of America']"
             )
    I also tried: TwelveStocks.country.in_([TDCountry.america, TDCountry.us_america]) but that didn't work either
    I had it working with pydantic and sqlalchemy before switching to sqlmodel:
        if country in [TDCountry.america, TDCountry.us_america]:
            query = query.where(
                or_(
                    stocks.c.country == TDCountry.america,
                    stocks.c.country == TDCountry.us_america,
                )
            )
    Federico Caselli
    @CaselIT

    I also tried: TwelveStocks.country.in_([TDCountry.america, TDCountry.us_america]) but that didn't work either

    this should be the correct form. what's the issue with this on?

    fraschm1998
    @fraschm1998
    TwelveStocks.country.in_([TDCountry.america, TDCountry.usamerica]) ■ Cannot access member "in" for type "TDCountry" Member "in_" is unknown
    huh
    I don't think there was an error this whole time but pyright was telling me there was
     Cannot access member "in" for type "TDCountry" Member "in" is unknown Pyright (reportGeneralTypeIssues) [31, 34]
    Federico Caselli
    @CaselIT
    what type does country has according to pyright?
    at the moment the typing story with sqlalchemy is still best effort, so you probably have to ignore that error or cast it explicitly
    fraschm1998
    @fraschm1998
    should be an enum
    class TDCountry(str, enum.Enum):
    canada = "Canada"
    america = "United States"
    us_america = "United States of America"
    fraschm1998
    @fraschm1998
    sqlalchemy.exc.ProgrammingError: (sqlalchemy.dialects.postgresql.asyncpg.ProgrammingError) <class 'asyncpg.exceptions.DatatypeMismatchError'>: column "market" is of type tdmarket but expression is of type character varying
    HINT:  You will need to rewrite or cast the expression.
    [SQL: INSERT INTO halts (date, "haltTime", ticker, "haltPrice", market, reason, "resumeTime", "resumePrice", country, watching) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)]
    [parameters: (datetime.date(2021, 12, 16), datetime.time(11, 11, 11), 'string', 0.0, <TDMarket.nasdaq: 'NASDAQ'>, 'string', None, None, <Country.canada: 'canada'>, False)]
    (Background on this error at: https://sqlalche.me/e/14/f405)
    10 replies
    class TDMarket(str, enum.Enum):
         nasdaq = "NASDAQ"
         nyse = "NYSE"
         iex = "IEX"
         otc = "OTC"
         cboe = "CBOE"
         tsx_v = "TSX_V"  # "TSX-Venture" "TSXV"
         tsx = "TSX"
         cnq = "CNQ"
         neo = "NEO"
        halt = Halts(
            date=halt.date,
            haltTime=halt.haltTime,
            ticker=halt.ticker,
            haltPrice=halt.haltPrice,
            market=TDMarket(halt.market),
            reason=halt.reason,
            resumeTime=halt.resumeTime,
            resumePrice=halt.resumePrice,
            country=halt.country.value,
            watching=halt.watching,
        )
    Scott Ziegler
    @mentat-enki
    Good day everyone - I've recently come across an idiosyncrasy regarding the IN clause between the MySQL and SQLite dialects, and am wondering if anyone has a canonical answer as to how to deal with the issue at hand...
    SQLite requires IN clauses to be parenthesized AND have a '?' cardinality that matches the parameter, e.g. execute(" [...] IN (?, ?, ?)", (102, 103, 104))
    whereas MySQL is quite happy with a simple binding using text(), e.g. text("SELECT iT.typeID FROM invTypes AS iT WHERE iT.typeID IN :foo"), foo=(2603, 11563, 3829,)
    however, trying to use the text/bindings approach w/ SQLite generates "bad SQL" for the dialect...
    i.e. trying to use bindings generates:
    sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "?": syntax error
    [SQL: SELECT iT.typeID FROM invTypes AS iT WHERE iT.typeID IN ?]
    [parameters: ((2603, 11563, 3829, 5445, 33180, 434, 6159, 18639, 26929, 26929, 31358),)]
    Where the ? is neither parenthesized nor expanded to match the arguments cardinality
    Scott Ziegler
    @mentat-enki
    I presume I MUST use the SQLalchemy expression lang's _in to get the proper dialect behavior when generating the query?
    Scott Ziegler
    @mentat-enki
    Or stated differently, is there a way for me to use the text("...WHERE foo IN :foo", foo=(1, 2, 3)) approach that will generate valid SQLite SQL, i.e. NOT just "... foo IN ?" but rather, "... foo IN (?, ?, ?)"?
    Federico Caselli
    @CaselIT
    you can probably tell text that :foo is a bindparam with expaing=True. But I'm not sure if that will work
    Scott Ziegler
    @mentat-enki
    is that expanding=True?
    (am actually just grokking the docs for bindparam right now, so we are thinking the same way in any case :) )
    Scott Ziegler
    @mentat-enki
    Indeed! Thank you very much... this will give me something to chew on for a little bit :D
    Jonathan Vanasco
    @jvanasco
    The type_ on the binds in @CaselIT's solution should work
    Another thing to read up on, is the @compiles decorator for custom functions
    Scott Ziegler
    @mentat-enki
    oh nice yeah will check that out - worst case I can force it with prejudice :D
    That would let you make a "where_foo" function in python, which compiles the SQL clause differently for each backend.
    Federico Caselli
    @CaselIT
    as an alternative you can use sa.column('foo').in_([1,2,3])
    (note the lowercase c)
    Scott Ziegler
    @mentat-enki
    Thanks again, everyone - SQLAlchemy is so damned powerful if you know all the hidden tricks! Your insights are very much appreciated.
    Nirmal Kumar Kosuru
    @nikkijyos:matrix.org
    [m]
    Hi Anyone have idea about this issue with Snowflake UDF function I am having.
    SQL Error [90150] [22000]: Single-row subquery returns more than one row.
    I have a UDF lookup function to accept US state and return the Countr_Code for the state
    I am having this error when I call the function like below:
    SQL Error [90150] [22000]: Single-row subquery returns more than one row.
    CREATE OR REPLACE function GET_HCOUNTRY( State_Code varchar )
    returns varchar
    LANGUAGE SQL
    AS
    selectCountryCodefromPUBLIC.STATEHLookupwhereStateCode=StateCode select Country_Code from PUBLIC.STATE_HLookup where State_Code = State_Code
    ;
    Scott Ziegler
    @mentat-enki
    per the error msg, it may be that your inner select is somehow returning more than one result for the state code you are using (which is admittedly odd, seeing there should prob be 1:1 correspondence between state codes and rows... Perhaps execute the inner select alone with the state that is causing the error to see the result and it's cardinality
    Hunter Kiely
    @hkiely
    Are any of you familiar with Miguel's tutorial? More specifically, I am looking for assistance with with issues regarding database joins.
    This is the Flask Mega-Tutorial.
    Scott Ziegler
    @mentat-enki
    It's been a while, but Mr. Grinberg is a real gangr - god bless him. What issues are you having with the joins?
    Hunter Kiely
    @hkiely
    Something about how one of the columns won't be part of the declarative mappings.