两个Python脚本轻松解决ETL工作:统计多个服务器下所有数据表信息(2)

avatar
作者
猴君
阅读量:2
  • 所以我写了两个Python脚本轻松搞定!

脚本1

=====================================================================

效果展示


统计出对应服务器,库名所对应表的建表时间、数据表总行数、数据表总大小、使用大小、未使用大小

在这里插入图片描述

代码解析


1)导入所用库

import pymssql # 操作SqlServer的库

import openpyxl as op # 操作Excel表的库

2)获取服务器下所有库名

def get_databases_name(self, cursor):

“”“获取服务器下所有库名”“”

sql = ‘SELECT * FROM sys.sysdatabases’

cursor.execute(sql)

rows = cursor.fetchall() # 逐行读取

存储

databases_name = []

for list in rows:

databases_name.append(list[0])

移除系统库

databases_name.remove(“master”)

databases_name.remove(“model”)

databases_name.remove(“msdb”)

databases_name.remove(“tempdb”)

移除无用库,如果没有则可以把try…except删除

try:

databases_name.remove(“ReportServer”)

databases_name.remove(“ReportServerTempDB”)

except Exception as e:

print(e)

print(databases_name)

return databases_name

  • 提取服务器下所有数据库名,用于后续SQL语句的拼接,利用列表的remove方法移除不需要的数据库

3)获取表详细信息并保存

def save(self, databases_name, cursor, server_name):

“”“获取表信息并保存”“”

加入count是为了换行写入数据

count = self.count

databases_name:[‘master’, ‘tempdb’, ‘model’, ‘msdb’, ‘ReportServer’, ‘ReportServerTempDB’, ‘test’]

取出每个库名,用于拼接sql,获取对应库名下表信息

for database in databases_name:

sql = ‘’’

USE [%s]

SELECT a.name table_name,

a.crdate crdate,

b.rows rows,

8*b.reserved/1024 reserved,

rtrim(8*b.dpages/1024) used,

8*(b.reserved-b.dpages)/1024 unused

FROM sysobjects AS a

INNER JOIN sysindexes AS b ON a.id = b.id

WHERE ( a.type = ‘u’ )

AND ( b.indid IN ( 0, 1 ) )

ORDER BY a.name,b.rows DESC;

‘’’ % database

cursor.execute(sql)

rows = cursor.fetchall() # 逐行读取

for i in rows:

要写入excel的数据

server = server_name

database_name = database

table_name = i[0]

crdate = i[1]

rows_size = i[2]

reserved = i[3]

used = i[4]

unused = i[5]

打印获取到的数据

print(server, database_name, table_name, crdate, rows_size, reserved, used, unused)

row:count所对应的就是行数,从第二行开始累加,colum:表示第几列,value:表示插入的值

self.wb.cell(row=count, column=1, value=server)

self.wb.cell(row=count, column=2, value=database_name)

self.wb.cell(row=count, column=3, value=table_name)

self.wb.cell(row=count, column=4, value=crdate)

self.wb.cell(row=count, column=5, value=rows_size)

self.wb.cell(row=count, column=6, value=reserved)

self.wb.cell(row=count, column=7, value=used)

self.wb.cell(row=count, column=8, value=unused)

count加1,进入到下一行写入数据

count += 1

self.count = count

  • 拼接SQL效果:查看表名所对应建表时间、数据表总行数、数据表总大小、使用大小、未使用大小

在这里插入图片描述

4)实现主要逻辑

def main(self):

“”“实现主要逻辑”“”

1.创建Excel表对象,设置列名

self.ws = op.Workbook()

self.wb = self.ws.create_sheet(index=0)

self.wb.cell(row=1, column=1, value=‘服务器地址’)

self.wb.cell(row=1, column=2, value=‘库名’)

self.wb.cell(row=1, column=3, value=‘表名’)

self.wb.cell(row=1, column=4, value=‘建表时间’)

self.wb.cell(row=1, column=5, value=‘数据表总行数’)

self.wb.cell(row=1, column=6, value=‘数据表总大小(MB)’)

self.wb.cell(row=1, column=7, value=‘使用大小(MB)’)

self.wb.cell(row=1, column=8, value=‘未使用大小(MB)’)

self.count = 2 # 定义为全局变量每次用完会更新

服务器列表,括号内为:服务器名、账号、密码

如果多个服务器元组用逗号隔开

list = [(‘.’, ‘sa’, ‘yuan427’)]

2.遍历服务器列表,实现统计多个服务器

for server in list:

server_name = server[0]

user_name = server[1]

password = server[2]

conn = pymssql.connect(server_name, user_name, password)

if conn:

print(“连接成功!”)

cursor = conn.cursor()

3.获取库名

databases_name = self.get_databases_name(cursor)

4.获取详细信息并保存

self.save(databases_name, cursor, server_name)

所有服务器表插入完后保存

excel_name = “./本地数据库统计.xlsx”

self.ws.save(excel_name)

关闭游标,关闭数据库

cursor.close()

conn.close()

  • 1.在服务器列表循环外创建Excel文件

  • 2.遍历服务器列表,实现统计多个服务器

  • 3.获取当前遍历服务器的所有库名

  • 4.用库名拼接SQL实现获取数据表详细信息

  • 5.换行保存在Excel文件

完整代码


需要修改的地方只有服务器地址、账号、密码,每一个服务器信息放一个元组中;如果有多个服务器用逗号隔开

import pymssql

import openpyxl as op

class ErTransUtils():

def get_databases_name(self, cursor):

“”“获取服务器下所有库名”“”

sql = ‘SELECT * FROM sys.sysdatabases’

cursor.execute(sql)

rows = cursor.fetchall() # 逐行读取

存储

databases_name = []

for list in rows:

databases_name.append(list[0])

移除系统库

databases_name.remove(“master”)

databases_name.remove(“model”)

databases_name.remove(“msdb”)

databases_name.remove(“tempdb”)

移除无用库

try:

databases_name.remove(“ReportServer”)

databases_name.remove(“ReportServerTempDB”)

except Exception as e:

print(e)

print(databases_name)

return databases_name

def save(self, databases_name, cursor, server_name):

“”“获取表信息并保存”“”

加入count是为了换行写入数据

count = self.count

databases_name:[‘master’, ‘tempdb’, ‘model’, ‘msdb’, ‘ReportServer’, ‘ReportServerTempDB’, ‘test’],取出每个库名

for database in databases_name:

sql = ‘’’

USE [%s]

SELECT a.name table_name,

a.crdate crdate,

b.rows rows,

8*b.reserved/1024 reserved,

rtrim(8*b.dpages/1024) used,

8*(b.reserved-b.dpages)/1024 unused

FROM sysobjects AS a

INNER JOIN sysindexes AS b ON a.id = b.id

WHERE ( a.type = ‘u’ )

AND ( b.indid IN ( 0, 1 ) )

ORDER BY a.name,b.rows DESC;

‘’’ % database

cursor.execute(sql)

rows = cursor.fetchall() # 逐行读取

for i in rows:

要写入excel的数据

server = server_name

database_name = database

table_name = i[0]

crdate = i[1]

rows_size = i[2]

reserved = i[3]

used = i[4]

unused = i[5]

打印获取到的数据

print(server, database_name, table_name, crdate, rows_size, reserved, used, unused)

self.wb.cell(row=count, column=1, value=server)

self.wb.cell(row=count, column=2, value=database_name)

self.wb.cell(row=count, column=3, value=table_name)

self.wb.cell(row=count, column=4, value=crdate)

self.wb.cell(row=count, column=5, value=rows_size)

self.wb.cell(row=count, column=6, value=reserved)

self.wb.cell(row=count, column=7, value=used)

self.wb.cell(row=count, column=8, value=unused)

count加1,进入到下一行写入数据

count += 1

self.count = count

def main(self):

“”“实现主要逻辑”“”

创建Excel表对象,设置列名

self.ws = op.Workbook()

self.wb = self.ws.create_sheet(index=0)

self.wb.cell(row=1, column=1, value=‘服务器地址’)

self.wb.cell(row=1, column=2, value=‘库名’)

self.wb.cell(row=1, column=3, value=‘表名’)

self.wb.cell(row=1, column=4, value=‘建表时间’)

self.wb.cell(row=1, column=5, value=‘数据表总行数’)

self.wb.cell(row=1, column=6, value=‘数据表总大小(MB)’)

self.wb.cell(row=1, column=7, value=‘使用大小(MB)’)

self.wb.cell(row=1, column=8, value=‘未使用大小(MB)’)

self.count = 2

服务器列表,括号内为:服务器名、账号、密码

如果多个服务器用逗号隔开

list = [(‘.’, ‘sa’, ‘yuan427’)]

for server in list:

server_name = server[0]

user_name = server[1]

password = server[2]

conn = pymssql.connect(server_name, user_name, password)

if conn:

print(“连接成功!”)

cursor = conn.cursor()

databases_name = self.get_databases_name(cursor)

self.save(databases_name, cursor, server_name)

所有服务器表插入完后保存

excel_name = “./本地数据库统计.xlsx”

self.ws.save(excel_name)

关闭游标,关闭数据库

cursor.close()

conn.close()

if name == ‘main’:

er = ErTransUtils()

er.main()

脚本2

=====================================================================

效果展示


自动创建服务器文件夹,服务器文件夹下是所有库文件夹,库文件夹下是以表名命名的Excel文件名,文件中有表字段名称、是否为主键、字段类型、字段长度、索引名称等。

  • 我本地的test库

在这里插入图片描述

  • 表中字段信息如下,代码设置了Excel表格式,网格根据字段数量自动填充

在这里插入图片描述

在这里插入图片描述

代码解析


由于和第一个脚本相似只讲讲思路

  • 1.获取所有数据库名

  • 2.获取库中所有表名,把库名和表名存放在元组内放入列表,如:[('test', 'student', 'UTIL_IP1', 'test4', 'test5', 'test6', 'TM_AP', 'test1', 'test2', 'test3', 'UTIL_IP')],第一个是库名其他都是表名

  • 3.拼接获取字段信息的SQL,把库名、表名传进去,SQL能获取到的信息如下图(拼接的地方为上面的库名和红框那的表名):

在这里插入图片描述

  • 4.设置Excel内格式:字体、加粗、居中、合并、网格线、行高、列宽等

  • 5.一个Excel文件保存完毕,生成另一个表的Excel文件,只到当前服务器下所有表统计完毕,才开始统计另一个服务器

完整代码


需要修改的地方只有服务器地址、账号、密码,每一个服务器信息放一个元组中;如果有多个服务器用逗号隔开

import pymssql

import openpyxl as op

from openpyxl.styles import Font, Alignment, Side, Border

import os

class ErTransUtils():

def get_databases_name(self, cursor):

“”“获取服务器下所有库名”“”

sql = ‘SELECT * FROM sys.sysdatabases’

cursor.execute(sql)

rows = cursor.fetchall() # 逐行读取

存储

databases_name = []

for list in rows:

databases_name.append(list[0])

移除系统库和无用库

databases_name.remove(“master”)

databases_name.remove(“model”)

databases_name.remove(“msdb”)

databases_name.remove(“tempdb”)

try:

databases_name.remove(“ReportServer”)

databases_name.remove(“ReportServerTempDB”)

except Exception as e:

print(e)

print(databases_name)

return databases_name

def get_tables_name(self, databases_name, cursor):

“”“获取库中所有表名,并把对应的库名和表名存储在一起”“”

item1 = [] # 存储

for i in databases_name:

sql = f’SELECT * FROM [{i}].sys.tables’

cursor.execute(sql)

rows = cursor.fetchall() # 逐行读取

item = []

for j in rows:

自我介绍一下,小编13年上海交大毕业,曾经在小公司待过,也去过华为、OPPO等大厂,18年进入阿里一直到现在。

深知大多数Python工程师,想要提升技能,往往是自己摸索成长或者是报班学习,但对于培训机构动则几千的学费,着实压力不小。自己不成体系的自学效果低效又漫长,而且极易碰到天花板技术停滞不前!

因此收集整理了一份《2024年Python开发全套学习资料》,初衷也很简单,就是希望能够帮助到想自学提升又不知道该从何学起的朋友,同时减轻大家的负担。

img

img

img

img

img

img

既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,基本涵盖了95%以上前端开发知识点,真正体系化!

由于文件比较大,这里只是将部分目录大纲截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频,并且后续会持续更新

如果你觉得这些内容对你有帮助,可以扫码获取!!!(备注Python)

在。**

深知大多数Python工程师,想要提升技能,往往是自己摸索成长或者是报班学习,但对于培训机构动则几千的学费,着实压力不小。自己不成体系的自学效果低效又漫长,而且极易碰到天花板技术停滞不前!

因此收集整理了一份《2024年Python开发全套学习资料》,初衷也很简单,就是希望能够帮助到想自学提升又不知道该从何学起的朋友,同时减轻大家的负担。

[外链图片转存中…(img-T2nqtNEg-1712637568510)]

[外链图片转存中…(img-sSS3Vy2q-1712637568511)]

img

img

img

img

既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,基本涵盖了95%以上前端开发知识点,真正体系化!

由于文件比较大,这里只是将部分目录大纲截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频,并且后续会持续更新

如果你觉得这些内容对你有帮助,可以扫码获取!!!(备注Python)

img

广告一刻

为您即时展示最新活动产品广告消息,让您随时掌握产品活动新动态!