将NULL值插入到双数据类型的MySQL Python中

I have a table. This is the create statement.

我有一个表。这是create语句。

   CREATE TABLE `runsettings` (
  `runnumber` mediumint(9) NOT NULL,
  `equipment` varchar(45) NOT NULL,
  `wafer` varchar(45) NOT NULL,
  `settingname` varchar(100) NOT NULL,
  `float8value` double DEFAULT NULL,
  `apcrunid` varchar(45) DEFAULT NULL,
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `intvalue` int(11) DEFAULT NULL,
  `floatvalue` float DEFAULT NULL,
  `Batch` varchar(45) DEFAULT NULL,
  `IndexNum` smallint(6) DEFAULT '1',
  `stringvalue` mediumtext,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1056989 DEFAULT CHARSET=latin1;

This is my insert statement :

这是我的补充声明:

import mysql.connector

cnx = mysql.connector.connect(user='test',
                                password ='test',host='0.0.0.1',
                              database='test')


vallist = [(471285, u'CT19', 7, u'271042', u'Etch Time Min', None, None, None),
           (471285, u'CT19', 7, u'00000', u'Etch Time Min', None, None, 'None')]


cursor = cnx.cursor()
# ss = 

cursor.executemany("INSERT INTO runsettings (apcrunid,equipment,runnumber,wafer,settingname,intvalue,floatvalue,float8value) VALUES (%s,%s,%s,%s,%s,%s,%s,%s)",vallist)
cnx.commit()

So I try to insert these values .

我试着插入这些值。

vallist = [471285, u'CT19', 7, u'271042', u'Etch Time Min', None, None, None,
           471285, u'CT19', 7, u'00000', u'Etch Time Min', None, None, 'None']

This is getting inserted. Result on DB But when the list value is this one (difference is the string 'None' gets evaluated first):

这是插入。在DB上的结果，但当列表值是这个值时(差值是字符串'None'首先被求值):

vallist = [471285, u'CT19', 7, u'271042', u'Etch Time Min', None, None, 'None', 
           471285, u'CT19', 7, u'271042', u'Etch Time Min', None, None, None]

It gives out the truncated error.

它给出了被截断的错误。

Data truncated for column 'float8value' at row 2

How come when the row that contains None is the first on the list it doesn't give out the same truncated error on the first list?

为什么包含None的行是列表中的第一行，它没有在第一个列表中给出相同的截断错误?

1 个解决方案

#1

This is not entirely suprising. In the first instance you are inserting the python predefined constant None

这并不完全出乎意料。在第一个实例中，您插入了python预定义的常量None

The sole value of types.NoneType. None is frequently used to represent the absence of a value, as when default arguments are not passed to a function.

类型的唯一值。None通常用于表示值的缺失，如未将默认参数传递给函数时。

That equates to SQL NULL. In the second instance you are inserting a String called 'None' into the table. These two are very different. If you insert a string into a double or float field you will see all kinds of errors, most often, the exact one you have seen.

这等于SQL NULL。在第二个实例中，您正在插入一个名为“None”的字符串。这两个非常不同。如果您将一个字符串插入到一个双字段或浮点字段中，您将看到各种各样的错误，最常见的是您所看到的错误。

in the first instance it works because you have declared :

在第一种情况下，它是有效的，因为您已经声明:

 `float8value` double DEFAULT NULL,

This accepts NULL and None is in the 8th place in your values list. When lots of different parameters are being used, it's always a good idea to use named parameters so that it's obvious at a glance what's being bound to each column.

它接受NULL，并且None在值列表的第8位。当使用许多不同的参数时，最好使用命名参数，这样就可以一目了然地看出每个列的绑定是什么。

Updates:

更新:

After running your code, the only conclusion that can be reached is that you have found a bug, by using print(cursor.statement) it's possible to discover that the executed query is.

在运行代码之后，可以得出的唯一结论是您发现了一个bug，通过使用print(cursor.statement)可以发现已执行的查询是错误的。

INSERT INTO runsettings (apcrunid,equipment,runnumber,wafer,settingname,intvalue,floatvalue,float8value) 
VALUES (471285,'CT19',7,'271042','Etch Time Min',NULL,NULL,NULL),
       (471285,'CT19',7,'00000','Etch Time Min',NULL,NULL,'None')

This does not produce an error, but if you erase the first set of values the error is indeed produced. My recommendation is to file a bug report

这不会产生错误，但是如果删除第一个值集，错误就会产生。我的建议是提交错误报告。

#1