PYODBC 到 Pandas - DataFrame 不工作 - 传递值的形状是 (x,y),索引意味着 (w,z) [英] PYODBC to Pandas - DataFrame not working - Shape of passed values is (x,y), indices imply (w,z)
问题描述
我之前在 python 中使用了 pyodbc,但现在我已经将它安装在一台新机器上(win 8 64 位,Python 2.7 64 位,PythonXY with Spyder).
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)
它会工作得很好.现在似乎 DataFrame 无法再从从游标获取的数据中进行转换.它返回:
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:
传递值的形状是 (x,y),索引表示 (w,z)
Shape of passed values is (x,y), indices imply (w,z)
我有点明白问题出在哪里.基本上,想象一下我只获取一行.然后 DataFrame 想对它进行整形(1,1),只有一个元素.虽然我想要 (1,X) 其中 X 是列表的长度.
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.
我不确定为什么行为会改变.可能是我的Pandas版本,或者pyodbc,但是更新有问题.我试图更新一些模块,但它搞砸了一切,我使用的任何方法(二进制文件--用于正确的机器/安装--pip 安装、简易安装、任何东西!等等.这确实非常令人沮丧.我可能会避免从现在开始,为 Python 赢得 8 64 位).
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).
真实例子:
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)
返回:* ValueError: 传递值的形状是 (1, 1540),索引意味着 (51, 1540)
Returns: * ValueError: Shape of passed values is (1, 1540), indices imply (51, 1540)
请注意:
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: 传递值的形状是 (1, 3),索引意味着 (3, 3)
* ValueError: Shape of passed values is (1, 3), indices imply (3, 3)
如果我们这样做:
ipdb> DataFrame([[1,2,3]],columns=['a','b','c'])
a b c0 1 2 3
a b c 0 1 2 3
然而,即使尝试:
ipdb> DataFrame([data[0]], columns=columns)
*** ValueError: Shape of passed values is (1, 1), indices imply (51, 1)
或
ipdb> DataFrame(data[0], columns=columns)
*** PandasError: DataFrame constructor not properly called!
请帮忙:)谢谢!
推荐答案
从 Pandas 0.12(我相信)你可以做到:
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)
在 0.12 之前,您可以:
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)
这篇关于PYODBC 到 Pandas - DataFrame 不工作 - 传递值的形状是 (x,y),索引意味着 (w,z)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!