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)
