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

简介: 两个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])


相关文章
|
1天前
|
Go Python
go语言调用python脚本
go语言调用python脚本
4 0
|
2天前
|
存储 区块链 Python
怎么把Python脚本打包成可执行程序?
【6月更文挑战第3天】最近根据用户提的需求用python做了一个小工具,但是在给客户使用的时候不能直接发送python文件,毕竟让客户去安装python环境,那就离了大谱了。所以这时候就需要把多个py文件带着运行环境打包成EXE可执行文件。
8 1
|
3天前
|
JSON 数据格式 Python
python3 服务端使用CGI脚本处理POST的Json数据
python3 服务端使用CGI脚本处理POST的Json数据
20 6
|
14天前
|
Python Windows
一步步教你将包含其他文件的 Python 脚本等打包成 EXE
最近我编写了一个Python脚本,该脚本需要依赖两个同级目录下的文件才能正常运行。然而,当我将脚本打包成EXE程序后,必须将这两个文件放在EXE文件的同级目录下才能正常执行。为了简化部署,我希望能将这两个文件一起打包到EXE文件中,这时候该怎么办呢?
|
14天前
|
存储 区块链 Python
怎么把Python脚本打包成可执行程序?
最近根据用户提的需求用python做了一个小工具,但是在给客户使用的时候不能直接发送python文件,毕竟让客户去安装python环境,那就离了大谱了。所以这时候就需要把多个py文件带着运行环境打包成EXE可执行文件。
怎么把Python脚本打包成可执行程序?
|
16天前
|
API 网络安全 数据处理
使用Python调用远程服务器上的依赖
通过使用SSH远程执行、远程API调用和Jupyter Notebook等方法,我们可以有效地调用远程服务器上的依赖,简化本地环境配置,提高计算效率。这种方法在处理大规模数据、复杂计算任务时尤为有用。希望本文能为您提供一些有用的参考,助您在开发过程中更加高效地利用远程依赖。
|
22天前
|
XML 数据格式 Python
Python自动化脚本编写技巧
本文介绍了Python自动化脚本的编写技巧:选择合适的库(如os, requests, BeautifulSoup)以简化编程,利用循环和条件语句实现流程控制,通过函数和模块提高代码的可读性和可维护性,使用异常处理保证脚本稳定性,以及借助日志记录进行问题追踪。通过这些方法,可以编写出高效、稳定的自动化脚本。
|
25天前
|
网络协议 数据格式 Python
Python进阶---HTTP协议和Web服务器
Python进阶---HTTP协议和Web服务器
24 4
|
20小时前
|
弹性计算
阿里云ECS使用体验
在申请高校学生免费体验阿里云ECS云服务器后的一些使用体验和感受。
|
1天前
|
弹性计算
阿里云ECS的使用心得
本文主要讲述了我是如何了解到ECS,使用ECS的一些经验,以及自己的感悟心得