15

I used pyodbc with python before but now I have installed it on a new machine ( win 8 64 bit, Python 2.7 64 bit, PythonXY with Spyder).

Before I used to (at the bottom you can find more real examples):

columns = [column[0] for column in cursor.description]
temp = cursor.fetchall()
data = pandas.DataFrame(temp,columns=columns)

and it would work fine. Now it seems like DataFrame is not able to convert from the data fetched from the cursor anymore. It returns:

Shape of passed values is (x,y), indices imply (w,z)

I kind of see where the issue is. Basically, imagine I fetch only one row. Then DataFrame would like to shape it (1,1), one element only. While I would like to have (1,X) where X is the length of the list.

I am not sure why the behavior changed. Maybe it is the Pandas version I have, or the pyodbc, but updating is problematic. I tried to update some modules but it screws up everything, any method I use (binaries--for the right machine/installation--pip install, easy-install,anything! etc.. which is very frustrating indeed. I would probably avoid Win 8 64 bit from now on for Python).

Real examples:

sql = 'Select * form TABLE'
cursor.execute(sql)
columns = [column[0] for column in cursor.description]
data    = cursor.fetchall()
        con.close()
            results = DataFrame(data, columns=columns)

Returns: * ValueError: Shape of passed values is (1, 1540), indices imply (51, 1540)

Notice that:

ipdb> type(data)
<type 'list'>
ipdb> np.shape(data)
(1540, 51)
ipdb> type(data[0])
<type 'pyodbc.Row'>

Now, for example, if we do:

ipdb> DataFrame([1,2,3],columns=['a','b','c'])

* ValueError: Shape of passed values is (1, 3), indices imply (3, 3)

and if we do:

ipdb> DataFrame([[1,2,3]],columns=['a','b','c'])

a b c 0 1 2 3

However, even trying:

ipdb> DataFrame([data[0]], columns=columns)
*** ValueError: Shape of passed values is (1, 1), indices imply (51, 1)

or

ipdb> DataFrame(data[0], columns=columns)
*** PandasError: DataFrame constructor not properly called!

Please help :) Thanks!

user1350191
  • 311
  • 1
  • 3
  • 9

2 Answers2

39

As of Pandas 0.12 (I believe) you can do:

import pandas
import pyodbc

sql = 'select * from table'
cnn = pyodbc.connect(...)

data = pandas.read_sql(sql, cnn)

Prior to 0.12, you could do:

import pandas
from pandas.io.sql import read_frame
import pyodbc

sql = 'select * from table'
cnn = pyodbc.connect(...)

data = read_frame(sql, cnn)
Paul H
  • 65,268
  • 20
  • 159
  • 136
  • It gives a similar error! In fact, as I mentioned, my code worked before as well. Therefore, it must be pyodbc that changed something? File "xxx.py", line 253, in xxx results = read_frame(sql,con); File "C:\Python27\lib\site-packages\pandas\io\sql.py", line 162, in read_frame coerce_float=coerce_float) File "C:\Python27\lib\site-packages\pandas\core\frame.py", line 1115, in from_records columns) – user1350191 Nov 19 '13 at 01:38
  • File "C:\Python27\lib\site-packages\pandas\core\frame.py", line 5338, in _arrays_to_mgr return create_block_manager_from_arrays(arrays, arr_names, axes) File "C:\Python27\lib\site-packages\pandas\core\internals.py", line 1825, in create_block_manager_from_arrays construction_error(len(arrays),arrays[0].shape[1:],axes) File "C:\Python27\lib\site-packages\pandas\core\internals.py", line 1799, in construction_error tuple(map(int, [len(ax) for ax in axes])))) ValueError: Shape of passed values is (51,), indices imply (51, 1402) – user1350191 Nov 19 '13 at 01:38
  • To be clear, this happens on ALL of my machines, 2 desktop and 1 laptop. It didn't happen before (I updated all of them :/) – user1350191 Nov 19 '13 at 01:43
  • @user1350191 I can't reproduce this with pyodbc 3.0.6. Can you edit your question to show me *exactly* what you're doing when you try my method? (leave out your DB password, etc, obviously). – Paul H Nov 19 '13 at 17:00
  • It's pretty basic and similar to what you wrote. I will see what I can do as soon as I get some 10 free minutes. In the meantime, consider that I am using pyodbc 3.0.7 with Python 2.7 (both 32 and 64 bit gives the same problem, both pythonxy and anaconda distribution as well). – user1350191 Nov 23 '13 at 23:46
  • This answer made my day. – ako Aug 29 '14 at 18:48
17

This is because the cursor returns not a list of tuples but a list of the Row objects, which are similar to tuples, better, actually, but they confuse the pandas dataframe constructor. In the original example, do this before creating the data frame:

for i in range(0,len(temp)):
    temp[i]=tuple(temp[i])
Gena Kukartsev
  • 1,515
  • 2
  • 17
  • 19