Btukfyl

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.

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

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.