SQLite : Return true if duplicate values are found in table-CodePudding

I want to know how you can find duplicate values in a table and make it return True. I have seen alot of questions about this but none of them helped, Thanks!

Here's my example :

import hashlib
import sqlite3

con = sqlite3.connect('users/accounts.db')
cur = con.cursor()
info = cur.execute("SELECT * FROM accounts;").fetchall()

print("Sign Up.")
username = input("Input your username : ")
password = input("Input your password : ")
email = input("Input your email: ")

result = hashlib.sha256(password.encode("utf-8"))
result_2 = str(result.digest)

cur.execute("insert into accounts (username, password, email) values(?,?,?)", (username, result_2, email))

con.commit()
print(info)
con.close()

Disclaimer

To those wondering, No this will not be used in a production environment, It is unsafe and can be easily exploited. Haven't even salted the hashes.

CodePudding user response：

If your goal be to prevent inserting a new user record with a username or email which already is being used by someone else, then an exists query provides one option:

INSERT INTO accounts (username, password, email)
SELECT ?, ?, ?
WHERE NOT EXISTS (SELECT 1 FROM accounts WHERE username = ? OR email = ?);

That is, in a single statement we also can check if the username or email provided already appears somewhere in the accounts table.

Updated Python code:

sql = """
    INSERT INTO accounts (username, password, email)
    SELECT ?, ?, ?
    WHERE NOT EXISTS (SELECT 1 FROM accounts WHERE username = ? OR email = ?)
"""
cur.execute(sql, (username, result_2, email, username, email))

CodePudding user response：

Let's suppose you have this table:

create table foo (
   foo_id numeric(12,0) primary key,
   str_value varchar(200)
);

And you want to find duplicate values for str_value.

You could do:

select str_value
  from foo
 group by str_value
having count(1) > 1;

You will have a list of str_values with dups on it.

If you want to know how many duplicates there are for each str_value you could add count(1) to your select clause:

select str_value, count(1)
  from foo
 group by str_value
having count(1) > 1;

CodePudding user response：

So this code worked

b = cur.execute("select * from accounts").fetchall()

sql = """INSERT INTO accounts (username, password, email)
         SELECT ?, ?, ?
         WHERE NOT EXISTS (SELECT 1 FROM accounts WHERE username = ? OR email = ?)"""
cur.execute(sql, (username, str(result.digest()), email, username, email))

a = cur.execute("select * from accounts").fetchall()

if a > b:
    print("Registration Complete.")
else:
    print("Failed : Username or Email already exists.")