Dec-06-2018, 12:15 AM
Hello,
First of all I am a total newbie as this are my first python coding...
I am trying to write a python script to extract data from mysql tatabase to a csv.
The data I want are in a single table except for user first and last name. Therefore in the csv, I would like to change the user id by the first and last name. I can do this with the following code but I cannot write the final csv properly. Could any one help ? I would also apreciate if anyone has a better way of coding this
thank you
First of all I am a total newbie as this are my first python coding...
I am trying to write a python script to extract data from mysql tatabase to a csv.
The data I want are in a single table except for user first and last name. Therefore in the csv, I would like to change the user id by the first and last name. I can do this with the following code but I cannot write the final csv properly. Could any one help ? I would also apreciate if anyone has a better way of coding this
thank you
#!/usr/bin/python import csv import pymysql import shutil mydb = pymysql.connect(host='localhost', user='maths', passwd='maths-maths', db='maths') cursor = mydb.cursor() ## query queryall = ("SELECT * FROM wp_h5p_results ") cursor.execute(queryall) ### write to csv file csv_temp = csv.writer(open("temp.csv", "wt")) # create csv csv_temp.writerow(["id","activité","userid","score","score_max","commencé","terminé","durée"]) # write headers csv_temp.writerows(cursor) # write records del csv_temp # close csv file with open('temp.csv') as csvinput: with open('score.csv', 'w') as csvoutput: writer = csv.writer(csvoutput) next(csvinput) for row in csv.reader(csvinput): queryfirstname = ("select meta_value from wp_usermeta where meta_key='first_name' and user_id = %s ;"%row[2]) cursor.execute(queryfirstname) name = cursor.fetchall() row.append(name[0][0]) querylastname = ("select meta_value from wp_usermeta where meta_key='last_name' and user_id = %s ;"%row[2]) cursor.execute(querylastname) name = cursor.fetchall() row.append(name[0][0]) print(row) writer.writerows(row) # write records del writer # close csv file cursor.close() mydb.close()