title: SQLAlchemy, scoped_session - raw SQL INSERT doesn't write to DB
body: <p>I have a Pyramid / SQLAlchemy, MySQL python app. 
  When I execute a raw SQL INSERT query, nothing gets written to the DB. 
  When using ORM, however, I can write to the DB. I read the docs, I read up about the ZopeTransactionExtension, read a good deal of SO questions, all to no avail. 
  What hasn't worked so far:</p>
  
  <p><ul>
  <li><code>transaction.commit()</code> - nothing is written to the DB. I do realize this statement is necessary with ZopeTransactionExtension but it just doesn't do the magic here.</li>
  <li><code>dbsession().commit</code> - doesn't work since I'm using ZopeTransactionExtension</li>
  <li><code>dbsession().close()</code> - nothing written</li>
  <li><code>dbsession().flush()</code> - nothing written</li>
  <li><code>mark_changed(session)</code> - </p>
  
  <blockquote>
    <p>File "/home/dev/.virtualenvs/sc/local/lib/python2.7/site-packages/zope/sqlalchemy/datamanager.py", line 198, in join_transaction
    if session.twophase:
    AttributeError: 'scoped_session' object has no attribute 'twophase'"</li>
    </ul>
    What has worked but is not acceptable because it doesn't use scoped_session:</p>
  </blockquote>
  
  <ul>
  <li><code>engine.execute(...)</code></li>
  </ul>
  
  <p>I'm looking for how to execute raw SQL with a <code>scoped_session</code> (<code>dbsession()</code> in my code) </p>
  
  <p>Here is my SQLAlchemy setup (<code>models/__init__.py</code>)</p>
  
  <pre><code>def dbsession():
      assert (_dbsession is not None)
      return _dbsession
  
  def init_engines(settings, _testing_workarounds=False):
      import zope.sqlalchemy
      extension = zope.sqlalchemy.ZopeTransactionExtension()
      global _dbsession
  
      _dbsession = scoped_session(
          sessionmaker(
              autoflush=True,
              expire_on_commit=False,
              extension=extension,
          )
      )
  
      engine = engine_from_config(settings, 'sqlalchemy.')
      _dbsession.configure(bind=engine)
  </code></pre>
  
  <p>Here is a python script I wrote to isolate the problem. It resembles the real-world environment of where the problem occurs. All I want is to make the below script insert the data into the DB:</p>
  
  <pre><code># -*- coding: utf-8 -*-
  import sys
  import transaction
  
  from pyramid.paster import setup_logging, get_appsettings
  from sc.models import init_engines, dbsession
  from sqlalchemy.sql.expression import text
  
  
  def __main__():
  
  if len(sys.argv) &lt; 2:
      raise RuntimeError()
  config_uri = sys.argv[1]
  setup_logging(config_uri)
  aa = init_engines(get_appsettings(config_uri))
  
  session = dbsession()
  session.execute(text("""INSERT INTO
           operations (description, generated_description)
           VALUES ('hello2', 'world');"""))
  
  print list(session.execute("""SELECT * from operations""").fetchall()) # prints inserted data
  transaction.commit()
  
  print list(session.execute("""SELECT * from operations""").fetchall()) # doesn't print inserted data
  
  if __name__ == '__main__':
      __main__()
  </code></pre>
  
  <p>What is interesting, if I do:</p>
  
  <pre><code>session = dbsession()
  session.execute(text("""INSERT INTO
           operations (description, generated_description)
           VALUES ('hello2', 'world');"""))
  op = Operation(generated_description='aa', description='oo')
  session.add(op)
  </code></pre>
  
  <p>then the first print outputs the raw SQL inserted row ('hello2' 'world'), and the second print prints <strong>both</strong> rows, and in fact <strong>both rows are inserted into the DB</strong>.  </p>
  
  <p>I cannot comprehend why using an ORM insert alongside raw SQL "fixes" it.</p>
  
  <p>I really need to be able to call execute() on a scoped_session to insert data into the DB using raw SQL. Any advice?</p>