Did CREATE TABLE IF NOT EXISTS create the table?

Question

import sqlite3
connection = sqlite3.connect("...")
cursor = connection.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS ...")

How can I find out, after executing the CREATE TABLE IF NOT EXISTS, whether the table was created or already in place?

Do you have a utility that allows you to browse your database? You could look at your data before you run this script. — Bob Kaufman, Aug 24 '15 at 21:16
Check if the table exists before; and remember if it did (or didn't). I am pretty sure there is no useful success result from the CREATE TABLE DDL. — user2864740, Aug 24 '15 at 21:18

David Rajcok · Answer 1 · 2015-09-09T16:54:38.697

EDIT: Updated answer It sounds like you don't know beforehand what tables might exist, what those tables have in them (if anything), or have a way to check beforehand if tables exist.

To check if a table was created after using the IF NOT EXISTS clause on a CREATE TABLE command, you could try one of these:

Make the "new" table have at least one column name that is guaranteed to be different from the old table. After the CREATE TABLE command, you select the column guaranteed to be new.

CREATE TABLE newTable IF NOT EXISTS (column1 INTEGER, somethingUnique INTEGER) SELECT somethingUnique FROM newTable

If you don't get back an error from selecting somethingUnique, then you know that you have created a new table, else the table already existed. If you end up creating a new table and do not want that somethingUnique column anymore, then you can just delete that column.
Even if you don't want to make a somethingUnique column, there is the possibility that if the old table existed, it would have at least one row in it already. All you have to do is select anything from the table. If nothing returned, then you may or may not be dealing with your new table (so go back to suggestion 1). If something does get returned, then you know that you are dealing with an old table.

Old answer

One way to see if the table was created (or exists) is to go into a terminal, navigate to the directory where your database is, and then use sqlite commands.

$ sqlite3
sqlite> .open yourDatabase.db
sqlite> SELECT * FROM theTableYouWantedToCreate;

If the table does not exist, you would get back the following error:

Error: no such table: theTableYouWantedToCreate

If the table did exist, obviously it would return everything that is in the table. If nothing is in the table (since you just created it), sqlite will give you back another prompt, indicating that the table does indeed exist.

There are many ways to find out, whether the table had existed before, also programatically, and also including a check whether it has the desired structure. That is perfectly clear. The question is just, if I can find out **after** the `CREATE TABLE IF NOT EXISTS` whether it was newly created or had existed before. Sorry for being ambigous, I will update the question accordingly. — flaschbier, Sep 09 '15 at 12:57
Okay, I understand now. I edited my answer. The easiest way is probably option 1 - make your new table have a column name that is guaranteed to be unique and then select that new column. If an error occurs (that the column name does not exist), then you know that you're dealing with a preexisting table. — David Rajcok, Sep 09 '15 at 16:32

score 0 · Answer 2 · edited May 23 '17 at 11:59

The only way to check is by removing the IF NOT EXISTS part of the query and checking for a sqlite3.OperationalError with a message of the form "table $tablename already exists". I wouldn't trust the error message to be stable, but Python apparently does not supply an error code along with the exception.

The safest thing to do would be to begin a transaction and query the sqlite_master table beforehand, create the table if there were no results, then commit the transaction.

Note that none of these solutions will work correctly if the table you are attempting to create has a different schema than the one that exists in the database; database migrations are more complicated and usually require case-by-case handling.

Did CREATE TABLE IF NOT EXISTS create the table?

2 Answers2