MySQL Version 8 Python 3.7 Cannot write None value as NULL

Question

Previous version of code wrote fine with Python 2.7 to AWS MySQL Version 8 with the following:

"""INSERT INTO test_data(test_instance_testid,
meas_time, data_type_name, value, corner_case, xmit, string_value) VALUES('15063', '2021-03-19 20:36:00', 'DL_chamber_temp', '23.4', 'None', 'None', 'None')"""

But now, porting to Python 3.7 to the same server I get this:

pymysql.err.InternalError: (1366, "Incorrect integer value: 'None' for column 'xmit' at row 1")

This makes sense since it is a str value 'None' and not Python type None (although it used to work).

It is legal to fill these columns as NULL values--that is their default in the test_data table.

If I change the code and set the values to Python None, I get a different error which I don't understand at all:

"""INSERT INTO test_data(test_instance_testid,
meas_time, data_type_name, value, corner_case, xmit, string_value) VALUES('15063', '2021-03-19 20:36:00', 'DL_chamber_temp', '23.4', None, None, None)"""

pymysql.err.InternalError: (1054, "Unknown column 'None' in 'field list'")

I really appreciate any help or suggestions. Thanks, Mike

Thanks for the help! Yes, NULL does work, but I'm stuck on how to handle value types on the fly within my method. Depending on the call I need to write a quoted value in one case and non-quoted NULL on others. For some reason my old code (illogically!) worked. I've tried everything I can think of without any luck. Any thoughts on how to do this?

None is not a valid value in MySQL. Does NULL work instead? — mackorone
– mackorone, Commented Mar 20, 2021 at 5:13

user4371848 · Accepted Answer · 2021-03-20 05:26:32Z

1

shouldn't work for python 2.7 either. 'None' is a string and you can't put it into Integer field. anyway try this

"""INSERT INTO test_data(test_instance_testid,
meas_time, data_type_name, value, corner_case, xmit, string_value) VALUES('15063', '2021-03-19 20:36:00', 'DL_chamber_temp', '23.4', null, null, null)"""

answered Mar 20, 2021 at 5:26

user4371848

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

Comments

user2117830 · Accepted Answer · 2021-03-22 01:18:46Z

Very convoluted using this MySQL connector, but after some effort I got it to work--hopefully this can be useful to someone else as well... To restate--why can't I pass a Python None and have it INSERTed as a MySQL NULL? All attempts failed - here's the one I think should work:

    testid = 15063
    meas_time = '2021-03-21 18:36:00'
    data_type = 'DL_chamber_temp'
    value = 33.4
    cc = None
    xmit = None
    str_val = None

    """INSERT INTO test_data(test_instance_testid,  
              meas_time,
              data_type_name,
              value,
              corner_case,
              xmit,
              string_value)
              VALUES({}, {}, {}, {}, {}, {}, {})""".format(
        "'{}'".format(testid),
        "'{}'".format(meas_time),
        "'{}'".format(data_type),
        "'{}'".format(value),
        "'{}'".format(cc) if cc is not None else None,
        "'{}'".format(xmit) if xmit is not None else None,
        "'{}'".format(str_val) if str_val is not None else None)

Here's the error - why is a value bring interpreted as a column name?

pymysql.err.InternalError: (1054, "Unknown column 'None' in 'field list'")

So, I brute forced it using 'NULL'.

q = """INSERT INTO test_data(test_instance_testid,
          meas_time,
          data_type_name,
          value,
          corner_case,
          xmit,
          string_value)
          VALUES({}, {}, {}, {}, {}, {}, {})""".format(
    "'{}'".format(testid),
    "'{}'".format(meas_time),
    "'{}'".format(data_type),
    "'{}'".format(value),
    "'{}'".format(cc) if cc is not None else 'NULL',
    "'{}'".format(xmit) if xmit is not None  else 'NULL',
    "'{}'".format(str_val) if str_val is not None else 'NULL'
)

And here's the query printed before execution:

q = INSERT INTO test_data(test_instance_testid,
          meas_time,
          data_type_name,
          value,
          corner_case,
          xmit,
          string_value)
          VALUES('15063', '2021-03-21 18:36:00', 'DL_chamber_temp', '33.4', NULL, NULL, NULL)

bahoz99 · Accepted Answer · 2021-03-22 01:26:40Z

0

make default value as NULL at PhpMyAdmin

answered Mar 22, 2021 at 1:26

bahoz99

1231 silver badge7 bronze badges

Collectives™ on Stack Overflow

MySQL Version 8 Python 3.7 Cannot write None value as NULL

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related