combine SQLAlchemy Core and ORM get problems
<p>How do I combine the two component of SQLAlchemy -- Core (SQL Expression) and ORM ?
I have some table that using ORM mapper and others just Table object, and I want <strong>one connection and one transaction for the two</strong>.  </p>

<p>I have following two examples but run into problems (result is not consistent to my queries that interleaving two access styles).
One using autocommit session, another using default session.</p>

<hr>

<pre><code>session_autocommit=Session(bind=db,autocommit=True)
def f():
    with session_autocommit.begin() as trans:
        # ORM 
        x=session_autocommit.query(Mytable).filter(Mytable.id==1).first()
        # sql expression by SQLAlchemy Core 
        session_autocommit.execute(mytable.update().where(mytable.c.id==1)\
            .values(note=None)) 
        # update via ORM
        x.note='a'
f() # ok two update appear in log.
f() # ! only one update as below

x.note == 'a'
# the second run of f() returns False but should be True.
</code></pre>

<p>the log of second run of f()  says it only one update (note=None) , the second update is missed?</p>

<pre><code>INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
INFO sqlalchemy.engine.base.Engine SELECT mytable.id AS mytable_id, mytable.note AS mytable_note
FROM mytable 
WHERE mytable.id = %(id_1)s 
 LIMIT %(param_1)s
INFO sqlalchemy.engine.base.Engine {'param_1': 1, 'id_1': 1}
INFO sqlalchemy.engine.base.Engine UPDATE mytable SET note=%(note)s WHERE mytable.id = %(id_1)s
INFO sqlalchemy.engine.base.Engine {'id_1': 1, 'note': None}
INFO sqlalchemy.engine.base.Engine COMMIT
</code></pre>

<hr>

<p><strong>UPDATE</strong> The second problem is solved.<br>
Thanks @univerio 's comment, I need to <code>flush</code> it first for right ordering of executions. Since I am using two independent mechanism of SQLAlchemy.</p>

<pre><code>session=Session(bind=db,autocommit=False)
# default session must rely commit to control transaction.

session.commit()
x=session.query(Mytable).filter(Mytable.id==1).first()
x.note='a'
session.execute(mytable.update().where(mytable.c.id==1)\
    .values(note=None)) 
session.commit()

x.note=='a'
# the test return True but should be None.
</code></pre>

<p>the log says the different order of two updates ?</p>

<pre><code>INFO sqlalchemy.engine.base.Engine COMMIT
INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
INFO sqlalchemy.engine.base.Engine SELECT mytable.id AS mytable_id, mytable.note AS mytable_note
FROM mytable 
WHERE mytable.id = %(id_1)s 
 LIMIT %(param_1)s
INFO sqlalchemy.engine.base.Engine {'id_1': 1, 'param_1': 1}
INFO sqlalchemy.engine.base.Engine UPDATE mytable SET note=%(note)s WHERE mytable.id = %(id_1)s
INFO sqlalchemy.engine.base.Engine {'note': None, 'id_1': 1}
INFO sqlalchemy.engine.base.Engine SELECT mytable.id AS mytable_id
FROM mytable 
WHERE mytable.id = %(param_1)s
INFO sqlalchemy.engine.base.Engine {'param_1': 1}
INFO sqlalchemy.engine.base.Engine UPDATE mytable SET note=%(note)s WHERE mytable.id = %(mytable_id)s
INFO sqlalchemy.engine.base.Engine {'note': 'a', 'mytable_id': 1}
INFO sqlalchemy.engine.base.Engine COMMIT
</code></pre>

<p>After reading more about <code>session</code>, I have answer about it.   </p>

<hr>

<p><code>session</code> has its working mechanisms, one is <code>unit of work</code> --</p>

<blockquote>
  <p>"All changes to objects maintained by a Session are tracked - before
  the database is queried again or before the current transaction is
  committed, it flushes all pending changes to the database. This is
  known as the Unit of Work pattern."
  <a href="http://docs.sqlalchemy.org/en/latest/orm/session_basics.html" rel="nofollow">http://docs.sqlalchemy.org/en/latest/orm/session_basics.html</a></p>
</blockquote>

<ul>
<li><p>The first problem is because of <code>unit of work</code> so that SQLAlchemy will track what is changed and smartly do only what's really needed. The result is only one update is needed.   </p></li>
<li><p>The second problem is solved by manual <code>session.flush()</code> the ORM update to get the right ordering of executions. Since I am using two independent mechanism of SQLAlchemy and it seems that <code>unit of work</code> doesn't also apply to raw SQL expression.    </p></li>
</ul>

python sql orm sqlalchemy relational-database