Hi,
I have a situation where an update tries to update the wrong table on when
a column comes from the parent table and is not on the current table. I'll
grant I didn't quite understand all the caveats in
https://docs.sqlalchemy.org/en/13/orm/query.html#sqlalchemy.orm.query.Query.update
so let me apologise if it's written there - then I just need a pointer in
the right direction. Here is some sample code (mostly adapted from
https://stackoverflow.com/questions/44183500/problems-with-update-and-table-inheritance-with-sqlalchemy
- the code won't work on sqlite).
import os
import sys
from sqlalchemy import Column, create_engine, ForeignKey, Integer, String,
DateTime
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql import func, update
from sqlalchemy.ext.declarative import declarative_base
try:
os.remove('test.db')
except FileNotFoundError:
pass
engine = create_engine('postgresql+psycopg2://user:pass@server/database',
echo=True)
Session = sessionmaker(engine)
Base = declarative_base()
class People(Base):
__tablename__ = 'people'
discriminator = Column('type', String(50))
__mapper_args__ = {'polymorphic_on': discriminator}
id = Column(Integer, primary_key=True)
name = Column(String(50))
updated = Column(DateTime, server_default=func.now(),
onupdate=func.now())
class Engineer(People):
__tablename__ = 'engineer'
__mapper_args__ = {'polymorphic_identity': 'engineer'}
id = Column(Integer, ForeignKey('people.id'), primary_key=True)
kind = Column(String(100), nullable=True)
Base.metadata.create_all(engine)
session = Session()
e = Engineer()
e.name = 'Mike'
session.add(e)
session.flush()
session.commit()
# works when updating the object
e.name = "Doug"
session.add(e)
session.commit()
# works using the base class for the query
count = session.query(People).filter(
People.name == 'Doug').update({People.name:
'James'})
# fails when using the derived class
count = session.query(Engineer).filter(Engineer.id == People.id,
Engineer.name == 'James',
).update({Engineer.name: 'Mary'})
session.commit()
print("Count: {}".format(count))
----
The error message is:
Traceback (most recent call last):
File
"/home/joao/testes/sa-bugs/.ve/lib/python3.6/site-packages/sqlalchemy/engine/base.py",
line 1244, in _execute_context
cursor, statement, parameters, context
File
"/home/joao/testes/sa-bugs/.ve/lib/python3.6/site-packages/sqlalchemy/engine/default.py",
line 550, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.UndefinedColumn: column "name" of relation "engineer" does
not exist
LINE 1: UPDATE engineer SET name='Mary', updated=now() FROM people W...
^
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "update-inheritance.py", line 63, in <module>
Engineer.name == 'James', ).update({Engineer.name: 'Mary'})
File
"/home/joao/testes/sa-bugs/.ve/lib/python3.6/site-packages/sqlalchemy/orm/query.py",
line 3824, in update
update_op.exec_()
File
"/home/joao/testes/sa-bugs/.ve/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py",
line 1673, in exec_
self._do_exec()
File
"/home/joao/testes/sa-bugs/.ve/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py",
line 1866, in _do_exec
self._execute_stmt(update_stmt)
File
"/home/joao/testes/sa-bugs/.ve/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py",
line 1678, in _execute_stmt
self.result = self.query._execute_crud(stmt, self.mapper)
File
"/home/joao/testes/sa-bugs/.ve/lib/python3.6/site-packages/sqlalchemy/orm/query.py",
line 3356, in _execute_crud
return conn.execute(stmt, self._params)
File
"/home/joao/testes/sa-bugs/.ve/lib/python3.6/site-packages/sqlalchemy/engine/base.py",
line 988, in execute
return meth(self, multiparams, params)
File
"/home/joao/testes/sa-bugs/.ve/lib/python3.6/site-packages/sqlalchemy/sql/elements.py",
line 287, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File
"/home/joao/testes/sa-bugs/.ve/lib/python3.6/site-packages/sqlalchemy/engine/base.py",
line 1107, in _execute_clauseelement
distilled_params,
File
"/home/joao/testes/sa-bugs/.ve/lib/python3.6/site-packages/sqlalchemy/engine/base.py",
line 1248, in _execute_context
e, statement, parameters, cursor, context
File
"/home/joao/testes/sa-bugs/.ve/lib/python3.6/site-packages/sqlalchemy/engine/base.py",
line 1466, in _handle_dbapi_exception
util.raise_from_cause(sqlalchemy_exception, exc_info)
File
"/home/joao/testes/sa-bugs/.ve/lib/python3.6/site-packages/sqlalchemy/util/compat.py",
line 383, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File
"/home/joao/testes/sa-bugs/.ve/lib/python3.6/site-packages/sqlalchemy/util/compat.py",
line 128, in reraise
raise value.with_traceback(tb)
File
"/home/joao/testes/sa-bugs/.ve/lib/python3.6/site-packages/sqlalchemy/engine/base.py",
line 1244, in _execute_context
cursor, statement, parameters, context
File
"/home/joao/testes/sa-bugs/.ve/lib/python3.6/site-packages/sqlalchemy/engine/default.py",
line 550, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column
"name" of relation "engineer" does not exist
LINE 1: UPDATE engineer SET name='Mary', updated=now() FROM people W...
^
[SQL: UPDATE engineer SET name=%(people_name)s, updated=now() FROM people
WHERE engineer.id = people.id AND people.name = %(name_1)s]
[parameters: {'people_name': 'Mary', 'name_1': 'James'}]
(Background on this error at: http://sqlalche.me/e/f405)
I can get the correct table to update through:
table = Engineer.name.property.columns[0].table
And the following update works (albeit with a slightly different semantics):
update(table).where(table.c.name == 'James').values({table.c.name : 'Mary'})
>From what I've read, adding the People.id == Engineer.id should be enough,
but it seems like I missed some detail. Can you help?
Thanks in advance for any help/pointers,
João
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/4b8377e7-3fff-4750-aebf-aaa5eaac35da%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
