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用的
''' file
192.1.1.121 tomcat
192.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,如需转载请自行联系原作者