python中查询数据库个数_【数据库】MySQL中数据库和表容量大小的查询方法以及Python实现...

import MySQLdb

# 连接管理

class Client():

def __init__(self, **kwargs):

try:

self.conn = MySQLdb.connect(host=kwargs.get('host'),

user=kwargs.get('user', 'root'),

password=kwargs.get('password'),

charset=kwargs.get('charset', 'utf8'),

use_unicode=kwargs.get('use_unicode', True))

self.cursor = self.conn.cursor()

print("\nMySQL连接成功!\n")

if kwargs.get('db'):

self.SetDatabase(kwargs.get('db'))

except Exception as errMsg:

raise Exception(errMsg)

# ---------------------------------------------------------------------------------------------------

def SetDatabase(self, db):

try:

self.cursor.execute('USE {0}'.format(db)) # 设置(修改)当前数据库

print("\n已连接数据库{0}\n".format(db))

except Exception as e:

print(e)

def CloseConnection(self):

# 关闭数据库连接

self.conn.close()

print("\nMySQL连接已关闭!\n")

class MysqlPython(Client):

def __init__(self, **kwargs):

Client.__init__(self, db=kwargs.get('db', 'testdb'), host=kwargs.get('host', 'localhost'),

password=kwargs.get('password', '123'))

self.COMMIT_THRESHOLD = 10000

def DescDatabases(self):

"""查看所有数据库容量大小"""

sql = """select

table_schema,

sum(table_rows) as '记录数',

sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',

sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'

from information_schema.tables

group by table_schema

order by sum(data_length) desc, sum(index_length) desc

"""

self.cursor.execute(sql)

info = self.cursor.fetchall()

return info

def DescDatabase(self, db):

"""查看(指定库)mysql库容量大小"""

sql = """select

table_schema as '数据库',

sum(table_rows) as '记录数',

sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',

sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'

from information_schema.tables

where table_schema='{0}';

""".format(db)

self.cursor.execute(sql)

info = self.cursor.fetchall()

return info

def DescDbsTables(self):

"""查看所有数据库各表容量大小"""

sql = """select

table_schema as '数据库',

table_name as '表名',

table_rows as '记录数',

truncate(data_length/1024/1024, 2) as '数据容量(MB)',

truncate(index_length/1024/1024, 2) as '索引容量(MB)'

from information_schema.tables

order by data_length desc, index_length desc

"""

self.cursor.execute(sql)

info = self.cursor.fetchall()

return info

def DescDbTables(self, db):

"""查看(指定库)各表容量大小"""

sql = """select

table_schema as '数据库',

table_name as '表名',

table_rows as '记录数',

truncate(data_length/1024/1024, 2) as '数据容量(MB)',

truncate(index_length/1024/1024, 2) as '索引容量(MB)'

from information_schema.tables

where table_schema='{0}'

order by data_length desc, index_length desc;

""".format(db)

self.cursor.execute(sql)

info = self.cursor.fetchall()

return info

if __name__ == '__main__':

client = MysqlPython()

print(client.DescDatabases())

print(client.DescDatabase(db='mysql'))

print(client.DescDbsTables())

print(client.DescDbTables(db='mysql'))