Python SQLite delete where value is "None"

Question

I need to delete DB entries where the value could be None. I get my values from a ListBox. The function looks like this:

    def OnDelete(self, e): 
    num = self.list.GetItemCount()
    for i in range(num):
        if self.list.IsChecked(i):
            itemValueList = []
            for i2 in range(6):
                item = self.list.GetItem(i, i2)

                if item.GetText() == "None":
                    itemValueList.append("")
                else:
                    itemValueList.append(item.GetText()) 

    DBManager.DeleteEntry(itemValueList)

And the function in my DBManager looks like this:

def DeleteEntry(itemValueList):
    # Open Database
    conn = sqlite3.connect('Database.db')
    c = conn.cursor()
    # Delete a row of data
    c.execute('delete from Database where Value1=? and Value2=? and Value3=? and Value4=? and Value5=? and Value6=?',  
              (itemValueList[0], itemValueList[1], itemValueList[2], itemValueList[3], itemValueList[4], itemValueList[5]))

    # Save (commit) the changes
    conn.commit()

So in my case at the moment Value5 and Value6 are "None" or NULL in the SQLite DB. Therefore i set the string added to the itemValueList to "". But that doesnt work. The DB Entriy doesnt get deleted.

What do i have to change that also Entries where some columns can have no value are getting deleted?

Thank you.

[EDIT]:

c.execute('delete from Database where isnull(Value1,"")=? and isnull(Value2,"")=? and isnull(Value3,"")=? and isnull(Value4,"")=? and isnull(Value5,"")=? and isnull(Value6,"")=?',  
          (itemValueList[0], itemValueList[1], itemValueList[2], itemValueList[3], itemValueList[4], itemValueList[5]))

An empty string is not the same thing as NULL. To match a null value in SQL you have to use columnName IS NULL, you can't use =. — Barmar
– Barmar, Commented Feb 6, 2019 at 9:24

Corion · Accepted Answer · 2019-02-06 09:28:23Z

3

In SQL (and especially, SQLite), the value NULL is not identical to the empty string. So, setting the string in the itemValueList to the empty string makes no sense. You will need to change your SQL query to allow for the NULL value like:

delete
  from Database
 where     Value1=?
       and Value2=?
       and Value3=?
       and Value4=?
       and Value5 is null
       and Value6=?

And use that form of the query if your Value5 is None, or you need to convert your NULL values to the empty string:

delete
  from Database
 where     isnull(Value1,'') =?
       and isnull(Value2,'') =?
       and isnull(Value3,'') =?
       and isnull(Value4,'') =?
       and isnull(Value5,'') =?
       and isnull(Value6,'') =?

edited Feb 6, 2019 at 9:28

answered Feb 6, 2019 at 9:25

Corion

3,9151 gold badge20 silver badges27 bronze badges

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

5 Comments

K-Doe Over a year ago

How do I use that if every value could be NULL. I dont know what values are NULL.

Corion Over a year ago

@K-Doe Use the same phrase as I showed for Value5 for every value maybe?! I'll update my answer

K-Doe Over a year ago

I tried to convert my code with yours. But now i get: OperationalError: near "isnull": syntax error. I Add is to my post.

Corion Over a year ago

@K-Doe You are using double quotes. SQL does not know about double quotes. Use single quotes, as I did in the SQL in my answer. SQL is not Python and you cannot interchange double and single quotes.

K-Doe Over a year ago

Got it! Thank you.

Barmar · Accepted Answer · 2019-02-06 09:27:54Z

2

See What is the equivalent of the null-safe equality operator <=> in SQLite?. You should use the IS operator instead of = in your query, and it will match a NULL value properly.

You then need to change the code to use Python None rather than an empty string. This will be translated to SQL NULL in the prepared statement.

            if item.GetText() == "None":
                itemValueList.append(None)
            else:
                itemValueList.append(item.GetText())

answered Feb 6, 2019 at 9:27

Barmar

789k57 gold badges555 silver badges669 bronze badges

Collectives™ on Stack Overflow

Python SQLite delete where value is "None"

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related