Create table in SQLite only if it doesn't exist already

Question

I want to create a table in a SQLite database only if doesn't exist already. Is there any way to do this? I don't want to drop the table if it exists, only create it if it doesn't.

possible duplicate of [Creating an SQLite table only if it doesn't already exist](http://stackoverflow.com/questions/3716443/creating-an-sqlite-table-only-if-it-doesnt-already-exist) — Toby Allen, Aug 16 '12 at 04:00

David Wolever · Accepted Answer · 2018-06-01T17:21:02.660

609

From http://www.sqlite.org/lang_createtable.html:

CREATE TABLE IF NOT EXISTS some_table (id INTEGER PRIMARY KEY AUTOINCREMENT, ...);

edited Jun 01 '18 at 17:21

answered Nov 04 '10 at 15:06

David Wolever

148,955
89
346
502

7

This works for indices, too: `CREATE UNIQUE INDEX IF NOT EXISTS some_index ON some_table(some_column, another_column);` – Michael Scheper Oct 22 '18 at 18:36
1

how about if I want to then also do a bunch of inserts only if it didn't exist? What I want is to create a derived table on the fly if I find it doesn't exists, without paying for a bunch of REPLACE statement every time. – Britton Kerin Nov 18 '19 at 22:51
1

@BrittonKerin , so first you've gotta check if the table exists or not (this is the key i suppose...the rest is just running your code after doing the conditional check). Do see my reply in the answers on this condition. – aaronlhe May 31 '20 at 08:48

score 4 · Answer 2 · edited Aug 14 '23 at 15:08

I am going to try and add value to this very good question and to build on @BrittonKerin's question in one of the comments under @David Wolever's fantastic answer. Wanted to share it here because I faced the same challenge as @BrittonKerin and I managed to make it work (i.e. just want to run a piece of code only IF the table doesn't exist).

# for completeness lets do the routine thing of connections and cursors
conn = sqlite3.connect(db_file, timeout=1000) 

cursor = conn.cursor() 

# get the count of tables with the name  
tablename = 'KABOOM' 
cursor.execute("SELECT count(name) FROM sqlite_master WHERE type='table' AND name=? ", (tablename, ))

print(cursor.fetchone()) # this SHOULD BE in a tuple containing count(name) integer.

# check if the db has existing table named KABOOM
# if the count is 1, then table exists 
if cursor.fetchone()[0] ==1 : 
    print('Table exists. I can do my custom stuff here now.... ')
    pass
else: 
   # then table doesn't exist. 
   custRET = myCustFunc(foo,bar) # replace this with your custom logic

Create table in SQLite only if it doesn't exist already

2 Answers2

Linked

Related