Matching Python None with MySQL Null in a 'where' statement

Question

So I'm using Python and MySQL (using mysql connector). While I'm a little on the newbie side, I've managed to carry out a few of the basic functions and things have worked out so far.

In this case, I have three tables of interest: STUDENTS, GRADES and STUDENT_GRADES. As you might figure, I'm adding references from the first two to populate the third.

The way I'm doing this is as follows:

def StudentGradesToSQL(self, tableName, sqlConnectObject):
    lItems = []
    for student in self.listStudents:
        for sGrade in student.grades:
            lItems.append((student.cm_id, sGrade.name, sGrade.gradeLevelID, sGrade.schoolTypeID, sGrade.gradeValueID, sGrade.weaponTypeID))
            #print(f'{sGrade.weaponTypeID}')
    sqlConnectObject.ExecuteQueryMultiple(f"INSERT INTO {tableName} (STUDENT_CM_ID, GRADE_ID) \
                                            SELECT %s, gr.ID \
                                            FROM cmdb_01.GRADES gr \
                                            WHERE gr.NAME=%s \
                                            AND gr.GRADE_LEVEL_ID=%s \
                                            AND gr.SCHOOL_TYPE_ID=%s \
                                            AND gr.GRADE_VALUE_ID=%s \
                                            AND gr.WEAPON_TYPE_ID=%s", lItems)

So basically I have the student ID and I want to match the grade ID using the properties of the grade type. 'tableName' is 'STUDENT_GRADES'.

If I run this I get nothing. Well, there is no error reported from mySQL, but there are no entries in STUDENT_GRADES.

So I tried a few things out. The first was to reduce the number of AND conditions one at a time, and realised that I only needed to remove the check against WEAPON_TYPE_ID to get some results (as in, STUDENT_GRADES would be populated with expected values).

The problem, then, is that I'd rather like to check against all of these properties. What's different about WEAPON_TYPE_ID? It is often NULL in the database (none of the others are). You can see there is a print statement above to see what the sGrade.weaponTypeID value is, and it reads out 'None' each time (which is fine).

You might think that there are no matching entries, but I don't think this is the case. In fact I've seen that there should definitely be matches. It's just that, apparently, gr.WEAPON_TYPE_ID=None returns 'false' - no match.

So how is this solved? Am I naive for assuming that Python None would be treated as mySQL NULL? How else can I do this check?

If you want to check if a column is null, use the SQL condition column-name IS NULL. The MySQL = operator does not evaluate to true when the operands are null. — khelwood
– khelwood, Commented Apr 30, 2021 at 21:07
You could also try using the <=> operator instead of =. That might do what you want. — khelwood
– khelwood, Commented Apr 30, 2021 at 21:10
Does this answer your question? How can I insert NULL data into MySQL database with Python? — Sam M
– Sam M, Commented Apr 30, 2021 at 22:04

khelwood · Accepted Answer · 2021-05-01 16:58:46Z

1

The SQL operator = does not evaluate to true when its operands are null. So a condition like

WHERE x = NULL

will never match, even when x is null.

A standard way to check if a column is null is the condition:

x IS NULL

which works as you would expect.

For your situation, where you want to compare a column to a value that may or may not be null, you can use the mysql null-safe equality operator, <=>, which evaluates to true if its operands are equal, or are both null.

So your condition could be changed to

AND gr.WEAPON_TYPE_ID <=> %s

answered May 1, 2021 at 16:58

khelwood

59.7k14 gold badges91 silver badges116 bronze badges

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

2 Comments

TheFaithfulLearner Over a year ago

Great answer, thanks. I have a follow up, if you don't mind. I wonder, should I replace all my '=' with this if the mySQL field is nullable? That is to say, is that common practise?

khelwood Over a year ago

If you want to be able to compare to null like a value, then <=> is an easy way to do it. It depends what meaning null has in your schema. Some people try to avoid null in their schema because it requires special handling.

Collectives™ on Stack Overflow

Matching Python None with MySQL Null in a 'where' statement

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related