将NULL值插入双精度数据类型MySQL Python [英] Inserting NULL value to a double data type MySQL Python

问题描述

我有一张桌子.这是create语句.

I have a table. This is the create statement.

   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;

这是我的插入语句:

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']

这将被插入. 数据库结果 但是当列表值是这个值时(区别是字符串'None'首先被求值):

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

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 

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

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?

推荐答案

这并不完全令人惊讶.在第一个实例中，您要插入python预定义常量无

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

types.NoneType的唯一值.没有一个经常被用来表示 缺少值，例如默认参数未传递给 功能.

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.

等同于SQL NULL.在第二个实例中，您将在表中插入一个名为"None"的字符串.这两个是非常不同的.如果将字符串插入double或float字段中，您将看到各种错误，大多数情况下，就是您所看到的错误.

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.

在第一个实例中它有效，因为您已经声明了:

in the first instance it works because you have declared :

 `float8value` double DEFAULT NULL,

这接受NULL，并且None在值列表中排在第八位.当使用许多不同的参数时，使用命名参数始终是一个好主意，这样一眼便可以看出绑定到每列的内容.

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.

更新:

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

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.

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

这篇关于将NULL值插入双精度数据类型MySQL Python的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！