mport csv
import MySQLdb as mdb
import csv, codecs, cStringIO
db_user = 'root'
db_pass = 'FGifPYRMcuQYF3dh'
db_schema = 'site1.local'
dbs = {
'db1': 'localhost'
}
class UnicodeWriter:
"""
A CSV writer which will write rows to CSV file "f",
which is encoded in the given encoding.
"""
def __init__(self, f, dialect=csv.excel, encoding="utf-8", **kwds):
# Redirect output to a queue
self.queue = cStringIO.StringIO()
self.writer = csv.writer(self.queue, dialect=dialect, **kwds)
self.stream = f
self.encoder = codecs.getincrementalencoder(encoding)()
def writerow(self, row):
self.writer.writerow([s.encode("utf-8") for s in row])
# Fetch UTF-8 output from the queue ...
data = self.queue.getvalue()
data = data.decode("utf-8")
# ... and reencode it into the target encoding
data = self.encoder.encode(data)
# write to the target stream
self.stream.write(data)
# empty queue
self.queue.truncate(0)
def writerows(self, rows):
for row in rows:
self.writerow(row)
con = None
for dbnames in dbs:
con = mdb.connect(dbs[dbnames], db_user, db_pass, db_schema)
cur = con.cursor()
db_query = "select * from tabCustomer;"
cur.execute(db_query)
rows = cur.fetchall()
with open("/home/utf.csv", 'wb') as f:
writer = UnicodeWriter(f)
rows = [[unicode(x) for x in row] for row in rows]
writer.writerows(rows)
If Data don’t have UTF-8 row. It will run. But If data have Utf-8 it will have Error.
Traceback (most recent call last):
File "/root/Desktop/utf-8", line 52, in <module>
rows = [[unicode(x) for x in row] for row in rows]
UnicodeDecodeError: 'ascii' codec can't decode byte 0xe2 in position 1: ordinal not in range(128)
[Finished in 0.3s with exit code 1]
[shell_cmd: python -u "/root/Desktop/utf-8"]
[dir: /root/Desktop]
[path: /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games]
data in mysql : tabCustomer:
NULL NULL NULL CUST- NULL Company Lâm Chấn Huy NULL NULL NULL Vietnam NULL NULL Auraca JSC NULL Government NULL 0912321 hjuybk[quote=“pdvyas, post:4, topic:4078”]
rows = [[x.decode(‘utf-8’) for x in row] for row in rows]
[/quote]
Thank pydvyas very much.
but. It’s Error
Traceback (most recent call last):
File “/home/frappe/frappe-bench/apps/frappe/frappe/core/page/data_import_tool/test.py”, line 51, in
rows = [[x.decode(‘utf-8’) for x in row] for row in rows]
AttributeError: ‘NoneType’ object has no attribute ‘decode’
[Finished in 0.1s with exit code 1]
[shell_cmd: python -u “/home/frappe/frappe-bench/apps/frappe/frappe/core/page/data_import_tool/test.py”]
[dir: /home/frappe/frappe-bench/apps/frappe/frappe/core/page/data_import_tool]
[path: /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games]
Thank. I have’nt found solution yet. I use to frappe.db.sql() method of Database class.
from future import unicode_literals
import frappe, json, os
from frappe import _
from frappe.database import Database
import MySQLdb as mdb
import csv, codecs, cStringIO
@frappe.whitelist(allow_guest=True)
def export_csv_accounts():
rows = frappe.db.sql(“”“SELECT tabCustomer.name as customer_id, tabCustomer.customer_name, tabCustomer.customer_group, tabAddress.address_line1, tabAddress.address_line2, tabAddress.city, tabAddress.state, tabAddress.pincode, tabAddress.country, tabAddress.is_primary_address, tabContact.first_name, tabContact.last_name, tabContact.phone, tabContact.mobile_no, tabContact.email_id, tabContact.is_primary_contact
FROM tabCustomer
left join tabAddress on ( tabAddress.customer=tabCustomer.name
)
left join tabContact on ( tabContact.customer=tabCustomer.name
)
WHERE tabCustomer.docstatus<2
ORDER BY tabCustomer.name asc”“”,as_utf8=1)
out_file = csv.writer(open(“/home/customers.csv”, “wb”))
columns = [c[0] for c in frappe.db.get_description()]
out_file.writerow(columns)
out_file.writerows(rows)