Exporting data from SQL server to Excel using python pyodbc and pandas Data frame

Question

I am new to Stack overflow as well as Python. Below is my program and I am trying to learn how to export data from SQL server to excel using python program. I know ways to directly exporting from SQL server, this is I am just trying to learn. Program below does not give any errors but it does just gives column headers but not actual data.

import pyodbc
import pandas as pd

cnxn = pyodbc.connect("Driver={SQL Server};SERVER=hostname;Database=Practice;UID=XXX;PWD=XXX")

cursor = cnxn.cursor()
cursor.execute('SELECT * FROM insurance')

columns = [desc[0] for desc in cursor.description]
data = cursor.fetchall()
df = pd.DataFrame([tuple(t) for t in cursor.fetchall()], columns=columns)

writer = pd.ExcelWriter('foo.xlsx')
df.to_excel(writer, sheet_name='bar')
writer.save()

dude, maybe is on purpose but just in case, you have username and password there!!! — lorenzori
– lorenzori, Commented Jan 15, 2018 at 16:37

lorenzori · Accepted Answer · 2020-02-26 16:44:59Z

10

easier than that!

import pyodbc
import pandas as pd

cnxn = pyodbc.connect("Driver={SQL Server};SERVER=xxx;Database=xxx;UID=xxx;PWD=xxx")

pd.read_sql('SELECT * FROM insurance',cnxn).to_excel('foo.xlsx')

edited Feb 26, 2020 at 16:44

answered Jan 15, 2018 at 16:37

lorenzori

7477 silver badges24 bronze badges

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

3 Comments

Pinakpani Shah Over a year ago

Awesome it worked pretty well. Can you please help me understand why did not work with my code?

Pinakpani Shah Over a year ago

Error i was getting was ValueError: Shape of passed values is (1, 64), indices imply (153, 64)

Lornioiz Over a year ago

Ths answer solved me from an headache. There is just one typo: data = pandas.read_sql('SELECT * FROM insurance',cnxn) Should read data = pd.read_sql('SELECT * FROM insurance',cnxn)

Collectives™ on Stack Overflow

Exporting data from SQL server to Excel using python pyodbc and pandas Data frame

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related