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: 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.

