python 获取mysql中所有数据库名称

秒速五厘米 2021-09-02 09:06 542阅读 0赞

通过mysql ->show databases 命令获取所有非默认数据库存入列表中返回

实例如下：

import pymysql
    class Mysql(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_name
    
        def select(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()
                return res
            except Exception as e:
                print(e)
                return False
    
        def get_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:  # 判断结果非空
                return False
    
            db_list = []  # 数据库列表
            for i in res:
                db_name = i['Database']
                # 判断不在排除列表时
                if db_name not in exclude_list:
                    db_list.append(db_name)
                    
            if not db_list:
                return False
    
            return db_list
    
    
    if __name__ == '__main__':
        host = "ip"
        user = "root"
        passwd = "密码"
        port = 3306
        db_name = "mysql"
    
        obj = Mysql(host, user, passwd, port, db_name)
        all_db_list = obj.get_all_db()
        print("all_db_list", all_db_list)

result：

![在这里插入图片描述][20200602174711437.png]

[20200602174711437.png]: /images/20210728/f8785f50b7434750906195e08165e277.png