Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Activity
    nth2tell
    @nth2tell
    Does anyone know what I did wrong?
    Alex Grönholm
    @agronholm
    @nth2tell why are you calling URL.create()?
    Federico Caselli
    @CaselIT
    The problem is that you are using the create method wrongly. I suggest you to refer to the documentation https://docs.sqlalchemy.org/en/14/core/engines.html?highlight=url+create#sqlalchemy.engine.URL.create
    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.
    Federico Caselli
    @CaselIT
    you can use table_valued https://docs.sqlalchemy.org/en/14/core/functions.html?highlight=table_valued#sqlalchemy.sql.functions.FunctionElement.table_valued
    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
    you may need to make alembic skip it using this hook https://alembic.sqlalchemy.org/en/latest/autogenerate.html#omitting-based-on-object
    see also https://alembic.sqlalchemy.org/en/latest/api/runtime.html?highlight=include_object#alembic.runtime.environment.EnvironmentContext.configure.params.include_object
    @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?