Re: [sqlalchemy] Re: Conditional insert in one transaction
Additional background: In the proposed code snippet, I placed the try/except around the `begin_nested()` block instead of just the `orm_session.add()` statement because the `add()` might not get flushed to database until the savepoint is released in the event the session is not auto-flushing. I also came across a nice graphical illustration of the race condition associated with concurrent INSERT here: http://rachbelaid.com/handling-race-condition-insert-with-sqlalchemy/ On Thursday, February 4, 2021 at 5:42:40 PM UTC-8 Vitaly Kruglikov wrote: > Thanks Mike! I followed up on your suggestion concerning savepoints and > came up with the following code snippet. Does this look right to you? > > ``` > try: > with orm_session.begin_nested(): > orm_session.add(record) > except psycopg2.errors.UniqueViolation as error: # or is it > `sqlalchemy.exc.IntegrityError`??? > # Matching metadata row already inserted by someone else > # Fetch and return existing record > > # We inserted it! > return record > ``` > -- 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/ded97aab-d4f3-498b-aea9-af89d2e411fan%40googlegroups.com.
Re: [sqlalchemy] Re: Conditional insert in one transaction
Thanks Mike! I followed up on your suggestion concerning savepoints and came up with the following code snippet. Does this look right to you? ``` try: with orm_session.begin_nested(): orm_session.add(record) except psycopg2.errors.UniqueViolation as error: # or is it `sqlalchemy.exc.IntegrityError`??? # Matching metadata row already inserted by someone else # Fetch and return existing record # We inserted it! return record ``` -- 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/b3002fec-c8ac-4b41-8101-1283455bfe7dn%40googlegroups.com.
Re: [sqlalchemy] Parameterized "Relationships" with Eager Loading Capability
it's not simple to work with a criteria object since you would have to walk
through it to make some programmatic decision about it, and you'd also have to
search for it.I can't completely guarantee this wont change so it would be
better if you solved your problem differently, but here's how to see it:
s = Session(e)
s.add(A(bs=[B(), B(), B(), B(data='x'), B(data='x')]))
s.commit()
a1 = s.scalar(select(A).options(selectinload(A.bs.and_(B.data == 'x'
from sqlalchemy.orm import LoaderCriteriaOption
some_b = a1.bs[0]
opt = [o for o in inspect(some_b).load_options if isinstance(o,
LoaderCriteriaOption)][0]
crit = opt.where_criteria
On Thu, Feb 4, 2021, at 6:34 PM, agrot...@gmail.com wrote:
> In the case of using the PropComparator.and_() operator
> (https://docs.sqlalchemy.org/en/14/orm/loading_relationships.html#adding-criteria-to-loader-options),
> is there any way to then introspect the relationship to tell what if any
> and_ filtering has been applied to the load?
>
> For example on a, an instance of A:
> a.bs._loader_options
>
> so that when I go to use the collection I can tell how it has been filtered /
> "reduced" from what it would have been without the additional filter?
>
>
> On Wednesday, September 23, 2020 at 5:07:20 PM UTC-4 Mike Bayer wrote:
>> __
>> the warning is not a big deal but you probably dont want to be making these
>> classes on the fly. mapping a class is not a quick operation internally,
>> it's messy and somewhat questionable in highly concurrent situations.
>>
>>
>> On Wed, Sep 23, 2020, at 9:30 PM, agrot...@gmail.com wrote:
>>> Gotcha.
>>>
>>> Should I be worried about this warning or the approach of defining a
>>> transient class to solve my problem (at least until 1.4 comes out):
>>> SAWarning: This declarative base already contains a class with the same
>>> class name and module name as my_app.graphql.queries.ATmp, and will be
>>> replaced in the string-lookup table
>>> On Wednesday, September 23, 2020 at 4:20:01 PM UTC-4 Mike Bayer wrote:
__
yeah I don't have a solution to that problem right now, as mapped
attributes are only a class-bound concept and there is no concept of an
arbitrary attribute on an object that's not associated with a class-level
mapped attribute.
I think this problem long term would be solved more through some kind of
@property selector that works from a class and is not specific to mapping.
On Wed, Sep 23, 2020, at 8:51 PM, agrot...@gmail.com wrote:
> Understood. I was thinking though some sort of alias would be an
> interesting solution to the problem outlined about `contains_eager` as
> well:
> >Keep in mind that when we load only a subset of objects into a
> >collection, that collection no longer represents what’s actually in the
> >database.
> In that rather than re-using a property which does have specific meaning,
> a new property could be created that explicitly has different meaning.
> On Wednesday, September 23, 2020 at 3:21:23 PM UTC-4 Mike Bayer wrote:
>> __
>> A.bs only goes to the "bs" collection on an A. there's no eagerloading
>> that puts the collection on some other arbitrary place.
>>
>> On Wed, Sep 23, 2020, at 6:03 PM, agrot...@gmail.com wrote:
>>> Cool, yes I think that is what I am looking for. Is there any way to
>>> alias the relationship (as read only) to: 1. allow for multiple
>>> different filters of the same property and 2. make sure when I read the
>>> value (in another place in the code), I have confidence the filter was
>>> applied?
>>>
>>> something like (I made up the syntax):
>>> q = session.query(A).options(selectinload(A.bs).and_(B.some_field ==
>>> value).as(f'bs_filtered_by_{value}')
>>> ...
>>> for a in q:
>>>for b in a.bs_filtered_by_:
>>>
>>>
>>>
>>> On Wednesday, September 23, 2020 at 12:21:41 PM UTC-4 Mike Bayer wrote:
On Wed, Sep 23, 2020, at 4:17 PM, agrot...@gmail.com wrote:
> I actually don't really care that much to have the attribute remain
> dynamic. In fact there is only one *specific* filtering that I want
> to apply to it, but that filtering will vary from (web) request to
> (web) request. This is what made me think of using contains_eager.
>
> Right now this is the best solution I have come up with, which is to
> define a temporary class that extends A and add to that class a new
> relationship with the custom filter applied. I then specify to
> selectinload that property. Is there a better way to do this?
I would still use a separate relationship on the same class, you can
always make a @hybrid_property that switches between the two
relationships depending on what you want to do.
Re: [sqlalchemy] Parameterized "Relationships" with Eager Loading Capability
In the case of using the PropComparator.and_() operator
(https://docs.sqlalchemy.org/en/14/orm/loading_relationships.html#adding-criteria-to-loader-options),
is there any way to then introspect the relationship to tell what if any
and_ filtering has been applied to the load?
For example on a, an instance of A:
a.bs._loader_options
so that when I go to use the collection I can tell how it has been filtered
/ "reduced" from what it would have been without the additional filter?
On Wednesday, September 23, 2020 at 5:07:20 PM UTC-4 Mike Bayer wrote:
> the warning is not a big deal but you probably dont want to be making
> these classes on the fly. mapping a class is not a quick operation
> internally, it's messy and somewhat questionable in highly concurrent
> situations.
>
>
> On Wed, Sep 23, 2020, at 9:30 PM, agrot...@gmail.com wrote:
>
> Gotcha.
>
> Should I be worried about this warning or the approach of defining a
> transient class to solve my problem (at least until 1.4 comes out):
> SAWarning: This declarative base already contains a class with the same
> class name and module name as my_app.graphql.queries.ATmp, and will be
> replaced in the string-lookup table
> On Wednesday, September 23, 2020 at 4:20:01 PM UTC-4 Mike Bayer wrote:
>
>
> yeah I don't have a solution to that problem right now, as mapped
> attributes are only a class-bound concept and there is no concept of an
> arbitrary attribute on an object that's not associated with a class-level
> mapped attribute.
>
> I think this problem long term would be solved more through some kind of
> @property selector that works from a class and is not specific to
> mapping.
>
> On Wed, Sep 23, 2020, at 8:51 PM, agrot...@gmail.com wrote:
>
> Understood. I was thinking though some sort of alias would be an
> interesting solution to the problem outlined about `contains_eager` as well:
> >Keep in mind that when we load only a subset of objects into a
> collection, that collection no longer represents what’s actually in the
> database.
> In that rather than re-using a property which does have specific meaning,
> a new property could be created that explicitly has different meaning.
> On Wednesday, September 23, 2020 at 3:21:23 PM UTC-4 Mike Bayer wrote:
>
>
> A.bs only goes to the "bs" collection on an A. there's no eagerloading
> that puts the collection on some other arbitrary place.
>
> On Wed, Sep 23, 2020, at 6:03 PM, agrot...@gmail.com wrote:
>
> Cool, yes I think that is what I am looking for. Is there any way to alias
> the relationship (as read only) to: 1. allow for multiple different filters
> of the same property and 2. make sure when I read the value (in another
> place in the code), I have confidence the filter was applied?
>
> something like (I made up the syntax):
> q = session.query(A).options(selectinload(A.bs).and_(B.some_field ==
> value).as(f'bs_filtered_by_{value}')
> ...
> for a in q:
>for b in a.bs_filtered_by_:
>
>
>
> On Wednesday, September 23, 2020 at 12:21:41 PM UTC-4 Mike Bayer wrote:
>
>
>
> On Wed, Sep 23, 2020, at 4:17 PM, agrot...@gmail.com wrote:
>
> I actually don't really care that much to have the attribute remain
> dynamic. In fact there is only one *specific* filtering that I want to
> apply to it, but that filtering will vary from (web) request to (web)
> request. This is what made me think of using contains_eager.
>
> Right now this is the best solution I have come up with, which is to
> define a temporary class that extends A and add to that class a new
> relationship with the custom filter applied. I then specify to selectinload
> that property. Is there a better way to do this?
>
>
> I would still use a separate relationship on the same class, you can
> always make a @hybrid_property that switches between the two relationships
> depending on what you want to do.
>
> In version 1.4, which will be in betas as soon as I can get a huge amount
> of new docs written, you will have a potentially better option for this
> which is the PropComparator.and_() operator. you can play with this now
> from git master if you were interested:
>
>
> https://docs.sqlalchemy.org/en/14/orm/loading_relationships.html#adding-criteria-to-loader-options
>
> that way you'd say:
>
> session.query(A).options(selectinload(A.bs).and_(B.some_field == value))
>
> that might be what you're waiting for here
>
>
>
>
> # Run this code for each web request, reading some_field_value from the
> value the client specifies in the request:
> some_field_value = ...
>
> class ATmp(A):
> bs_temp = relationship(
> lambda: models.B,
> primaryjoin=(
> (models.A.id == models.B.a_id)
> & (models.B.some_field == some_field_value)
> ),
> )
>
> q = return db.session.query(ATmp).options(selectinload(cls.bs_temp))
> # iterate over the q (which in iterable of "A"s) and for each A, iterate
> over the bs_temp, which is a filtered
Re: [sqlalchemy] Invalidated Collection
Thanks, Mike! I have some studying to do...
On Wednesday, February 3, 2021 at 6:42:17 PM UTC-5 Mike Bayer wrote:
>
>
> On Wed, Feb 3, 2021, at 6:23 PM, Christian Henning wrote:
>
> Hi Mike,
>
> thanks for your advice! I'll make the changes.
>
> But let me ask you one thing. My classmethod create() is but more complex
> than I have posted. It's meant to catch IntegrityError so that unique
> constraints are enforced. Image a User table has a "unique" constraint on
> the name. When I try to create a user with the same name the create() would
> catch the exception and return the original user. I believe this only
> possible when all objects are committed to the database.
>
> class ModelBase:
>
> @classmethod
> def create(cls, session, **kwargs):
> obj = cls(**kwargs)
> try:
> obj.save(session)
> except IntegrityError:
> # print('HOLA - Integrity Error')
>
> session.rollback()
> obj = cls.find(session, **kwargs)
>
> return obj
>
> def save(self, session):
> session.add(self)
> session.commit()
>
> How would you solve the problem? Is there anything in the documentation?
>
>
> yes, use a SAVEPOINT for that, so you can roll back to the savepoint and
> still have the transaction continue:
>
>
> https://docs.sqlalchemy.org/en/13/faq/sessions.html#but-why-does-flush-insist-on-issuing-a-rollback
>
> so you can place in your method:
>
> session.begin_nested():
> session.add(obj)
> try:
> session.flush()
> except IntegrityError:
> session.rollback()
> else:
> session.commit()
>
>
>
>
> Thanks,
> Christian
>
>
>
>
>
> On Wednesday, February 3, 2021 at 5:12:30 PM UTC-5 Mike Bayer wrote:
>
>
> the session.commit() method expires all attributes by default:
>
> https://docs.sqlalchemy.org/en/13/orm/session_basics.html#committing
>
> your code is organized in an unusual way such that transactions are being
> committed inside of attribute assignment operations:
>
> jack.addresses.append(Address.create(session))
>
> We can expand this out as follows:
>
> addr = jack.addresses
> address = Address()
> session.add(address)
> session.commit() # <--- expires all attributes on "jack", "address",
> invalidates "addr"
> attr.append(address) # <--- this is invalid, transaction was already
> commited
>
> I would strongly advise keeping session scope manage external to the set
> of operations you are performing. See
> https://docs.sqlalchemy.org/en/13/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it
>
> for guidelines.
>
>
> On Wed, Feb 3, 2021, at 2:27 PM, Christian Henning wrote:
>
> I don't understand why SQLAlchemy gives me the following warning:
>
> SAWarning: This collection has been invalidated.
> util.warn("This collection has been invalidated.")
>
> Here is a minimal code example:
>
> -
> -
>
> import sqlalchemy
> print(sqlalchemy.__version__)
>
> from sqlalchemy import create_engine
> from sqlalchemy import Column, Integer, ForeignKey
>
> from sqlalchemy.exc import IntegrityError
> from sqlalchemy.ext.declarative import declarative_base
>
> from sqlalchemy.orm import relationship
> from sqlalchemy.orm import sessionmaker
>
> Base = declarative_base()
>
> class ModelBase:
>
> @classmethod
> def create(cls, session, **kwargs):
> obj = cls(**kwargs)
> session.add(obj)
> session.commit()
>
> return obj
>
>
> class User(ModelBase, Base):
> __tablename__ = 'users'
>
> id = Column(Integer, primary_key=True)
>
> addresses = relationship("Address", order_by="Address.id",
> back_populates="user")
>
> class Address(ModelBase, Base):
> __tablename__ = 'addresses'
>
> id = Column(Integer, primary_key=True)
>
> user_id = Column(Integer, ForeignKey('users.id'))
>
> user = relationship("User", back_populates="addresses")
>
>
> engine = create_engine('sqlite:///:memory:', echo=False)
> Base.metadata.create_all(engine)
>
> Session = sessionmaker(bind=engine)
> session = Session()
>
> jack = User.create(session)
> jack.addresses.append(Address.create(session))
>
> -
> -
> I have tested the code with SQLAlchemy for 1.3.18 and 1.4.0b1. I'm using
> python 3.9.1
>
> When I rewrite the last few lines a bit the warning disappears. For
> instance:
>
> jack = User.create(session)
> a = Address.create(session)
> jack.addresses.append(a)
>
>
> Any insight is appreciated. I'm still learning.
>
> Thanks,
> Christian
>
>
>
> --
> 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
Re: [sqlalchemy] SQLAlchemy (v.1.3.22) can not find Teradata engine inside Amazon Glue Job's script in Amazon environment
Ah, OK, so the real problem is that the teradata package is trying to
load a .so file from site-packages.zip and failing. This presumably
happens when the module is imported, and Python is catching the
underlying exception and raising an ImportError instead.
It sounds like the teradata package is not expecting to be loaded from
a zip file at all, so you might have to find a different way of
packaging it for the Amazon environment. As you suspected, it's not an
SQLAlchemy question any more.
Good luck!
Simon
On Thu, Feb 4, 2021 at 10:43 AM Anhelina Rudkovska
wrote:
>
> Thanks a lot for your answer and for explanations, Simon!
> According to your suggestions I checked yesterday site-packages.zip and yes,
> directory teradatasqlalchemy-17.0.0.0.dist-info contains entry_point.txt. I
> also compared all content of site-packages.zip with the same libs'
> directories in python site-packages folder and everything the same. Also I
> was trying to use site-packages.zip created in different systems: windows
> through git bash, macos and inside linux-based docker container with mounted
> volume to save .zip I got in container - so in every try it was created using
> command line interfaces.
> Some screenshots of .zip file content and corresponding .dist-info content
> were attached.
>
> Also I found out the way to resolve problem mentioned in this topic
> yesterday, there were such steps:
> > from sqalchemy.dialects import registry
> > registry.register("teradatasql", "teradatasqlalchemy.dialect",
> > "TeradataDialect")
>
> Engine was finally successfully created inside AWS Glue job's env, but then
> OSError with teradatasql.so file was occurred (also there is attached
> screenshot). In local env there is no such problem.
>
> I believe this is not related to SQAlchemy itself, but to Spark context and
> Amazon environment.
> https://stackoverflow.com/questions/61931878/running-teradatasql-driver-for-python-code-using-spark
> here is the same topic, but everything in my site-packages.zip identical to
> ordinary site-packages in python dir.
>
> I 99% sure that it's not the question to SQAlchemy team, but to summarize the
> state of an issue I mentioned it. And probably I also hope that someone
> accidentally know something about it. A big sorry for wasting your time and
> thank you for an effort you did!
>
> BR, Anhelina
> вторник, 2 февраля 2021 г. в 19:04:00 UTC+2, Simon King:
>>
>> SQLAlchemy uses setuptools entry points to load database drivers.
>> Here's the definition for the teradata dialect:
>>
>> https://github.com/Teradata/sqlalchemy-teradata/blob/master/setup.py#L25
>>
>> For that to work, you would normally have a directory called something
>> like "sqlalchemy_teradata-0.1.0.dist-info" (or possible .egg-info) in
>> your site-packages. The directory would contain an entry_points.txt
>> file that points to the dialect class.
>>
>> Does your site-packages.zip contain that dist info directory with the
>> entry_points file inside?
>>
>> Simon
>>
>> On Mon, Feb 1, 2021 at 4:50 PM Anhelina Rudkovska
>> wrote:
>> >
>> >
>> > Everything works as expected on local machine.
>> >
>> > Code fails where SQLAlchemy db engine initializes. We use latest
>> > (17.0.0.8) release of https://pypi.org/project/teradatasqlalchemy/ library
>> > to provide DB engine for SQLAlchemy. SQLAlchemy reports that it can not
>> > load plugin teradatasql. I attached screenshot with error and piece of
>> > code which is used to establish connection.
>> > Seems like library pkg_resources which is called inside SQLAlchemy can't
>> > resolve teradatasql inside Amazon environment from .zip. Site-packages
>> > shipped for Amazon as site-packages.zip placed on AWS s3.
>> > Direct imports of teradatasql or pkg_resources work fine (or
>> > teradatasqlalchemy which is located in .zip with site-packages on s3 too).
>> > Site-packages in archive look same as
>> > site-packages in their directory on local machine (i.e. where python
>> > located or in virtual env, or inside filesystem of docker container).
>> >
>> > To develop script and run ETL job locally we use container (as described
>> > here
>> > https://aws.amazon.com/blogs/big-data/developing-aws-glue-etl-jobs-locally-using-a-container/)
>> > created from our image (installation of python libraries for script from
>> > requirements.txt in Dockerfile was added) which inherits from
>> > amazon/aws-glue-libs.
>> >
>> > I also notified Amazon Support.
>> >
>> > --
>> > 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
