How do I check in SQLite whether a table exists?

If you're using fmdb, I think you can just import FMDatabaseAdditions and use the bool function:

[yourfmdbDatabase tableExists:tableName].

class CPhoenixDatabase():
    def __init__(self, dbname):
        self.dbname = dbname
        self.conn = sqlite3.connect(dbname)

    def is_table(self, table_name):
        """ This method seems to be working now"""
        query = "SELECT name from sqlite_master WHERE type='table' AND name='{" + table_name + "}';"
        cursor = self.conn.execute(query)
        result = cursor.fetchone()
        if result == None:
            return False
        else:
            return True

Note: This is working now on my Mac with Python 3.7.1

You can write the following query to check the table existance.

SELECT name FROM sqlite_master WHERE name='table_name'

Here 'table_name' is your table name what you created. For example

 CREATE TABLE IF NOT EXISTS country(country_id INTEGER PRIMARY KEY AUTOINCREMENT, country_code TEXT, country_name TEXT)"

and check

  SELECT name FROM sqlite_master WHERE name='country'

Using a simple SELECT query is - in my opinion - quite reliable. Most of all it can check table existence in many different database types (SQLite / MySQL).

SELECT 1 FROM table;

It makes sense when you can use other reliable mechanism for determining if the query succeeded (for example, you query a database via QSqlQuery in Qt).

The most reliable way I have found in C# right now, using the latest sqlite-net-pcl nuget package (1.5.231) which is using SQLite 3, is as follows:

var result = database.GetTableInfo(tableName);
if ((result == null) || (result.Count == 0))
{
    database.CreateTable<T>(CreateFlags.AllImplicit);
}

My preferred approach:

SELECT "name" FROM pragma_table_info("table_name") LIMIT 1;

If you get a row result, the table exists. This is better (for me) then checking with sqlite_master, as it will also check attached and temp databases.

The function dbExistsTable() from R DBI package simplifies this problem for R programmers. See the example below:

library(DBI)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
# let us check if table iris exists in the database
dbExistsTable(con, "iris")
### returns FALSE

# now let us create the table iris below,
dbCreateTable(con, "iris", iris)
# Again let us check if the table iris exists in the database,
dbExistsTable(con, "iris")
### returns TRUE 

Table exists or not in database in swift

func tableExists(_ tableName:String) -> Bool {
        sqlStatement = "SELECT name FROM sqlite_master WHERE type='table' AND name='\(tableName)'"
        if sqlite3_prepare_v2(database, sqlStatement,-1, &compiledStatement, nil) == SQLITE_OK {
            if sqlite3_step(compiledStatement) == SQLITE_ROW {
                return true
            }
            else {
                return false
            }
        }
        else {
            return false
        }
            sqlite3_finalize(compiledStatement)
    }

c++ function checks db and all attached databases for existance of table and (optionally) column.

bool exists(sqlite3 *db, string tbl, string col="1")
{
    sqlite3_stmt *stmt;
    bool b = sqlite3_prepare_v2(db, ("select "+col+" from "+tbl).c_str(),
    -1, &stmt, 0) == SQLITE_OK;
    sqlite3_finalize(stmt);
    return b;
}

Edit: Recently discovered the sqlite3_table_column_metadata function. Hence

bool exists(sqlite3* db,const char *tbl,const char *col=0)
{return sqlite3_table_column_metadata(db,0,tbl,col,0,0,0,0,0)==SQLITE_OK;}

You can also use db metadata to check if the table exists.

DatabaseMetaData md = connection.getMetaData();
ResultSet resultSet = md.getTables(null, null, tableName, null);
if (resultSet.next()) {
    return true;
}

If you are running it with the python file and using sqlite3 obviously. Open command prompt or bash whatever you are using use

1. python3 file_name.py first in which your sql code is written.
2. Then Run sqlite3 file_name.db.
3. .table this command will give tables if they exist.

I wanted to add on Diego Vélez answer regarding the PRAGMA statement.

From https://sqlite.org/pragma.html we get some useful functions that can can return information about our database. Here I quote the following:

For example, information about the columns in an index can be read using the index_info pragma as follows:

PRAGMA index_info('idx52');

Or, the same content can be read using:

SELECT * FROM pragma_index_info('idx52');

The advantage of the table-valued function format is that the query can return just a subset of the PRAGMA columns, can include a WHERE clause, can use aggregate functions, and the table-valued function can be just one of several data sources in a join...

Diego's answer gave PRAGMA table_info(table_name) like an option, but this won't be of much use in your other queries.

So, to answer the OPs question and to improve Diegos answer, you can do

SELECT * FROM pragma_table_info('table_name');

or even better,

SELECT name FROM pragma_table_list('table_name');

if you want to mimic PoorLuzers top-voted answer.

If you deal with Big Table, I made a simple hack with Python and Sqlite and you can make the similar idea with any other language

Step 1: Don't use (if not exists) in your create table command

you may know that this if you run this command that will have an exception if you already created the table before, and want to create it again, but this will lead us to the 2nd step.

Step 2: use try and except (or try and catch for other languages) to handle the last exception

here if you didn't create the table before, the try case will continue, but if you already did, you can put do your process at except case and you will know that you already created the table.

Here is the code:

def create_table():
    con = sqlite3.connect("lists.db")
    cur = con.cursor()
    try:
        cur.execute('''CREATE TABLE UNSELECTED(
        ID INTEGER PRIMARY KEY)''')
        print('the table is created Now')

    except sqlite3.OperationalError:
        print('you already created the table before')
    con.commit()
    cur.close()

You can use a simple way, i use this method in C# and Xamarin,

public class LoginService : ILoginService
{
    private SQLiteConnection dbconn; 
}

in login service class, i have many methods for acces to the data in sqlite, i stored the data into a table, and the login page it only shows when the user is not logged in.

for this purpose I only need to know if the table exists, in this case if it exists it is because it has data

public int ExisteSesion()
    {
        var rs = dbconn.GetTableInfo("Sesion");
        return rs.Count;
    }

if the table does not exist, it only returns a 0, if the table exists it is because it has data and it returns the total number of rows it has.

In the model I have specified the name that the table must receive to ensure its correct operation.

    [Table("Sesion")]
public class Sesion
{
    [PrimaryKey]
    public int Id { get; set; }
    public string Token { get; set; }
    public string Usuario { get; set; }

}