如何用Python将NULL数据插入MySQL数据库？

I'm getting a weird error when inserting some data from a Python script to MySQL. It's basically related to a variable being blank that I am inserting. I take it that MySQL does not like blank variables but is there something else I can change it to so it works with my insert statement?

将一些数据从Python脚本插入MySQL时，我遇到了一个奇怪的错误。它基本上与我插入的变量空白有关。我认为MySQL不喜欢空白变量但是还有其他东西我可以改变它以便它适用于我的insert语句吗？

I can successfully use an IF statement to turn it to 0 if its blank but this may mess up some of the data analytics I plan to do in MySQL later. Is there a way to convert it to NULL or something so MySQL accepts it but doesn't add anything?

如果它的空白，我可以成功地使用IF语句将其变为0，但这可能会破坏我计划稍后在MySQL中执行的一些数据分析。有没有办法将其转换为NULL或其他东西，所以MySQL接受它但不添加任何东西？

3 个解决方案

#1

-9

Do a quick check for blank, and if it is, set it equal to NULL:

快速检查空白，如果是，则将其设置为NULL：

if(!variable_to_insert)
    variable_to_insert = "NULL"

...then make sure that the inserted variable is not in quotes for the insert statement, like:

...然后确保插入的变量不在insert语句的引号中，如：

insert = "INSERT INTO table (var) VALUES (%s)" % (variable_to_insert)
...

not like:

不喜欢：

insert = "INSERT INTO table (var) VALUES ('%s')" % (variable_to_insert)
...

#2

100

When using mysqldb and cursor.execute(), pass the value None, not "NULL":

使用mysqldb和cursor.execute（）时，传递值None，而不是“NULL”：

value = None
cursor.execute("INSERT INTO table (`column1`) VALUES (%s)", (value,))

Found the answer here

在这里找到答案

#3

Why not set the variable equal to some string like 'no price' and then filter this out later when you want to do math on the numbers?

为什么不将变量设置为某个字符串，如“无价格”，然后在想要对数字进行数学运算时将其过滤掉？

filter(lambda x: x != 'no price',list_of_data_from_database)

#1

-9