how to export sql database to excel using python

Question

How to export sql database to excel including column name also (using python)? Here I tried it:

import pyodbc
import pandas as pd

cnxn = pyodbc.connect("myconnection")
cursor = cnxn.cursor()
a=input("enter your source code:")
script = """
xlsexcel '"+a+"' """
cursor.execute(script)
b=list()
for row in cursor:
   b.append(row)
   print(b)
columns = [desc[0] for desc in cursor.description]
data = cursor.fetchall()
df = pd.DataFrame(list(data), columns=columns)
df = pd.read_sql_query(script, cnxn)
writer = pd.ExcelWriter('foo.xlsx')
df.to_excel(writer, sheet_name='bar')
writer.save()

where did it not work?

PV8
– PV8

2019-07-02 05:43:54 +00:00
Commented Jul 2, 2019 at 5:43 — PV8
– PV8, Commented Jul 2, 2019 at 5:43

Prayson W. Daniel · Accepted Answer · 2019-07-02 11:24:05Z

1

Pandas reads data directly from your database connection:

import pyodbc
import pandas as pd


sql_query = input("enter sql query:")

with pyodbc.connect("myconnection") as c:

    df = pd.read_sql(sql_query, c)

    with pd.ExcelWriter('foo.xlsx') as writer:
        df.to_excel(writer, sheet_name='bar')

This ought work

edited Jul 2, 2019 at 11:24

answered Jul 2, 2019 at 5:55

Prayson W. Daniel

15.8k6 gold badges57 silver badges62 bronze badges

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

Collectives™ on Stack Overflow

how to export sql database to excel using python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related