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

 

posted @ 2024-07-19 13:21  宇少vlog  阅读(62)  评论(0编辑  收藏  举报