SQLAlchemy ORM update value by checking if other table value in list
<p>I have a Kanji (Japanese characters) list which looks like:</p>

<pre><code>kanji_n3 = ['æ¿', 'è­°', 'æ°', 'é£']  # But then with 367 Kanji
</code></pre>

<p>and I have 2 tables: <code>TableKanji</code> and <code>TableMisc</code>. <code>TableMisc</code> has a column called 'jlpt', from which some currently have the value <code>2</code>, but this has to be updated to value <code>3</code>, if the Kanji is in <code>kanji_n3</code>.</p>

<p>tableclass.py</p>

<pre><code>import sqlalchemy as sqla
from sqlalchemy.orm import relationship
import sqlalchemy.ext.declarative as sqld
sqla_base = sqld.declarative_base()

class TableKanji(sqla_base):
    __tablename__ = 'Kanji'

    id = sqla.Column(sqla.Integer, primary_key=True)
    character = sqla.Column(sqla.String, nullable=False)

    misc = relationship("TableMisc", back_populates='kanji')

class TableMisc(sqla_base):
    __tablename__ = 'Misc'

    kanji_id = sqla.Column(sqla.Integer, sqla.ForeignKey('Kanji.id'), primary_key=True)
    jlpt = sqla.Column(sqla.Integer)

    kanji = relationship("TableKanji", back_populates="misc")
</code></pre>

<p>So the query I came up with is, kanjiorigin_index.py:</p>

<pre><code>import sqlalchemy as sqla
import sqlalchemy.orm as sqlo
from tableclass import TableKanji, TableMisc

kanji_n3 = ['æ¿', 'è­°', 'æ°', 'é£']  # But then with 367 Kanji

session.query(TableMisc)\
    .filter(TableMisc.jlpt == 2).filter(TableKanji.character in kanji_n3)\
    .update({TableMisc.jlpt: TableMisc.jlpt + 1}, synchronize_session='fetch')
</code></pre>

<p>This runs succesfully, but doesn't update anything. The output:</p>

<pre><code>2016-10-18 04:05:53,908 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2016-10-18 04:05:53,908 INFO sqlalchemy.engine.base.Engine ()
2016-10-18 04:05:53,908 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2016-10-18 04:05:53,909 INFO sqlalchemy.engine.base.Engine ()
2016-10-18 04:05:53,909 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2016-10-18 04:05:53,909 INFO sqlalchemy.engine.base.Engine SELECT "Misc".kanji_id AS "Misc_kanji_id" 
FROM "Misc" 
WHERE 0 = 1
2016-10-18 04:05:53,909 INFO sqlalchemy.engine.base.Engine ()
2016-10-18 04:05:53,910 INFO sqlalchemy.engine.base.Engine UPDATE "Misc" SET jlpt=("Misc".jlpt + ?) WHERE 0 = 1
2016-10-18 04:05:53,910 INFO sqlalchemy.engine.base.Engine (1,)
2016-10-18 04:05:53,911 INFO sqlalchemy.engine.base.Engine COMMIT
</code></pre>

<h2>Question</h2>

<p>How do I update <code>TableMisc.jlpt</code> where the current value is 2 and where TableKanji.character is in <code>kanji_n3</code>? Does my <code>in kanji_n3</code> statement not work like this? I also tried to add an <code>.outerjoin(TableKanji)</code>, but this results in:</p>

<pre><code>sqlalchemy.exc.InvalidRequestError: Can't call Query.update() or Query.delete() when join(), outerjoin(), select_from(), or from_self() has been called
</code></pre>

<p>Seems like that your intention is to make an update on joined tables. Not all databases support this.</p>

<p>First of all you should use <a href="http://stackoverflow.com/questions/8603088/sqlalchemy-in-clause"><code>in_</code></a> method instead of <code>in</code> operator.</p>

<p>You can make select first and than update all selected records like this:</p>

<pre><code>records = session.query(TableMisc).\
    join(TableKanji).\
    filter(TableMisc.jlpt == 2).\
    filter(TableKanji.character.in_(kanji_n3)).\
    all()

for record in records:
    record.jlpt += 1

session.commit()
</code></pre>

python sqlalchemy