python list to dataframe with column headers and removing data types

Question

Hoping someone can help me out here to write an efficient code. I am fairly new to python and not the most efficient.

I have a ODBC connection to a SQL database and here is the code for that:

import pyodbc
import pandas as pd
import csv

cnxn = pyodbc.connect("DSN=acc_DB")
cursor = cnxn.cursor()
cursor.execute("select top 10 * from Table_XX")
rows = cursor.fetchall()

Now from here what i get is a this:

Then i put that in a dataframe and out to a csv using this code

DF = pd.DataFrame(rows)
DF.to_csv('out.csv',sep=',')

The problem is:

1. DF is not recognizing the column names, there is just a value of 0 for columns

   2. The column type is being transferred to the decimal('xxx') is also being captured in the df

How do i put this in the Dataframe in a table format with the column headers and no column types?? Like i would in a SQL query i execute on MS SQL management studio?

Answer 1

If you want the DataFrame to include the column names you can do

crsr = cnxn.cursor()
rows = crsr.execute('select top 10 * from Table_XX').fetchall()
df = pd.DataFrame.from_records(rows, columns=[x[0] for x in crsr.description])

Then to dump the results to CSV with column headings you can do

df.to_csv(r'C:\Users\Gord\Desktop\out.csv', index=False)