how to check if a value exists in a column in sqlite 3 python

Question

I have a table (server_ preference) which has these headers: guild_id - int mod_channel_role_id - int report_channel_id - int i want to check if guild_id exist in the guild id column if it does not i want to insert to the table new staff i have tried many things and haven’t found any thing that worked this was my last attempt

conn = sqlite3.connect("server_preference.db")
    c = conn.cursor()
    for guild in client.guilds:
        c.execute("""IF NOT EXISTS ( SELECT guild_id FROM server_preference WHERE guild_id = ?)
                     BEGIN
                     INSERT INTO server_preference (guild_id) VALUES (:guild_id)
                     END""", guild.id)
        conn.commit()
    conn.close()

and I got this error:

sqlite3.OperationalError: near "IF": syntax error

SQLite flavor sql syntax is documented at sqlite.org/lang.html ... Nowhere will you find a command starting with IF. You can't just throw random stuff at it and hope it works. — Shawn
– Shawn, Commented Apr 25, 2020 at 11:10

Gordon Linoff · Accepted Answer · 2020-04-25 11:23:12Z

1

If you don't want duplicate guild_ids in your table, then you should have the database enforce uniqueness with a unique constraint or index (they are pretty much equivalent).

So, start with:

CREATE UNIQUE INDEX unq_server_preference_guild_id ON server_preference(guild_id);

Then, if you run:

INSERT INTO server_preference (guild_id) VALUES (:guild_id);

You will get an error. If you want to avoid an error, use an ON CONFLICT clause:

INSERT INTO server_preference (guild_id)
    VALUES (:guild_id)
    ON CONFLICT (guild_id) DO NOTHING;

answered Apr 25, 2020 at 11:23

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

GMB · Accepted Answer · 2020-04-25 11:16:14Z

0

You can implement this logic in a sngle statement by using the insert ... select syntax with a not exists condition:

insert into server_preference(guild_id)
select :guild_id
where not exists (select 1 from server_preference where guild_id = :guild_id)

answered Apr 25, 2020 at 11:16

GMB

224k25 gold badges103 silver badges151 bronze badges

Collectives™ on Stack Overflow

how to check if a value exists in a column in sqlite 3 python

2 Answers 2

Comments

Comments

Your Answer

Collectives™ on Stack Overflow

how to check if a value exists in a column in sqlite 3 python

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related