Use SQL query result column names as pandas dataframe column names

Question

How can I add the columns names from sql query to pandas dataframe. I'm doing the following, but columns=columns doesn't work in my case.

import pymssql
import pandas as pd


con = pymssql.connect(
     server="MSSQLSERVER",
     port="1433",
     user="us",
     password="pass",
     database="loc")

cur = conn.cursor()

cur.execute("SELECT id from ide")

data=cur.fetchall()

pandas=pd.DataFrame(data)

cur.close()
con.close()

So when I print "pandas" it turns out to be without headers. So how can I receive them?

Look into `read_sql_table`: http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_sql_table.html#pandas.read_sql_table — DeepSpace, Jul 13 '16 at 13:37
hey guys, read sql dont work in my case-please other suggestions — Keithx, Jul 13 '16 at 13:46

score 12 · Answer 1 · answered Aug 12 '19 at 15:16

12

I've also found adding as_dict=True to the cursor object works:

cur = conn.cursor(as_dict=True)

Then you can run the rest as you have it:

cur.execute("SELECT id from ide")
data=cur.fetchall()
pandas=pd.DataFrame(data)

and pandas will grab the column names from the dictionary

answered Aug 12 '19 at 15:16

Eric

121
1
2

1

Which one is better? pymssql or pyodbc? Using pyodbc, I found a lot of difficulty when using it through AWS Lambda. – Aakash Basu Oct 14 '19 at 07:28

score 2 · Accepted Answer · edited Jul 13 '16 at 13:59

2

First establish the connection: I saw you used MSSQL

import pyodbc
# Parameters
server = 'server_name'
db = 'db_name'

# Create the connection
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=' + server + ';DATABASE=' + db + ';Trusted_Connection=yes')

Then use pandas:

df = pandas.read_sql(sql, conn)

edited Jul 13 '16 at 13:59

joris

133,120
36
247
202

answered Jul 13 '16 at 13:44

SerialDev

2,777
20
34

Use SQL query result column names as pandas dataframe column names

2 Answers2