备案控制台
开发者社区 数据库 文章 正文

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

2024-05-17 27
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 两个Python脚本轻松解决ETL工作：统计多个服务器下所有数据表信息(1)

正文

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:
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])


文章标签：
SQL
数据库
Python
数据安全/隐私保护
存储
关键词：
Python信息
Python服务器
Python脚本
脚本云服务器 ECS
python云服务器 ECS
老板这功能得加钱
目录
相关文章
程序员晚枫
|
2天前
|
程序员
大家来决定：python-office运行时的提示信息，要不要删除？
**摘要：** 程序员晚枫发起投票，询问是否应去除`python-office`项目运行时显示的中文提示信息，这些信息包含教程、源码链接和答疑群等。提示虽无运行影响，但显得不够专业。项目因用户咨询增加而添加此信息，作者考虑删除，因觉得与常见开源项目风格不同且其教程收费。附三张截图展示提示内容。用户可在评论区投票决定，输入“取消”或“保留”。
程序员晚枫
9 3
大家来决定：python-office运行时的提示信息，要不要删除？
小肉球不吃肉1
|
2天前
|
运维 监控 API
自动化运维实践指南：Python脚本优化服务器管理任务
本文探讨了Python在自动化运维中的应用，介绍了使用Python脚本优化服务器管理的四个关键步骤：1) 安装必备库如paramiko、psutil和requests；2) 使用paramiko进行远程命令执行；3) 利用psutil监控系统资源；4) 结合requests自动化软件部署。这些示例展示了Python如何提升运维效率和系统稳定性。
小肉球不吃肉1
23 8
一只大鸽子
|
3天前
|
监控 数据可视化 数据库
【python项目推荐】键盘监控--统计打字频率
【python项目推荐】键盘监控--统计打字频率
一只大鸽子
37 13
爱喝兽奶的熊孩子
|
3天前
|
Python 存储 数据处理
【Python数据类型的奥秘】：构建程序基石，驾驭信息之海
【Python数据类型的奥秘】：构建程序基石，驾驭信息之海
爱喝兽奶的熊孩子
12 1
NorthS
|
6天前
|
Linux Shell 测试技术
Linux服务器测试脚本集合
LemonBench是iLemonrain创作的Linux服务器性能测试工具，能一键检测系统信息、网络、CPU、内存和硬盘性能。
NorthS
7 0
孜倦与shine
|
6天前
|
JavaScript 关系型数据库 MySQL
Python实战：从猎聘网获取职位信息并存入数据库
Python实战：从猎聘网获取职位信息并存入数据库
孜倦与shine
36 1
技术蜜糖罐
|
11天前
|
存储 供应链 开发者
Python列表打造简易进销存系统：轻松管理库存信息！
Python列表打造简易进销存系统：轻松管理库存信息！
技术蜜糖罐
19 0
愿天堂没有BUG（公众号同名）
|
19天前
|
API 网络安全 数据处理
使用Python调用远程服务器上的依赖
通过使用SSH远程执行、远程API调用和Jupyter Notebook等方法，我们可以有效地调用远程服务器上的依赖，简化本地环境配置，提高计算效率。这种方法在处理大规模数据、复杂计算任务时尤为有用。希望本文能为您提供一些有用的参考，助您在开发过程中更加高效地利用远程依赖。
愿天堂没有BUG（公众号同名）
8 1
宇宙超级无敌霸王龙
|
1天前
|
弹性计算 缓存 安全
云服务器 ECS产品使用问题之如何解决阿里云幻兽帕鲁服务器游戏版本不兼容
云服务器ECS（Elastic Compute Service）是各大云服务商阿里云提供的一种基础云计算服务，它允许用户租用云端计算资源来部署和运行各种应用程序。以下是一个关于如何使用ECS产品的综合指南。
宇宙超级无敌霸王龙
134 0
宇宙超级无敌霸王龙
|
1天前
|
弹性计算 Linux 云计算
云服务器 ECS产品使用问题之如何解决阿里云幻兽帕鲁服务器转移后无法进入的问题
云服务器ECS（Elastic Compute Service）是各大云服务商阿里云提供的一种基础云计算服务，它允许用户租用云端计算资源来部署和运行各种应用程序。以下是一个关于如何使用ECS产品的综合指南。
宇宙超级无敌霸王龙
130 0

热门文章

最新文章

  • 1
    玩转ECS第8讲 | 服务器迁移中心SMC最佳实践及新特性介绍
  • 2
    无法初始化SFTP协议。主机是SFTP服务器吗
  • 3
    Linux【环境部署 01】NTP时间服务器搭建及Linux+Windows客户端使用（一篇学会使用NTP服务）
  • 4
    阿里云通用算力型与新品经济型云服务器价格参考（ESSD Entry和ESSD云盘）
  • 5
    阿里云通用算力型u1实例云服务器优势、价格及计算能力和使用场景介绍
  • 6
    阿里云服务器8核64G配置多少钱？带宽和系统盘最新收费价格分享
  • 7
    如何根据阿里云服务器 选择香港和大陆地区，他们的区别在哪里
  • 8
    四条命令快速在Ubuntu16.04上配置DNS服务器
  • 9
    Thrift第七课 C#语言测试C++服务器
  • 10
    服务器设计笔记(5)-----分享几个线程安全的容器
  • 1
    Python random模块（获取随机数）常用方法和使用例子
    27
  • 2
    【Python小技巧】通过实例说明推导式，条件表达式和Lambda函数
    31
  • 3
    Python中魔术方法汇总
    31
  • 4
    一键实现数据采集和存储：Python爬虫、Pandas和Excel的应用技巧
    47
  • 5
    Python tqdm使用方法
    46
  • 6
    Python类(class)中self的理解
    22
  • 7
    Pandas基本操作：Series和DataFrame（Python）
    152
  • 8
    NumPy的基本操作（Python）
    37
  • 9
    各种排序算法及Python源代码
    32
  • 10
    Python类定义：从小白到专家的旅程
    11

    • 相关课程

    更多
  • Python Web开发基础
  • Python开发基础入门
  • Python常用数据科学库
  • Python网络爬虫实战
  • Python完全自学手册图文教程
  • Python基础快速入门实战教程

    • 相关电子书

    更多
  • From Python Scikit-Learn to Sc
  • Data Pre-Processing in Python:
  • 双剑合璧-Python和大数据计算平台的结合

    • 相关实验场景

    更多
  • 配置流程编排实现根据天气情况播放歌曲
  • 用Python画圣诞树
  • 使用Python完成RDS数据开发及分析
  • 玩转MaxCompute SQL! 30分钟搞定数据分析挖掘
  • Github实时数据分析与可视化
  • 基于Kibana Discover筛选数据，自由搜索航班信息
    • 下一篇
    2024年阿里云免费云服务器及学生云服务器申请教程参考