title: Create new table from query results is slow in Python with SQLite
body: <p>Using Python, I try to create a new SQLite table in a database from a query result which is executed on two other tables in that database.</p>
  
  <p>For the record, the query is</p>
  
  <pre><code>CREATE TABLE results AS
  
      SELECT table_1.*, table_2.*
      FROM table_1
          LEFT JOIN table_2
              ON table_1.ID_1 = table_2.ID_2
      UNION ALL
      SELECT table_1.*, table_2.*
      FROM table_2
          LEFT JOIN table_1
              ON table_1.ID_1 = table_2.ID_2
      WHERE table_1.ID_1 IS NULL
  </code></pre>
  
  <p>which is supposed to be a workaround for a <code>FULL OUTER JOIN</code> which is not directly available in SQLite (this method can be found on different SO threads on this topic).</p>
  
  <p>However, this operation is slow on my tables with ~1 million rows each... so slow that I get the impression it's going to take hours when hitting refresh in the Explorer Window showing the database's file size while updating.</p>
  
  <p>How can I get this faster? I already did a lot of research on this and most of the time, people are talking about using transactions because otherwise, each row will open up a new connection to the database or whatever... however, I could not find a working example on how to use this.</p>
  
  <p>My two approaches so far which are way too slow:</p>
  
  <p>Using Python's <code>sqlite3</code> module:</p>
  
  <pre><code># open sqlite database
  conn = sqlite3.connect('Database.sqlite')
  
  # get a cursor
  cursor = conn.cursor()
  
  # start query
  cursor.execute("""
  CREATE TABLE results AS
      SELECT table_1.*, table_2.*
      FROM table_1
          LEFT JOIN table_2
              ON table_1.ID = table_2.ID
      UNION ALL
      SELECT table_1.*, table_2.*
      FROM table_2
          LEFT JOIN table_1
              ON table_1.ID = table_2.NETWORK_ID
      WHERE table_1.ID IS NULL
  ;""")
  </code></pre>
  
  <p>Using <a href="http://www.sqlalchemy.org/" rel="nofollow">sqalchemy</a>:</p>
  
  <pre><code>from sqlalchemy import create_engine, event
  from sqlite3 import dbapi2 as sqlite
  
  # create database engine
  engine = create_engine('sqlite:///Database.sqlite')
  
  # open sqlite database
  connection = engine.connect()
  
  # query
  with connection.begin() as trans:
      connection.execute("BEGIN TRANSACTION;")
  
      connection.execute("""
      CREATE TABLE results AS
  
          SELECT table_1.*, table_2.*
          FROM table_1
              LEFT JOIN table_2
                  ON table_1.ID = table_2.ID
          UNION ALL
          SELECT table_1.*, table_2.*
          FROM table_2
              LEFT JOIN table_1
                  ON table_1.ID = table_2.ID
          WHERE table_1.ID IS NULL
      ;""")
  
      trans.commit()
  </code></pre>