python pandas dtypes detection from sql
<p>I am quite troubled by the behaviour of Pandas DataFrame about Dtype detection.</p>

<p>I use 'read_sql_query' to retrieve data from a database to build a DataFrame, and then dump it into a csv file.</p>

<p>I don't need any transformation. Just dump it into a csv file and change date fields in the form : <strong>'%d/%m/%Y'</strong></p>

<p>However :</p>

<pre><code>self.dataframe.to_csv(self.fic,
                   index=False,
                   header=False,
                   sep='|',
                   mode='a',
                   encoding='utf-8',
                   line_terminator='\n',
                   date_format='%d/%m/%Y
                   )
</code></pre>

<p>Would miss to transforme/format some date fields...</p>

<p>I tried to do it another way :</p>

<pre><code>l = list(self.dataframe.select_dtypes(include=['datetime64']).columns)
for i in l:
    self.dataframe[i] = self.dataframe[i].dt.strftime('%d/%m/%Y')
</code></pre>

<p>I was about to be satisfied, but some more tests showed a weird behaviour :</p>

<p><strong>if my sql request only selects two nuplets :</strong></p>

<pre><code>requete = 'select * from DOMMAGE_INTERET where doi_id in (176433, 181564)'
</code></pre>

<p>Everything works, whatever formating in the csv or in the DataFrame.</p>

<p>It detects date fields properly :</p>

<pre><code>df.dtypes
doi_id                         int64
aff_id                         int64
pdo_id                         int64
doi_date_decision     datetime64[ns]
doi_date_mod          datetime64[ns]
doi_montant                  float64
doi_reste_a_payer             object
doi_appliquer_taux             int64
doi_date_update       datetime64[ns]
afg_id                         int64
dtype: object
</code></pre>

<p><strong>But when using a different selection :</strong></p>

<pre><code>requete = 'select * from DOMMAGE_INTERET where rownum &lt; 100'
</code></pre>

<p>It miss again. And actually, fields types are detected differently :</p>

<pre><code>doi_id                         int64
aff_id                         int64
pdo_id                         int64
doi_date_decision             object
doi_date_mod          datetime64[ns]
doi_montant                  float64
doi_reste_a_payer             object
doi_appliquer_taux             int64
doi_date_update       datetime64[ns]
afg_id                         int64
dtype: object
</code></pre>

<p>As you can see : <strong>'doi_date_decision' type does change depending of the request selection</strong> but, of course, this is the same set of data !!!</p>

<p>Isn't it weird?</p>

<p>Do you have a explanation for this behaviour?</p>

<p>Thanks to Boud and Parfait. Their answers are right : </p>

<p>All my tests shows that missing date fields can make Dtype detection to fail.</p>

<p>read_sql_query() has a parameter to define fields with date type. I guess to cure this problem.</p>

<p>Sadly, since now, I have been using a complete generic treatment to process a hundred of tables. 
To use 'read_sql_query' parameter 'parse_dates' would implies to do a prior work of metadata definition (like a json file describing each table).</p>

<p>Actually, I also found out that integers are changed to float when there is NaN field in the column...</p>

<p>If I would be reading csv flat files, I could understand that data type can be hard to detect... but from a database (read_sql_query)! Pandas has SqlAlchelmy as a dependence. And SqlAlchemy (and even any lower level Python database driver (cx_Oracle, DB API)) has reflection mechanism to detect data types. Pandas could have been using those metadatas to keep data types integrity.</p>

python sql csv pandas dataframe