How to Avoid duplicate entries in SQLite3

February 3, 2022

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

>Solution :

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