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

Should the SQL have a semicolon at the end? just wondering.

Script and Compile
– Script and Compile

2018-05-27 21:36:08 +00:00
Commented May 27, 2018 at 21:36 — Script and Compile
– Script and Compile, Commented May 27, 2018 at 21:36

Wang Dingwei · Accepted Answer · 2018-10-16 08:49:49Z

44

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.

edited Oct 16, 2018 at 8:49

answered Apr 27, 2011 at 10:57

Wang Dingwei

4,8896 gold badges34 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

xiao 啸 Over a year ago

+1 for the simple script and the solution. I have tried to just type those statements in the sqlite console, it is OK. I just wonder it should be a bug of pysqlite library.

jsw Over a year ago

btw, you should not need to commit after running DDL, these statements do not run in transactions.

TimothyAWiseman Over a year ago

I just ran into a very similar issue and closing and recreating the connection in code resolves it. Thanks to you both. Its strange that this is happening though. My best guess is its a bug in sqlLite.

Felix Yan · Accepted Answer · 2011-04-27 09:59:53Z

0

I tried exactly the same program and succeeded with no problem at all, only changing the location to :memory:

EDIT: tried exactly the same code and no problem at all, too.

FYI:

felix@felix-arch ~ $ python2 --version
Python 2.7.1

felix@felix-arch ~ $ sqlite3 --version
3.7.6.1

Maybe you should double check the writability of file data?

edited Apr 27, 2011 at 9:59

answered Apr 27, 2011 at 9:36

Felix Yan

15.4k8 gold badges51 silver badges61 bronze badges

5 Comments

jsw Over a year ago

The problem is repeatable. Try not using :memory:

Felix Yan Over a year ago

Okay now I tried changing location to data and tried again and again , no problem at all, output remains: Inserted 1

jsw Over a year ago

I take it that you ran the script twice as mentioned in the question?

jsw Over a year ago

okay, well I do get the same problem as described. I'll see if I can simplify and see where the problem is

hippocrates Over a year ago

Thanks for checking it out. I'm currently running Python 2.6.5 and sqlite3 3.6.22, though my sqlite3.version is 2.4.1 and sqlite3.sqlite_version is 3.6.22 in Python. I've also tried this with Python 3 and gotten the same result. Making data world-writable doesn't help, either.

Collectives™ on Stack Overflow

python sqlite "create table if not exists" problem

2 Answers 2

3 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related