Python, mySQL INSERT fails if returned combobox values are null

Question

Total newbie to Python.

I'm creating a form (with TKinter) that populates a mySQL table. The form has comboboxes with lookups to reference tables in the DB. When the user selects a combobox value, the primary key for the selected value from the lookup table will be inserted as a foreign key into the target table (fields 1 and 3 in the code below). The form also contains short text (tk Entry) and long text (tk Text) fields (fields 2 and 4). All of the fields are optional.

The sql statement works fine if there are no nulls returned from the combobox fields, but the sql insert fails if one or more of the combobox fields are null. It works fine if there are nulls in the text fields. I want to be able to populate the foreign key fields in the DB with nulls as well.

# -- Parse data to prepare for sql insert -- #
def parse_data():
    field1 = field1_combobox.get() 
    field2 = field2_entry.get() 
    field3 = field3_combobox.get()
    field4 = field4_textbox.get(1.0, 'end')

# -- truncate combobox values to retain only the primary keys --  Pipes for delimiters in debugging --#
    print (field1[0:1], "|", 
           field2, "|",
           field3[0:1], "|",
           field4)

    sql = """INSERT INTO mytable (field1, field2, field3, field4) VALUES (%s, %s, %s, %s)"""
    val = (field1[0:1], field2, field3[0:1], field4)
    
    try:
         cursor.execute(sql, val)
         conn.commit()
         print("record inserted")

    except:
         conn.rollback
         print("update failed")

    conn.close

I want the SQL statement to work if there are NULL values from the combo boxes. I've checked that the relevant fields in the DB are all nullable.

I'm not getting any error messages from Python or SQL- the record simply doesn't insert.

Does anyone have any insight as to why? Is it because the code is trying to truncate a NULL? But if that is the case, the print statement still works fine if I comment out the SQL. It's mySQL that seems to have a problem.

---

Incidentally, I know that truncating data returned from the comboboxes is probably not the most elegant solution: I want to display the values in the comboboxes in the form, but I only want the IDs to be populated as FKs in the target table. If there is a better way to do it, I'd appreciate advice on that, too.

UPDATE: Found a better solution for stripping IDs out of returned combobox values that have the ID as well as the value, where I only want to use the ID to populate the target table with a foreign key, which works with ID strings that are longer than 1 character:

f1 = int(field1.split(" ")[0])

Answer 1

In order to get a NULL value in SQL, you need to replace the empty string from the combobox with None.

    val = (field1[0:1] or None, field2 or None, field3[0:1] or None, field4 or None)