当前位置:Gxlcms > 数据库问题 > Python统计数据库中的数据量【含MySQL、Oracle】

Python统计数据库中的数据量【含MySQL、Oracle】

时间:2021-07-01 10:21:17 帮助过:4人阅读

-*- coding: utf-8 # File : start.py # Author : baoshan import json import pymysql import cx_Oracle import pandas as pd def main(): dataSum = [] connInfo = "connInfo.json" # 配置文件名称 connFile = open(connInfo, r, encoding=utf8) connRecords = connFile.read(102400) #一次读取多个字节 connRecordsjs = json.loads(connRecords) for single in connRecordsjs: if "mysql" == single.get("dbtype"): conn = pymysql.connect(host=single.get("host"), port=single.get("port"), user=single.get("user"), passwd=single.get("passwd"), charset=single.get("charset")) if "gongxiangwangzhan" == single.get("source", "0"): # 共享网站 公安局、民政局、聊城市发展和改革委员会 定制 sql = "select table_schema as ‘数据库‘, " "table_name as ‘数据表‘, " "TABLE_COMMENT as ‘表注释‘, " "round(data_length/1024/1024,2) as ‘数据大小(M)‘, " "round(index_length/1024/1024,2) as ‘索引大小(M)‘, " "TABLE_ROWS as ‘行数‘ " "from information_schema.tables " "where TABLE_SCHEMA in (‘"+single.get("dbschema")+"‘) " "AND TABLE_ROWS > 0 " "and table_name in "+single.get("selectkeystr")+"" else: sql = "select " "table_schema as ‘数据库‘," "table_name as ‘数据表‘, " "TABLE_COMMENT as ‘表注释‘, " "round(data_length/1024/1024,2) as ‘数据大小(M)‘, " "round(index_length/1024/1024,2) as ‘索引大小(M)‘, " "TABLE_ROWS as ‘行数‘" "from information_schema.tables " "where TABLE_SCHEMA in (‘"+single.get("dbschema")+"‘) " "and (table_name "+single.get("selectstr")+""+single.get("selectkeystr")+"‘) " "and TABLE_ROWS > 0" df = pd.read_sql(sql, conn) print(single.get("key"), str(df[行数].sum())) dataSum.append(df[行数].sum()) conn.close() elif "oracle" == single.get("dbtype"): if "table" == single.get("selecttype"): sql = "select owner as owner," "table_name as table_name," "tablespace_name as tablespace_name, " "num_rows as num_rows " "from all_tables " "where num_rows > 0 " "and table_name like ‘"+single.get("selectkeystr")+"" "order by num_rows desc " elif "database" == single.get("selecttype"): # 共享网站-oracle-工商局 定制 sql = "select owner as owner, " "table_name as table_name, " "tablespace_name as tablespace_name, " "num_rows as num_rows " "from all_tables " "where num_rows > 0 " "and tablespace_name in(‘"+single.get("dbschema")+"‘) " "order by num_rows desc" db = cx_Oracle.connect(single.get("connstr"), encoding=utf-8) cursor = db.cursor() cursor.execute(sql) rs = cursor.fetchall() df = pd.DataFrame(rs) print(single.get("key"), str(df[3].sum())) dataSum.append(df[3].sum()) cursor.close() db.close() elif "sqlserver" == single.get("dbtype"): print(single.get("key"), 55568045) dataSum.append(55568045) # "SELECT A.NAME ,B.ROWS FROM sysobjects A JOIN sysindexes B ON A.id = B.id WHERE A.xtype = ‘U‘ AND B.indid IN(0,1) and b.rows >0 ORDER BY B.ROWS DESC" else: print("please give right database type.") connFile.close() print(-*30) print("数据量总计:", str(sum(dataSum))) if __name__ == __main__: print("***一次性统计所有对接数据的委办局,和其对应的数据(条数)***") main()

 

所需要的配置文件格式如下:

  1. <span style="color: #000000;">[
  2. {
  3. </span><span style="color: #800000;">"</span><span style="color: #800000;">key</span><span style="color: #800000;">"</span>: <span style="color: #800000;">"</span><span style="color: #800000;">智慧公交</span><span style="color: #800000;">"</span><span style="color: #000000;">,
  4. </span><span style="color: #800000;">"</span><span style="color: #800000;">dbtype</span><span style="color: #800000;">"</span>: <span style="color: #800000;">"</span><span style="color: #800000;">oracle</span><span style="color: #800000;">"</span><span style="color: #000000;">,
  5. </span><span style="color: #800000;">"</span><span style="color: #800000;">connstr</span><span style="color: #800000;">"</span>: <span style="color: #800000;">"</span><span style="color: #800000;">nicai/123456@10.10.10.10:1521/ORCL</span><span style="color: #800000;">"</span><span style="color: #000000;">,
  6. </span><span style="color: #800000;">"</span><span style="color: #800000;">selecttype</span><span style="color: #800000;">"</span>: <span style="color: #800000;">"</span><span style="color: #800000;">table</span><span style="color: #800000;">"</span><span style="color: #000000;">,
  7. </span><span style="color: #800000;">"</span><span style="color: #800000;">selectstr</span><span style="color: #800000;">"</span>: <span style="color: #800000;">"</span><span style="color: #800000;">like</span><span style="color: #800000;">"</span><span style="color: #000000;">,
  8. </span><span style="color: #800000;">"</span><span style="color: #800000;">selectkeystr</span><span style="color: #800000;">"</span>: <span style="color: #800000;">"</span><span style="color: #800000;">BUS%</span><span style="color: #800000;">"</span><span style="color: #000000;">
  9. },
  10. {
  11. </span><span style="color: #800000;">"</span><span style="color: #800000;">key</span><span style="color: #800000;">"</span>: <span style="color: #800000;">"</span><span style="color: #800000;">公共自行车</span><span style="color: #800000;">"</span><span style="color: #000000;">,
  12. </span><span style="color: #800000;">"</span><span style="color: #800000;">dbtype</span><span style="color: #800000;">"</span>: <span style="color: #800000;">"</span><span style="color: #800000;">oracle</span><span style="color: #800000;">"</span><span style="color: #000000;">,
  13. </span><span style="color: #800000;">"</span><span style="color: #800000;">connstr</span><span style="color: #800000;">"</span>: <span style="color: #800000;">"</span><span style="color: #800000;">nicai/123456@10.10.10.10:1521/ORCL</span><span style="color: #800000;">"</span><span style="color: #000000;">,
  14. </span><span style="color: #800000;">"</span><span style="color: #800000;">selecttype</span><span style="color: #800000;">"</span>: <span style="color: #800000;">"</span><span style="color: #800000;">table</span><span style="color: #800000;">"</span><span style="color: #000000;">,
  15. </span><span style="color: #800000;">"</span><span style="color: #800000;">selectstr</span><span style="color: #800000;">"</span>: <span style="color: #800000;">"</span><span style="color: #800000;">like</span><span style="color: #800000;">"</span><span style="color: #000000;">,
  16. </span><span style="color: #800000;">"</span><span style="color: #800000;">selectkeystr</span><span style="color: #800000;">"</span>: <span style="color: #800000;">"</span><span style="color: #800000;">BICYCLE%</span><span style="color: #800000;">"</span><span style="color: #000000;">
  17. },
  18. {
  19. </span><span style="color: #800000;">"</span><span style="color: #800000;">key</span><span style="color: #800000;">"</span>: <span style="color: #800000;">"</span><span style="color: #800000;">安监局</span><span style="color: #800000;">"</span><span style="color: #000000;">,
  20. </span><span style="color: #800000;">"</span><span style="color: #800000;">dbtype</span><span style="color: #800000;">"</span>: <span style="color: #800000;">"</span><span style="color: #800000;">mysql</span><span style="color: #800000;">"</span><span style="color: #000000;">,
  21. </span><span style="color: #800000;">"</span><span style="color: #800000;">host</span><span style="color: #800000;">"</span>: <span style="color: #800000;">"</span><span style="color: #800000;">10.10.10.10</span><span style="color: #800000;">"</span><span style="color: #000000;">,
  22. </span><span style="color: #800000;">"</span><span style="color: #800000;">port</span><span style="color: #800000;">"</span>: 3306<span style="color: #000000;">,
  23. </span><span style="color: #800000;">"</span><span style="color: #800000;">user</span><span style="color: #800000;">"</span>: <span style="color: #800000;">"</span><span style="color: #800000;">nicai</span><span style="color: #800000;">"</span><span style="color: #000000;">,
  24. </span><span style="color: #800000;">"</span><span style="color: #800000;">passwd</span><span style="color: #800000;">"</span>: <span style="color: #800000;">"</span><span style="color: #800000;">123456</span><span style="color: #800000;">"</span><span style="color: #000000;">,
  25. </span><span style="color: #800000;">"</span><span style="color: #800000;">charset</span><span style="color: #800000;">"</span>: <span style="color: #800000;">"</span><span style="color: #800000;">utf8</span><span style="color: #800000;">"</span><span style="color: #000000;">,
  26. </span><span style="color: #800000;">"</span><span style="color: #800000;">selecttype</span><span style="color: #800000;">"</span>: <span style="color: #800000;">"</span><span style="color: #800000;">table</span><span style="color: #800000;">"</span><span style="color: #000000;">,
  27. </span><span style="color: #800000;">"</span><span style="color: #800000;">selectstr</span><span style="color: #800000;">"</span>: <span style="color: #800000;">"</span><span style="color: #800000;">like</span><span style="color: #800000;">"</span><span style="color: #000000;">,
  28. </span><span style="color: #800000;">"</span><span style="color: #800000;">dbschema</span><span style="color: #800000;">"</span>: <span style="color: #800000;">"</span><span style="color: #800000;">statistics_data</span><span style="color: #800000;">"</span><span style="color: #000000;">,
  29. </span><span style="color: #800000;">"</span><span style="color: #800000;">selectkeystr</span><span style="color: #800000;">"</span>: <span style="color: #800000;">"</span><span style="color: #800000;">ajj%</span><span style="color: #800000;">"</span><span style="color: #000000;">
  30. },
  31. {
  32. </span><span style="color: #800000;">"</span><span style="color: #800000;">key</span><span style="color: #800000;">"</span>: <span style="color: #800000;">"</span><span style="color: #800000;">百度交通</span><span style="color: #800000;">"</span><span style="color: #000000;">,
  33. </span><span style="color: #800000;">"</span><span style="color: #800000;">dbtype</span><span style="color: #800000;">"</span>: <span style="color: #800000;">"</span><span style="color: #800000;">mysql</span><span style="color: #800000;">"</span><span style="color: #000000;">,
  34. </span><span style="color: #800000;">"</span><span style="color: #800000;">host</span><span style="color: #800000;">"</span>: <span style="color: #800000;">"</span><span style="color: #800000;">10.10.10.2</span><span style="color: #800000;">"</span><span style="color: #000000;">,
  35. </span><span style="color: #800000;">"</span><span style="color: #800000;">port</span><span style="color: #800000;">"</span>: 3306<span style="color: #000000;">,
  36. </span><span style="color: #800000;">"</span><span style="color: #800000;">user</span><span style="color: #800000;">"</span>: <span style="color: #800000;">"</span><span style="color: #800000;">nicai</span><span style="color: #800000;">"</span><span style="color: #000000;">,
  37. </span><span style="color: #800000;">"</span><span style="color: #800000;">passwd</span><span style="color: #800000;">"</span>: <span style="color: #800000;">"</span><span style="color: #800000;">123456</span><span style="color: #800000;">"</span><span style="color: #000000;">,
  38. </span><span style="color: #800000;">"</span><span style="color: #800000;">charset</span><span style="color: #800000;">"</span>: <span style="color: #800000;">"</span><span style="color: #800000;">utf8</span><span style="color: #800000;">"</span><span style="color: #000000;">,
  39. </span><span style="color: #800000;">"</span><span style="color: #800000;">selecttype</span><span style="color: #800000;">"</span>: <span style="color: #800000;">"</span><span style="color: #800000;">table</span><span style="color: #800000;">"</span><span style="color: #000000;">,
  40. </span><span style="color: #800000;">"</span><span style="color: #800000;">selectstr</span><span style="color: #800000;">"</span>: <span style="color: #800000;">"</span><span style="color: #800000;">like</span><span style="color: #800000;">"</span><span style="color: #000000;">,
  41. </span><span style="color: #800000;">"</span><span style="color: #800000;">dbschema</span><span style="color: #800000;">"</span>: <span style="color: #800000;">"</span><span style="color: #800000;">statistics_data</span><span style="color: #800000;">"</span><span style="color: #000000;">,
  42. </span><span style="color: #800000;">"</span><span style="color: #800000;">selectkeystr</span><span style="color: #800000;">"</span>: <span style="color: #800000;">"</span><span style="color: #800000;">bdu%</span><span style="color: #800000;">"</span><span style="color: #000000;">
  43. }
  44. ]</span>

 

关于SqlServer的数据量查询,由于当时连不上,就没有嵌入到这个程序中。

不过查询的方法已经列出。

 

精进自己,分享他人!

 

谢谢

Python统计数据库中的数据量【含MySQL、Oracle】

标签:name   mat   user   and   sel   sch   acl   cts   文件名   

人气教程排行