Re: [sqlalchemy] Non-repeatable reads, vectorized refresh
What I’m doing wrong?
class ModelObjectTable(BaseTable, BaseOwner):
__tablename__ = 'model_object'
model_id = Column(Integer, ForeignKey('model.id', ondelete='cascade'))
# other columns
model = relationship('ModelTable', uselist=False, lazy='joined',
join_depth=1)
def __init__(self):
...
mo_list = session.execute(select(ModelObjectTable).where(
ModelObjectTable.id.in_(id_list)),
execution_options={'populate_existing': True}).all()
get this error:
Traceback (most recent call last):
File "/home/user/anaconda3/envs/python37/lib/python3.7/code.py", line 90, in
runcode
exec(code, self.locals)
File "", line 1, in
File
"/home/user/anaconda3/envs/python37/lib/python3.7/site-packages/sqlalchemy/engine/result.py",
line 417, in _allrows
for made_row in made_rows
File
"/home/user/anaconda3/envs/python37/lib/python3.7/site-packages/sqlalchemy/engine/result.py",
line 417, in
for made_row in made_rows
File
"/home/user/anaconda3/envs/python37/lib/python3.7/site-packages/sqlalchemy/orm/loading.py",
line 194, in require_unique
"The unique() method must be invoked on this Result, "
sqlalchemy.exc.InvalidRequestError: The unique() method must be invoked on this
Result, as it contains results that include joined eager loads against
collections
среда, 24 августа 2022 г. в 16:53:26 UTC+3, Mike Bayer:
>
>
> On Wed, Aug 24, 2022, at 6:32 AM, Evgenii wrote:
>
> Thank you one more time!
> Just in case I leave fixed expression here:
>
> session.execute(
>select(Instance).where(Instance.id.in([list_of_ids])),
> execution_options={"populate_existing": True}
> )
>
> Is there any way to set populate_existing = True for engine or
> sessionmaker?
>
>
> not right now, there is an issue to maybe have session-wide execution
> options. the option can be set on an engine but I'm not sure it takes
> effect for the ORM when set only at the engine level.
>
>
>
> вторник, 23 августа 2022 г. в 22:34:23 UTC+3, Mike Bayer:
>
>
>
> On Tue, Aug 23, 2022, at 3:00 PM, Evgenii wrote:
>
>
> Thanks a lot!
> I used “vectorized” for sqlalchemy.orm.Session.refresh method and mean
> that it would be nice to use:
>
>
> session.refresh(instances_list)
>
> that make a single query to database. Instead of:
>
>
>
> do this:
>
> session.execute(
>select(Instance).where(Instance.id.in([list_of_ids]),
> execution_options={"populate_existing": True})
> )
>
> all objects refreshed
>
>
>
>
> for el in instances_list:
> session.refresh(el)
>
> that make N queries.
>
> like:
>
> res = session.query(InstrumentTable).filter(InstrumentTable.id.in_([id1, id2,
> ..., idn])).all()*# and sort res with given ids order*
>
> is much faster than (especially in low network connections):
>
> instuments = [session.query(InstrumentTable).get(id_) *for* id_ *in* [id1,
> id2, ..., idn]]
>
>
> вторник, 23 августа 2022 г. в 21:04:05 UTC+3, Mike Bayer:
>
>
>
> On Tue, Aug 23, 2022, at 1:50 PM, Evgenii wrote:
>
> Hi there!
>
>
> Please help me to understand:
> I want to make two equal queries, that sends real queries to database each
> time:
>
>
> session.query(InstrumentTable).get(instr_id)
>
> session.query(InstrumentTable).get(instr_id)
>
>
> The problem is that second query returns instance from identity_map.
> I know how to hardcode it, but I want a beautiful solution.
>
>
>
>1. No, I cannot use refresh method, because it is not vectorized
>(this part of code is used in custom vectorized refresh method,
>because in our case each transaction is slow and therefore expensive) -
>having vectorized SA refresh is a perfect solution, but it does not
>exist.
>
>
> "vectorized". googled it, seems to mean "Vectorization is the process of
> converting an algorithm from operating on a single value at a time to
> operating on a set of values at one time. " OK. which would mean that
> get() is also not "vectorized" since it also operates on one value at a
> time...so..not really sure what that means.
>
> Anyway, don't use query.get(), use session.get() and pass populate_existing
>
>
> https://docs.sqlalchemy.org/en/14/orm/session_api.html?highlight=session+get#sqlalchemy.orm.Session.get
>
> session.get(InstrumentTable, id, populate_existing=True)
>
>
>
>
>
>
>1. rollback make real second query, but it seems to be hardcode.
>2. session.identity_map._dict = {} hardcode too
>3. Opening another session hardcode too.
>4. Using one of AUTOCOMMIT
Re: [sqlalchemy] Non-repeatable reads, vectorized refresh
Thank you one more time!
Just in case I leave fixed expression here:
session.execute(
select(Instance).where(Instance.id.in([list_of_ids])),
execution_options={"populate_existing": True}
)
Is there any way to set populate_existing = True for engine or sessionmaker?
вторник, 23 августа 2022 г. в 22:34:23 UTC+3, Mike Bayer:
>
>
> On Tue, Aug 23, 2022, at 3:00 PM, Evgenii wrote:
>
> Thanks a lot!
> I used “vectorized” for sqlalchemy.orm.Session.refresh method and mean
> that it would be nice to use:
>
> session.refresh(instances_list)
>
> that make a single query to database. Instead of:
>
>
>
> do this:
>
> session.execute(
>select(Instance).where(Instance.id.in([list_of_ids]),
> execution_options={"populate_existing": True})
> )
>
> all objects refreshed
>
>
>
> for el in instances_list:
> session.refresh(el)
>
> that make N queries.
>
> like:
>
> res = session.query(InstrumentTable).filter(InstrumentTable.id.in_([id1, id2,
> ..., idn])).all()*# and sort res with given ids order*
>
> is much faster than (especially in low network connections):
>
> instuments = [session.query(InstrumentTable).get(id_) *for* id_ *in* [id1,
> id2, ..., idn]]
>
>
> вторник, 23 августа 2022 г. в 21:04:05 UTC+3, Mike Bayer:
>
>
>
> On Tue, Aug 23, 2022, at 1:50 PM, Evgenii wrote:
>
> Hi there!
>
>
> Please help me to understand:
> I want to make two equal queries, that sends real queries to database each
> time:
>
>
> session.query(InstrumentTable).get(instr_id)
>
> session.query(InstrumentTable).get(instr_id)
>
>
> The problem is that second query returns instance from identity_map.
> I know how to hardcode it, but I want a beautiful solution.
>
>
>
>1. No, I cannot use refresh method, because it is not vectorized
>(this part of code is used in custom vectorized refresh method,
>because in our case each transaction is slow and therefore expensive) -
>having vectorized SA refresh is a perfect solution, but it does not
>exist.
>
>
> "vectorized". googled it, seems to mean "Vectorization is the process of
> converting an algorithm from operating on a single value at a time to
> operating on a set of values at one time. " OK. which would mean that
> get() is also not "vectorized" since it also operates on one value at a
> time...so..not really sure what that means.
>
> Anyway, don't use query.get(), use session.get() and pass populate_existing
>
>
> https://docs.sqlalchemy.org/en/14/orm/session_api.html?highlight=session+get#sqlalchemy.orm.Session.get
>
> session.get(InstrumentTable, id, populate_existing=True)
>
>
>
>
>
>
>1. rollback make real second query, but it seems to be hardcode.
>2. session.identity_map._dict = {} hardcode too
>3. Opening another session hardcode too.
>4. Using one of AUTOCOMMIT, READ COMMITTED, READ UNCOMMITTED,
>REPEATABLE READ, SERIALIZABLE as isolation_level seems to be a true
>way,
>but none of them does not work as I expect.
>
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/0808aeb1-e32d-4a42-bac2-959ee6d03ba7n%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/0808aeb1-e32d-4a42-bac2-959ee6d03ba7n%40googlegroups.com?utm_medium=email_source=footer>
> .
>
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com.
>
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/4af71fa7-9cbe-478f-84dc-8df6ba856a1en%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/4af71fa7
Re: [sqlalchemy] Non-repeatable reads, vectorized refresh
Thanks a lot!
I used “vectorized” for sqlalchemy.orm.Session.refresh method and mean that
it would be nice to use:
session.refresh(instances_list)
that make a single query to database. Instead of:
for el in instances_list:
session.refresh(el)
that make N queries.
like:
res = session.query(InstrumentTable).filter(InstrumentTable.id.in_([id1, id2,
..., idn])).all()# and sort res with given ids order
is much faster than (especially in low network connections):
instuments = [session.query(InstrumentTable).get(id_) for id_ in [id1, id2,
..., idn]]
вторник, 23 августа 2022 г. в 21:04:05 UTC+3, Mike Bayer:
>
>
> On Tue, Aug 23, 2022, at 1:50 PM, Evgenii wrote:
>
> Hi there!
>
>
> Please help me to understand:
> I want to make two equal queries, that sends real queries to database each
> time:
>
>
> session.query(InstrumentTable).get(instr_id)
>
> session.query(InstrumentTable).get(instr_id)
>
>
> The problem is that second query returns instance from identity_map.
> I know how to hardcode it, but I want a beautiful solution.
>
>
>
>1. No, I cannot use refresh method, because it is not vectorized
>(this part of code is used in custom vectorized refresh method,
>because in our case each transaction is slow and therefore expensive) -
>having vectorized SA refresh is a perfect solution, but it does not
>exist.
>
>
> "vectorized". googled it, seems to mean "Vectorization is the process of
> converting an algorithm from operating on a single value at a time to
> operating on a set of values at one time. " OK. which would mean that
> get() is also not "vectorized" since it also operates on one value at a
> time...so..not really sure what that means.
>
> Anyway, don't use query.get(), use session.get() and pass populate_existing
>
>
> https://docs.sqlalchemy.org/en/14/orm/session_api.html?highlight=session+get#sqlalchemy.orm.Session.get
>
> session.get(InstrumentTable, id, populate_existing=True)
>
>
>
>
>
>1. rollback make real second query, but it seems to be hardcode.
>2. session.identity_map._dict = {} hardcode too
>3. Opening another session hardcode too.
>4. Using one of AUTOCOMMIT, READ COMMITTED, READ UNCOMMITTED,
>REPEATABLE READ, SERIALIZABLE as isolation_level seems to be a true
>way,
>but none of them does not work as I expect.
>
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/0808aeb1-e32d-4a42-bac2-959ee6d03ba7n%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/0808aeb1-e32d-4a42-bac2-959ee6d03ba7n%40googlegroups.com?utm_medium=email_source=footer>
> .
>
>
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/4af71fa7-9cbe-478f-84dc-8df6ba856a1en%40googlegroups.com.
[sqlalchemy] Non-repeatable reads, vectorized refresh
Hi there!
Please help me to understand:
I want to make two equal queries, that sends real queries to database each
time:
session.query(InstrumentTable).get(instr_id)
session.query(InstrumentTable).get(instr_id)
The problem is that second query returns instance from identity_map.
I know how to hardcode it, but I want a beautiful solution.
1. No, I cannot use refresh method, because it is not vectorized
(this part of code is used in custom vectorized refresh method,
because in our case each transaction is slow and therefore expensive) -
having vectorized SA refresh is a perfect solution, but it does not
exist.
2. rollback make real second query, but it seems to be hardcode.
3. session.identity_map._dict = {} hardcode too
4. Opening another session hardcode too.
5. Using one of AUTOCOMMIT, READ COMMITTED, READ UNCOMMITTED,
REPEATABLE READ, SERIALIZABLE as isolation_level seems to be a true way,
but none of them does not work as I expect.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/0808aeb1-e32d-4a42-bac2-959ee6d03ba7n%40googlegroups.com.
Re: [sqlalchemy] sqlalchemy serialization bug (TypeError: list indices must be integers or slices, not str)
Yes, it works пятница, 24 июня 2022 г. в 17:58:46 UTC+3, Evgenii: > Sure, I can > > пятница, 24 июня 2022 г. в 17:06:39 UTC+3, Mike Bayer: > >> if you can test this patch and confirm it reads your old pickle format, I >> can merge it and release 1.4.39 >> >> diff --git a/lib/sqlalchemy/ext/mutable.py b/lib/sqlalchemy/ext/mutable.py >> index 934ac37a05..cbec06a31f 100644 >> --- a/lib/sqlalchemy/ext/mutable.py >> +++ b/lib/sqlalchemy/ext/mutable.py >> @@ -502,8 +502,14 @@ class MutableBase(object): >> >> def unpickle(state, state_dict): >> if "ext.mutable.values" in state_dict: >> -for val in state_dict["ext.mutable.values"][key]: >> -val._parents[state] = key >> +collection = state_dict["ext.mutable.values"] >> +if isinstance(collection, list): >> +# legacy format >> +for val in collection: >> +val._parents[state] = key >> +else: >> +for val in state_dict["ext.mutable.values"][key]: >> +val._parents[state] = key >> >> event.listen(parent_cls, "load", load, raw=True, propagate=True) >> event.listen( >> >> >> >> >> On Fri, Jun 24, 2022, at 10:01 AM, Mike Bayer wrote: >> >> hey there - >> >> yes this is unfortunately the case as we had to fix a bug in the mutable >> extension, issue 8133 doc'ed at >> https://docs.sqlalchemy.org/en/14/changelog/changelog_14.html#change-f8b03063d70397a8f275287ed2c8f2e6 >> >> . this seemed to be enough of an edge case that I didn't attempt to >> reconcile the old broken format. >> >> On Fri, Jun 24, 2022, at 9:16 AM, Evgenii wrote: >> >> Hello! >> We found bug in SA version `1.4.38` during deserialization. >> How to reproduce: >> >> Serialize any ORM object with pickle using environment with any SA >> compatible version `1.4.n` except `1.4.38` in , ex. `1.4.31`: >> >> ``` >> pickle.dumps(obj) >> ``` >> >> Deserialize result using environment with SA version == `1.4.38`: >> >> ``` >> obj_loaded = pickle.loads(b'\x... >> ``` >> Got: >> ``` >> Traceback (most recent call last): >> File "/home/user/anaconda3/envs/python375/lib/python3.7/code.py", line >> 90, in runcode >> exec(code, self.locals) >> File "", line 1, in >> File >> "/home/user/anaconda3/envs/python375/lib/python3.7/site-packages/sqlalchemy/orm/state.py", >> >> line 577, in __setstate__ >> state_dict["manager"](self, inst, state_dict) >> File >> "/home/user/anaconda3/envs/python375/lib/python3.7/site-packages/sqlalchemy/orm/instrumentation.py", >> >> line 506, in __call__ >> manager.dispatch.unpickle(state, state_dict) >> File >> "/home/user/anaconda3/envs/python375/lib/python3.7/site-packages/sqlalchemy/event/attr.py", >> >> line 343, in __call__ >> fn(*args, **kw) >> File >> "/home/user/anaconda3/envs/python375/lib/python3.7/site-packages/sqlalchemy/ext/mutable.py", >> >> line 505, in unpickle >> for val in state_dict["ext.mutable.values"][key]: >> TypeError: list indices must be integers or slices, not str >> ``` >> >> Seems that backward compatibility is lost. >> >> >> -- >> SQLAlchemy - >> The Python SQL Toolkit and Object Relational Mapper >> >> http://www.sqlalchemy.org/ >> >> To post example code, please provide an MCVE: Minimal, Complete, and >> Verifiable Example. See http://stackoverflow.com/help/mcve for a full >> description. >> --- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to sqlalchemy+...@googlegroups.com. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/sqlalchemy/f1ef4019-5ce4-4d14-938e-7a58f17db807n%40googlegroups.com >> >> <https://groups.google.com/d/msgid/sqlalchemy/f1ef4019-5ce4-4d14-938e-7a58f17db807n%40googlegroups.com?utm_medium=email_source=footer> >> . >> >> >> >> -- >> SQLAlchemy - >> The Python SQL Toolkit and Object Relational Mapper >> >> http://www.sqlalchemy.org/ >> >> To post
Re: [sqlalchemy] sqlalchemy serialization bug (TypeError: list indices must be integers or slices, not str)
Sure, I can пятница, 24 июня 2022 г. в 17:06:39 UTC+3, Mike Bayer: > if you can test this patch and confirm it reads your old pickle format, I > can merge it and release 1.4.39 > > diff --git a/lib/sqlalchemy/ext/mutable.py b/lib/sqlalchemy/ext/mutable.py > index 934ac37a05..cbec06a31f 100644 > --- a/lib/sqlalchemy/ext/mutable.py > +++ b/lib/sqlalchemy/ext/mutable.py > @@ -502,8 +502,14 @@ class MutableBase(object): > > def unpickle(state, state_dict): > if "ext.mutable.values" in state_dict: > -for val in state_dict["ext.mutable.values"][key]: > -val._parents[state] = key > +collection = state_dict["ext.mutable.values"] > +if isinstance(collection, list): > +# legacy format > +for val in collection: > +val._parents[state] = key > +else: > +for val in state_dict["ext.mutable.values"][key]: > +val._parents[state] = key > > event.listen(parent_cls, "load", load, raw=True, propagate=True) > event.listen( > > > > > On Fri, Jun 24, 2022, at 10:01 AM, Mike Bayer wrote: > > hey there - > > yes this is unfortunately the case as we had to fix a bug in the mutable > extension, issue 8133 doc'ed at > https://docs.sqlalchemy.org/en/14/changelog/changelog_14.html#change-f8b03063d70397a8f275287ed2c8f2e6 > > . this seemed to be enough of an edge case that I didn't attempt to > reconcile the old broken format. > > On Fri, Jun 24, 2022, at 9:16 AM, Evgenii wrote: > > Hello! > We found bug in SA version `1.4.38` during deserialization. > How to reproduce: > > Serialize any ORM object with pickle using environment with any SA > compatible version `1.4.n` except `1.4.38` in , ex. `1.4.31`: > > ``` > pickle.dumps(obj) > ``` > > Deserialize result using environment with SA version == `1.4.38`: > > ``` > obj_loaded = pickle.loads(b'\x... > ``` > Got: > ``` > Traceback (most recent call last): > File "/home/user/anaconda3/envs/python375/lib/python3.7/code.py", line 90, > in runcode > exec(code, self.locals) > File "", line 1, in > File > "/home/user/anaconda3/envs/python375/lib/python3.7/site-packages/sqlalchemy/orm/state.py", > > line 577, in __setstate__ > state_dict["manager"](self, inst, state_dict) > File > "/home/user/anaconda3/envs/python375/lib/python3.7/site-packages/sqlalchemy/orm/instrumentation.py", > > line 506, in __call__ > manager.dispatch.unpickle(state, state_dict) > File > "/home/user/anaconda3/envs/python375/lib/python3.7/site-packages/sqlalchemy/event/attr.py", > > line 343, in __call__ > fn(*args, **kw) > File > "/home/user/anaconda3/envs/python375/lib/python3.7/site-packages/sqlalchemy/ext/mutable.py", > > line 505, in unpickle > for val in state_dict["ext.mutable.values"][key]: > TypeError: list indices must be integers or slices, not str > ``` > > Seems that backward compatibility is lost. > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/f1ef4019-5ce4-4d14-938e-7a58f17db807n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/f1ef4019-5ce4-4d14-938e-7a58f17db807n%40googlegroups.com?utm_medium=email_source=footer> > . > > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+...@googlegroups.com. > > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/be8f330d-d0ab-4b40-a753-df3f02a87cc7%40www.fastm
[sqlalchemy] sqlalchemy serialization bug (TypeError: list indices must be integers or slices, not str)
Hello! We found bug in SA version `1.4.38` during deserialization. How to reproduce: Serialize any ORM object with pickle using environment with any SA compatible version `1.4.n` except `1.4.38` in , ex. `1.4.31`: ``` pickle.dumps(obj) ``` Deserialize result using environment with SA version == `1.4.38`: ``` obj_loaded = pickle.loads(b'\x... ``` Got: ``` Traceback (most recent call last): File "/home/user/anaconda3/envs/python375/lib/python3.7/code.py", line 90, in runcode exec(code, self.locals) File "", line 1, in File "/home/user/anaconda3/envs/python375/lib/python3.7/site-packages/sqlalchemy/orm/state.py", line 577, in __setstate__ state_dict["manager"](self, inst, state_dict) File "/home/user/anaconda3/envs/python375/lib/python3.7/site-packages/sqlalchemy/orm/instrumentation.py", line 506, in __call__ manager.dispatch.unpickle(state, state_dict) File "/home/user/anaconda3/envs/python375/lib/python3.7/site-packages/sqlalchemy/event/attr.py", line 343, in __call__ fn(*args, **kw) File "/home/user/anaconda3/envs/python375/lib/python3.7/site-packages/sqlalchemy/ext/mutable.py", line 505, in unpickle for val in state_dict["ext.mutable.values"][key]: TypeError: list indices must be integers or slices, not str ``` Seems that backward compatibility is lost. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/f1ef4019-5ce4-4d14-938e-7a58f17db807n%40googlegroups.com.
[sqlalchemy] AttributeError("module 'sqlalchemy' has no attribute 'dialects'"
Hello!
Thank you for the previous answer.
I use prefect and dask to execute workflows, that use SA inside and I get
very strange errors periodically:
Task 'HE Quotes[5]': Exception encountered during task execution!
Traceback (most recent call last):
File
"/home/prefect_env/lib/python3.7/site-packages/prefect/engine/task_runner.py",
line 880, in get_task_run_state
logger=self.logger,
File
"/home/prefect_env/lib/python3.7/site-packages/prefect/utilities/executors.py",
line 499, in run_task_with_timeout
name=f"Task '{name}'",
File
"/home/prefect_env/lib/python3.7/site-packages/prefect/utilities/executors.py",
line 417, in run_with_multiprocess_timeout
raise result
File
"/home/prefect_env/lib/python3.7/site-packages/prefect/utilities/executors.py",
line 300, in multiprocessing_safe_run_and_retrieve
return_val = fn(*args, **kwargs)
File
"/home/prefect_env/lib/python3.7/site-packages/prefect_flows/flows/proc/HE1.py",
line 42, in transform_data_task
rd_ids = transform_data(HE_params, pref_connection_path,
config_connection_path, minio_keys, logger)
File
"/home/prefect_env/lib/python3.7/site-packages/prefect_flows/tasks/proc/he.py",
line 141, in transform_data
client.run(trim_memory)
File "/home/prefect_env/lib/python3.7/site-packages/distributed/client.py",
line 2472, in run
return self.sync(self._run, function, *args, **kwargs)
File "/home/prefect_env/lib/python3.7/site-packages/distributed/utils.py", line
311, in sync
self.loop, func, *args, callback_timeout=callback_timeout, **kwargs
File "/home/prefect_env/lib/python3.7/site-packages/distributed/utils.py", line
364, in sync
raise exc.with_traceback(tb)
File "/home/prefect_env/lib/python3.7/site-packages/distributed/utils.py", line
349, in f
result[0] = yield future
File "/home/prefect_env/lib/python3.7/site-packages/tornado/gen.py", line 762,
in run
value = future.result()
File "/home/prefect_env/lib/python3.7/site-packages/distributed/client.py",
line 2401, in _run
nanny=nanny,
File "/home/prefect_env/lib/python3.7/site-packages/distributed/core.py", line
886, in send_recv_from_rpc
result = await send_recv(comm=comm, op=key, **kwargs)
File "/home/prefect_env/lib/python3.7/site-packages/distributed/core.py", line
679, in send_recv
raise exc.with_traceback(tb)
File
"/srv/data/test_distr_calc/dask-env/lib/python3.7/site-packages/distributed/core.py",
line 521, in handle_comm
File
"/srv/data/test_distr_calc/dask-env/lib/python3.7/site-packages/distributed/scheduler.py",
line 6021, in broadcast
File
"/srv/data/test_distr_calc/dask-env/lib/python3.7/site-packages/distributed/utils.py",
line 208, in All
File
"/srv/data/test_distr_calc/dask-env/lib/python3.7/site-packages/distributed/scheduler.py",
line 6015, in send_message
File
"/srv/data/test_distr_calc/dask-env/lib/python3.7/site-packages/distributed/core.py",
line 681, in send_recv
Exception: AttributeError("module 'sqlalchemy' has no attribute 'dialects'")
The only similar problem I found is:
https://stackoverflow.com/questions/61304397/attributeerror-module-sqlalchemy-dialects-has-no-attribute-postgresql
The main issue of that is you should not name modules with built-in names.
Therefore:
- I checked, I don’t have any script named sqlalchemy.py
- Don’t have any stupid imports like import not_sa_module as sqlalchemy
- sqlalchemy is installed from pypi and has attribute dialects
Do you have any ideas to solve this?
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/1b411508-4d25-400d-8fa8-9deb6b9945abn%40googlegroups.com.
Re: [sqlalchemy] SQLAlchemy + multiprocessing problems
Works! Thank you! четверг, 31 марта 2022 г. в 15:42:25 UTC+3, Mike Bayer: > when using multiprocessing, the connection pool in the new process must be > replaced with a new one. This is usually accomplished by calling > engine.dispose(). However, to maintain the pool in the parent process as > well, replace the connection pool alone without disposing the old one: > > engine = create_engine(...) > > Session = sessionmaker(bind=engine) > > def job_update_rd(data_list): > updated = [] > with Session() as session: > for t, ts in data_list: > rd = session.query(RawDataTable).filter(and_( > RawDataTable.timestamp == t, > RawDataTable.ts == ts)).one() > > rd.ts = updated_ts[ts] > session.commit() > > updated.append(rd) > > return updated > > > def initializer(): > engine.pool = engine.pool.recreate() > > with Pool(10, initializer=initializer) as p: > upd_list = p.map(job_update_rd, chunks) > > > For many years we've advised calling engine.dispose() here as documented > at > https://docs.sqlalchemy.org/en/14/core/pooling.html#using-connection-pools-with-multiprocessing-or-os-fork > > .It was recently pointed out that this closes out the parent process' > connections, so in SQLAlchemy 1.4.33 there will be a parameter so you can > change the above code to engine.dispose(close=False). > > > > On Thu, Mar 31, 2022, at 7:06 AM, Evgenii wrote: > > > Hello! > From time to time, I need to update data in tables and multiprocessing can > speed up this process. Last example: I’m trying to update data 7M rows in > table > > > SQLAlchemy 1.4.31, psycopg2 2.8.6, PostgreSQL > > *def* *job_update_rd*(data_list): > updated = [] > *with* Session() *as* session: > *for* t, ts *in* data_list: > rd = session.query(RawDataTable).filter(and_( > RawDataTable.timestamp == t, > RawDataTable.ts == ts)).one() > > rd.ts = updated_ts[ts] > session.commit() > > updated.append(rd) > > *return* updated > *with* Pool(10) *as* p: > upd_list = p.map(job_update_rd, chunks) > > Code is very simple, but it does not work. I get these errors randomly: > >- psycopg2.OperationalError: SSL error: sslv3 alert bad record mac >- sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) SSL >SYSCALL error: EOF detected > > But this example works fine: > > *def* *other_job*(data_list): > *with* Session() *as* s: > *return* [s.query(RawDataTable).filter(and_( >RawDataTable.timestamp == t, >RawDataTable.ts == ts)).all() *for* t, ts *in* data_list] > *with* Pool(10) *as* p: > res = p.map(other_job, chunks) > > > Please, help to solve this problem. > Some people is our team also uses multiprocessing, and 1 time a week get > these errors. > > > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/4d61a93d-f83f-455c-ab2a-1cb28e154af8n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/4d61a93d-f83f-455c-ab2a-1cb28e154af8n%40googlegroups.com?utm_medium=email_source=footer> > . > > > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/26fb15e2-7ab8-420f-a48a-4bcc42ddd906n%40googlegroups.com.
[sqlalchemy] SQLAlchemy + multiprocessing problems
Hello! >From time to time, I need to update data in tables and multiprocessing can speed up this process. Last example: I’m trying to update data 7M rows in table SQLAlchemy 1.4.31, psycopg2 2.8.6, PostgreSQL def job_update_rd(data_list): updated = [] with Session() as session: for t, ts in data_list: rd = session.query(RawDataTable).filter(and_( RawDataTable.timestamp == t, RawDataTable.ts == ts)).one() rd.ts = updated_ts[ts] session.commit() updated.append(rd) return updated with Pool(10) as p: upd_list = p.map(job_update_rd, chunks) Code is very simple, but it does not work. I get these errors randomly: - psycopg2.OperationalError: SSL error: sslv3 alert bad record mac - sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) SSL SYSCALL error: EOF detected But this example works fine: def other_job(data_list): with Session() as s: return [s.query(RawDataTable).filter(and_( RawDataTable.timestamp == t, RawDataTable.ts == ts)).all() for t, ts in data_list] with Pool(10) as p: res = p.map(other_job, chunks) Please, help to solve this problem. Some people is our team also uses multiprocessing, and 1 time a week get these errors. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/4d61a93d-f83f-455c-ab2a-1cb28e154af8n%40googlegroups.com.
Re: [sqlalchemy] sqlalchemy.exc.InvalidRequestError: Can't attach instance another instance with key is already present in this session
As it was mentioned before, I create repeated elements in relationship
deliberately. Moreover, alchemy allows me to do that, but it fails during
deleting instances and modifying relationships.
Unfortunately, this is not that case, where I can start all over again. All
examples are maximally simplified and depersonalized and clearly describes
my problem.
As you STRONGLY recommend *never have* repeated elements in a relationship,
is there another way to do this in alchemy?
Just imagine in your simple example that Parent instance has list of two
absolutely same children (that children have same ids).
Is this possible to do via alchemy?
вторник, 20 июля 2021 г. в 16:10:12 UTC+3, Mike Bayer:
> if you are mapping ORM classes to the same table that is also used as the
> "secondary" table in a relationship() that can lead to the ORM inserting
> more than one row for that table. based on the name "foo_bar" I would
> imagine something like this might be going on.
>
>
>
> Pls tell how to delete instance with repeated elements in relation.
>
>
> So assuming this is a development database where you can start all over
> again, the approach here is to *never have* repeated elements in a
> relationship. When you make a "secondary" table, make sure you set the
> columns that refer to the related tables inside of a constraint, such as
> UNIQUE constraint, or more commonly make them the primary key. I should
> add this to the docs at
> https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html#many-to-many
> as this seems to not be mentioned:
>
> association_table = Table('association', Base.metadata,
> Column('left_id', Integer, ForeignKey('left.id'), primary_key=True),
> Column('right_id', Integer, ForeignKey('right.id'), primary_key=True)
> )
>
> class Parent(Base):
> __tablename__ = 'left'
> id = Column(Integer, primary_key=True)
> children = relationship("Child",
> secondary=association_table)
>
> class Child(Base):
> __tablename__ = 'right'
> id = Column(Integer, primary_key=True)
>
>
>
>
>
>
> P.S. It seems the error to be raised implicit, because "ONLY 2 were
> matched"
> понедельник, 19 июля 2021 г. в 16:22:01 UTC+3, Mike Bayer:
>
>
> This is all expected behavior, the main reason you're having problems is
> that you are using multiple sessions and mixing their results together.
> If you need to do this, there are few approaches, the most basic being to
> use the merge() method:
> https://docs.sqlalchemy.org/en/14/orm/session_api.html?highlight=session%20merge#sqlalchemy.orm.Session.merge
>
> however the main issue is that you are mixing results from multiple
> sessions, which in the vast majority of cases is unnecessary. The session
> corresponds to working in a single transaction at a time, and you should
> normally be able to complete all the work you have for a particular
> operation within that single scope.
>
> On Mon, Jul 19, 2021, at 8:31 AM, Evgenii wrote:
>
>
>
> Hello!
> I’m using many-to-many relation, and this relationship bar_list must have
> list of
> instances. Some of them can be repeated (ex. [inst1, inst2, inst1]).
> I attach very simplified code there (all of database interaction is hidden
> under the hood, user accesses database at top level, but this example
> reflects
> my problem).
>
>
> foo_bar_association = Table(
> 'foo_bar', Base.metadata,
> Column('foo_id', Integer, ForeignKey('foo.id')),
> Column('bar_id', Integer, ForeignKey('bar.id'))
> )
> *class* *FooTable*(Base):
> __tablename__ = 'foo'
>
> id = Column(Integer, primary_key=*True*)
> type = Column(String, nullable=*False*)
>
> bar_list = relationship('BarTable',
> secondary=foo_bar_association,
> lazy='subquery')
>
> *def* *__init__*(self, type_, bar_list):
> self.type = type_
> self.bar_list = bar_list
> *class* *BarTable*(Base):
> __tablename__ = 'bar'
>
> id = Column(Integer, primary_key=*True*)
> name = Column(String, nullable=*False*)
>
> *def* *__init__*(self, name):
> self.name = name
>
>
> When I pass two exact instances [bar_one, bar_same_one](as a
> relationship) have to be related (before session.close()) to different
> sessions I have this error:
> sqlalchemy.exc.InvalidRequestError: Can't attach instance another instance
> with key is already present in this session.
>
>
> *with* Session() *as* session:
> bar_one = session.query(BarTable).get(1)
> *with* Session() *as* session:
> bar_same_one = session.query(B
Re: [sqlalchemy] sqlalchemy.exc.InvalidRequestError: Can't attach instance another instance with key is already present in this session
Mike, thank you for the answer.
But I have another problem with deleting the instance.
Even though all instances belong to the same session (it is possible to
push foo instance):
with Session() as session:
b1 = session.query(BarTable).get(1)
b2 = session.query(BarTable).get(1)
foo = FooTable('some_type', [b1, b2])
session.add(foo)
session.commit()
I get this error:
sqlalchemy.orm.exc.StaleDataError: DELETE statement on table 'foo_bar' expected
to delete 1 row(s); Only 2 were matched.
After simple delete ex.:
with Session() as session:
foo = session.query(FooTable).get()
session.delete(foo)
session.commit()
Pls tell how to delete instance with repeated elements in relation.
P.S. It seems the error to be raised implicit, because "ONLY 2 were matched"
понедельник, 19 июля 2021 г. в 16:22:01 UTC+3, Mike Bayer:
> This is all expected behavior, the main reason you're having problems is
> that you are using multiple sessions and mixing their results together.
> If you need to do this, there are few approaches, the most basic being to
> use the merge() method:
> https://docs.sqlalchemy.org/en/14/orm/session_api.html?highlight=session%20merge#sqlalchemy.orm.Session.merge
>
> however the main issue is that you are mixing results from multiple
> sessions, which in the vast majority of cases is unnecessary. The session
> corresponds to working in a single transaction at a time, and you should
> normally be able to complete all the work you have for a particular
> operation within that single scope.
>
> On Mon, Jul 19, 2021, at 8:31 AM, Evgenii wrote:
>
>
> Hello!
> I’m using many-to-many relation, and this relationship bar_list must have
> list of
> instances. Some of them can be repeated (ex. [inst1, inst2, inst1]).
> I attach very simplified code there (all of database interaction is hidden
> under the hood, user accesses database at top level, but this example
> reflects
> my problem).
>
> foo_bar_association = Table(
> 'foo_bar', Base.metadata,
> Column('foo_id', Integer, ForeignKey('foo.id')),
> Column('bar_id', Integer, ForeignKey('bar.id'))
> )
> *class* *FooTable*(Base):
> __tablename__ = 'foo'
>
> id = Column(Integer, primary_key=*True*)
> type = Column(String, nullable=*False*)
>
> bar_list = relationship('BarTable',
> secondary=foo_bar_association,
> lazy='subquery')
>
> *def* *__init__*(self, type_, bar_list):
> self.type = type_
> self.bar_list = bar_list
> *class* *BarTable*(Base):
> __tablename__ = 'bar'
>
> id = Column(Integer, primary_key=*True*)
> name = Column(String, nullable=*False*)
>
> *def* *__init__*(self, name):
> self.name = name
>
> When I pass two exact instances [bar_one, bar_same_one](as a
> relationship) have to be related (before session.close()) to different
> sessions I have this error:
> sqlalchemy.exc.InvalidRequestError: Can't attach instance another instance
> with key is already present in this session.
>
> *with* Session() *as* session:
> bar_one = session.query(BarTable).get(1)
> *with* Session() *as* session:
> bar_same_one = session.query(BarTable).get(1)
> *with* Session() *as* session:
> foo = FooTable('some_type', [bar_one, bar_same_one])
> session.add(foo)
> session.commit()
>
> But I don’t have any error after I create instances in same session:
>
> *with* Session() *as* session:
> bar_one = session.query(BarTable).get(1)
> bar_same_one = session.query(BarTable).get(1)
> *with* Session() *as* session:
> foo = FooTable('some_type', [bar_one, bar_same_one])
> session.add(foo)
> session.commit()
>
> And after:
>
> *with* Session() *as* session:
> foo = FooTable('some_type', [bar_one, bar_one])
> session.add(foo)
> session.commit()
>
> I can make a work around:
>
>1. Find unique instances in bar_list and replace not unique with unique
>or
>2. Get all bar_list ids and get all instances in same session before
>adding
>foo instance.
>But both of them are not pure python way and seems to be complicated
>and ugly.
>I hope there is simple alchemy solution (as adding simple attribute in
>relationship)
>
> Python 3.7.10
> SQLAlchemy==1.4.15
>
>
>
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received thi
[sqlalchemy] sqlalchemy.exc.InvalidRequestError: Can't attach instance another instance with key is already present in this session
Hello!
I’m using many-to-many relation, and this relationship bar_list must have
list of
instances. Some of them can be repeated (ex. [inst1, inst2, inst1]).
I attach very simplified code there (all of database interaction is hidden
under the hood, user accesses database at top level, but this example
reflects
my problem).
foo_bar_association = Table(
'foo_bar', Base.metadata,
Column('foo_id', Integer, ForeignKey('foo.id')),
Column('bar_id', Integer, ForeignKey('bar.id'))
)
class FooTable(Base):
__tablename__ = 'foo'
id = Column(Integer, primary_key=True)
type = Column(String, nullable=False)
bar_list = relationship('BarTable',
secondary=foo_bar_association,
lazy='subquery')
def __init__(self, type_, bar_list):
self.type = type_
self.bar_list = bar_list
class BarTable(Base):
__tablename__ = 'bar'
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)
def __init__(self, name):
self.name = name
When I pass two exact instances [bar_one, bar_same_one](as a relationship)
have to be related (before session.close()) to different sessions I have
this error:
sqlalchemy.exc.InvalidRequestError: Can't attach instance another instance
with key is already present in this session.
with Session() as session:
bar_one = session.query(BarTable).get(1)
with Session() as session:
bar_same_one = session.query(BarTable).get(1)
with Session() as session:
foo = FooTable('some_type', [bar_one, bar_same_one])
session.add(foo)
session.commit()
But I don’t have any error after I create instances in same session:
with Session() as session:
bar_one = session.query(BarTable).get(1)
bar_same_one = session.query(BarTable).get(1)
with Session() as session:
foo = FooTable('some_type', [bar_one, bar_same_one])
session.add(foo)
session.commit()
And after:
with Session() as session:
foo = FooTable('some_type', [bar_one, bar_one])
session.add(foo)
session.commit()
I can make a work around:
1. Find unique instances in bar_list and replace not unique with unique
or
2. Get all bar_list ids and get all instances in same session before
adding
foo instance.
But both of them are not pure python way and seems to be complicated and
ugly.
I hope there is simple alchemy solution (as adding simple attribute in
relationship)
Python 3.7.10
SQLAlchemy==1.4.15
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/8205e75c-6d78-4123-842b-4e342ad244een%40googlegroups.com.
