Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Activity
    Leonid Komarovsky
    @shpoont
    I assume that with default cascade having merge it should propagate merge to tags as well, but it doesn't work in my case
    Federico Caselli
    @CaselIT
    is name the pk? IIRC merge works on pk only
    4 replies
    corporal9736
    @corporal9736

    Hello, I'm tring to create an Engine using FILEDSN, which is a file path for a dsn file.
    I use pyodbc to connect to a sqlserver. I succesfully connected by using

    pyodbc.connect("FILEDSN=/path/to/dsn/file")

    but it seems not work on create_engine, I tried to use

    test_url = URL.create("mssql+pyodbc", query={"odbc_connect": "FILEDSN=/path/to/dsn/file"})
    engine = create_engine(test_url)
    but it doesn't work. I wonder if there are proper ways to do that.
    Federico Caselli
    @CaselIT
    corporal9736
    @corporal9736
    it doesn't work... I found that it seems to force using unixodbc... even if I installed msodbc and specific it in dsn file. I'm using ubuntu20.04 and sqlalchemy 1.4.27.
    Michael Bourke
    @bourke
    you can customise how the connection gets created by parsing and manipulating the URL args: https://docs.sqlalchemy.org/en/14/core/engines.html#custom-dbapi-connect-arguments-on-connect-routines
    corporal9736
    @corporal9736
    I've sloved this problem. Thanks a lot! However, why do we still need a url when I pass the pool or creator param to create_engine? Because of the standard?
    Michael Bourke
    @bourke
    that would be my assumption, yah
    Federico Caselli
    @CaselIT

    I've sloved this problem. Thanks a lot! However, why do we still need a url when I pass the pool or creator param to create_engine? Because of the standard?

    that's needed to load the proper dialect by sqlalchemy

    Boris Knop
    @boris.knop_gitlab
    Hi Guys, im using the most recent stable SqlAlchemy version, i keep on getting the following error : "cannot import name 'DefaultEngineStrategy' from 'sqlalchemy.engine.strategies'"
    in one of the forums, it says i should downgrade, but any version prior to 1.4 doesnt have the Asyncsession which i need... so i kinda stuck here. any suggestions?
    2 replies
    Ian Wilson
    @ianjosephwilson
    hi, I think I've confused myself, i'm using 1.4.21. Does appending to a relation trigger an insert? (And likewise a remove()?) Like user.shipping_addresses(ShippingAddress(address=user.default_address)). I'm using a concrete association object.
    Or do I need to ALSO call session.add(shipping_address) where shipping_address=ShippingAddress(address=user.default_address).
    Federico Caselli
    @CaselIT
    if you append to a relationship that's not viewonly=True it should be added to db without the need for add
    Ian Wilson
    @ianjosephwilson
    It seem that if I append the child to the parent but also link the child to the parent then it seems to add two entries.
    My naming scheme is confusing but here are actual snippets from the code, I think maybe I have something misconfigured, https://gist.github.com/ianjosephwilson/ed8384661f3e3ec25164b98244ed50ee
    If I comment out that album link it seems to work.
    Federico Caselli
    @CaselIT
    Christian Klinger
    @goschtl
    Hey i have a question i have a postgres DATERANGE column. I want to query only the year in the dateobject
    i tried this

    sqlalchemy.extract('upper.year', Tab_MGLBetreuer.gueltigkeit) >= jahr.year

    jahr is a datetime.date object
    gueltigkeit is the DATERANGE field
    Sergey Maranchuk
    @slav0nic
    is it possible force commit dirty but not modified object? (for example if do user.name = 'Bob' and old value was also 'Bob' sqlalchemy don't generate UPDATE statement)
    svaranasi-night-market
    @svaranasi-night-market
    Hi All, can someone help me with sqlalchemy function for update with limit
    I am using mysql
    Luis Roel
    @luisroel91
    Has anyone used SQLAlchemy with FactoryBoy?...I'm having an issue where a generated factory fails to save objects because they violate not-null constraints. Do I have to manually define each field in the factory?
    9 replies
    Luis Roel
    @luisroel91
    Anyone know if factory boy works with SA's automap_base?
    Leonid Komarovsky
    @shpoont

    Looks like I don't use session properly. Not sure what am I missing 🤔
    When fetching existing data from the database and then updating, looks like SQL Alchemy doesn't recognize already stored objects that are part of relationship and tries to insert them again.

    """ Here I generate data and merge it into the database """
    for track_name in ['track-one','track-two','track-three']:
        track = Track(name=track_name)
        track.load_top_tags()
        session.merge(track)
        session.commit()
    
    """ Clean session """
    session.expunge_all()
    
    """ Here I fetch stored tracks and update their related tag objects (doesnt work) """
    for track in session.query(Track).all():
        for top_tag in track.top_tags: # Load tags from database
            print(f"{track.name} has top tag {top_tag.tag.name}")
        track.load_top_tags()
        session.merge(track)
        session.commit()

    This results in (sqlite3.IntegrityError) UNIQUE constraint failed: tags.id error because SQLAlchemy is trying to insert data that already exists

    Here is the full example with resulting output: https://colab.research.google.com/drive/18OMhYNxZGnKU4gaQ8l-ObKmVQEf50wtp?usp=sharing

    5 replies
    Shredder
    @Shredder
    I have a table with an auto-increment integer id (primary key), and a unique-key constraint for three other columns. In my code, I am creating many instances of the mapped class and would like to insert them in the table, but only if they don't exist already. What is the best way to do this check for existence? I obviously can't check the primary key, so I could do a query for each object I want to insert to check whether an object with the unique-key values already exists -- this seems somewhat clunky. I was hoping to pre-fetch all objects from the table, then do a direct comparison (my_instance in set(fetched_objs_from_table)), but that doesn't seem to work. Or I could try to insert each object and catch IntegrityError when the unique-key constraint fails, but that is probably bad for performance.
    Federico Caselli
    @CaselIT
    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
    Shredder
    @Shredder

    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:
            session.add(foo)
    session.commit()

    Issues:

    • 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
    @CaselIT
    You can add __hash__ to make the instances hashable.
    Overall if the db you are using supports it I would use insert on conflict
    mikenerone
    @mikenerone:matrix.org
    [m]

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

    class ThingWithPaths(BaseModel):
        ...
        path1 = Column(Array(Text))
        path2 = Column(Array(Text))
    
        @hybrid_property
        def paths(self):
            return self.path1, self.path2
    
        @paths.expression
        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é
    @phaltoe
    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
    @asyschikov
    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
    @CaselIT
    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
    @jvanasco
    @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
    @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,
                )
            )