Re: [sqlalchemy] Re: Conditional insert in one transaction

[Mike Bayer]

for inserts, not really.   if you want to guard against duplicate, the pattern 
is use a savepoint (begin_nested()), catch integrity error, then use the new 
row if someone else inserted it.this is the more performant option as well 
as opposed to pessimistic locking.

https://docs.sqlalchemy.org/en/14/orm/session_transaction.html#session-begin-nested
  includes an example with merge().


The *other* way to do it is to use INSERT..ON CONFLICT (PostgreSQL, SQLite) or 
INSERT..ON DUPLICATE KEY (MySQL, MariaDB) statements.  These are popular 
choices as well but you need to construct them as statement objects.




On Wed, Feb 3, 2021, at 8:49 PM, Vitaly Kruglikov wrote:
> How can the query/merge or query/add be performed atomically? What would 
> happen if between the two calls another process inserted a row with the same 
> unique key? Wouldn't the `s.merge()` then trigger a 
> `psycopg2.errors.UniqueViolation` exception (in postgres case) when the new 
> row insert is flushed? Is there a way to make the execution of the ORM atomic 
> so that the `s.merge()` would be guaranteed to succeed (barring network 
> errors, etc.)? 
> 

> -- 
> 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/d117fb88-2fd5-4006-a60f-424cc25c4bd7n%40googlegroups.com
>  
> .

-- 
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/c5176c7b-5c25-4fcd-8891-9f91bf87f554%40www.fastmail.com.

Re: [sqlalchemy] Re: Conditional insert in one transaction

[Vitaly Kruglikov]

How can the query/merge or query/add be performed atomically? What would 
happen if between the two calls another process inserted a row with the 
same unique key? Wouldn't the `s.merge()` then trigger a 
`psycopg2.errors.UniqueViolation` exception (in postgres case) when the new 
row insert is flushed? Is there a way to make the execution of the ORM 
atomic so that the `s.merge()` would be guaranteed to succeed (barring 
network errors, etc.)?

-- 
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/d117fb88-2fd5-4006-a60f-424cc25c4bd7n%40googlegroups.com.

Re: [sqlalchemy] Invalidated Collection

[Mike Bayer]

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.

Re: [sqlalchemy] Invalidated Collection

[Christian Henning]

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?

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 sqlalchemy+...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/7a369ff7-659c-4ee4-a8d0-a786ec3579aen%40googlegroups.com
>  
> 
> .
>
>
>

-- 
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 

Re: [sqlalchemy] Invalidated Collection

[Mike Bayer]

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 sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/7a369ff7-659c-4ee4-a8d0-a786ec3579aen%40googlegroups.com
>  
> .

-- 
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/f16f3de5-b48d-4c89-9ebf-2430e65c579f%40www.fastmail.com.

[sqlalchemy] Invalidated Collection

[Christian Henning]

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 sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/7a369ff7-659c-4ee4-a8d0-a786ec3579aen%40googlegroups.com.

Re: [sqlalchemy] Creating column SQLAlchemy property on parent class based on child column property

[Mike Bayer]

I think what the OP would have to do is write a UNION, that is:

s.query(Child1).filter(Child1.thing == "thing").union(
   s.query(Child2).filter(Child2.thing == "thing")
)

that's how this problem is approached in SQL.With SQLAlchemy, any problem 
should be solved by considering the desired structure in SQL first.


On Wed, Feb 3, 2021, at 10:14 AM, Simon King wrote:
> I don't think you're going to find a way to do that built in to SQLAlchemy.
> 
> When you write "session.query(Parent)", SQLAlchemy constructs a query
> against the "parent" table. But to filter by your "is_done" property,
> it would suddenly need to join every child table into the query and
> construct a complicated WHERE clause along the lines of "(type_ =
> 'child_one' AND child_one.requested_type_one =
> child_one.delivered_type_one) OR (type_ = 'child_two' AND
> child_two.requested_type_two = child_one.delivered_type_two)".
> 
> In theory, I think you *could* implement this as a hybrid property on
> Parent, where the "expression" part of the hybrid property constructs
> a subquery with a union or a join of all the child tables. It'll be
> pretty messy, and might not perform particularly well.
> 
> Simon
> 
> On Wed, Feb 3, 2021 at 10:08 AM Mehrdad Pedramfar
>  wrote:
> >
> > The structure I have created is like below:
> >
> >
> > class Parent(Base):
> > __tablename__ = 'parent'
> >
> > id = Field(
> > Integer,
> > primary_key=True
> > )
> >
> > type_ = Field(
> > String(50),
> > readonly=True
> > )
> >
> > __mapper_args__ = {
> > 'polymorphic_on': type_,
> > }
> >
> > class ChildOne(Parent):
> > __tablename__ = 'child_one'
> > __mapper_args__ = {
> > 'polymorphic_identity': 'child_one'
> > }
> >
> > id = Field(
> > Integer,
> > ForeignKey('parent.id'),
> > primary_key=True
> > )
> >
> > requested_type_one = Column(
> > Integer,
> > nullable=False,
> > )
> > delivered_type_one = Column(
> > Integer,
> > nullable=False,
> > )
> > is_done = column_property(
> > requested_type_one == delivered_type_one
> > )
> >
> > class ChildTwo(Parent):
> > __tablename__ = 'child_two'
> > __mapper_args__ = {
> > 'polymorphic_identity': 'child_two'
> > }
> >
> > id = Field(
> > Integer,
> > ForeignKey('parent.id'),
> > primary_key=True
> > )
> >
> > requested_type_two = Column(
> > Integer,
> > nullable=False,
> > )
> > delivered_type_two = Column(
> > Integer,
> > nullable=False,
> > )
> > is_done = column_property(
> > requested_type_two == delivered_type_two
> > )
> >
> >
> > What I am looking for is to execute ORM query like this:
> >
> > session.query(Parent).filter(Parent.is_done.is_(True)).all()
> >
> > which raises `Parent class does not have is_done` error.
> >
> > I want  that parent class gets Child class's `is_done` based on different 
> > types of child classes, I have tried to created `is_done` as 
> > `column_property` on parent but I couldn't make it work. Also I tried using 
> > `hybrid_property` and neither it is.
> >
> > What should I do to make Parent class get `is_done` from its children?
> >
> > stackoverflow link: 
> > https://stackoverflow.com/questions/66024558/creating-column-sqlalchemy-property-on-parent-class-based-on-child-column-proper
> >
> > --
> > 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/1310342569.2406235.1612346870964%40mail.yahoo.com.
> 
> -- 
> 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 
> 

Re: [sqlalchemy] Creating column SQLAlchemy property on parent class based on child column property

[Simon King]

I don't think you're going to find a way to do that built in to SQLAlchemy.

When you write "session.query(Parent)", SQLAlchemy constructs a query
against the "parent" table. But to filter by your "is_done" property,
it would suddenly need to join every child table into the query and
construct a complicated WHERE clause along the lines of "(type_ =
'child_one' AND child_one.requested_type_one =
child_one.delivered_type_one) OR (type_ = 'child_two' AND
child_two.requested_type_two = child_one.delivered_type_two)".

In theory, I think you *could* implement this as a hybrid property on
Parent, where the "expression" part of the hybrid property constructs
a subquery with a union or a join of all the child tables. It'll be
pretty messy, and might not perform particularly well.

Simon

On Wed, Feb 3, 2021 at 10:08 AM Mehrdad Pedramfar
 wrote:
>
> The structure I have created is like below:
>
>
> class Parent(Base):
> __tablename__ = 'parent'
>
> id = Field(
> Integer,
> primary_key=True
> )
>
> type_ = Field(
> String(50),
> readonly=True
> )
>
> __mapper_args__ = {
> 'polymorphic_on': type_,
> }
>
> class ChildOne(Parent):
> __tablename__ = 'child_one'
> __mapper_args__ = {
> 'polymorphic_identity': 'child_one'
> }
>
> id = Field(
> Integer,
> ForeignKey('parent.id'),
> primary_key=True
> )
>
> requested_type_one = Column(
> Integer,
> nullable=False,
> )
> delivered_type_one = Column(
> Integer,
> nullable=False,
> )
> is_done = column_property(
> requested_type_one == delivered_type_one
> )
>
> class ChildTwo(Parent):
> __tablename__ = 'child_two'
> __mapper_args__ = {
> 'polymorphic_identity': 'child_two'
> }
>
> id = Field(
> Integer,
> ForeignKey('parent.id'),
> primary_key=True
> )
>
> requested_type_two = Column(
> Integer,
> nullable=False,
> )
> delivered_type_two = Column(
> Integer,
> nullable=False,
> )
> is_done = column_property(
> requested_type_two == delivered_type_two
> )
>
>
> What I am looking for is to execute ORM query like this:
>
> session.query(Parent).filter(Parent.is_done.is_(True)).all()
>
> which raises `Parent class does not have is_done` error.
>
> I want  that parent class gets Child class's `is_done` based on different 
> types of child classes, I have tried to created `is_done` as 
> `column_property` on parent but I couldn't make it work. Also I tried using 
> `hybrid_property` and neither it is.
>
> What should I do to make Parent class get `is_done` from its children?
>
> stackoverflow link: 
> https://stackoverflow.com/questions/66024558/creating-column-sqlalchemy-property-on-parent-class-based-on-child-column-proper
>
> --
> 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/1310342569.2406235.1612346870964%40mail.yahoo.com.

-- 
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/CAFHwexdFiYC-AotO5ZNcNFLGfnYXEHmJpHB%3DWg3hQ0mCXHKu5g%40mail.gmail.com.

[sqlalchemy] Creating column SQLAlchemy property on parent class based on child column property

[Mehrdad Pedramfar]

The structure I have created is like below:

    class Parent(Base):        __tablename__ = 'parent'            id = Field(  
          Integer,            primary_key=True        )            type_ = 
Field(            String(50),            readonly=True        )            
__mapper_args__ = {            'polymorphic_on': type_,        }        class 
ChildOne(Parent):        __tablename__ = 'child_one'        __mapper_args__ = { 
           'polymorphic_identity': 'child_one'        }            id = Field(  
          Integer,            ForeignKey('parent.id'),            
primary_key=True        )            requested_type_one = Column(            
Integer,            nullable=False,        )        delivered_type_one = 
Column(            Integer,            nullable=False,        )        is_done 
= column_property(            requested_type_one == delivered_type_one        ) 
       class ChildTwo(Parent):        __tablename__ = 'child_two'        
__mapper_args__ = {            'polymorphic_identity': 'child_two'        }     
       id = Field(            Integer,            ForeignKey('parent.id'),      
      primary_key=True        )            requested_type_two = Column(         
   Integer,            nullable=False,        )        delivered_type_two = 
Column(            Integer,            nullable=False,        )        is_done 
= column_property(            requested_type_two == delivered_type_two        )

What I am looking for is to execute ORM query like this:
    session.query(Parent).filter(Parent.is_done.is_(True)).all()
which raises `Parent class does not have is_done` error.
I want  that parent class gets Child class's `is_done` based on different types 
of child classes, I have tried to created `is_done` as `column_property` on 
parent but I couldn't make it work. Also I tried using `hybrid_property` and 
neither it is.
What should I do to make Parent class get `is_done` from its children?
stackoverflow link: 
https://stackoverflow.com/questions/66024558/creating-column-sqlalchemy-property-on-parent-class-based-on-child-column-proper

-- 
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/1310342569.2406235.1612346870964%40mail.yahoo.com.