python3--pymsql--联合查询--检索数据

这个脚本解决了之前我引用pymysql，使用like语句 %% 参数 报错的问题

[root@publicsyncserver 20200618]# cat find_data.py
#!/usr/bin/python3
import pymysql
import sys
import re

#key = input('输入需要检索的应用: ')
key = sys.argv[1]



sql = ("SELECT a.app_pgm, a.busniess_name, a.app_name, a.app_cluster, b.app_ip ,a.app_nameid, "
"( CASE "
"WHEN a.app_pgm LIKE %s THEN 'windows' "
"WHEN a.app_pgm LIKE %s THEN 'linux' "
"ELSE 'none' "
"END "
") os "
"from "
"springcmdb.syseasyopspgm a "
"INNER JOIN "
"springcmdb.syseasyops b "
"ON "
"( a.app_name = b.app_name "
"AND "
"a.app_cluster = b.app_cluster "
") "
"AND ")

def isIP(key) :
  p = re.compile('^((25[0-5]|2[0-4]\d|[0-1]?\d\d?)\.){3}(25[0-5]|2[0-4]\d|[0-1]?\d\d?)$')
  if p.match(key) :
    this_sql = sql + " b.app_ip LIKE %s;"
  else :
    this_sql = sql + " a.app_name LIKE %s;"
  return str(this_sql)

this_sql = isIP(key)
#print(this_sql)
conn = pymysql.connect(host='none-ops.db.chinner.com', user='test',passwd='123456', db='test', port=3306, charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

#cursor.execute(str(sql).format(),('%:\\\\%','%/%','%'+key+'%'))
cursor.execute(str(this_sql),('%:\\\\%','%/%','%'+key+'%'))
result_of_sql = cursor.fetchall()

cursor.close()
conn.close()

for li in result_of_sql :
    this_str = str(li['app_pgm'])
    this_str = this_str + " " + str(li['busniess_name'])
    this_str = this_str + " " + str(li['app_name'])
    this_str = this_str + " " + str(li['app_cluster'])
    this_str = this_str + " " + str(li['app_ip'])
    this_str = this_str + " " + str(li['app_nameid'])
    this_str = this_str + " " + str(li['os'])
    print(this_str)

import sys

#key = input('输入需要检索的应用: ')
key = sys.argv[1]

sql = "SELECT a.app_pgm, a.busniess_name, a.app_name, a.app_cluster, b.app_ip ,a.app_nameid, \
( \
CASE \
WHEN a.app_pgm LIKE %s THEN 'windows' \
WHEN a.app_pgm LIKE %s THEN 'linux' \
ELSE 'none' \
END \
) os \
from \
springcmdb.syseasyopspgm a \
INNER JOIN \
springcmdb.syseasyops b \
ON \
( \
a.app_name = b.app_name \
AND \
a.app_cluster = b.app_cluster \
) \
AND \
a.app_name LIKE %s;"


conn = pymysql.connect(host='none-ops.db.chinner.com', user='test',passwd='123456', db='test', port=3306, charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

cursor.execute(str(sql).format(),('%:\\\\%','%/%','%'+key+'%'))
result_of_sql = cursor.fetchall()

cursor.close()
conn.close()

for l in result_of_sql :
    this_str = str(l['app_pgm'])
    this_str = this_str + " " + str(l['busniess_name'])
    this_str = this_str + " " + str(l['app_name'])
    this_str = this_str + " " + str(l['app_cluster'])
    this_str = this_str + " " + str(l['app_ip'])
    this_str = this_str + " " + str(l['app_nameid'])
    this_str = this_str + " " + str(l['os'])
    print(this_str)