如何测试表是否已经存在? [英] How to test if a table already exists?

问题描述

我正在开发一个拼字游戏

I'm working on a scrabblecheat program

按照一些例子，我有下面的代码，它使用 SQLite 作为一个简单的数据库来存储我的话.

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.

如何检查是否已经存在名为 spwords 的表，然后跳过尝试创建它?

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

错误:

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

代码:

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()

推荐答案

您要查找的查询是:

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)

SQLite 3.3+ 的一个方便的替代方法是使用更智能的查询来创建表:

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)

来自文档:

尝试在已经包含同名表、索引或视图的数据库中创建新表通常是错误的.但是，如果IF NOT EXISTS"子句被指定为 CREATE TABLE 语句的一部分，并且同名的表或视图已经存在，则 CREATE TABLE 命令根本不起作用(并且不会返回错误消息).如果由于现有索引而无法创建表，即使指定了IF NOT EXISTS"子句，仍会返回错误.

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.

这篇关于如何测试表是否已经存在?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！