使用python将mysql数据导入excel-select into outfile

来源：互联网发布：越南语离线翻译软件编辑：程序博客网时间：2024/05/29 06:33

-- 本地路径一定要加引号 'D:\\luoluowork\\baidu20160901.csv'

-- mysql一般都是utf8编码，而excel都是gb2312编码，所以要转换

-- 我试了.xls和.xlsx都失败了，只有csv可以

select CONVERT(`年份` using gb2312)
from `汇总`
where `年份`=2016 and `月份`=9
into OUTFILE 'D:\\luoluowork\\baidu20160901.csv'

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'

下面粘贴一个python使用select into outfile的代码

需要注意的几个地方

1.csv使用gbk方式，mysql一般使用utf8，所以一定要转换编码

2.字段名，表明要有反引号

3.拼接sql1的过程中使用一个引号报错，所以使用引号的地方全部用了三个引号

4.FIELDS TERMINATED BY ','

5.sys.setdefaultencoding('utf-8')

6.我试了.xls和.xlsx都失败了，只有csv可以

# coding=utf-8import sysimport osimport MySQLdbimport csvreload(sys)sys.setdefaultencoding('utf-8')list1 = ['2014']#list2 = ['3']list2 = ['1','2','3','4','5','6','7','8','9','10','11','12']# list2=['01','02','03','04','05','06','07','08','09','10','11','12']exception=[]conn=MySQLdb.connect(host='localhost',user='root',passwd='123456')conn.select_db('ctr')conn.set_character_set('utf8')cur = conn.cursor()for nian in list1:    for yue in list2:        if int(yue) < 10:            excel_file = "C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/baidu_CRT/baidu"+nian+"0"+yue+".csv"        else:            excel_file = "C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/baidu_CRT/baidu"+nian+yue+".csv"        sql1 = """select CONVERT(`年份` using  gb2312) ,CONVERT(`月份` using  gb2312),CONVERT(`媒介类型` using  gb2312),CONVERT(`区域` using  gb2312),CONVERT(`省份` using  gb2312),CONVERT(`产品大类` using  gb2312),CONVERT(`产品中类` using  gb2312),CONVERT(`产品小类` using  gb2312),CONVERT(`品牌` using  gb2312),CONVERT(`产品` using  gb2312),CONVERT(`总次数` using  gb2312),CONVERT(`总费用` using  gb2312),CONVERT(`总长度面积` using  gb2312)from `汇总` where `年份` = """+ nian + """ and `月份` =""" + yue +""" INTO OUTFILE '"""+ excel_file +"'" + """ FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'"""        print sql1        cur.execute(sql1)cur.close()conn.close()

阅读全文

0 0