13

I'm working on a scrabblecheat program

Following some examples I have the following code below which uses SQLite for a simple database to store my words.

However it tells me I can't recreate the database table.

How do I write in a check for if there is already a table named spwords, then skip trying to create it?

The error:

(<class 'sqlite3.OperationalError'>, OperationalError('table spwords already exists',), None)

The Code:

def load_db(data_list): # create database/connection string/table conn = sqlite.connect("sowpods.db") #cursor = conn.cursor() # create a table tb_create = """CREATE TABLE spwords (sp_word text, word_len int, word_alpha text, word_score int) """ conn.execute(tb_create) # <- error happens here conn.commit() # Fill the table conn.executemany("insert into spwords(sp_word, word_len, word_alpha, word_score) values (?,?,?,?)", data_list) conn.commit() # Print the table contents for row in conn.execute("select sp_word, word_len, word_alpha, word_score from spwords"): print (row) if conn: conn.close()
Improve this question

4 Answers 4

Reset to default
21

The query you're looking for is:

SELECT name FROM sqlite_master WHERE type='table' AND name='spwords'

So, the code should read as follows:

tb_exists = "SELECT name FROM sqlite_master WHERE type='table' AND name='spwords'" if not conn.execute(tb_exists).fetchone(): conn.execute(tb_create)

A convenient alternative for SQLite 3.3+ is to use a more intelligent query for creating tables instead:

CREATE TABLE IF NOT EXISTS spwords (sp_word text, word_len int, word_alpha text, word_score int)

From the documentation:

It is usually an error to attempt to create a new table in a database that already contains a table, index or view of the same name. However, if the "IF NOT EXISTS" clause is specified as part of the CREATE TABLE statement and a table or view of the same name already exists, the CREATE TABLE command simply has no effect (and no error message is returned). An error is still returned if the table cannot be created because of an existing index, even if the "IF NOT EXISTS" clause is specified.

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

1 Comment

Note: If you have raise_on_warnings set to True, then an exception will be thrown even if you're using IF NOT EXISTS.
2 
conn = sqlite3.connect('sowpods.db') curs = conn.cursor() try: curs.execute('''CREATE TABLE spwords(sp_word TEXT, word_len INT, word_alpha TEXT,word_score INT)''') conn.commit() except OperationalError: None

https://docs.python.org/2/tutorial/errors.html

I believe if it already exists you can just skip the error and move directly into the inserting of the data

Improve this answer

Comments

1

I am not a fan of the bounce the CREATE off the database approach. You should know whether the table exists so that first time initialization can occur.

Here is the same query based answer but based on general purpose functions:

def getTables(conn): """ Get a list of all tables """ cursor = conn.cursor() cmd = "SELECT name FROM sqlite_master WHERE type='table'" cursor.execute(cmd) names = [row[0] for row in cursor.fetchall()] return names def isTable(conn, nameTbl): """ Determine if a table exists """ return (nameTbl in getTables(conn))

Now the top code is

if not(isTable(conn, 'spwords')): # create table and other 1st time initialization
Improve this answer

2 Comments

You forgot to pass a connection to isTable in your example call
Corrected. Thanks, but you should feel free to correct answers yourself.
0

Here is an example that shows how to cleanly consume the result from fetchone() call:

table_exists(conn:sqlite3.Connection, tbl_name:string) -> bool: (count,) = conn.execute("SELECT count(*) FROM sqlite_master WHERE type='table' AND name='{}'".format(tbl_name)).fetchone() return (count > 0)
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.