Pandas的ValueError-传递值的形状 [英] ValueError with Pandas - shaped of passed values

问题描述

我正在尝试使用Pandas和PyODBC从SQL Server视图中提取内容并将其转储到excel文件中.

I'm trying to use Pandas and PyODBC to pull from a SQL Server View and dump the contents to an excel file.

但是，转储数据框时出现错误(我可以打印列和数据框内容):

However, I'm getting the error when dumping the data frame (I can print the colums and dataframe content):

ValueError: Shape of passed values is (1, 228), indices imply (2, 228)

此论坛上还有其他几个与同一问题有关的问题，但没有一个讨论从SQL Server表中提取消息.

There are several other issues on this forum pertaining to the same issue, but none discuss pulling from a SQL Server table.

我不知道是什么导致了此错误，并且更改视图以不同方式强制转换源列无效.

I can't figure out what is causing this error, and altering the view to cast the source columns differently has no effect.

这是我正在使用的python代码:

Here is the python code i'm using:

import pyodbc
import pandas as pd
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=servername;DATABASE=dbname;UID=username;PWD=password')
cursor = cnxn.cursor()
script = """
SELECT * FROM schema.ActiveEnrollmentCount
"""
cursor.execute(script)
columns = [desc[0] for desc in cursor.description]

data = cursor.fetchall()

df = pd.DataFrame(list(data), columns=columns)

writer = pd.ExcelWriter('c:\temp\ActiveEnrollment.xlsx')
df.to_excel(writer, sheet_name='bar')

writer.save()

我要提取的2列都是3位整数.

The 2 columns I'm trying to pull are both 3-digit integers.

推荐答案

要从数据库查询数据，最好使用内置的

To query data from a database, you can better use the built-in read_sql_query function instead of doing the execute and converting to dataframe manually.
For your example, this would give something like:

df = pd.read_sql_query(script, cnxn)

请参阅文档了解有关read_sql/to_sql.

这篇关于Pandas的ValueError-传递值的形状的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！