title: SQLAlchemy raw sql vs expression language statements
body: <p>When inserting multiple rows in a MySQL-DB via a SQLA-Expression-Language statement, f.e.</p>
  
  <pre><code>Foo.__table__.insert().execute([{'bar': 1}, {'bar': 2}, {'bar': 3}])
  </code></pre>
  
  <p>it´s extremly slow, when compared to the execution of a "raw" sql statement for the same task, i.e.</p>
  
  <pre><code>engine.execute("insert into foo (bar) values (1),(2),(3)")
  </code></pre>
  
  <p>What is the reason for this? Can´t SQLA generate a single bulk insert statement and therefore executes multiple inserts? Due to the speed limits of the orm, i need a fast way to add several thousand rows at once, but the SQLA-Expression-Language-Version is too slow. So, do i need to write the raw sql by myself? The documentation isn't too clear about this.</p>
  
  <p>I ran a speed test with the ORM insert, the ORM with preassigned PK and the SQLA bulk insert (see <a href="https://stackoverflow.com/questions/11769366/why-is-sqlalchemy-insert-with-sqlite-25-times-slower-than-using-sqlite3-directly">SQLA bulk insert speed</a>) like this (<a href="https://gist.github.com/3341940" rel="nofollow noreferrer">https://gist.github.com/3341940</a>):</p>
  
  <ul>
  <li>SqlAlchemy ORM: Total time for 500 records 9.61418914795 secs</li>
  <li>SqlAlchemy ORM pk given: Total time for 500 records 9.56391906738 secs</li>
  <li>SqlAlchemy Core: Total time for 500 records 9.5362598896 secs</li>
  <li>SQLAlchemy RAW String Execution: Total time for 500 records 1.233677 secs</li>
  </ul>
  
  <p>As you can see, there is practically no difference between the three versions. Only the execution of a raw string insert, where all the records are included in the raw sql statement is significantly faster. Thus, for fast inserts, SQLA seems sub-optimal.</p>