Python 读取txt文本数据批量插入sqlite数据库中
1 import sqlite3 2 3 start_row = 0 4 end_row = 10000 5 conn = sqlite3.connect('ball.db') 6 cur = conn.cursor() 7 with open('demo.txt', 'r', encoding='utf-8') as f: 8 total_lines = f.readlines() 9 print(len(total_lines)) 10 print("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~") 11 12 while start_row < len(total_lines): 13 print("line: ", start_row, " line2: ", end_row) 14 lines = total_lines[start_row: end_row] 15 str_tuple = tuple() 16 my_list = [] 17 for item in lines: 18 # 获取到索引 19 str_index = item.find("[") 20 str_01 = item[:str_index] 21 # print("~~~~~~~~~>>>当前插入条数: ", str_01) 22 # 获取数据列表(6) 23 str_end = item.find("]") 24 str_02 = item[str_index:str_end] 25 str_03 = str_02[1:] 26 # print("~~~~~~~~~>>>真实数据源: ", str_03) 27 28 # 获取最后一位数据 29 str_05 = item.find("]") 30 str_06 = item[str_05:] 31 str_07 = str_06[1:] 32 str_last = str_07.strip() 33 # print("~~~~~~~~~>>>获取最后一位数据: ", str_last) 34 35 # 对获取的真实数据进行分割排序 36 real = str_03.split(",") 37 real.sort(key=int) 38 # print("~~~~~~~~~>>>分割排序后的列表: ", real) 39 if int(real[0]) < 10: 40 str1 = str(int(real[0])).zfill(2) 41 else: 42 str1 = str(int(real[0])) 43 44 if int(real[1]) < 10: 45 str2 = str(int(real[1])).zfill(2) 46 else: 47 str2 = str(int(real[1])) 48 49 if int(real[2]) < 10: 50 str3 = str(int(real[2])).zfill(2) 51 else: 52 str3 = str(int(real[2])) 53 54 if int(real[3]) < 10: 55 str4 = str(int(real[3])).zfill(2) 56 else: 57 str4 = str(int(real[3])) 58 59 if int(real[4]) < 10: 60 str5 = str(int(real[4])).zfill(2) 61 else: 62 str5 = str(int(real[4])) 63 64 if int(real[5]) < 10: 65 str6 = str(int(real[5])).zfill(2) 66 else: 67 str6 = str(int(real[5])) 68 remark = str1 + str2 + str3 + str4 + str5 + str6 69 insert_value = [real[0], real[1], real[2], real[3], real[4], real[5], str_last, 0, remark] 70 71 str_tuple = tuple(insert_value) 72 my_list.append(str_tuple) 73 74 insert_query = "insert into double_ball(read_ball_1,read_ball_2,read_ball_3,read_ball_4,read_ball_5,read_ball_6,blue_ball,flag,remark) values(?,?,?,?,?,?,?,?,?)" 75 cur.executemany(insert_query, my_list) 76 conn.commit() 77 start_row += 10000 78 end_row += 10000 79 80 conn.close() 81 print("~~~~~~~~~~~~~~~>>>>执行完成<<<<~~~~~~~~~~~~~~~")