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

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, Aug 12 '22 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, Aug 13 '22 at 08:30

Paproch · Accepted Answer · 2022-08-13T20:06:58.843

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 '22 at 20:06

answered Aug 13 '22 at 14:16

Paproch

1
3

`...SET player_name= player_name` what is this? – forpas Aug 13 '22 at 14:20
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. – Paproch Aug 13 '22 at 19:39
Then you should use INSERT OR IGNORE: https://stackoverflow.com/questions/12105198/sqlite-how-to-get-insert-or-ignore-to-work – forpas Aug 13 '22 at 19:42
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 Aug 13 '22 at 19:49
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. – Paproch Aug 13 '22 at 19:55

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

1 Answers1