Where communities thrive

  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
    Federico Caselli
    depending of the db you can use insert-on-conflict. I knot that postgresql support that: https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT and sqlalchemy support https://docs.sqlalchemy.org/en/14/dialects/postgresql.html#insert-on-conflict-upsert
    I think sqlite has something similar

    Thanks Federico, I will check that out.

    What I am using now as workaround is a class decorator which adds an equality check (i.e., overriding __eq__ and __ne__ of the objects) similar to the one described here: https://stackoverflow.com/questions/39043003/comparing-sqlalchemy-object-instances-for-equality-of-attributes. The difference is my decorator takes the names of the attributes to compare as arguments, instead of checking all. (Unfortunately, at decoration time the __table_args__ are not populated yet, otherwise I could use the attributes used in the UniqueConstraint of the table for the comparison).

    With this I can do:

    # Too bad those are in a list and can't be put in a set
    foos_existing = session.execute(sqlalchemy.select(Foo)).scalars().all()
    for foo in foos_new:
        # Fancy equality check happening here
        if foo not in foos_existing:


    • Unfortunately, this check is O(N), not O(1), as the objects are not hashable, so I can't put them in a set().
    • The inserts are not atomic -- if more Foo records get inserted to the table while this code runs, the insert might insert a record which already exists (i.e., has just been added). So I might have to wrap this in a transaction.

    Any ideas to improve this overall?

    Federico Caselli
    You can add __hash__ to make the instances hashable.
    Overall if the db you are using supports it I would use insert on conflict

    Ok, I'm trying to do something like this with postgresql:

    class ThingWithPaths(BaseModel):
        path1 = Column(Array(Text))
        path2 = Column(Array(Text))
        def paths(self):
            return self.path1, self.path2
        def paths(cls):
            return postgresql.array((self.path1, self.path2))

    I'd hoped that that last expression would make the "fake" paths field queryable, like:

    ...filter(ThingWithPaths.paths == postgresql.array((postgresql.array(('one', 'two')), postgresql.array(('three', 'four')))))...

    ...which does produce the kindof right looking WHERE clause:

    WHERE ARRAY[thing_with_paths.path1, thing_with_paths.path2] = ARRAY[ARRAY[%(param_1)s, %(param_2)s, %(param_3)s], ARRAY[%(param_4)s, %(param_5)s, %(param_6)s]]]

    But upon execution, I get a non-specific DB error. Any suggestions?

    Oh, my bad - my session (in REPL) was broken. The above actually did work with a fresh session.
    Pedro Henrique Altoé
    Hi guys, can anyone point me to the right direction on how to upload a sqlalchemy query response straight to s3 using aws_s3.query_export_to_s3? I am lost in the syntax I am afraid. Thank you!
    Andrey Syschikov
    Hi everyone! I have a question. I want to have two entities mapped to the same table with lots of columns, so that I can select different set of columns (defined in either model) based on what I need. Is it possible? (I am asking because if I do select(User.id. User.name) I don't get an object and I would like to have one).
    1 reply
    Federico Caselli
    you can use undefer / deferred and or groups as an alternative
    1 reply
    (that uses the old style query, but it will work the same with select)
    Jonathan Vanasco
    @phaltoe briefly searching the net for that aws package, I believe you may be able to use the connection's cursor object. i don't know of any sqlalchemy users who use that package.
    7 replies
    Uttam Velani
    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
    1 reply
    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(
                    stocks.c.country == TDCountry.america,
                    stocks.c.country == TDCountry.us_america,
    Federico Caselli

    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?

    TwelveStocks.country.in_([TDCountry.america, TDCountry.usamerica]) ■ Cannot access member "in" for type "TDCountry" Member "in_" is unknown
    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
    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
    should be an enum
    class TDCountry(str, enum.Enum):
    canada = "Canada"
    america = "United States"
    us_america = "United States of America"
    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(
    Scott Ziegler
    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
    I presume I MUST use the SQLalchemy expression lang's _in to get the proper dialect behavior when generating the query?
    Scott Ziegler
    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
    you can probably tell text that :foo is a bindparam with expaing=True. But I'm not sure if that will work
    Scott Ziegler
    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
    Indeed! Thank you very much... this will give me something to chew on for a little bit :D
    Jonathan Vanasco
    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
    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
    as an alternative you can use sa.column('foo').in_([1,2,3])