How to use SQlite NULL inside function default parameter?

Question

Id like to get domains from sqlite database where ip is NULL. I also want to make function so I can get domains with specific IPs in the future. I wrote function like this:

def get_domains(self, ip=None):
    """gets all the domains from domain table

    Keyword Arguments:
        ip {str} -- IP of the domain (default: {None})

    Returns:
        sqlite3.Row -- rows of the selected domains
    """
    sql = "SELECT domain FROM domains WHERE ip = ?"
    try:
        self.cursor.execute(sql, (ip,))
        rows = self.cursor.fetchall()
        return rows
    except Error as e:
        print(e)

but it returns no rows. It works when I test with other values, but can't make it to read WHERE ip = null. How do I return rows where default ip is null?

GMB · Accepted Answer · 2020-05-12 22:51:14Z

1

You can't check for nullness with the equality operator; for this you need special expression IS NULL. Some databases offer a null-safe operator for equality (typically, MySQL has <=>, Postgres supports the standard IS [NOT] DISTINCT FROM). SQLite offers IS for that.

SELECT domain FROM domains WHERE ip IS ?

answered May 12, 2020 at 22:51

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Comment

cikatomo Over a year ago

It works. Does this means that we should do one or both of this: 1. avoid NULL in sqlite and use proper value; 2. avoid "WHERE ip =" and always use "WHERE ip IS" no matter what.?

Collectives™ on Stack Overflow

How to use SQlite NULL inside function default parameter?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related