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

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)

获取字段的行数,+2表示Excel的行数

excel_line = len(lists) + 2

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

count = 3

wb = op.Workbook()

ws = wb.create_sheet(index=0)

table_name = f’所统计数据表:({database[table]})’

ws.cell(row=1, column=1, value=table_name)

ws.cell(row=2, column=1, value=‘中文名称’)

ws.cell(row=2, column=2, value=‘数据库字段名称’)

ws.cell(row=2, column=3, value=‘是否为主键’)

ws.cell(row=2, column=4, value=‘字段类型’)

ws.cell(row=2, column=5, value=‘字段长度’)

ws.cell(row=2, column=6, value=‘是否多值’)

ws.cell(row=2, column=7, value=‘索引名称’)

ws.cell(row=2, column=8, value=‘数据样本’)

ws.cell(row=2, column=9, value=‘应用说明’)

整体格式

row_range = ws[1:excel_line]

for row in row_range:

for cell in row:

cell.font = Font(name=“等线”, size=12, bold=False, italic=False,

color=“00000000”) # name=字体名称,size=字体大小,bold=是否加粗,color=字体颜色

cell.alignment = Alignment(horizontal=“center”, vertical=“center”, wrap_text=True) # 字体上下左右居中

side1 = Side(style=“thin”, color=“00000000”)

side2 = Side(style=“thin”, color=“00000000”)

cell.border = Border(left=side1, right=side1, top=side2, bottom=side2) # 边框

设置列宽

ws.column_dimensions[‘A’].width = 14

ws.column_dimensions[‘B’].width = 16

ws.column_dimensions[‘C’].width = 14

ws.column_dimensions[‘D’].width = 14

ws.column_dimensions[‘E’].width = 14

ws.column_dimensions[‘F’].width = 14

ws.column_dimensions[‘G’].width = 20

ws.column_dimensions[‘H’].width = 22

ws.column_dimensions[‘I’].width = 29

单独设置应用说明列

column = f’I3:I{excel_line}’

font1 = ws[column]

for a in font1:

for a1 in a:

a1.alignment = Alignment(horizontal=“left”, vertical=“justify”, wrap_text=True)

设置第一行格式

ws.row_dimensions[1].height = 46.5 # 行高

ws.merge_cells(‘A1:I1’) # 合并单元格

cell = ws[“A1”]

cell.font = Font(name=“等线”, size=16, bold=True, italic=False,

color=“00000000”) # name=字体名称,size=字体大小,bold=是否加粗,color=字体颜色

设置第二行格式

ws.row_dimensions[2].height = 24 # 行高

font2 = ws[‘A2:I2’]

for b in font2:

for b1 in b:

b1.font = Font(name=“等线”, size=12, bold=True, italic=False,

color=“00000000”) # name=字体名称,size=字体大小,bold=是否加粗,color=字体颜色

for i in lists:

要写入excel的数据

field_name = i[0]

key_name = i[1]

field_type = i[2]

field_length = i[3]

index_name = i[4]

将数据写入到下一行

ws.cell(row=count, column=2, value=field_name)

ws.cell(row=count, column=3, value=key_name)

ws.cell(row=count, column=4, value=field_type)

ws.cell(row=count, column=5, value=field_length)

ws.cell(row=count, column=7, value=index_name)

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

count += 1

excel_name = f’./{server_name}/{database[0]}/{database[table]}.xlsx’

wb.save(excel_name)

print(‘%s库统计完成’ % database[0])

def main(self):

服务器列表

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)

文末有福利领取哦~

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

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

👉二、Python必备开发工具

👉三、Python视频合集

观看零基础学习视频，看视频学习是最快捷也是最有效果的方式，跟着视频中老师的思路，从基础到深入，还是很容易入门的。

👉 四、实战案例

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

👉五、Python练习题

检查学习结果。

👉六、面试资料

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

👉因篇幅有限，仅展示部分资料，这份完整版的Python全套学习资料已经上传

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化学习资料的朋友，可以戳这里无偿获取

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！