Python 将文本中的1700万条数据批量处理后插入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 11 while start_row < len(total_lines): 12 print("line: ", start_row, " line2: ", end_row) 13 lines = total_lines[start_row: end_row] 14 str_tuple = tuple() 15 my_list = [] 16 for item in lines: 17 # 获取到索引 18 str_index = item.find("[") 19 str_01 = item[:str_index] 20 # print("~~~~~~~~~>>>当前插入条数: ", str_01) 21 # 获取数据列表(6) 22 str_end = item.find("]") 23 str_02 = item[str_index:str_end] 24 str_03 = str_02[1:] 25 # print("~~~~~~~~~>>>真实数据源: ", str_03) 26 27 # 获取最后一位数据 28 str_05 = item.find("]") 29 str_06 = item[str_05:] 30 str_07 = str_06[1:] 31 str_last = str_07.strip() 32 # print("~~~~~~~~~>>>获取最后一位数据: ", str_last) 33 34 # 对获取的真实数据进行分割排序 35 real = str_03.split(",") 36 real.sort(key=int) 37 # print("~~~~~~~~~>>>分割排序后的列表: ", real) 38 39 insert_value = [real[0], real[1], real[2], real[3], real[4], real[5], str_last, 0, ""] 40 41 str_tuple = tuple(insert_value) 42 my_list.append(str_tuple) 43 44 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(?,?,?,?,?,?,?,?,?)" 45 cur.executemany(insert_query, my_list) 46 conn.commit() 47 start_row += 10000 48 end_row += 10000 49 50 conn.close() 51 print("~~~~~~~~~~~~~~~>>>>执行完成<<<<~~~~~~~~~~~~~~~")
耗时：1分43秒
