Export Postgresql Table to excel with header in Python

May 26, 2022

My code works but it doesn’t bring the header with the names, it only brings the numbers 0 1 … 10 , what can I do ?

Utils_db.py

def consulta_sql(sql):
try:
   connection = psycopg2.connect(user="postgres",
                                password="postgres",
                                host="localhost",
                                port="5432",
                                database="tb_cliente")
   cursor = connection.cursor()
except (Exception, psycopg2.Error) as error:
       
try:
   cursor.execute(sql)
   connection.commit()
   
except (Exception, psycopg2.Error) as error:
   
finally:
   if connection:
      result = cursor.fetchall()
      cursor.close()
      connection.close()
      return result

main.py

Excel = Utils_db.consulta_sql("Select * from corantes")

df = pd.DataFrame(Excel)
df.to_excel('C:/Users/Dnaxis2/Downloads/Corantes.xlsx', index=False)

generated excel

0 1 2 3 4 5 6 7 8 9 10

1 FF BK 20 200 10 200 200 200 200 30

2 PP BK 100 500 150 0 0 0 35 30

correct excel (would have to come like this)

Corant Pags Table Polo Jetta Fox Ps Ilu Kik Qly

1 FF BK 20 200 10 200 200 200 200 30

2 PP BK 100 500 150 0 0 0 35 30

>Solution :

You can ask psycopg2 to return the result as a dictionary, using the column names as keys for the dictionary.

Pass the parameter value RealDictCursor (import it from psycopg2.extras) to cursor_factory parameter in the connect method.

The line will be

from psycopg2.extras import RealDictCursor
connection = psycopg2.connect(user="postgres",
                                password="postgres",
                                host="localhost",
                                port="5432",
                                database="tb_cliente",
                                cursor_factory=RealDictCursor)