python sqlite "create table if not exists" problem

Question

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:

Traceback (most recent call last):   File "test.py", line 40, in <module>     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 

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 if not exists 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:

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() 

Thanks in advance :)

Answer 1

I can duplicate the issue with the following simplified script:

import sqlite3  location = 'data' table_name = 'table_name'  conn = sqlite3.connect(location) c = conn.cursor()  sql = 'create table if not exists ' + table_name + ' (id integer)' c.execute(sql)  sql = 'drop table ' + table_name c.execute(sql)  sql = 'create table if not exists ' + table_name + ' (id integer)' c.execute(sql)  sql = 'insert into ' + table_name + ' (id) values (%d)' % (1) c.execute(sql) conn.commit() 

To fix that, you have to close and reconnect to the db after creating or dropping the table, i.e., add the following code in between:

c.close() conn.close() conn = sqlite3.connect(location) c = conn.cursor() 

Not sure what is the cause though.

---

Update Oct 16, 2018

Starting from 2.7.12 this issue is no longer reproducible, though I don't see it mentioned in the change log. If you are still seeing it, try update your python version first.