Inserting NULL value to a double data type MySQL Python
<p>I have a table. This is the create statement. </p>

<pre><code>   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;
</code></pre>

<p>This is my insert statement :</p>

<pre><code>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()
</code></pre>

<p>So I try to insert these values .</p>

<pre><code>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']
</code></pre>

<p>This is getting inserted. 
Result on DB
<a href="http://i.stack.imgur.com/soQHW.png" rel="nofollow"><img src="http://i.stack.imgur.com/soQHW.png" alt="enter image description here"></a>
But when the list value is this one (difference is the string <code>'None'</code> gets evaluated first): </p>

<pre><code>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]
</code></pre>

<p>It gives out the truncated error. </p>

<pre><code>Data truncated for column 'float8value' at row 2 
</code></pre>

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

<p>This is not entirely suprising. In the first instance you are inserting the python predefined constant <a href="https://docs.python.org/2/library/constants.html" rel="nofollow">None</a></p>

<blockquote>
  <p>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.</p>
</blockquote>

<p>That equates to SQL <code>NULL</code>. 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.</p>

<p>in the first instance it works because you have declared :</p>

<pre><code> `float8value` double DEFAULT NULL,
</code></pre>

<p>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.</p>

<p>Updates:</p>

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

<pre><code>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')
</code></pre>

<p>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</p>

python mysql prepared-statement