python3 rowinput_python3 文件读入写出小结

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)