How to compare None Python and NULL SQLite?

Question

I scrape the site and write the result in the database, some elements may be None, this is not a problem, in the database they are displayed as NULL. The problem is that before writing to the database, I check if this record already exists or not:

cursor = await db.execute('SELECT * FROM MATCHES WHERE (a="{}" AND b="{}" AND c="{}".format(a, b, c)))

The problem is that SELECT thinks that a = 'None'but not a = None And this is strange because when recording everything is ok:

INSERT INTO MATCHES (a, b, c) VALUES (?,?,?), (a, b, c)

In this case, if some element is None in the database then it will be NULL I tried another option:

SELECT * FROM MATCHES WHERE (a=? AND b=? AND c=?), (a, b, c)

But it works like the first. What can I do?

Lukasz Szozda · Accepted Answer · 2019-07-17 20:16:54Z

1

You could use "NULL-safe" operator IS:

SELECT * FROM MATCHES WHERE (a IS ? AND b IS ? AND c IS ?);

CREATE TABLE tab(i INT, j INT);
INSERT INTO tab(i, j) VALUES (1,1),(NULL,NULL),(1,2);

SELECT *
FROM tab
WHERE i = j;

SELECT *
FROM tab
WHERE i IS j;

db-fiddle.com demo

answered Jul 17, 2019 at 20:16

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

Collectives™ on Stack Overflow

How to compare None Python and NULL SQLite?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related