SQLAlchemy can't set attribute error when updating value

Question

I have a table mailtable in a backend PostgreSQL database and I'm querying it using SQLAlchemy ORM using a given mailId. For the following code, the amEngine is already set up. When it gets to the mailRow.usercomment = 'Hello World' line, it throws:

AttributeError: can't set attribute

Base = declarative_base()
Base.metadata.reflect(amEngine)
metaAm = MetaData()
metaAm.reflect(bind=amEngine)
mt = metaAm.tables['mailtable']
Session = sessionmaker(bind=amEngine)
amSession = Session()
mailRow = amSession.query(
        mt
    ).filter(
        mt.c.id == mailId
    ).first()
mailAddress = mailRow.address
mailRow.usercomment = 'Hello World'
amSession.flush()
amSession.commit()

I need to read the address column (successful) and update the usercomment column (throws exception). The PostgreSQL type for both columns is text. How can I do this? I'm sure this must be a very simple issue, but for the life of me I cannot see the problem.

Many thanks.

Related: stackoverflow.com/questions/51391039/…

SuperShoot
– SuperShoot

2018-11-27 04:21:14 +00:00
Commented Nov 27, 2018 at 4:21 — SuperShoot
– SuperShoot, Commented Nov 27, 2018 at 4:21

KT. · Accepted Answer · 2018-11-27 03:49:35Z

By calling MetaData.reflect you asked SQLAlchemy to autodetect the Table objects, describing your database. You may use these objects to create language-agnostic SQL queries, such as

engine.execute(update(mt).where(mt.c.id==1).values(usercomment='Hello World'))

You cannot yet use the Table objects to do proper ORM, however. When you query a Table object (via session.query(mt)) you will get the results returned to you as read-only namedtuple-like objects. Setting their attributes makes no sense, hence you observe the exception.

To enjoy actual ORM, you need to create the corresponding ORM classes and map them to the Table objects.

Of course, you can ask SQLAlchemy to auto-reflect the ORM classes for you together with the tables using automap. Here is the code you probably wanted to write:

from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import sessionmaker

# Ask SQLAlchemy to reflect the tables and 
# create the corresponding ORM classes:
Base = automap_base()
Base.prepare(amEngine, reflect=True)

# This is the ORM class we are interested in:
MailTable = Base.classes.mailtable

# Create the session, query, update and commit:
Session = sessionmaker(bind=amEngine)
session = Session()
mailRow = session.query(MailTable).get(mailId)
mailAddress = mailRow.address
mailRow.usercomment = 'Hello World'
session.commit()

SuperShoot · Accepted Answer · 2018-11-27 06:01:01Z

4

You are querying a Table object directly, not via an instrumented model:

mt = metaAm.tables['mailtable']
print(type(mt))
# <class 'sqlalchemy.sql.schema.Table'>

When you query a table, you are returned an instance of <class 'sqlalchemy.util._collections.result'> which is a type of namedtuple. Tuples are immutable, so you cannot change the value of its attributes.

edited Nov 27, 2018 at 6:01

answered Nov 27, 2018 at 3:44

SuperShoot

11k2 gold badges45 silver badges63 bronze badges

Collectives™ on Stack Overflow

SQLAlchemy can't set attribute error when updating value

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related