On 12/15/2015 06:26 PM, Gerald Thibault wrote: > I can't figure out the syntax to issue an update, everything I try (attr > name, column name, attribute) results in "unconsumed column names". > > Am I doing something wrong here?
MoreTest is mapped using joined table inheritance, mapped to the table "update_test_2", and you are referring to columns that are in its base table, "update_test". A SQL UPDATE statement is normally against a single table as the target to be updated. MySQL includes a vendor-specific syntax that allows more than one table to be updated in a single statement, and SQLAlchemy has support for this unusual syntax, though it isn't going to work on other backends. The documentation at http://docs.sqlalchemy.org/en/rel_1_0/orm/query.html?highlight=query.update#sqlalchemy.orm.query.Query.update does include an extensive set of warnings and caveats about using query.update() such as with joined-table inheritance. When referring to multiple tables in a single UPDATE statement, the join condition must be manually stated, which has the effect of including both tables in the UPDATE, and also the target backend must support a multi-table form of UPDATE; MySQL and Postgresql support this, SQLite and others do not. But only MySQL allows multiple tables to actually be modified at once, e.g. multiple target tables as is being requested here. So if you want to update columns that are in "update_test" in a SQL UPDATE statement, and you're dealing with 99.9999% of all UPDATE statements ever made, then the target table of that UPDATE must also be "update_test", e.g. the Test class here. Otherwise, to use the MySQL syntax, this will be pulled in automatically if you ensure to include both tables in the statement: query_base = session.query(MoreTest).filter(MoreTest.id == Test.id).filter(MoreTest.id==test.id) producing the entirely non-standard SQL of: UPDATE update_test2, update_test SET update_test.somevalue=%s WHERE update_test2.id = update_test.id AND update_test2.id = %s If you run this query on Postgresql or others, you get the slightly less weird UPDATE..FROM syntax, which most backends will reject, Postgresql accepts it, but still, PG expects the SET columns to be the same as the target table so it's rejected: (psycopg2.ProgrammingError) column "somevalue" of relation "update_test2" does not exist LINE 1: UPDATE update_test2 SET somevalue=3 FROM update_test WHERE u... ^ [SQL: 'UPDATE update_test2 SET somevalue=%(update_test_somevalue)s FROM update_test WHERE update_test2.id = update_test.id AND update_test2.id = %(id_1)s'] [parameters: {'id_1': 1, 'update_test_somevalue': 3}] That's the story here, hope this helps. > > > from sqlalchemy import * > from sqlalchemy.ext.declarative import declarative_base > from sqlalchemy.orm.session import Session > > > dsn = 'sqlite:///foo.db' > engine = create_engine(dsn) > Base = declarative_base(bind=engine) > > class Test(Base): > __tablename__ = 'update_test' > id = Column(Integer, primary_key=True) > discriminator = Column('type', String(10)) > value = Column('somevalue', Integer) > __mapper_args__ = {'polymorphic_on': discriminator} > > class MoreTest(Test): > __tablename__ = 'update_test2' > __mapper_args__ = {'polymorphic_identity': 'test'} > id = Column(Integer, ForeignKey('update_test.id'), primary_key=True) > value2 = Column('somevalue2', Integer) > > > Base.metadata.drop_all() > Base.metadata.create_all() > > session = Session(bind=engine) > > test = MoreTest(value=2) > session.add(test) > session.flush() > > update1 = {'value': 3} > update2 = {'somevalue': 3} > update3 = {Test.value: 3} > update4 = {MoreTest.value: 3} > > query_base = session.query(MoreTest).filter(MoreTest.id==test.id) > > try: > query_base.update(update1) > except Exception as e: > session.rollback() > print e # Unconsumed column names: value > > try: > query_base.update(update2) > except Exception as e: > session.rollback() > print e # Unconsumed column names: somevalue > > try: > query_base.update(update3) > except Exception as e: > session.rollback() > print e # Unconsumed column names: somevalue > > try: > query_base.update(update4) > except Exception as e: > session.rollback() > print e # Unconsumed column names: somevalue > > -- > 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] > <mailto:[email protected]>. > To post to this group, send email to [email protected] > <mailto:[email protected]>. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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. For more options, visit https://groups.google.com/d/optout.
