Valueerror With Pandas - Shaped Of Passed Values

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 t

Solution 1:

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)

See the docs for more explanation on read_sql/to_sql.

Solution 2:

This worked from to export data from sqlserver to excel sheet .

import pyodbc
    import pandas as pd
    from openpyxl import Workbook


    cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};""Server=WINS2012;""Database=NameOfDataBase;""Trusted_Connection=yes;")


    cursor = cnxn.cursor()
    script = """
    SELECT * FROM ims_employee
    """
    cursor.execute(script)

    columns = [desc[0] for desc in cursor.description]

    data = cursor.fetchall()

    df = pd.read_sql_query(script, cnxn)

    writer = pd.ExcelWriter('C:\SqlExcel\Book1.xlsx')
    df.to_excel(writer, sheet_name='bar')

    writer.save()