<ul dir="auto">
<li>I have a dataframe with nan value inside and wanna use io.sql.write_frame function to store it in a table.</li>
<li>But, nan value can not be treated well by MySQLdb, it will raise an exception:</li>
</ul>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="OperationalError: (1054, &quot;Unknown column 'nan' in 'field list'&quot;)"><pre class="notranslate"><span class="pl-v">OperationalError</span>: (<span class="pl-c1">1054</span>, <span class="pl-s">"Unknown column 'nan' in 'field list'"</span>)</pre></div>
<ul dir="auto">
<li>I've tried to convert all nan value to None, but it turns out to be impossible,  fillna function treat param value=None as "the caller don't pass a value" not "the nan value should be None"</li>
<li>I think add some hacks to convert nan to None in io.sql._write_mysql can fix this.</li>
</ul>
<p dir="auto">UPDATE from <a class="user-mention notranslate" data-hovercard-type="user" data-hovercard-url="/users/jorisvandenbossche/hovercard" data-octo-click="hovercard-link-click" data-octo-dimensions="link_type:self" href="https://github.com/jorisvandenbossche">@jorisvandenbossche</a>:</p>
<p dir="auto">Overview of current status writing nan values (see also tests added in <a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="33265727" data-permission-text="Title is private" data-url="https://github.com/pandas-dev/pandas/issues/7100" data-hovercard-type="pull_request" data-hovercard-url="/pandas-dev/pandas/pull/7100/hovercard" href="https://github.com/pandas-dev/pandas/pull/7100">#7100</a>):</p>
<ul dir="auto">
<li>For MySQL using pymysql/MySQLdb nothing works: you get message <code class="notranslate">Unknown column 'nan' in 'field list'</code> (see also eg <a href="http://stackoverflow.com/questions/23353732/python-pandas-write-to-sql-with-nan-values" rel="nofollow">http://stackoverflow.com/questions/23353732/python-pandas-write-to-sql-with-nan-values</a>)</li>
<li>Numeric columns:
<ul dir="auto">
<li>working for sqlite and postgres</li>
<li>Only full NaN columns stay <code class="notranslate">None</code> in sqlite</li>
</ul>
</li>
<li>Object columns (eg strings)
<ul dir="auto">
<li>for postgresql: NaN is converted to the string <code class="notranslate">u'NaN'</code>, which is not really what we want</li>
<li>for sqlite it is returned as <code class="notranslate">None</code></li>
</ul>
</li>
<li><code class="notranslate">NaT</code>:
<ul dir="auto">
<li>postgresql: gives error on inserting "0001-255-255T00:00:00"</li>
<li>sqlite3: writing works, but reading it with query returns <code class="notranslate">'-001--1--1 -1:-1:-1.-00001'</code></li>
</ul>
</li>
<li>MSSQL: not working with message "The supplied value is not a valid instance of data type float", see <a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="40782513" data-permission-text="Title is private" data-url="https://github.com/pandas-dev/pandas/issues/8088" data-hovercard-type="issue" data-hovercard-url="/pandas-dev/pandas/issues/8088/hovercard" href="https://github.com/pandas-dev/pandas/issues/8088">#8088</a> for more details</li>
</ul>
<hr>
<p dir="auto">not sure exactly what sql expects (Nones?) rather than np.nan (or NaT)</p>
<p dir="auto"><a href="https://groups.google.com/forum/?fromgroups#!topic/pydata/lxhnFtuzvWQ" rel="nofollow">https://groups.google.com/forum/?fromgroups#!topic/pydata/lxhnFtuzvWQ</a></p>
<p dir="auto">also provide pandas <code class="notranslate">datetime64[ns]</code>, instead of <code class="notranslate">datetime/date</code> types<br>
<a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="14008059" data-permission-text="Title is private" data-url="https://github.com/pandas-dev/pandas/issues/3532" data-hovercard-type="issue" data-hovercard-url="/pandas-dev/pandas/issues/3532/hovercard" href="https://github.com/pandas-dev/pandas/issues/3532">#3532</a></p>