title: python sqlite "create table if not exists" problem
body: <p>I'm having an issue using sqlite to create a table only if it doesn't exist.  Basically, I have a table that I'm dropping and remaking once in a long while.  However, if the table already existed (before I drop and remake it), then I get the following error when trying to insert for the first time:</p>
  
  <pre><code>Traceback (most recent call last):
    File "test.py", line 40, in &lt;module&gt;
      remake()
    File "test.py", line 31, in remake
      insert_record(1)
    File "test.py", line 36, in insert_record
      c.execute(sql)
  sqlite3.OperationalError: no such table: table_name
  </code></pre>
  
  <p>At this point the table does not exist (for some reason), so the next time I run the script no errors occur.  Basically, if I keep running the test script, exactly every other run will result in an error, and I'm stumped as to why - but I have determined that creating the database without using <code>if not exists</code> fixes the issue.  I still don't know what the original problem is though, and I'd appreciate if anyone could point me in the right direction.  Test script demonstrating the problem below:</p>
  
  <pre><code>import sqlite3
  
  location = 'data'
  table_name = 'table_name'
  
  def init():    
      global conn
      global c
      conn = sqlite3.connect(location)
      c = conn.cursor()
      create_database()
  
  def create_database():
      sql = 'create table if not exists ' + table_name + ' (id integer)'
      c.execute(sql)
      conn.commit()
  
  def create_database2():
      sql = 'create table ' + table_name + '(id integer)'
      c.execute(sql)
      conn.commit()
  
  def clear_database():
      sql = 'drop table ' + table_name
      c.execute(sql)
      conn.commit()
  
  def remake():
      clear_database()
      create_database() # Replacing this with create_database2() works every time
      insert_record(1)
      conn.commit()
  
  def insert_record(id):
      sql = 'insert into ' + table_name + ' (id) values (%d)' % (id)
      c.execute(sql)
      print 'Inserted ', id
  
  init()
  remake()
  </code></pre>
  
  <p>Thanks in advance :)</p>