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: pandas.pydata.org/pandas-docs/stable/generated/… — DeepSpace
– DeepSpace, Commented Jul 13, 2016 at 13:37
hey guys, read sql dont work in my case-please other suggestions — Keithx
– Keithx, Commented Jul 13, 2016 at 13:46

Eric · Accepted Answer · 2019-08-12 15:16:14Z

13

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, 2019 at 15:16

Eric

1311 silver badge2 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aakash Basu Over a year ago

Which one is better? pymssql or pyodbc? Using pyodbc, I found a lot of difficulty when using it through AWS Lambda.

jtlz2 Over a year ago

pymssql works well now

joris · Accepted Answer · 2016-07-13 13:59:31Z

3

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, 2016 at 13:59

joris

140k37 gold badges257 silver badges207 bronze badges

answered Jul 13, 2016 at 13:44

SerialDev

2,84722 silver badges34 bronze badges

Collectives™ on Stack Overflow

Use SQL query result column names as pandas dataframe column names

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related