Check if entity already exists in a table

I want to check if entity already exists in a table, I tried to search google from this and I found this , but it didn’t help me. I want to return False if the entity already exists but it always insert the user.

def insert_admin(admin_name) -> Union[bool, None]:
    cursor.execute(f"SELECT name FROM admin WHERE name='{admin_name}'")
    print(cursor.fetchall()) # always return empty list []
    if cursor.fetchone():
        return False
    cursor.execute(f"INSERT INTO admin VALUES('{admin_name}')") # insert the name

def current_admins() -> list:
    print(cursor.execute('SELECT * FROM admin').fetchall()) # [('myname',)]

When I run the program again, I can still see that print(cursor.fetchall()) return empty list. Why is this happening if I already insert one name into the table, and how can I check if the name already exists ?

Answer

If you want to avoid duplicate names in the table, then let the database do the work — define a unique constraint or index:

ALTER TABLE admin ADD CONSTRAINT unq_admin_name UNIQUE (name);

You can attempt to insert the same name multiple times. But it will only work once, returning an error on subsequent attempts.

Note that this is also much, much better than attempting to do this at the application level. In particular, the different threads could still insert the same name at (roughly) the same time — because they run the first query, see the name is not there and then insert the same row.

When the database validates the data integrity, you don’t have to worry about such race conditions.