CODEWITHSUNDEEP
python-3.xsqlalchemy
P Bouillon
P Bouillon

Reputation: 11

SAWarning: Evaluating non-mapped column expression

I have been testing sqlalchemy for a few days and have some problems using the update method in ORM mode.

What I basically want to do is to update a table from the result of a subquery performed on a second table.

Here is a simplified example:

from sqlalchemy import create_engine, Column, Integer, Float, Boolean
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session

Base = declarative_base()
engine = create_engine('postgresql+psycopg2://urldb', echo=True)
session = Session(engine)

class User1(Base):
    __tablename__ = 'user1'
    index = Column(Integer, primary_key=True)
    value1 = Column(Float)
    value2 = Column(Boolean)


class User2(Base):
    __tablename__ = 'user2'
    index = Column(Integer, primary_key=True)
    value1 = Column(Float)
    value2 = Column(Boolean)

try:
    User1.__table__.drop(engine)
    User2.__table__.drop(engine)
    Base.metadata.create_all(engine)
except:
    Base.metadata.create_all(engine)


user1 = User1(value1=12, value2=True)
user2 = User1(value1=15, value2=True)
user3 = User1(value1=7, value2=True)
user4 = User2(value1=12, value2=True)
user5 = User2(value1=15, value2=False)

session.add(user1)
session.add(user2)
session.add(user3)
session.add(user4)
session.add(user5)

session.flush()
session.commit()

stmt = session.query(User2).filter(User2.value1 > 12).subquery()

session.query(User1).filter(User1.value1 == stmt.c.value1).update({User1.value2 : stmt.c.value2})
session.commit()

I expect value2 from user2 to be updated to False, which that code does correctly. However, I get the following warning message. As far as I understand, update would expect some ORM like expression instead of stmt.c. But I do not see how to convert my stmt object into an ORM one (tried aliased to User1 but failed, I understood why afterwards...).

Any idea to circumvent that warning msg? Thanks in advance!

/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/orm/evaluator.py:71: SAWarning: Evaluating non-mapped column expression '%(4357770376 anon)s.value1' onto ORM instances; this is a deprecated use case. Please make use of the actual mapped columns in ORM-evaluated UPDATE / DELETE expressions. "UPDATE / DELETE expressions." % clause) /Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/orm/evaluator.py:71: SAWarning: Evaluating non-mapped column expression '%(4357770376 anon)s.value2' onto ORM instances; this is a deprecated use case. Please make use of the actual mapped columns in ORM-evaluated UPDATE / DELETE expressions. "UPDATE / DELETE expressions." % clause)

Upvotes: 1

Views: 1737

Answers (2)

Eric M.
Eric M.

Reputation: 2977

The subquery declaration is not necessary. You can directly update from on version 1.4:

from sqlalchemy import update

session.execute(
    update(User1).values(
        value2=User2.value2
    )
    .where(User1.value1==User2.value1, User2.value1 > 12)
    .execution_options(synchronize_session="fetch") # won't work with 'evaluate'
)
session.commit()
# UPDATE user1 SET value2=(SELECT user2.value2 
# FROM user2 
# WHERE user1.value1 = user2.value1) WHERE user1.value1 > %(value1_1)s 
# RETURNING user1.index

You have to set the execution_options to not raise an error.

Another option involves a correlated subquery for the values only:

from sqlalchemy import select, update

session.execute(
    update(User1).values(
        value2=select(User2.value2).\
                where(User1.value1 == User2.value1).\
                scalar_subquery()
    )
    .where(User1.value1 > 12)
)
session.commit()
# UPDATE user1 SET value2=user2.value2 
# FROM user2 WHERE user1.value1 = user2.value1 AND user2.value1 >= %(value1_1)s 
# RETURNING user1.index

But then the update where condition has to be applied on User1.

Upvotes: 1

P Bouillon
P Bouillon

Reputation: 11

Found the following solution: 1. perform a session.query(stmt).all(), 2. build a mydict dictionnary based on result of 1., 3. create a new User1 instance with new_user = User1(**mydict), 4. Perform the update using new_user instead of stmt.c. No more SAWarning msg. Anyone having a more elegant/efficient solution?

Upvotes: 0

Related Questions