How do you update a SQLAlchemy RowProxy?

Question

I'm working with the SQLAlchemy Expression Language (not the ORM), and I'm trying to figure out how to update a query result.

I've discovered that RowProxy objects don't support assignment, throwing an AttributeError instead:

# Get a row from the table
row = engine.execute(mytable.select().limit(1)).fetchone()

# Check that `foo` exists on the row
assert row.foo is None

# Try to update `foo`
row.foo = "bar"

AttributeError: 'RowProxy' object has no attribute 'foo'

I've found this solution, which makes use of the ORM, but I'm specifically looking to use the Expression Language.

I've also found this solution, which converts the row to a dict and updates the dict, but that seems like a hacky workaround.

So I have a few questions:

• Is this in fact the only way to do it?
• Moreover, is this the recommended way to do it?
• And lastly, the lack of documentation made me wonder: am I just misusing SQLAlchemy by trying to do this?

Answer 1

You are misusing SQLAlchemy. The usage you've described is the benefit of using an ORM. If you only want to restrict yourself to SQLAlchemy Core, then you need to do

engine.execute(mytable.update().where(mytable.c.id == <id>).values(foo="bar"))