Export Postgresql Table to excel with header in Python

Question

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

Answer 1

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

Pass the 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)