如何避免SQLite3中的重复条目

Question

我在这里学习Sqlite，我正在尝试创建一个简单的数据库，用户名为&密码，数据库创建成功&我也能看到值，但每次我运行脚本时，都会在db中添加相同的项目。我如何在这里避免重复

import sqlite3
print("Creating Db ")
conn = sqlite3.connect("lp.db")
print("Db created successfully")
try:
conn.execute(
"""CREATE TABLE Users
(
"username"  TEXT ,
"password"  TEXT
);"""
)
except Exception as e:
print(e)
else:
print("Table created successfully")
cur = conn.cursor()
# The result of a "cursor.execute" can be iterated over by row
row = cur.execute('SELECT * FROM Users').fetchall()
if not row:
print("Empty")
else:
print("row")
conn.execute("INSERT INTO Users (username,password) VALUES ('lp',1234 )")
conn.commit()
row = cur.execute('SELECT * FROM Users').fetchall()
if not row:
print("Empty")
else:
print(row)
print("Done")
conn.close()

Answer 1

删除旧数据库文件&只需在用户名前添加UNIQUE

import sqlite3
print("Creating Db ")
conn = sqlite3.connect("lp.db")
print("Db created successfully")
try:
conn.execute(
"""CREATE TABLE Users
(
"username"  TEXT UNIQUE ,
"password"  TEXT
);"""
)
except Exception as e:
print(e)
else:
print("Table created successfully")
cur = conn.cursor()

conn.execute("INSERT OR IGNORE INTO Users  (username,password) VALUES ('lp2',1234 )")
conn.commit()
row = cur.execute('SELECT * FROM Users').fetchall()
if not row:
print("Empty")
else:
print(row)
print(conn.total_changes)
# Be sure to close the connection
print("Done")
conn.close()

Answer 2

只需将UNIQUE约束应用于正在创建的字段即可避免重复值：

conn.execute(
"""CREATE TABLE Users
(
"username"  TEXT UNIQUE,
"password"  TEXT UNIQUE
);"""
)

Answer 3

您需要删除表来启动新的数据库。

# drop table
connection.execute("DROP TABLE Users")

要忽略已经存在的数据，可以使用：

connection.execute(INSERT OR IGNORE INTO Users (username,password) VALUES ('lp',1234))