How to Avoid duplicate entries in SQLite3-CodePudding

I am learning Sqlite here I am trying to create a simple database with username & password , The database gets created successfully & I am able too see values but everytime I am running the script same item is getting added in db how can I avoid duplicates here

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

CodePudding user response：

Delete old database file & Just Add UNIQUE in front of username

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

CodePudding user response：

You need to drop table to start with fresh database.

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

To ignore the data if already present, you can use:

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

CodePudding user response：

Just apply the UNIQUE constrant to the fields you are creating to avoid duplicate values:

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