Error writing null values to table.

[dakin80]

I've been using this code very successfully for some months now but have just encountered a problem with writing null values into a table. I've created a simple test table and script as listed below. The table has one column called columna and it's a float datatype which permits null values and the default value is NULL. There are no indexes on the column. Writing a value of, for example 0.0, works OK but I get an error if the value is set to None.

import time
import network
import upymysql
import upymysql.cursors

wlan = network.WLAN(network.STA_IF)

def wifi():
    wlan.active(True)
    wlan.connect("ssid", "password")

    retries = 10
    while  retries > 0:
        if wlan.status() < 0 or wlan.status() >= 3:
            break
        retries -= 1
        time.sleep(1)

    if wlan.status() == 3:
        print("Wifi connected")
    else:
        print("Wifi connection failed")
        flashLed()

def connect_sql():
    connection = upymysql.connect(host='192.168.1.2',
       user='user',
       password='password',
       port=3307,
       db='MogWeather',
       charset='utf8mb4',
       cursorclass=upymysql.cursors.DictCursor)
    return connection

def main():
    wifi()
    connection = connect_sql()

    value = 0.0
    sql = ("INSERT INTO test (columna)"
    "VALUES (%s)")
    data = (value)
    connection.cursor().execute(sql, data)
    connection.commit()
    
    value = 0.0
    sql = ("INSERT INTO test (columna)"
    "VALUES (%s)")
    data = (None)
    connection.cursor().execute(sql, data)
    connection.commit()

if __name__ == "__main__":
    main()

>>> %Run -c $EDITOR_CONTENT
Wifi connected
Traceback (most recent call last):
  File "<stdin>", line 54, in <module>
  File "<stdin>", line 50, in main
  File "/lib/upymysql/cursors.py", line 163, in execute
  File "/lib/upymysql/cursors.py", line 311, in _query
  File "/lib/upymysql/connections.py", line 815, in query
  File "/lib/upymysql/connections.py", line 1036, in _read_query_result
  File "/lib/upymysql/connections.py", line 1319, in read
  File "/lib/upymysql/connections.py", line 983, in _read_packet
  File "/lib/upymysql/connections.py", line 399, in check_error
ValueError: 
>>> 

[dvrhax]

I think it may be an issue with your code. You're not passing a tuple, in the first instance you're passing a float and in the second instance a NoneType I suspect that the function is overloaded and can either handle an iterable or a value but may stick on the None. Try putting a comma after value in data = (value) to get data = (value, ) and see if that works.

[dakin80]

Thank you for the reply.

Slightly modified code for clarity:

def main():
    wifi()
    connection = connect_sql()

    sql = ("INSERT INTO test (columna)"
    "VALUES (%s)")
    data = 1.0
    print(sql)
    print(data)
    connection.cursor().execute(sql, data)
    connection.commit()
    
    sql = ("INSERT INTO test (columna)"
    "VALUES (%s)")
    data = None
    print(sql)
    print(data)
    connection.cursor().execute(sql, data)
    connection.commit()

Output:

Wifi connected
INSERT INTO test (columna)VALUES (%s)
1.0
INSERT INTO test (columna)VALUES (%s)
None
Traceback (most recent call last):
  File "<stdin>", line 56, in <module>
  File "<stdin>", line 52, in main
  File "upymysql/cursors.py", line 163, in execute
  File "upymysql/cursors.py", line 311, in _query
  File "upymysql/connections.py", line 815, in query
  File "upymysql/connections.py", line 1036, in _read_query_result
  File "upymysql/connections.py", line 1319, in read
  File "upymysql/connections.py", line 983, in _read_packet
  File "upymysql/connections.py", line 399, in check_error
ValueError: 

Have also tried with data=(None) and data=(None,) and it gives the same error.

So it looks like the execute function accepts either a single value or a tuple but the presence of a None as either causes it to fail.

[dvrhax]

Does it make sense to trap None and convert to 'NULL'? If that's the right answer you could do it in user code, or we could patch the library to do the conversion.

[dakin80]

If I execute:

    sql = (f'INSERT INTO test (columna) VALUES (NULL)')
    connection.cursor().execute(sql)
    connection.commit()

Then the row with a null column is inserted correctly.

The problem seems to be in the implementation of connection.cursor().execute(sql, data)

Any python None values in the data tuple are not handled correctly. Perhaps they are converted to "None" in the SQL string that is ultimately executed and NOT converted to "NULL"?

I can work around this by formatting an SQL string and using the connection.cursor().execute(sql) version of the call having substituted any None values with NULL. In an ideal world connection.cursor().execute(sql, data) should handle this itself (as seems to happen in the pure python version of mysqldb). It seems to be the generally accepted practice that using placeholders and a tuple of data values is preferable to guard against SQL injection so I'd like to try and use that version. In the absence of a fix it would be advisable to document this limitation.

Regardless of that I'd like to thank you for doing the port from python to micropython as, as best I can tell, it's the only version available and in my case it gave a good solution. At least until I decided to change columns to allow nulls!