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.
-
4possible duplicate of Creating an SQLite table only if it doesn't already existToby Allen– Toby Allen2012-08-16 04:00:28 +00:00Commented Aug 16, 2012 at 4:00
Add a comment
|
2 Answers
From http://www.sqlite.org/lang_createtable.html:
CREATE TABLE IF NOT EXISTS some_table (id INTEGER PRIMARY KEY AUTOINCREMENT, ...);
3 Comments
Michael Scheper
This works for indices, too:
CREATE UNIQUE INDEX IF NOT EXISTS some_index ON some_table(some_column, another_column);
Britton Kerin
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.
aaronlhe
@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.
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
