pythonsqlpandaspymssql

Use SQL query result column names as pandas dataframe column names

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?

Solution

  • 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)