Python读Mysql数据并写入csv文件

代码见下:

# -*- coding: UTF-8 -*-
import pymysql.cursors
import json
import csv

db = pymysql.connect("127.0.0.1", "root", "123456", "device",charset='utf8');
cursor = db.cursor()  #mysql cursor handle
sql = "select * from tempdata_20170417"
cursor.execute(sql)
csvfile = file('temp.csv', 'wb')
writer = csv.writer(csvfile)
writer.writerow(['','channel1','channel2','channel3','channel4','channel5','channel6','channel7','channel8','channel9','channel10','channel11','channel12','channel13','channel14','channel15','channel16'])
for r in cursor:
    json_data =  json.loads(r[1]) #["channel1"]
    try:
        writer.writerow([r[0],
                     json_data["channel0"][0],
                     json_data["channel0"][1],
                     json_data["channel0"][2],
                     json_data["channel0"][3],
                     json_data["channel0"][4],
                     json_data["channel0"][5],
                     json_data["channel0"][6],
                     json_data["channel0"][7],
                     json_data["channel1"][0],
                     json_data["channel1"][1],
                     json_data["channel1"][2],
                     json_data["channel1"][3],
                     json_data["channel1"][4],
                     json_data["channel1"][5],
                     json_data["channel1"][6],
                     json_data["channel1"][7]
                     ])
    except:
        continue
csvfile.close()
db.close()

 

分享到:更多 ()