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("~~~~~~~~~~~~~~~>>>>执行完成<<<<~~~~~~~~~~~~~~~")