Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Activity
    Federico Caselli
    @CaselIT
    To create an url from string you should use the make_url function instead
    Spruha Pattnaik
    @_Spruha_twitter
    Hi, Can someone please suggest the most efficient (in terms of DB latency) way to do bulk insert of objects & fetch the corresponding auto generated primary key?
    session.bulk_save_objects(objects, return_defaults=True) causes very high DB latency for the INSERT operation ....
    I also tried session.bulk_save_objects(objects) and then select the objects from DB using a hash key (which is indexed) but the select operations are also long running....
    Please help! It's a production issue ....
    Alex Grönholm
    @agronholm
    @_Spruha_twitter I think your latency problems are caused by something else
    it's not sqlalchemy's fault
    tvallois
    @tvallois:matrix.org
    [m]
    Hello, i try to use json_serializer and json_deserializer with the create_async_engine function but the workflow is never going into those functions when i try to insert/query a json column of a model. Is it only for psycopg2?
    Federico Caselli
    @CaselIT
    it should work also for async engines
    tvallois
    @tvallois:matrix.org
    [m]
    Hmm.. i wanted to try that because when i try to insert a dict in a JSON Postgres Column, i have an error "unhashable type: dict" however if i try to run json.dumps(my_dict) it works without any issue.
    If i understand the doc correctly, by default, engines do json.dumps and json.loads for serialization/deserialization.
    Federico Caselli
    @CaselIT
    yes, the issue here seems different though. without a stack trace it's hard to suggest anything
    tvallois
    @tvallois:matrix.org
    [m]
      File "/Users/thibaultvallois/etl-automation/service/etl_automation/persistors/base.py", line 32, in create_all
        async with self._get_session() as session:
      File "/Users/thibaultvallois/.asdf/installs/python/3.10.4/lib/python3.10/contextlib.py", line 206, in __aexit__
        await anext(self.gen)
      File "/Users/thibaultvallois/etl-automation/service/etl_automation/persistors/base.py", line 29, in _get_session
        raise err
      File "/Users/thibaultvallois/etl-automation/service/etl_automation/persistors/base.py", line 26, in _get_session
        await session.commit()
      File "/Users/thibaultvallois/Library/Caches/pypoetry/virtualenvs/etl-automation-GVE63ZMH-py3.10/lib/python3.10/site-packages/sqlalchemy/ext/asyncio/session.py", line 580, in commit
        return await greenlet_spawn(self.sync_session.commit)
      File "/Users/thibaultvallois/Library/Caches/pypoetry/virtualenvs/etl-automation-GVE63ZMH-py3.10/lib/python3.10/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 128, in greenlet_spawn
        result = context.switch(value)
      File "/Users/thibaultvallois/Library/Caches/pypoetry/virtualenvs/etl-automation-GVE63ZMH-py3.10/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1451, in commit
        self._transaction.commit(_to_root=self.future)
      File "/Users/thibaultvallois/Library/Caches/pypoetry/virtualenvs/etl-automation-GVE63ZMH-py3.10/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 829, in commit
        self._prepare_impl()
      File "/Users/thibaultvallois/Library/Caches/pypoetry/virtualenvs/etl-automation-GVE63ZMH-py3.10/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 808, in _prepare_impl
        self.session.flush()
      File "/Users/thibaultvallois/Library/Caches/pypoetry/virtualenvs/etl-automation-GVE63ZMH-py3.10/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 3383, in flush
        self._flush(objects)
      File "/Users/thibaultvallois/Library/Caches/pypoetry/virtualenvs/etl-automation-GVE63ZMH-py3.10/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 3522, in _flush
        with util.safe_reraise():
      File "/Users/thibaultvallois/Library/Caches/pypoetry/virtualenvs/etl-automation-GVE63ZMH-py3.10/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
        compat.raise_(
      File "/Users/thibaultvallois/Library/Caches/pypoetry/virtualenvs/etl-automation-GVE63ZMH-py3.10/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
        raise exception
      File "/Users/thibaultvallois/Library/Caches/pypoetry/virtualenvs/etl-automation-GVE63ZMH-py3.10/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 3483, in _flush
        flush_context.execute()
      File "/Users/thibaultvallois/Library/Caches/pypoetry/virtualenvs/etl-automation-GVE63ZMH-py3.10/lib/python3.10/site-packages/sqlalchemy/orm/unitofwork.py", line 456, in execute
        rec.execute(self)
      File "/Users/thibaultvallois/Library/Caches/pypoetry/virtualenvs/etl-automation-GVE63ZMH-py3.10/lib/python3.10/site-packages/sqlalchemy/orm/unitofwork.py", line 630, in execute
        util.preloaded.orm_persistence.save_obj(
      File "/Users/thibaultvallois/Library/Caches/pypoetry/virtualenvs/etl-automation-GVE63ZMH-py3.10/lib/python3.10/site-packages/sqlalchemy/orm/persistence.py", line 212, in save_obj
        for (
      File "/Users/thibaultvallois/Library/Caches/pypoetry/virtualenvs/etl-automation-GVE63ZMH-py3.10/lib/python3.10/site-packages/sqlalchemy/orm/persistence.py", line 398, in _organize_states_for_save
        and instance_key in uowtransaction.session.identity_map
      File "/Users/thibaultvallois/Library/Caches/pypoetry/virtualenvs/etl-automation-GVE63ZMH-py3.10/lib/python3.10/site-packages/sqlalchemy/orm/identity.py", line 100, in __contains__
        if key in self._dict:
    TypeError: unhashable type: 'dict'

    ```class Postings(Base):
    stamp = Column(Date, primary_key=True)
    dat = Column(JSON, primary_key=True)

    postings = Postings(stamp=my_date, dat={"a": "b"})
    await async_session.create(postings)
    await async_session.commit()
    ```

    Federico Caselli
    @CaselIT
    are you trying to use a json column as primary key?
    that's not supported
    tvallois
    @tvallois:matrix.org
    [m]
    Oh
    haha
    ty @CaselIT its obvious now
    Federico Caselli
    @CaselIT
    in the orm, with core you can do anything of course :)
    but the orm uses a dict to associate key -> orm instance, so the key needs to be hashable
    Jonathan Vanasco
    @jvanasco
    I am writing missing tests for an old sqlalchemy app, and ran into this issue- the app was built against an existing database and doesn't have any of the indexes on it. The basic unique indexes are straightforward. How should I implement "CREATE UNIQUE INDEX uidx_foo ON foo(lower(name))" with table_args ?
    Is it just DDL(raw_sql), or is there a way to leverage UniqueIndex, as I likely have some fkeys pointing to these?
    I meant UniqueConstraint
    Jonathan Vanasco
    @jvanasco
    Index("uidx", text("lower(name)"), unique=True) seems to work
    Mohi Mirza
    @mohimirza
    I am trying to use Attribute Events to create Audit for association tables in many to many relationships. In Mapper Events such as "after_insert" we get same same Connection object which can be used to create Audit for the Mapper Object in some Audit Table. In Attribute Events we don't get it. Is there a way I can get the Connection object from Attribute Events such as "append" and "remove" . I do not want to pass the Connection object manually to keep my ManytoManyAuditMixin class portable.
    Alex Grönholm
    @agronholm
    @mohimirza we use after_flush + after_commit events for that purpose
    in after_flush we use history to determine which attributes were changed
    Spruha Pattnaik
    @_Spruha_twitter
    Hi folks, can I convert this raw SQL into ORM syntax?
    message_ids = conn.execute(f"SELECT nextval('messages_id_seq') as id FROM generate_series(1, {num_msgs})")
    I've observed that session.bulk_save_objects(object_list, returning_defaults=True) doesn't scale up to highly concurrent inserts - it causes some latency. Therefore i'm trying to implement the above where I generate the defaults, i.e. id field, beforehand.
    What is your opinion on this? And what would be the best practice to scale up performance if i want to inserts objects into a model & fetch the same objects back with the id field.
    not sure what you mean by highly concurrent inserts though
    Yang Bo
    @Bryant-Yang
    Hi, is there a way to set multi-valued index in table_args? so that albemic could detect this index creation?
    Federico Caselli
    @CaselIT
    what do you mean by multi-valued?
    multi column?
    Yang Bo
    @Bryant-Yang
    CREATE TABLE mytable
    (
    rp_summary_path JSON
    );
    INSERT INTO mytable
    VALUES ('[
    {
    "user_id": 1
    },
    {
    "user_id": 2
    }
    ]'
    );
    ALTER TABLE mytable ADD INDEX myindex (
    (CAST(rp_summary_path -> '$[*].user_id' AS UNSIGNED ARRAY))
    );
    ALTER TABLE mytable ADD INDEX myindex (
    (CAST(rp_summary_path -> '$[*].userid' AS UNSIGNED ARRAY))
    );
    'myindex' is multi-valued index of mysql8
    it's just extract all "user_id" in a json list of dict as an unsigned int array index
    Federico Caselli
    @CaselIT
    I don't think there is support for reflection. you may be able to create it using a string in the index defintion Index('(CAST(rp_summary_path -> '$[*].userid' AS UNSIGNED ARRAY))') but reflection will most likely not work, so autogenerate on alembic will not pick it up
    Yang Bo
    @Bryant-Yang
    I've tired to add the index creation manually in an upgrade version, but when doing next migration, then generated script will drop the index.
    image.png
    Federico Caselli
    @CaselIT
    @Bryant-Yang I'll open an issue to track this, bug I can't promise any eta
    @Bryant-Yang do you know if mariadb has something similar?
    Yang Bo
    @Bryant-Yang
    well, thanks! I think I got these ideas in the articles. Is there a simple way to make an index "place holder" to tell albemic we need this index don't drop it?
    I'm not sure if mariadb has those features
    Federico Caselli
    @CaselIT
    you use the include object callback
    basically you tell alembic to ignore that index in the autogenerate