python操作数据库统计_用python脚本通过information_schema数据库统计信息

刚学python，用MySQLdb写了个简单的统计些mysql信息的小脚本。

各位有什么不妥或者好的建议，请和我联系。

值得注意的是：用information_schema统计的表的行数是个一段时间里面的估算值，是不准确的，要得到确切的行数，要使用count函数。

#!/usr/bin/python

#coding=utf-8

#Author:tony_413

#date:2009-4-30

import MySQLdb

def query_context(lists):

for list in lists:

print list

conn=MySQLdb.connect(host='192.168.0.161',user='tony',passwd='ni#cai_CAI@_@',db='information_schema',charset='utf8')

query=conn.cursor()

query.execute("show processlist")

processlists=query.fetchall()

print "MySQL server total processlist: %d" %(len(processlists)-1)

query.execute("select table_name,data_length,index_length from tables where \

table_schema = 'test4dev'order by data_length desc limit 10")

table_size=query.fetchall()

print "The following table is the tables of data length the top 10:"

print "+------------------------+---------------+---------------+---------------+"

print "| table_name |data_length(M) |index_length(M)|total_length(M)|"

print "+------------------------+---------------+---------------+---------------+"

for table_name,data_length,index_length in table_size:

print "|%-23s |%14d |%14d |%15d|" \

%(table_name,data_length/1048576,index_length/1048576,(data_length+index_length)/1048576)

print "+------------------------+---------------+---------------+---------------+"

query.execute("select table_name,table_rows from tables where table_schema = 'test4dev' order by table_rows desc limit 10")

table_row=query.fetchall()

print "The following table is the row of table the top 10:"

print "+------------------------+----------------+"

print "| table_name | table_rows |"

print "+------------------------+----------------+"

for table_name,table_rows in table_row:

print "|%-23s | %14d |" %(table_name,table_rows)

print "+------------------------+----------------+"

query.close()

conn.close()

分享到：

2009-04-30 14:30

浏览 1721

评论