-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathinsertsql.py
More file actions
151 lines (120 loc) · 4.7 KB
/
Copy pathinsertsql.py
File metadata and controls
151 lines (120 loc) · 4.7 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
# coding = utf-8
import mysql.connector
def login_sql():
conn = mysql.connector.connect(user='root', password='admin', database='test')
return conn
def ip2int(ip):
try:
# % X表示输出16进制形式，使用A～F的大写字符，比如15就输出F
# % 02 X表示输出的16进制使用两个位置，如果只有一位的前面添0，比如15就输出0F
hexn = ''.join(["%02X" % int(i) for i in ip.split('.')])
except BaseException:
hexn = ''.join(["%02X" % int(i) for i in '0.0.0.0'.split('.')])
return int(hexn, 16)
#轮子
# import struct,socket
# return struct.unpack("!I",socket.inet_aton(ip))[0]
def int2ip(n):
d = 256 * 256 * 256
q = []
while d > 0:
# 把除数和余数运算结果结合起来，返回一个包含商和余数的元组
m, n = divmod(n, d)
q.append(str(m))
d = d / 256
return '.'.join(q)
# 轮子
# import socket, struct
# return socket.inet_ntoa(struct.pack("!I", n))
def insert_row():
conn = mysql.connector.connect(user='root', password='admin', database='test')
# 指定编码格式
with open('./ipdata.csv', 'r', encoding='utf-8') as f:
lines = f.readlines()
nl_p_list = []
for l in lines:
# 指定切割次数，并将数据中的空格删除
ls = l.strip().split(',', 4)
# print(l)
# print(*ls)
# assert len(ls) == 5
c1, c2, c3, c4, c5 = ls[0], ip2int(ls[1]), ip2int(ls[2]), ls[3], ls[4]
nl = [c1, c2, c3, c4, c5]
nl_p_list.append(nl)
# print(nl)
# 连接数据数据库游标
cursor = conn.cursor()
# ret = cursor.executemany('insert into ipdata (id, startip, endip, country, carrier) values (%s, %s, %s, %s, %s)',
# nl_p_list)
for i in range(int(len(nl_p_list) / 1000 + 1)):
tmp_nl_p_list = nl_p_list[i * 1000: (i + 1) * 1000]
# print(tmp_nl_p_list[i][0] + ': ' + tmp_nl_p_list[i][4])
# 批量插入表中
ret = cursor.executemany(
'insert into ipdata (id, startip, endip, country, carrier) values (%s, %s, %s, %s, %s)',
tmp_nl_p_list)
# 提交事务;关闭数据库连接
conn.commit()
conn.close()
if __name__ == '__main__':
# print(ip2int('127.0.0.1'))
# print("%02X" % int(126))
# print(int('0', 16))
# print(int2ip(ip2int('127.0.0.1')))
with open('./ipdata.csv', 'r', encoding='utf-8') as f:
lines = f.readlines()
nl_p_list = []
for l in lines:
ls = l.strip().split(',', 4)
c1, c2, c3, c4, c5 = ls[0], ip2int(ls[1]), ip2int(ls[2]), ls[3], ls[4]
nl = [c1, c2, c3, c4, c5]
nl_p_list.append(nl)
import random
import time
ip_list = list(map(lambda x: x[1], random.sample(nl_p_list, 100)))
conn = login_sql()
#避免 mysql.connector InternalError: Unread result found 错误， 添加参数
cursor = conn.cursor(buffered=True)
# cursor.execute('SELECT * FROM ipdata WHERE 1780997668 BETWEEN startip AND endip')
ret_list = []
sql_list = []
sql_list2 = []
sql_str = 'SELECT {0}.* FROM (SELECT * FROM ipdata WHERE %s >= startip ORDER BY startip DESC LIMIT 1) {0}'
sql_str2 = 'SELECT {0}.* FROM (SELECT * FROM ipdata WHERE %s BETWEEN startip AND endip) {0}'
for i in range(len(ip_list)):
sql_list.append(sql_str.format('t' + str(i)) % ip_list[i])
sql_list2.append(sql_str2.format('t' + str(i)) % ip_list[i])
# for i in range(len(sql_list)):
# print(sql_list[i])
# 拼接 SQL 语句
sql = ' union all '.join(sql_list)
sql2 = ' union all '.join(sql_list2)
# zip() 函数接受任意多个（包括0个和1个）序列作为参数，返回一个tuple列表
# dict() 函数是从可迭代对象来创建新字典。比如一个元组组成的列表
# print(sql)
t0 = time.time()
# dict(zip(ip_list, cursor.execute(sql)))
cursor.execute(sql)
t1 = time.time()
# for i in ip_list:
# cursor.execute('SELECT * FROM ipdata WHERE %s >= startip ORDER BY startip DESC LIMIT 1' % i)
# result = cursor.fetchall()
# startip, endip = result[0][1], result[0][2]
# if startip <= i <= endip:
# ret_list.append((i, result[0][3]))
# else:
# ret_list.append((i, u'unknown'))
# print(result[0][1]+ ' str ' + result[0][2])
t2 = time.time()
# for i in ip_list:
# cursor.execute('SELECT * FROM ipdata WHERE %s BETWEEN startip AND endip' % i)
# result = cursor.fetchall()
t3 = time.time()
# cursor.execute(sql2)
t4 = time.time()
print(t1 - t0)
print(t2 - t1)
print(t4 - t3)
print(t3 - t2)
cursor.close()
conn.close()