Python3 各种文件格式的转换:
1 import json 2 import pymysql 3 from datetime import datetime 4 from xlrd import xldate_as_tuple 5 import xlrd 6 import xlwt 7 8 9 # 读CSV 10 def readCSV(): 11 ls = [] 12 with open('../ls/textfile/price2016.csv', 'r', encoding='utf-8') as in_csv: 13 for line in in_csv: 14 line = line.replace('\n', '') 15 ls.append(line.split(',')) 16 return ls 17 18 19 # 写csv 20 def writeCSV(ls): 21 out_csv = open('../ls/textfile/out_csv', 'w', encoding='utf-8') 22 for i in range(len(ls)): 23 out_csv.write(','.join(ls[i]) + '\n') 24 print("写入成功!") 25 26 27 # 读json 28 29 def readJson(): 30 in_json = open('../ls/textfile/price2016.json', 'r') 31 ls = json.load(in_json) 32 # print(ls) 33 data = list(ls[0].keys()) 34 for item in ls: 35 data.append(list(item.values())) 36 in_json.close() 37 for item in data: 38 print(' '.join(item) + '\n') 39 print('读入json完成') 40 41 42 # 写json 43 44 def writeJson(ls): 45 out_json = open('../ls/textfile/out_price2016.json', 'w') 46 for i in range(1, len(ls)): 47 ls[i] = dict(zip(ls[0], ls[i])) 48 json.dump(ls[1:], out_json, sort_keys=True, indent=4, ensure_ascii=False) 49 out_json.close() 50 51 52 # 读TEXT 53 def readText(): 54 in_text = open('../ls/textfile/test_text.txt', 'r') 55 for line in in_text.readlines(): 56 print(line) 57 print('input OK!') 58 59 60 # 写TEXT 61 def writeText(ls): 62 out_text = open('../ls/textfile/out_text.txt', 'w') 63 for i in range(len(ls)): 64 out_text.write(' '.join(ls[i]) + '\n') 65 66 67 # 读sql文件 68 def readsql(): 69 cursor, db = login() 70 sql = "select * from course" 71 doit(sql, cursor, db) 72 data = cursor.fetchall() 73 for d in data: 74 print('编号:', d[0], end='\t') 75 print('名称:', d[1], end='\t') 76 print('地点:', d[2], end='\t') 77 print('时长:', d[3], end='\t') 78 print('考试时间:', d[4], end='\t') 79 print("\n") 80 db.close() 81 82 83 # 写sql文件 84 def writesql(file): 85 cursor, db = login() 86 fw = open('../ls/textfile/' + file, 'r', encoding="utf-8") 87 for line in fw: 88 line = line.replace(' ', ',') 89 ans = line.split(',') 90 print(ans[0], ans[1], ans[2], ans[3], ans[4]) 91 sql = "insert into course(`number`,`name`,place,duration,examTime) values ('{}','{}','{}','{}','{}')".format(ans[0], 92 ans[1], 93 ans[2], 94 ans[3], 95 ans[4]) 96 doit(sql, cursor, db) 97 98 99 100 # 读excel文件 101 def readexcel(): 102 cur, db = login() 103 data = xlrd.open_workbook("../ls/textfile/test.xlsx", "r") 104 sheet = data.sheet_by_index(0) 105 for i in range(1, int(sheet.nrows)): 106 ans = sheet.row_values(i) 107 print(ans[0], ans[1], ans[2], ans[3], ans[4]) 108 sql = "insert into course(`number`,`name`,place,duration,examTime) values ('{}','{}','{}','{}','{}')".format(ans[0], 109 ans[1], 110 ans[2], 111 ans[3], 112 ans[4]) 113 doit(sql, cur, db) 114 115 116 def writeexcel(ls): 117 cur, db = login() 118 sql = 'select * from course' 119 cur.execute(sql) 120 fileds = [filed[0] for filed in cur.description] #列表生成式,所有字段 121 all_data = cur.fetchall() #所有数据 122 book = xlwt.Workbook() 123 sheet = book.add_sheet('sheet1') 124 col = 0 125 for filed in fileds: 126 sheet.write(0, col, filed) 127 col += 1 128 129 row = 1 130 131 for data in all_data: 132 for col, field in enumerate(data): # 控制列数 133 sheet.write(row, col, field) 134 row += 1 # 每次写完一行,行数加1 135 book.save('../ls/textfile/%s.xls' %ls) 136 # pass 137 138 def login(): 139 db = pymysql.Connect(host="localhost", user="root", password="XXXXX", db="pythontest") 140 cur = db.cursor() 141 cur.execute("use students") 142 return cur, db 143 144 145 def doit(sql, cur, db): 146 try: 147 # 执行sql语句 148 cur.execute(sql) 149 # 提交到数据库执行 150 db.commit() 151 except: 152 # 如果发生错误则回滚 153 db.rollback() 154 155 if __name__ == '__main__': 156 # ls = readCSV() 157 # writeCSV(ls) 158 # readJson() 159 # writeJson(ls) 160 # readText() 161 # writeText(ls) 162 # readsql() 163 # file = "sql.txt" 164 # writesql(file) 165 # readexcel() 166 ls = "course" 167 writeexcel(ls)