systematic bug in pandas/io/sql.py when trying to write a dataframe of one column of integers into sqlite

[stonebig]

Hello,

trying this code with pandasql, with ipython, I get a bogus result .

import pandas as pd

%load https://raw.github.com/yhat/pandasql/master/pandasql/sqldf.py

df=pd.DataFrame([ 1, 2], columns=['f'])
sqldf("select * from df ", locals())

this gives the mysterious (binary ?) output:
f
0 (1, 0, 0, 0, 0, 0, 0, 0)
1 (2, 0, 0, 0, 0, 0, 0, 0)

Digging further, I found that the problem is in those lines generated by pandas/io/sql/py :

import pandas as pd
import sqlite3
df=pd.DataFrame([ 1, 2], columns=['f' ])
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE df ( [f] INTEGER );")
data = [tuple(x) for x in df.values]
con.executemany("INSERT INTO df ([f]) VALUES (?)", data)

here is the way to see the bogus result

for row in con.execute("select * from f"):
print (row)

this print :

(b'\x01\x00\x00\x00\x00\x00\x00\x00',)

(b'\x02\x00\x00\x00\x00\x00\x00\x00',)

instead of expected :

(1,)

(2,)

I can't guess further what is causing the issue, it seems there is a misunderstanding between pandas and sqlite when passing a "data" list of integers.

problem doesn't exist if we switch to non-integers like :
df=pd.DataFrame([ 1., 2], columns=['f' ])

Nota : I'm under windows vista 32 bits, python 3.3, in a country where the decimal separator is a comma (,) , maybe it's related to the problem, maybe not.

[cpcloud]

I don't get a bogus result, this fails with an InterfaceError (Python 2.7.4, Arch Linux). I wonder if the Python 3 sqlite3 module has some strange default behavior where if sqlite3 does not understand the data type it just converts it to raw bytes. Worth figuring out. Not sure why the decimal separator would matter here.

Looks like sqlite3 is the culprit not pandas:

import sqlite3
con = sqlite3.connect(':memory:')
con.execute('create table stocks (qty integer)')
data = [(1,), (2,)]
con.executemany('insert into stocks values (?)', data)  # this doesn't work
data = [(1.0,), (2.0,)]
con.executemany('insert into stocks values (?)', data)  # this works but converts float 2 int

Strangely enough this works too:

data = [(1,), (2,)]
for d in data:
    con.execute('insert into stocks values (?)', d)

[stonebig]

I just submitted this simple patch for pandas/io/sql.py to send 'normal' types to sqlite.
It seems to solve the issue for Python 3.

def _write_sqlite(frame, table, names, cur):
...
#patch for a bug in sqlite with python 3.3.1
if not len(frame.columns )==1 :
data = [tuple(x) for x in frame.values]
else :
data = [tuple(x) for x in frame.values.tolist()]
cur.executemany(insert_query,data )
...

[cpcloud]

What does your patch do? The lines you've changed don't do anything different. For example,

frame = DataFrame(np.array([[1, 2], [3, 4]]))
values = frame.values
data = [tuple(row) for row in values] # convert each row from a 2 element array to a 2 element tuple
data2 = [tuple(row) for row in values.tolist()] # still converting each row from a 2-element list to a 2-element tuple
assert data == data2 # this should not raise

Am I missing something here? Essentially,

x1 = np.array([1, 2])
tup = tuple(x1)
tup_from_list = tuple(x1.tolist())
tup == tup_from_list

no?

[stonebig]

middleware bugs are always a nightmare.

THOREAU'S THEORIES OF ADAPTATION

1) After months of training and you finally understand all of a program's
commands, a revised version of the program arrives with an all-new
command structure.
2) After designing a useful routine that gets around a familiar "bug" in
the system, the system is revised, the "bug" taken away, and you're
left with a useless routine.
3) Efforts in improving a program's "user friendliness" invariable lead
to work in improving user's "computer literacy".
4) That's not a "bug", that's a feature!

[stonebig]

the same patch on winpython 2.7.3 solves the ungracious failure :
"sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type"

[cpcloud]

you should write a test...this seems like magic. if u can u should get tox running so that you can test across python versions and also set up travis which runs your new code on ubuntu. happy 2 provide more details if needed.

[jreback]

@stonebig keep patching SQL bugs and u too can gain a pandas title

mr SQL

[cpcloud]

@stonebig can u explain why this patch works or write a failing test with the old version? i'm curious why this even matters.

[cpcloud]

@jreback ur title must be mister EOP (entirety of pandas).

[stonebig]

I'm new in github, I don't know how to write tests.
A ipython example would be , with the patch on pandas applied

#playing with lists, dictionaries, dataframe, like if all Python was a tasty sql pie
df=pd.DataFrame([(1, 'the great'),('Ourgo' ,'the brave')], columns=['surname','pseudo'])
a=[1 , 2]
b= {1: '2341', 2: '9102',3: '3258'}
sqldf("select * from df inner join a on surname=a.c0 inner join b on a.c0=b.c0 ", locals())

[stonebig]

import pandas as pd

%load https://raw.github.com/stonebig/pandasql/working_with_list_and_patched_pandas/pandasql/sqldf.py

#playing with lists, dictionaries, dataframe, like if all Python was a tasty sql pie
df=pd.DataFrame([(1, 'the great'),('Ourgo' ,'the brave')], columns=['surname','pseudo'])
a=[1 , 2]
b= {1: '2341', 2: '9102',3: '3258'}
sqldf("select * from df inner join a on surname=a.c0 inner join b on a.c0=b.c0 ", locals())

[jreback]

ha! mr. replace

@stonebig

look in pandas/io/tests/test_sql.py

just make a new test, list the GH issue in it

eseentially you do the new behavior and get a result, and can compare vs expected

there are also ways to test if something raises, etc.

this might be slighly more involved because you have to create a specific setup (if its not already in the def setup)

The idea of course is to try to replicate and narrow down a specific behavior that you changed. Ideally your test should fail before your fix, and pass afterwords (w/o impacting other tests)...but life is rarely so neat and clean!

let us know how it goes and we'll try to help

[cpcloud]

A test is really an example coupled with an assert statement asserting the truthood of some expression usually involving a property of the data that u know to be true. Here you want to show that with your patch some previously failing operation is now not failing. If I were you I might make a branch where its diff with git master is the test and compare from there.