Write problem in SQL database with sqlite3

[Acanthostega]

I have a problem when writing some data into an SQL database in sqlite3 through the to_sql (and write_frame) method. I reduced the problem with only a few of the data, and can't reproduce with random data.

Let take the following csv file:

$ cat /tmp/file.csv
objid,stellarmass_r-z
1237661976011866148,10.92719608
1237662237467279416,10.680375280000003
1237658491208794136,10.354045940000002
1237658629157879827,10.19681978
1237658629695537327,10.28878822
1237661971185270795,10.18847402
1237658630770720842,10.18515162
1237664291001860141,10.18533078
1237658630769737814,10.05068674
1237661070337703967,10.02273786
1237654606947155983,10.174803600000002
1237662236930342972,9.8948685
1237661070337245268,9.971118260000004
1237661950255366168,10.083547300000003
1237661068728139808,9.867372900000003
1237662236392947770,10.139080600000002
1237661811738935340,9.98884806
1237661971722141706,10.13716722
1237658628620877841,9.7847806
1237658492818948135,10.01117626
1237661976548016173,9.987120960000002
1237661970648399911,9.96022516
1237661950255169580,9.97642532
1237661974936944710,10.07319712
1237658629695537161,9.89437576
1237664292076584979,9.845949080000002
1237662236393865225,9.95083568
1237661948645212163,9.805305520000003
1237668589187301427,9.8581666
1237661069263372384,10.162577240000003
1237661977085542412,9.8445422
1237658629159845928,9.94675
1237661812812415036,9.71412302
1237661974935830545,9.97575908
1237655125009629193,9.71575088
1237661813348106345,9.61460742
1237654605873676293,9.826896680000004
1237662525766828100,9.813211940000002
1237662239077498913,9.835223640000002
1237668588650692644,9.72306616
1237654605873479684,9.822821340000004
1237661972796604601,9.55444232
1237661951329435708,9.76327192
1237661970112643097,9.52970244
1237658491745599505,9.64106222
1237661975474012252,9.8955774
1237658491744944240,9.81476334
1237658628621860878,9.66009456
1237661972260061194,9.53882956
1237658628621074512,9.48091508
1237661971184877624,9.68729046
1237662236930474097,9.78220464
1237655126620045406,9.757867780000002
1237661813349220495,9.76147096
1237661971185795108,9.6245947
1237654606409760858,9.506159320000004
1237661813886746643,9.336260700000002
1237661069264617519,9.65304386
1237664289392558172,9.63690456
1237668623012200453,9.6114549
1237662238541283500,9.44454652
1237658628620877914,9.35708458
1237668588650299428,9.53530032
1237662525767352343,7.849380880000003
1237661972796866572,9.518680880000002
1237655126083174431,9.7224362
1237662526839914522,8.855016099999999
1237658629696913469,9.23278334
1237661976547491868,9.5446055
1237661971186188308,9.6738568
1237661950791057448,9.3330057
1237662238004609184,9.34737388
1237658491208466514,-4.552070699999998
1237658629695275065,9.35274034
1237654605873479808,9.389559000000002
1237661970649710733,9.20148508
1237661974399483956,9.023542599999999
1237661812275740750,9.42462462
1237661971723059346,9.27588078
1237654606410547205,9.43386082
1237661815485562947,9.40952882
1237658629159976966,9.402022700000002
1237661976010752146,9.19800654
1237654605873414182,9.43241374
1237661971185991818,9.26299112
1237655126620831784,8.90218828
1237661974937010350,9.1063516
1237661976010227741,9.23574972
1237655126620045511,9.1018017
1237661811202457690,9.337958960000002
1237655126083240130,9.48160272
1237664290465316880,9.180347439999998
1237654606410219714,9.379238660000002
1237655126082977857,9.3410276
1237661971723452464,9.182900720000003
1237655125546565757,9.38576674
1237662239079268544,9.30403386
1237662524694528046,9.64007364
1237661950256218200,9.18688404
1237661069263437997,9.26908462

Then with pandas I read it, and put it into sqlite3 database, and read it again to see if they are identical:

>>> import pandas as pd
>>> import numpy as np
>>> import sqlite3

>>> data = pd.read_csv("/tmp/file.csv")
>>> print(len(np.unique(data.objid)))
100

>>> conn = sqlite3.connect("/tmp/file.db")
>>> data.to_sql("DATA", conn, if_exists="replace")

>>> result = pd.read_sql("SELECT objid FROM DATA;", conn)
>>> print(len(np.unique(result.objid)))
98

The problem disappear if there is no column "stellarmass_r-z" which is a float64 in the data dataframe. It should be a dtype problem but can't point it out! I tried it on an other system with different version of sqlite3 so the problem isn't really the version... (3.7.9 and 3.8.4.3). No problem if I use to_hdf and to_csv.

INSTALLED VERSIONS

commit: None
python: 3.4.0.final.0
python-bits: 64
OS: Linux
OS-release: 3.13.8-1-ARCH
machine: x86_64
processor:
byteorder: little
LC_ALL: None
LANG: fr_FR.utf8

pandas: 0.13.1
Cython: 0.20.1
numpy: 1.8.1
scipy: 0.13.3
statsmodels: None
IPython: 2.0.0
sphinx: 1.2.2
patsy: None
scikits.timeseries: None
dateutil: 2.2
pytz: 2014.2
bottleneck: None
tables: 3.1.0
numexpr: 2.3.1
matplotlib: 1.3.1
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
sqlalchemy: None
lxml: None
bs4: None
html5lib: None
bq: None
apiclient: None

[cpcloud]

Can you try this on master? I can't reproduce this.

[Acanthostega]

I tried it on master, and effectively the problem doesn't appear. But still present in my version... I checked it too with the pip installer to see if it is due to the package of my Archlinux distribution but the result is the same.

If nobody has an idea, I will upgrade to the master version.

Thanks for your help !

[cpcloud]

If you're feeling adventurous you can do git bisect. I bet you'd find the commit that introduced this bug fairly quickly as there's only about 600 commits between 0.13.1 and HEAD. Otherwise, I'll do it later this evening.

[cpcloud]

@Acanthostega I have a very similar setup to you (Arch Linux, same kernel version, etc). I can repro this.

[cpcloud]

This was fixed in 5c60f5c

[cpcloud]

@Acanthostega Thanks for the report!