CODEWITHSUNDEEP
pythonmysql
TheFaithfulLearner
TheFaithfulLearner

Reputation: 693

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

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?

Upvotes: 0

Views: 1450

Answers (1)

khelwood
khelwood

Reputation: 59146

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

Upvotes: 1

Related Questions