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

最新推荐文章于 2024-05-21 21:44:36 发布
最新推荐文章于 2024-05-21 21:44:36 发布
阅读量411 收藏 9
点赞数 3
分类专栏： 程序员 文章标签： python etl 服务器
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/2401_83704192/article/details/138061899
版权

移除系统库

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:

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 – 索引及主键信息

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

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

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

img

img

img

img

img

img

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

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

如果你觉得这些内容对你有帮助，可以扫码获取！！！（备注：Python）

                   -- 索引及主键信息

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

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

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

[外链图片转存中…(img-4Xc7NAr3-1713750445387)]

[外链图片转存中…(img-ZZ1bUUB2-1713750445388)]

[外链图片转存中…(img-vr7uoTnw-1713750445388)]

[外链图片转存中…(img-jmmfhW58-1713750445389)]

img

img

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

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

如果你觉得这些内容对你有帮助，可以扫码获取！！！（备注：Python）

2401_83704192
关注
  • 3
    点赞
  • 踩
  • 9
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Data_Ingestion_ETL:一个代表python数据摄取方法的存储库
04-10
公司使用数据管道来获取原始数据（主要从服务器日志文件中提取数据），将这些数据转换为适当的格式（转换），最后进行编辑并将其推送到一个或多个数据库中。 这个过程称为ETL，在此存储库中，我们希望涵盖该领域中...
ETL工具、脚本和数据迁移
01-30
随着信息量的不断增长，企业数据仓库的数据量也随着日常生产和业务处理的增长而不断增加，这随之对数据仓库的性能和存储容量提出了更高的要求。IBM DB2数据库以其特有的数据库分区技术和数据压缩技术，为企业数据量...
两个Python脚本轻松解决ETL工作：统计多个服务器下所有数据表信息
热门推荐
努力让自己发光，对的人才能迎着光而来
01-11 1万+
在ETL相关工作中，将数据库中数据的ER结构整理为Excel可谓是一件投入产出比极低的、毫无技术含量的体力活， 尤其是当你有上百张表的ER结构都要整理成word时，简直是在燃烧生命。 大把的时间浪费在了低价值的事情上，是对时间这种资源的极大浪费。 生命短暂，更多的时间应该投入到更有意义的事情上。 ...
两个Python脚本轻松解决ETL工作：统计多个服务器下所有数据表信息(1)
m0_60721649的博客
04-18 812
self.wb.cell(row=1, column=6, value=‘数据表总大小(MB)’)如果有多个服务器用逗号隔开。self.wb.cell(row=1, column=7, value=‘使用大小(MB)’)self.wb.cell(row=1, column=7, value=‘使用大小(MB)’)self.wb.cell(row=1, column=1, value=‘服务器地址’)self.wb.cell(row=1, column=1, value=‘服务器地址’)
两个Python脚本轻松解决ETL工作：统计多个服务器下所有数据表信息(2)
2401_83704159的博客
04-22 900
item = []
zilliqa-etl:用于Zilliqa区块链数据的ETL（提取，转换和加载）作业的Python脚本
04-06
Zilliqa ETL 概述 Zilliqa ETL允许您在Google Cloud Platform中设置ETL管道，以将Zilliqa区块链数据提取到BigQuery中。 它带有用于将Zilliqa数据导出到按天划分的JSON换行符分隔的文件中。 您可以在立即查询数据。 ...
Python库 | lime_etl-0.1.13-py3-none-any.whl
04-23
资源分类：Python库 所属语言：Python 使用前提：需要解压 资源全名：lime_etl-0.1.13-py3-none-any.whl 资源来源：官方 安装方法：https://lanzao.blog.csdn.net/article/details/101784059
oracle数据同步到Greenplum的python脚本
08-31
DataBase:判断表是否存在、表或分区表是否有数据、是否为分区表、数据导出、表分区遍历、指定分区表导出等方法 py_main：主程序目录： Main_Mem.py：主程序目录： 运行：python3 Main_Mem.py ProcessLog1 ...
使用Python操作excel单元格——在单元格中插入公式
xll_007的博客
05-16 368
使用Python操作excel单元格——在单元格中插入公式。 通过使用Python的openpyxl库，来操作excel单元格，在单元格中插入公式的操作。把学习的过程分享给大家。大佬勿喷！
geopandas快速入门报错1
weixin_45213317的博客
05-19 174
报错：CRSError: Invalid projection: EPSG:4326: (Internal Proj Error: proj_create: no database context specified)解决方法：重新下载适合自己Python版本的pyproj安装。
Gradio 案例——将 dicom 文件转为 nii文件
蒋含竹的博客
05-15 363
Gradio 的WEB界面案例：利用 SimpleITK 库，将 dicom 文件转为 nii文件
jQuery知识点（二）
shsjssnn的博客
05-21 471
1.获取节点： 通过各种选择器或选择器的组合来获取想要的网页元素。 2.创建节点： 元素节点通过工厂函数$()直接创建，文本节点和属性节点可以随着元素节点创建时一起创建，也可以通过先创建元素节点，再使用操作内容和属性的方法产生。 3.插入节点： append()、prepend(): 是父元素的方法 appendTo()、prependTo(): 是要插入的节点的方法 before()、after(): 是要插入
使用XPath来解析网页数据
2301_80263861的博客
05-18 743
首先是定位到超链接为“link4.html”的 a 标签，然后向上翻一级，也就是到了 li 元素，然后再查找这个 a 标签对应的 li 的 class 属性值，即最后的输出为[‘item-1’]第一种是访问class属性为“item-0”的 li 标签下的文字，但是在这行代码里面，li 标签的下一级是 a 标签，没有文字，只有换行符，所以输出也就是['\n ']获取class属性值含 “li” 和 name 属性值为 “item” 的 li 标签下的 a 标签的文字部分。
Python slice() 使用方法及示例说明
ym62033的博客
05-15 206
处。
Python常用标准库模块及查询方法
par@ish的博客
05-21 596
也可以通过python官网查看各个版本自带的标准模块及用法：https://docs.python.org/3.11/library/index.html。Python 自带了许多标准库（模块），这些模块在安装Python时就已经包含在内，无需额外安装即可使用。这只是Python标准库中的一部分模块，实际上Python的标准库非常丰富，覆盖了网络编程、文本处理、数据压缩、GUI编程等多个领域。
python EEL + vue3.js 简单示例
bigcarp的专栏
05-15 193
eel官方示例中暴露的js函数是全局函数，vue中的自定义函数作用域通常都是组件范围内。要让eel.js调用，需要将其升为全局可用。一般方法有 app.config.globalProperties 或 mixin等。
Python代码：七、小数化整数
m0_61834747的博客
05-16 212
日常生活中我们会遇到很多小数，但是有的人不喜欢小数，因此会用四舍五入的方式将其去掉。在Python中我们更加简单，可以利用强制类型转换将小数转变成整数，请你试一试。
Python3 笔记：IDLE的几个基本设置
最新发布
Catherine_CSDN_的博客
05-21 78
安装Python后IDLE是默认先打开交互式窗口的，如果需要的话是可以更改为打开编辑窗口，也可以设置窗口大小。
用python写一个ETL抽数脚本
05-15
好的，下面是一个简单的Python ETL（抽取、转换、加载）脚本示例： ```python import pandas as pd import sqlite3 # 抽取数据 data = pd.read_csv('data.csv') # 转换数据 data['new_column'] = data['old_...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值