用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<span> 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()