cat 1.txt
tomcat 192.1.1.121
redis 192.1.1.121
CREATE TABLE `a` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ip` varchar(50) NOT NULL DEFAULT '',
`apply` varchar(50) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8;
|
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
|
#!/usr/bin/env python# -*- coding: utf-8 -*-import sys
#import pymysql#pymysql.install_as_MySQLdb()import MySQLdb as mdb
con = mdb.connect('192.1.1.197', 'root', 'xxxxxx', 'db03')
def db_execute(sql):
cursor = con.cursor()
'''cursor.execute(sql)
con.commit()
cursor.close()'''
try:
cursor.execute(sql)
con.commit()
cursor.close()
except:
con.rollback()
def insert_template(file_path):
with open(file_path, 'r') as file:
for lines in file.readlines():
line = lines.strip('\n').split()
print tuple(line)
# sql = 'insert table(field) values({0});'.format(line)
sql = "INSERT INTO a(apply,ip) VALUES('%s','%s')" %tuple(line)
print sql
db_execute(sql)
#print sql_lines
def select_template():
cursor = con.cursor()
sql = 'select bb.ip from b bb,a aa where bb.apply = aa.apply group by bb.ip'
cursor.execute(sql)
template_list = cursor.fetchall()
res = template_list
print res
for m in res:
print type(m[0])
#template_list = cursor.fetchall()
#print template_list
def test1():
cursor = con.cursor()
id_list = [1, 2, 3]
id_list = ','.join([str(cursor.connection.literal(i)) for i in id_list])
print id_list
sql = 'SELECT col1, col2 FROM table1 WHERE id IN (%s)' % id_list
print sql
def select_template2():
cursor = con.cursor()
id_list = [1, 2]
sql = 'SELECT * FROM a WHERE id IN %s', (id_list,)
print sql
#cursor.execute(sql)
cursor.execute('SELECT ip,apply FROM a WHERE id IN %s'% (tuple(id_list),))
template_list = cursor.fetchall()
res = template_list
print res
if __name__ == '__main__':
file_path = '1.txt'
insert_template(file_path)
#select_template()
select_template2()
|
只写了插入和查询，其他类似。
改进版，先判断表中是否存在，在进行插入
|
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
|
#!/usr/bin/env python2.7# -*- coding: utf-8 -*-import sys
# import pymysql# pymysql.install_as_MySQLdb()import MySQLdb as mdb
from ConfigParser import ConfigParser
# con = mdb.connect('172.1.1.1', 'root', 'root', 'db03')def init_db():
try:
con = mdb.connect(host=conf.get('Database', 'host'),
user=conf.get('Database', 'user'),
passwd=conf.get('Database', 'passwd'),
db=conf.get('Database', 'db'),
charset='utf8')
return con
except:
print "Error:数据库连接错误"
return None
def test(param):
with con:
cur = con.cursor()
sql = 'INSERT INTO a(ip,apply) VALUES(%s,%s)'
# param = [['tomcat', '192.1.1.121'], ['redis', '192.1.1.122'], ['mongodb', '192.1.1.122']]
# param = ((username1, salt1, pwd1), (username2, salt2, pwd2), (username3, salt3, pwd3))
cur.executemany(sql, param)
def db_execute(sql):
cursor = con.cursor()
'''cursor.execute(sql)
con.commit()
cursor.close()'''
try:
cursor.execute(sql)
con.commit()
except:
con.rollback()
cursor.close()
def insert_file(file):
with open(file, 'r') as file:
for lines in file.readlines():
line = lines.strip('\n').split()
tupleline = tuple(line[0:2])
# sql = 'insert table(field) values({0});'.format(line)
sql = "INSERT INTO a(apply,ip) VALUES('%s','%s')" % tuple(tupleline)
print sql
db_execute(sql)
def insert_dict(dict):
for key,items in dict.iteritems():
line = []
line.append(key)
for item in items:
line.append(item)
print line
sql = "INSERT INTO a(ip,apply) VALUES('%s','%s')" % tuple(line)
print sql
db_execute(sql)
del line[-1]
def select_example():
cursor = con.cursor()
sql = 'select bb.ip from b bb,a aa where bb.apply = aa.apply group by bb.ip'
cursor.execute(sql)
template_list = cursor.fetchall()
res = template_list
print res
for m in res:
print type(m[0])
# template_list = cursor.fetchall()
# print template_list
def select_style():
cursor = con.cursor()
id_list = [1, 2, 3]
id_list = ','.join([str(cursor.connection.literal(i)) for i in id_list])
print id_list
sql = 'SELECT col1, col2 FROM table1 WHERE id IN (%s)' % id_list
print sql
def select(ip):
cursor = con.cursor()
#id_list = [1, 2]
#sql = 'SELECT * FROM a WHERE id IN %s', (id_list,)
#cursor.execute('SELECT ip,apply FROM a WHERE id IN %s' % (tuple(id_list),))
sql = "SELECT * FROM a WHERE ip = '%s'" %(ip)
cursor.execute(sql)
contents = cursor.fetchall()
res = contents
iplist = []
applist = []
for row in contents:
iplist.append(row[1])
applist.append(row[2])
cursor.close()
return applist
def read_file(file):
sql_lines = []
with open(file, 'r') as file:
for lines in file.readlines():
line = lines.strip('\n').split()
# sql = 'insert table(field) values({0});'.format(line)
sql_lines.append(line)
print sql_lines
return sql_lines
# print '\r\n'.join(str(sql_lines)) python3用的
''' file192.1.1.121 tomcat192.1.1.122 redis,mongodb,tomcat'''def read_file_2(file):
app = {}
with open(file, 'r') as file:
for lines in file.readlines():
line = lines.strip('\n').split()
ip = line[1]
apply = line[-1]
if not ip in app:
app[ip] = []
app[ip].append(apply)
return app
if __name__ == '__main__':
conf = ConfigParser()
conf.read('mysql.conf')
con = init_db()
file = 'add'
apply = read_file_2(file)
for key,items in apply.iteritems():
dict = {}
dst_data = select(key)
src_data = list(set(items))
ret_list = [item for item in src_data if item not in dst_data]
dict[key] = ret_list
if ret_list:
insert_dict(dict)
|
本文转自 liqius 51CTO博客，原文链接：http://blog.51cto.com/szgb17/1971959，如需转载请自行联系原作者