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 ) |