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

• 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:

item.append(j[0])

item.insert(0, i)

item1.append(tuple(item))

return item1

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

“”“获取表中字段信息，并写入Excel”“”

根据服务器名称创建目录

os.makedirs(server_name)

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

取出每个库名

for database in item1:

根据库名名称创建目录

database1 = f’./{server_name}/{database[0]}’

os.makedirs(database1)

print(‘正在统计%s库’ % database[0])

for table in range(1, len(database)):

sql = ‘’’

USE [%s]

SELECT

ColumnName=C.name,

PrimaryKey=ISNULL(IDX.PrimaryKey,N’'),

Type=T.name,

Length=C.max_length,

IndexName=ISNULL(IDX.IndexName,N’')

FROM sys.columns C

INNER JOIN sys.objects O

ON C.[object_id]=O.[object_id]

AND O.type=‘U’

AND O.is_ms_shipped=0

INNER JOIN sys.types T

ON C.user_type_id=T.user_type_id

LEFT JOIN – 索引及主键信息

(

SELECT

IDXC.[object_id],

IDXC.column_id,

Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,‘IsDescending’)

WHEN 1 THEN ‘DESC’ WHEN 0 THEN ‘ASC’ ELSE ‘’ END,

PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N’√’ELSE N’’ END,

IndexName=IDX.Name

FROM sys.indexes IDX

INNER JOIN sys.index_columns IDXC

ON IDX.[object_id]=IDXC.[object_id]

AND IDX.index_id=IDXC.index_id

INNER JOIN – 对于一个列包含多个索引的情况,只显示第1个索引信息

(

SELECT [object_id], Column_id, index_id=MIN(index_id)

FROM sys.index_columns

GROUP BY [object_id], Column_id

) IDXCUQ

ON IDXC.[object_id]=IDXCUQ.[object_id]

AND IDXC.Column_id=IDXCUQ.Column_id

AND IDXC.index_id=IDXCUQ.index_id

) IDX

ON C.[object_id]=IDX.[object_id]

AND C.column_id=IDX.column_id

WHERE O.name=N’%s’ – 修改表名

ORDER BY O.name,C.column_id

‘’’ % (database[0], database[table])

执行sql语句

try:

cursor.execute(sql)

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

except Exception as e:

print(e)

存储

lists = []

for list in rows:

lists.append(list)

如果你也是看准了Python，想自学Python，在这里为大家准备了丰厚的免费学习大礼包，带大家一起学习，给大家剖析Python兼职、就业行情前景的这些事儿。

一、Python所有方向的学习路线

Python所有方向路线就是把Python常用的技术点做整理，形成各个领域的知识点汇总，它的用处就在于，你可以按照上面的知识点去找对应的学习资源，保证自己学得较为全面。

二、学习软件

工欲善其必先利其器。学习Python常用的开发软件都在这里了，给大家节省了很多时间。

三、全套PDF电子书

书籍的好处就在于权威和体系健全，刚开始学习的时候你可以只看视频或者听某个人讲课，但等你学完之后，你觉得你掌握了，这时候建议还是得去看一下书籍，看权威技术书籍也是每个程序员必经之路。

四、入门学习视频

我们在看视频学习的时候，不能光动眼动脑不动手，比较科学的学习方法是在理解之后运用它们，这时候练手项目就很适合了。

四、实战案例

光学理论是没用的，要学会跟着一起敲，要动手实操，才能将自己的所学运用到实际当中去，这时候可以搞点实战案例来学习。

五、面试资料

我们学习Python必然是为了找到高薪的工作，下面这些面试题是来自阿里、腾讯、字节等一线互联网大厂最新的面试资料，并且有阿里大佬给出了权威的解答，刷完这一套面试资料相信大家都能找到满意的工作。

成为一个Python程序员专家或许需要花费数年时间，但是打下坚实的基础只要几周就可以，如果你按照我提供的学习路线以及资料有意识地去实践，你就有很大可能成功！
最后祝你好运！！！

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

深知大多数初中级Python工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

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

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

如果你觉得这些内容对你有帮助，可以添加下面V无偿领取！（备注：python）

a2c5eeff0.png)

五、面试资料

我们学习Python必然是为了找到高薪的工作，下面这些面试题是来自阿里、腾讯、字节等一线互联网大厂最新的面试资料，并且有阿里大佬给出了权威的解答，刷完这一套面试资料相信大家都能找到满意的工作。

成为一个Python程序员专家或许需要花费数年时间，但是打下坚实的基础只要几周就可以，如果你按照我提供的学习路线以及资料有意识地去实践，你就有很大可能成功！
最后祝你好运！！！

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

深知大多数初中级Python工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

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

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

如果你觉得这些内容对你有帮助，可以添加下面V无偿领取！（备注：python）
[外链图片转存中…(img-gfjZcpUY-1710890566590)]