Python/Pandas How to get columns names AND values from a sql query

Question

I try to export the result of a query from mysql to a excel sheet. I use Msqlconnector, pandas librarys and i did that.

import mysql.connector
import pandas as pd

db = mysql.connector.connect(
  host="SQLServer",
  user="sqlusr",
  password="usrpasswd",
  database="sqldb"
)

cur = db.cursor()
cur.execute("SELECT * FROM direction_prix_proxi.matrice_sigma" \
                " LEFT JOIN direction_pri.trans_int_prix_gen using(SIGMA, UV)" \
                " LEFT JOIN direction_pri.trans_int_prix_module using(SIGMA, UV)" \
                " LEFT JOIN direction_pri.trans_int_vol1an_hors_module using(SIGMA, UV)" \
                " LEFT JOIN direction_pri.trans_int_vol1an_module using(SIGMA, UV);")

df = pd.DataFrame(cur.fetchall())
df.to_excel(file_path1, index = False, header=True)

It works but the columns names are not in the excel sheet, i have numbers instead.

I found that there is a function For get the columns names but i didnt succeed to implement it in the result.

print(cur.column_names)

Other solution i found :

result = cur.fetchall()
    num_fields = len(cur.description)
    field_names = [i[0] for i in cur.description]
    print(field_names)

    for value in result:
        print(value)

It displays the result in console, i want storage it in a data frame for export it on excel. How can i do that?

thanks

try using a cursor that returns dicts - i.e. cur = db.cursor(dictionary=True) - MySQLCursorDict — Asish M.
– Asish M., Commented Dec 29, 2021 at 16:20

GeorgeLPerkins · Accepted Answer · 2021-12-29 16:15:56Z

1

I'm not sure about using a cursor, but I use the following format when doing this and it works.

import mysql.connector
import pandas as pd

db_conn = mysql.connector.connect(
  host="SQLServer",
  user="sqlusr",
  password="usrpasswd",
  database="sqldb"
)

query = """SELECT * FROM direction_prix_proxi.matrice_sigma
            LEFT JOIN direction_pri.trans_int_prix_gen using(SIGMA, UV)
            LEFT JOIN direction_pri.trans_int_prix_module using(SIGMA, UV)
            LEFT JOIN direction_pri.trans_int_vol1an_hors_module using(SIGMA, UV)
            LEFT JOIN direction_pri.trans_int_vol1an_module using(SIGMA, UV);
        """

df = pd.read_sql(query, db_conn)
df.to_excel(file_path1, index = False, header=True)

answered Dec 29, 2021 at 16:15

GeorgeLPerkins

1,14611 silver badges24 bronze badges

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

Collectives™ on Stack Overflow

Python/Pandas How to get columns names AND values from a sql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related