How do I retain column headers when executing storedprocedure using python?

May 12, 2022

After I run my code below, i receive all the data from my storedproc. However, the column names are not returned

import pandas as pd
import pyodbc

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=PMI0317\Prod;'
                      'Database=Warehouse;'
                      'Trusted_Connection=yes;'
                      )


cursor = conn.cursor()
cursor.execute('SET NOCOUNT ON;EXEC Test.storedproc')
results = cursor.fetchall()
df = pd.DataFrame.from_records(results)
df
Out[113]: 
             0   1                  2   3    4    5   ... 15  16  17  18 19 20
0       9593746   0  COOKCHILDRENS.ORG   1  1.0  Dog  ...  0   0   0   0  0  2
1       9593723   0          gmail.com   1  1.0  Dog  ...  0   0  12  16  0  0
list(df.columns)
Out[114]: [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20]

As you see, it returns my data but column names are changes to numeric. why does this happen? What do I need to change in results function to retain original column names?

>Solution :

you can add the column name from cursor description:

df = pd.DataFrame.from_records(results, columns = [column[0] for column in cursor.description])

alternatively, you could use read_sql and directly load into pandas :

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=PMI0317\Prod;'
                      'Database=Warehouse;'
                      'Trusted_Connection=yes;'
                      )

df = pd.read_sql('exec Test.storedproc',conn)