How to write a table output with column names in SQL in python?

Question

I use pandas library to write data which I retrieve from sql table. It is working but I don't see a column names there. Also each row is appended in Excel like

('aa','aa','01/10/2019','zzz')
('bb','cc','03/10/2019','yy')
..

I want my Excel sheet with column names and without colon.(') A proper excel sheet.

eg:

Name  Address   Date       product
 aa    aa      01/10/2019   zzz

My code is as follows;

cursor.execute(sql, values)
records = cursor.fetchall()

data = []
for row in records:
    data.append(row)

df = pd.DataFrame(data)

cursor.close()
cnxn.close()
writer = pd.ExcelWriter('output.xlsx')
df.to_excel(writer, index=False, sheet_name='Invoice') 
writer.save()

How can I retrieve with column names and write that in excel using pandas?

I use pyodbc to connect to the SQL Server database.

Eden Trainor · Accepted Answer · 2019-12-03 13:41:29Z

1

I'd recommend using pandas' built in read_sql function to read from your sql database. This should read your header in properly and the datatypes too!

You'll need to define a sqlAlchemy connection to pass to pd.read_sql but that is simple for mssql:

from sqlalchemy import create_engine
engine = create_engine('mssql://timmy:tiger@localhost:5432/mydatabase')
connection = engine.connect()
query = "SELECT * FROM mytable"

df = pd.read_sql(query, engine)

EDIT: As per Ratha's comment, it should be noted that the sqlAlchemy connection isn't essential for read_sql to work, and indeed, from the docs a connection is defined as:

con : SQLAlchemy connectable (engine/connection) or database string URI or DBAPI2 connection (fallback mode)

Using SQLAlchemy makes it possible to use any DB supported by that library. If a DBAPI2 object, only sqlite3 is supported.

edited Dec 3, 2019 at 13:41

answered Dec 2, 2019 at 22:29

Eden Trainor

6016 silver badges18 bronze badges

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

2 Comments

Ratha Over a year ago

It is not necessary to have sqlalchemy connection string. I used pyodbc and working fine .Thanks

Ratha Over a year ago

do you know how can I achieve this with read_sql?stackoverflow.com/questions/59149457/…

Collectives™ on Stack Overflow

How to write a table output with column names in SQL in python?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related