Inserting NULL value to a double data type MySQL Python

Question

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;

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):

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?

I am pretty sure that is the query i am executing. Its also weird that a 0 value is getting inserted on that column. — ellaRT
– ellaRT, Commented Sep 22, 2016 at 5:05
actually either of the methods (prepared or not) gives me the same result on the database, so lets use the latest one (not prepared). — ellaRT
– ellaRT, Commented Sep 22, 2016 at 5:30

e4c5 · Accepted Answer · 2016-09-22 06:50:50Z

5

This is not entirely suprising. In the first instance you are inserting the python predefined constant 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.

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,

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.

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.

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

edited Sep 22, 2016 at 6:50

answered Sep 22, 2016 at 4:47

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

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

5 Comments

ellaRT Over a year ago

I am aware of that, the question is how come the first list got inserted without any error? How come it did not catch 'None' value on the second set of row?

ellaRT Over a year ago

okay so on the first vallist - > Those values got accepted. two rows have been inserted on the table.

ellaRT Over a year ago

im pretty sure thats the query, i only have 4 lines of code. Which includes the list and the query.

ellaRT Over a year ago

thank you for clarifying @e4c5! i will try to send a bug report to mysql

e4c5 Over a year ago

glad to have been of help

Collectives™ on Stack Overflow

Inserting NULL value to a double data type MySQL Python

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related