python查看数据库存在表_python 获取mysql数据库列表以及用户权限

#!/usr/bin/env python#coding: utf-8

importjsonimportpymysqlclassMysql(object):#mysql 端口号,注意：必须是int类型

def __init__(self, host, user, passwd, port, db_name):

self.host=host

self.user=user

self.passwd=passwd

self.port=port

self.db_name=db_namedefselect(self, sql):"""执行sql命令

:param sql: sql语句

:return: 元祖"""

try:

conn=pymysql.connect(

host=self.host,

user=self.user,

passwd=self.passwd,

port=self.port,

database=self.db_name,

charset='utf8',

cursorclass=pymysql.cursors.DictCursor

)

cur= conn.cursor() #创建游标

#conn.cursor()

cur.execute(sql) #执行sql命令

res = cur.fetchall() #获取执行的返回结果

cur.close()

conn.close()returnresexceptException as e:print(e)returnFalsedefget_all_db(self):"""获取所有数据库名

:return: list"""

#排除自带的数据库

exclude_list = ["sys", "information_schema", "mysql", "performance_schema"]

sql= "show databases" #显示所有数据库

res =self.select(sql)#print(res)

if not res: #判断结果非空

returnFalse

db_list= [] #数据库列表

for i inres:

db_name= i['Database']#判断不在排除列表时

if db_name not inexclude_list:

db_list.append(db_name)#print(db_name)

if notdb_list:returnFalsereturndb_listdefget_user_list(self):"""获取用户列表

:return: list"""

#排除自带的用户

exclude_list = ["root", "mysql.sys", "mysql.session"]

sql= "select User from mysql.user"res=self.select(sql)#print(res)

if not res: #判断结果非空

returnFalse

user_list=[]for i inres:

db_name= i['User']#判断不在排除列表时

if db_name not inexclude_list:

user_list.append(db_name)if notuser_list:returnFalsereturnuser_listdefget_user_power(self):"""获取用户权限

:return: {}

{

"test":{ # 用户名

"read":["db1","db2"], # 只拥有读取权限的数据库

"all":["db1","db2"], # 拥有读写权限的数据库

},

...

}"""info_dict= {} #最终结果字典

#获取用户列表

user_list =self.get_user_list()if notuser_list:returnFalse#查询每一个用户的权限

for user inuser_list:#print("user",user)

sql = "show grants for {}".format(user)

res=self.select(sql)if notres:returnFalsefor i inres:

key= 'Grants for {}@%'.format(user)#print("key",key)

#判断key值存在时

ifi.get(key):#print(i[key])

#包含ALL或者SELECT时

if "ALL" in i[key] or "SELECT" ini[key]:#print(i[key])

if notinfo_dict.get(user):

info_dict[user]= {"read": [], "all": []}

cut_str= i[key].split() #空格切割

#print(cut_str,len(cut_str))

power = cut_str[1] #权限，比如ALL，SELECT

if len(cut_str) == 6: #判断切割长度

#去除左边的`

tmp_str = cut_str[3].lstrip("`")else:

tmp_str= cut_str[4].lstrip("`")#替换字符串

tmp_str = tmp_str.replace('`.*', '')

value= tmp_str.replace('\_', '-')#判断权限为select 时

if power.lower() == "select":if value not in info_dict[user].get("read"):#只读列表

info_dict[user]["read"].append(value)else:if value not in info_dict[user].get("all"):#所有权限列表

info_dict[user]["all"].append(value)#print(info_dict)

returninfo_dictif __name__ == '__main__':

host= "192.168.10.10"user= "root"passwd= "123456"port= 3306db_name= "mysql"obj=Mysql(host, user, passwd, port, db_name)

all_db_list=obj.get_all_db()

user_power=obj.get_user_power()print("all_db_list",all_db_list)print("user_power",user_power)