exportation data from mysql database using csv

Question

i neeed a python script to generate a csv file from my database XXXX. i wrote thise script but i have something wrong :

import mysql.connector
import csv
filename=open('test.csv','wb')
c=csv.writer(filename)

cnx = mysql.connector.connect(user='XXXXXXX', password='XXXXX',
                              host='localhost',
                              database='XXXXX')


cursor = cnx.cursor()

query = ("SELECT `Id_Vendeur`, `Nom`, `Prenom`, `email`, `Num_magasin`, `Nom_de_magasin`, `Identifiant_Filiale`, `Groupe_DV`, `drt_Cartes`.`gain` as 'gain',  `Date_Distribution`, `Status_Grattage`, `Date_Grattage` FROM `drt_Cartes_Distribuer`,`drt_Agent`,`drt_Magasin`,`drt_Cartes` where `drt_Cartes_Distribuer`.`Id_Vendeur` = `drt_Agent`.`id_agent` AND `Num_magasin` = `drt_Magasin`.`Numero_de_magasin` AND `drt_Cartes_Distribuer`.`Id_Carte` = `drt_Cartes`.`num_carte`")

cursor.execute(query)

for Id_Vendeur, Nom, Prenom, email, Num_magasin, Nom_de_magasin, Identifiant_Filiale, Groupe_DV, gain, Date_Distribution, Status_Grattage, Date_Grattage in cursor:
    c.writerow([Id_Vendeur, Nom, Prenom, email, Num_magasin, Nom_de_magasin, Identifiant_Filiale, Groupe_DV, gain, Date_Distribution, Status_Grattage, Date_Grattage] )

cursor.close()
filename.close()
cnx.close()

when i executing the command on phpmyadmin its look working very well but from my shell i got thise message :

# python test.py
Traceback (most recent call last):
  File "test.py", line 18, in <module>
    c.writerow([Id_Vendeur, Nom, Prenom, email, Num_magasin, Nom_de_magasin, Identifiant_Filiale, Groupe_DV, gain, Date_Distribution, Status_Grattage, Date_Grattage] )
UnicodeEncodeError: 'ascii' codec can't encode character u'\xeb' in position 5: ordinal not in range(128)

`\xeb` is ë, non-ASCII. I suppose you get the error when piping. In that case see https://stackoverflow.com/questions/492483/setting-the-correct-encoding-when-piping-stdout-in-python. — 4ae1e1, Dec 04 '15 at 09:51
If you can access to the mysql server (ssh?), you can dump you DB in CSV (http://stackoverflow.com/a/12041241/1162467). Also, if you duplicate your tables and sets the engine to CSV, you have all your db tables just in CSV format and you can use those tables directly! All you need is to truncate each table and reinsert the data every time you need the updated CSV. — Andrea Rastelli, Dec 04 '15 at 10:27

score 0 · Answer 1 · answered Dec 04 '15 at 09:53

It looks you are using csv for Python 2.7. Quoting docs:

Note This version of the csv module doesn’t support Unicode input. Also, there are currently some issues regarding ASCII NUL characters. Accordingly, all input should be UTF-8 or printable ASCII to be safe; see the examples in section Examples.

Options, choice one of them:

Follow doc link, go to samples section, and modify your code accordantly.
Use a csv packet with unicode supprt like https://pypi.python.org/pypi/unicodecsv

score 0 · Answer 2 · edited May 23 '17 at 12:07

0

Your data from the database are not only ascii characteres. I suggest you use the 'unicodecvs' python module as suggested in the answer to this question: How to write UTF-8 in a CSV file

edited May 23 '17 at 12:07

Community

1
1

answered Dec 04 '15 at 10:01

jhilmer

286
1
8

exportation data from mysql database using csv

2 Answers2