Python3 各种文件格式的转换:
1 importjson2 importpymysql3 from datetime importdatetime4 from xlrd importxldate_as_tuple5 importxlrd6 importxlwt7
8
9 #读CSV
10 defreadCSV():11 ls =[]12 with open('../ls/textfile/price2016.csv', 'r', encoding='utf-8') as in_csv:13 for line inin_csv:14 line = line.replace('\n', '')15 ls.append(line.split(','))16 returnls17
18
19 #写csv
20 defwriteCSV(ls):21 out_csv = open('../ls/textfile/out_csv', 'w', encoding='utf-8')22 for i inrange(len(ls)):23 out_csv.write(','.join(ls[i]) + '\n')24 print("写入成功!")25
26
27 #读json
28
29 defreadJson():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 inls:35 data.append(list(item.values()))36 in_json.close()37 for item indata:38 print(' '.join(item) + '\n')39 print('读入json完成')40
41
42 #写json
43
44 defwriteJson(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 defreadText():54 in_text = open('../ls/textfile/test_text.txt', 'r')55 for line inin_text.readlines():56 print(line)57 print('input OK!')58
59
60 #写TEXT
61 defwriteText(ls):62 out_text = open('../ls/textfile/out_text.txt', 'w')63 for i inrange(len(ls)):64 out_text.write(' '.join(ls[i]) + '\n')65
66
67 #读sql文件
68 defreadsql():69 cursor, db =login()70 sql = "select * from course"
71 doit(sql, cursor, db)72 data =cursor.fetchall()73 for d indata: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 defwritesql(file):85 cursor, db =login()86 fw = open('../ls/textfile/' + file, 'r', encoding="utf-8")87 for line infw: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 defreadexcel():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 defwriteexcel(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 =0125 for filed infileds:126 sheet.write(0, col, filed)127 col += 1
128
129 row = 1
130
131 for data inall_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 deflogin():139 db = pymysql.Connect(host="localhost", user="root", password="XXXXX", db="pythontest")140 cur =db.cursor()141 cur.execute("use students")142 returncur, db143
144
145 defdoit(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)