How to insert variable to sql table in python if variable is not already in table? [duplicate]

Question

Im having a problem with my sqlite database in my python program. I'm trying to create a table that will hold records of players score. Player name is saved as variable "val" and that variable is used in sql code. Also if the player is already in my table I don't want to create duplicate.

My problem is that if I don't use WHERE {v}... it all works, but the moment i try to prevent table from creating duplicates it gives me an OperationalError: near "WHERE": syntax error.

Im quite new to sql so it's hard for me to find what i'm doing wrong. I used (?) and format and as long as i don't use WHERE it's fine. How can I make sure that my variable (player name) from outside of sql code is not in my table so i can insert it?

        val = "PlayerName"
        cur.execute( """
                    INSERT INTO Table (player_name)
                    VALUES {v}
                    WHERE  {v} NOT IN (
                    SELECT player_name FROM Table)""".format(v = val))

INSERT statements don't have WHERE clauses. You can either do a SELECT first to check for the name, or add a UNIQUE index on the name column to force an error if the name already exists (this is safer if your program has multiple concurrent users). — snakecharmerb
– snakecharmerb, Commented Aug 12, 2022 at 13:14
Later that night I realized that i can't use WHERE clause here, byt UNIQUE does not help me at all. I don't want to force an error but to correctly create my table and continue with my program as my sql code is implemented in bigger python program. — Paproch
– Paproch, Commented Aug 13, 2022 at 8:30

Paproch · Accepted Answer · 2022-08-13 20:06:58Z

0

Ok, it works now. My main problem was that i tried to use commands from MySQL instead of sqlite. My code that worked:

            cur.execute( """INSERT INTO Table (player_name)
                        VALUES (?)
                        ON CONFLICT(player_name) DO UPDATE SET player_name= player_name""",(val) )

Edit: Final version without player_name = player_name workaround:

            cur.execute( """INSERT OR IGNORE INTO Table (player_name) VALUES (?)""",(val) )

edited Aug 13, 2022 at 20:06

answered Aug 13, 2022 at 14:16

Paproch

13 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

forpas Over a year ago

...SET player_name= player_name what is this?

Paproch Over a year ago

In my case I wanted to only insert new row (new player name) to my table if it's not already in there and if it's in there I don't want to do anything. To do that I used DO UPDATE SET but I don't want to insert new value but just leve it as it was. Becouse of that I "updated" it to the value that it already had and thanks to that there was no changes.

forpas Over a year ago

Then you should use INSERT OR IGNORE: stackoverflow.com/questions/12105198/…

Paproch Over a year ago

I wanted to use INSERT OR IGNORE but for some reason I had problems in my program so I just replaced row value with itself.

Paproch Over a year ago

Ok, I just check again and I don't know why but after reloading my whole program again, INSERT OR IGNORE is working fine. Maybe it was problem with my computer memory or something else (as my computer is preatty old) but it works fine now.

Collectives™ on Stack Overflow

How to insert variable to sql table in python if variable is not already in table? [duplicate]

1 Answer 1

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Linked

Related