Check if entity already exists in a table

Question

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 ?

update `cursor.execute(f"INSERT INTO admin (name) VALUES('{admin_name}')") ` and commit need to call if auto commit is not configured. — Ashish Karn, Jul 20 '20 at 11:28
@AshishKarn It is now edit the current `name` and not add a new one. How can I add a new `name` instead of replacing the current `name`? For now as I can see, I can only hold one `name` in the table. — dsasd, Jul 20 '20 at 11:34

score 0 · Accepted Answer · edited Jul 20 '20 at 11:21

0

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.

edited Jul 20 '20 at 11:21

Szabolcs

3,990
18
38

answered Jul 20 '20 at 11:18

Gordon Linoff

1,242,037
58
646
786

Can you please use uppercase letters instead of lowercase, and what is `unq_admin_name` ? Do I need to replace `unq_admin_name` and `name` with my admin name ? – dsasd Jul 20 '20 at 11:20
@dsasd . . . I don't understand your comment at all. This creates a constraint in the database. Once you create it, `name` is guaranteed to be unique in `admin`. – Gordon Linoff Jul 20 '20 at 12:58
But I want to able to add many `admins` to the table. Not only one. – dsasd Jul 20 '20 at 13:07
@dsasd . . . Huh? Of course you can add as many admins as you like. They just can't have the same name. – Gordon Linoff Jul 20 '20 at 13:48

Check if entity already exists in a table

1 Answers1