PYODBC 到 Pandas - DataFrame 不工作 - 传递值的形状是 (x,y)，索引意味着 (w,z)

PYODBC to Pandas - DataFrame not working - Shape of passed values is (x,y), indices imply (w,z)

提问by user1350191

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).

我之前在 python 中使用了 pyodbc，但现在我已经将它安装在一台新机器上（win 8 64 位，Python 2.7 64 位，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:

它会正常工作。现在似乎 DataFrame 无法再从从游标获取的数据中进行转换。它返回：

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

传递值的形状是 (x,y)，索引意味着 (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.

我有点明白问题出在哪里。基本上，想象一下我只获取一行。然后 DataFrame 想对它进行整形（1,1），只有一个元素。虽然我想要 (1,X) 其中 X 是列表的长度。

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).

我不确定为什么行为改变了。可能是我的Pandas版本，或者pyodbc，但是更新有问题。我试图更新一些模块，但它搞砸了一切，我使用的任何方法（二进制文件--用于正确的机器/安装--pip 安装、简易安装、任何东西！等等。这确实非常令人沮丧。我可能会避免从现在开始为 Python 赢得 8 64 位）。

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)

返回： *ValueError：传递值的形状为 (1, 1540)，索引表示 (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)

*ValueError: 传递值的形状是 (1, 3)，索引意味着 (3, 3)

and if we do:

如果我们这样做：

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

a b c 0 1 2 3

美国广播公司 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!

请帮忙:) 谢谢！

回答by Paul H

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

从 Pandas 0.12（我相信）开始，您可以执行以下操作：

import pandas
import pyodbc

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

data = pandas.read_sql(sql, cnn)

Prior to 0.12, you could do:

在 0.12 之前，您可以执行以下操作：

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

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

data = read_frame(sql, cnn)

回答by Gena Kukartsev

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:

这是因为游标返回的不是元组列表，而是 Row 对象的列表，它们类似于元组，实际上更好，但它们混淆了 Pandas 数据帧构造函数。在原始示例中，在创建数据框之前执行此操作：

for i in range(0,len(temp)):
    temp[i]=tuple(temp[i])