mysql csv

[tsinapah]

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

#!/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()

[Larz60+]

you need to also close csv_temp otherwise your buffers may not be written.

[tsinapah]

Hello, Thank you for your advice, I have closed all the files but still the score.csv is incorrect.

Is there a python tool to extract data from mysql to replace or add columns in csv files?

Sincerely,

[Larz60+]

I don't use mysql, but every DBMS has an admin program, including MySQL, you can download here: https://dev.mysql.com/downloads/workbench/

[Gribouillis]

writer.writerows() takes a sequence of rows as its parameter. Use writer.writerow(row) instead, and indent it more to put it in the 'for row ...' block.

[tsinapah]

Great thanks it now works !!!

#!/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.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)
        writer.writerow(["id","activité","userid","score","score_max","commencé","terminé","durée","prenom","nom"]) # write headers
        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.writerow(row) # write records

del csvinput # close csv file
del csvoutput # close csv file
del writer # close csv file
cursor.close()
mydb.close()