PYTHON – Exclui arquivos de logs de transação

 

Este é um novo capítulo dentro do blog, falaremos um pouco, ou melhor, colocaremos a mão na massa com relação a programação.
A linguagem escolhida é Python. Mas, porque Python?

Python hoje está em ascensão devido a sua versatilidade em implementação, facilidade de aprendizado, facilidade com que se pode administrar servidores tanto Windows quanto Linux, criação de rotinas, padronização de processos com “robôs” e claro, sua grande interação com as novas tecnologias como Spark, Hadoop, pacotes de data science, inteligência artificial entre outros… No link você encontra 10 motivos para aprender Python 10 Motivos para aprender Python.

Não vou entrar em maiores detalhes, pois, nosso intuito é mão na massa. Quem tiver interesse em saber sobre instalação, realizei um post sobre onde pode ser acessado através do link Instale Python.

Vamos lá!!

Estudo de caso:

Administro alguns servidores de banco de dados onde nesses servidores possuo algumas dezenas de bases que tem habilitado o backup de log de transação. Para cada base de dados em cada servidor existe uma pasta que é guardado o backup de log de transação onde por dia são adicionados um total de 3000 arquivos .trn. Cada base possui sua pasta diretório e cada um deles é criado seu respectivo backup de log de transação.

Objetivo:

Automatizar a exclusão dos arquivos de log de transação (.trn) deixando apenas os arquivos do dia corrente.  Poderia deixar mais dias? Sim! Mas, em nosso caso foi definido na regra de negócio da empresa não havendo necessidade de deixar mais tempo já que os arquivos são transferidos para um servidor de backup todas as noites.

Mas Hudson, porque usar python? porque sofrer? com uma bat eu faço em 10 minutos. Com um T-SQL faço em 5 minutos a rotina. Sim, sei bem, mas, se você vê uma necessidade e pode aprender em cima dela uma nova linguagem, porque não? Muito bom motivo para apanhar aprender. E na maioria das minhas melhores experiências que tive com banco de dados foi realmente apanhando MUITO.

Sem mais delongas, vamos ao código.

Obs: Não sou especialista python, então com certeza deve haver uma forma mais fácil de realizar a tarefa, mas, a que indico a baixo funciona que é uma beleza… rs..

Tecnologias utilizadas:

  • Python 3.0;
  • Sublime; (Facilita na indentação e fatoramento de condigo).
  • Jupyter-notebook; (Excelente ferramenta para realizar testes e rodar os scripts).
  • SQL Server; (Tabelas de referencias e log).

Criaremos duas pastas que será a pasta base e a de classes. Sim, vamos encapsular o código para tentar deixar mais limpo possível e facilitar qualquer eventual manutenção. Fique a vontade para modificar a sua maneira e claro, fazer sugestões, críticas etc…

Criei em meu ambiente a pasta Python /documents/Python e dentro dela a pasta Class /documents/Python/Class.

Iremos começar com a criação das tabelas base e log no SQL Server. (Fique a vontade para testar em outros bancos, mas, lembrando que a string de conexão do Python para o BD será diferente).

01_rel

Script criação das tabelas:

CREATE TABLE [dbo].[TB_DIRETORIO](
[id] [int] IDENTITY(1,1) NOT NULL,
[Nome] [varchar](1000) NULL,
[Tipo] [varchar](50) NULL,
CONSTRAINT [PK_Diretorios] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TB_SERVIDOR](
[id] [int] IDENTITY(1,1) NOT NULL,
[Nome] [varchar](50) NOT NULL,
[Nome_Instancia] [varchar](50) NULL,
[IP] [varchar](30) NOT NULL,
[Porta_Instancia] [int] NOT NULL,
[Tipo] [varchar](50) NOT NULL,
CONSTRAINT [PK_TB_SERVIDORES] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TB_SERVIDOR_DIRETORIO](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ID_Servidores] [int] NULL,
[ID_Diretorios] [int] NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TB_SERVIDOR_DIRETORIO] WITH CHECK ADD CONSTRAINT [FK_TB_SERVIDORES_DIRETORIOS_TB_DIRETORIOS] FOREIGN KEY([ID_Diretorios])
REFERENCES [dbo].[TB_DIRETORIO] ([id])
GO

ALTER TABLE [dbo].[TB_SERVIDOR_DIRETORIO] CHECK CONSTRAINT [FK_TB_SERVIDORES_DIRETORIOS_TB_DIRETORIOS]
GO
ALTER TABLE [dbo].[TB_SERVIDOR_DIRETORIO] WITH CHECK ADD CONSTRAINT [FK_TB_SERVIDORES_DIRETORIOS_TB_SERVIDORES] FOREIGN KEY([ID_Servidores])
REFERENCES [dbo].[TB_SERVIDOR] ([id])
GO
ALTER TABLE [dbo].[TB_SERVIDOR_DIRETORIO] CHECK CONSTRAINT [FK_TB_SERVIDORES_DIRETORIOS_TB_SERVIDORES]
GO

CREATE TABLE [dbo].[TB_LOG_ARQUIVO_EXCLUIDO](
[id] [int] IDENTITY(1,1) NOT NULL,
[Nome_Arquivo] [varchar](500) NULL,
[Data_Exclusao] [datetime] NULL,
CONSTRAINT [PK_TB_LOG_ARQUIVO_EXCLUIDO] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TB_LOG_ARQUIVO_EXCLUIDO] ADD CONSTRAINT [DF_TB_LOG_ARQUIVO_EXCLUIDO_Data_Exclusao] DEFAULT (getdate()) FOR [Data_Exclusao]
GO

Vamos tomar de exemplo dois servidores:

  • Servidor: Server01 / Instancia: InstanceBD01 / IP: 192.168.10.1 / Porta: 91433 / Tipo: <Produção/homologação/teste>
  • Servidor: Server02 / Instancia: InstanceBD02 / IP: 192.168.10.2 / Porta: 91433 / Tipo: <Produção/homologação/teste>

Inserindo algumas linhas nas tabelas:

INSERT INTO [dbo].[TB_SERVIDOR]
([Nome],[Nome_Instancia],[IP],[Porta_Instancia],[Tipo])
VALUES('Server01', 'InstanceBD01', '192.168.10.1', 91433, 'Teste')
GO
INSERT INTO [dbo].[TB_SERVIDOR]
([Nome],[Nome_Instancia],[IP],[Porta_Instancia],[Tipo])
VALUES('Server02', 'InstanceBD02', '192.168.10.2', 91433, 'Teste')
GO
INSERT INTO [dbo].[TB_DIRETORIO]
([Nome],[Tipo])
VALUES ('\X$\CL01-INST01-BKP-LOG' ,'T-LOG')
GO
INSERT INTO [dbo].[TB_DIRETORIO]
([Nome],[Tipo])
VALUES ('\Y$\CL01-INST01-BKP-LOG' ,'T-LOG')
GO

Vamos relacionar as informações:

Diretório X do Server01 e diretório Y do Servidor02

INSERT INTO [dbo].TB_SERVIDOR_DIRETORIO
(ID_Servidores, ID_Diretorios)
VALUES ( 1, 1 )
GO
INSERT INTO [dbo].TB_SERVIDOR_DIRETORIO
(ID_Servidores, ID_Diretorios)
VALUES ( 2, 2 )
GO

A tabela de log será preenchida automaticamente pela rotina.

Após definido os servidores e seus respectivos diretórios vamos começar definido as classes do Python:

Conexão banco de dados

Criaremos a conexão de banco de dados para que seja buscado as informações das tabelas base e efetuar a inserção do log. Como é uma classe genérica, pode ser reutilizada em outro códigos como veremos em adiante:

class Conn_BancoDeDados:
       def connect():
           import pyodbc
           server = 'tcp:192.168.20.1,91433'   
           database = 'DB_GERENCIA'
           username = 'app_python'
           password = '123@345'
           cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER='+server

+';DATABASE='+database+';UID='+username+';PWD='+ password)
           return cnxn

 
Classe seleciona_diretorio

A classe seleciona_diretorio chamamos a classe de conexão com o banco de dados, importamos o pandas (usaremos ao longo das classes) e passamos a query com a busca dos dados de servidores e diretórios dando como saída um dataframe:

class seleciona_diretorio:

          def servidor_diretorio():
                   from Class.CONNECT import Conn_BancoDeDados
                   import pandas as pd
                   conn = Conn_BancoDeDados.connect()

                    query = """SELECT S.IP, D.NOME AS DIRETORIO FROM TB_SERVIDOR AS S 
INNER JOIN TB_SERVIDOR_DIRETORIO SD ON S.ID=SD.ID_SERVIDORES
INNER JOIN TB_DIRETORIO D ON D.ID=SD.ID_DIRETORIOS
WHERE S.TIPO = 'PRODUCAO'"""

                  src_read = pd.read_sql(query, conn)
                  return src_read

Ficamos atento com a classe a cima, pois, sua saída será tratada no código base que é a rotina que chama todas as classes alem de fazer o tratamento do dataframe.

Por agora vou explicar cada classe que será chamada no código base.

Classe quebra_extensao

Ela tem como parâmetro de entrada o nosso dataframe que nada mais é do que o caminho inteiro do diretório onde é armazenado os logs, busca cada um dos arquivos que está dentro do diretório, separa o nome do arquivo da extensão do mesmo e passa a extensão do arquivo, nome do arquivo e o caminho completo para a próxima classe tratar essa informação:

class quebra_extensao:

     def quebra(item):
         import os
         import pandas as pd
         for p, _, files in os.walk(os.path.abspath(item)):
     # QUEBRA EXTENSÃO DO ARQUIVO
         for file in files:
         path = os.path.join(p, file)
     # Explode o arquivo em cada ponto (.)
         explod_file = file.split('.')
     # Apenas o nome do arquivo
         only_name = '.'.join(explod_file[:-1])
     # Extensão do arquivo
         extension = explod_file[-1]
         extension = pd.Series(extension)

         busca_extensao.busca_trn(extension, path, file)

Classe busca_extensao

Basicamente está classe verifica se o arquivo é um arquivo .trn (arquivo de log de transação), retorna o cabeçalho do arquivo,  busca a data do cabeçalho e formata para ser comparada com a data atual. Caso a data do cabeçalho do arquivo seja diferente da data atual, chama a classe deleta_arquivo.

class busca_extensao:

   def busca_trn(extension, path, file):
         import time, datetime, os
         for extensao in extension:

             if(extensao == 'TRN'):
      # Busca informações da data no cabeçalho do diretório/arquivo
                  path = path
                  stats = os.stat(path)
                  lastmod_date = time.localtime(stats[8])
                  date_file_tuple = lastmod_date, file
      # "retira" a data do cabeçalho e realiza sua formatação
                  folder, file_name = os.path.split(date_file_tuple[1])
                  file_date = time.strftime("%m/%d/%Y", date_file_tuple[0])

      # Data atual
                  date_now = datetime.date.today()
                  date_now_year = time.strftime('%m/%d/%Y')

      # Deleta o arquivo caso seja diferente da data atual

                  deleta_arquivo.delete(path, date_now_year, file_date)
              else:
                  print(format(path) + ": ARQUIVO NÃO EXCLUIDO. EXTENSÃO DIFERENTE DE .TRN : ")

Classe deleta_arquivo

A classe deleta_arquivo faz a comparação entre as datas do cabeçalho com a data atual de sistema, remove os arquivos que não são do dia corrente e insere na tabela TB_LOG_ARQUIVO_EXCLUIDO o caminho com o nome do arquivo completo:

class deleta_arquivo:

    def delete(path, date_now_year, file_date):
        from Class.CONNECT import Conn_BancoDeDados
        import pandas as pd
        import pyodbc, os
# Copara as datas de arquivo com data atual      
        if (date_now_year != file_date):
            df_path = {'Diretorio_Arquivo' : [path]}
            df=pd.DataFrame(df_path)
# Abre conexão com banco de dados
            conn = Conn_BancoDeDados.connect()
# Remove arquivos .TRN
            os.remove(path)
            
            query = ("INSERT INTO TB_LOG_ARQUIVO_EXCLUIDO (Nome_Arquivo) VALUES (?)")
            VALUES = [path] #path #[Id,firstname,LastName,RegisterNo]

            cursor = conn.cursor()
            cursor.execute(query, VALUES)
            conn.commit()
            conn.close()
      else:
            print('ARQUIVO NÃO EXCLUIDO. DATA ATUAL: ' + path, file_date)

CODIGO BASE

Basicamente usaremos três códigos no tratamento:

Adiciona uma coluna concatenando servidor e diretório. Também adicionamos uma barra a mais a frente do nome do servidor para que seja efetuado a busca como fazemos no Windows, exemplo: \\192.168.160.1\c$.

src_read = src_read["IPDIRETORIO"] = '\\\\'+ src_read.iloc[:,0] + src_read["DIRETORIO"]

02_rel

Podemos analisar a cima que nosso dataframe está com três colunas e fizemos uma conferencia para validar se nossa variável realmente é um dataframe.

Bom, já realizamos o ETL do diretório adicionando a nova coluna com a formatação que precisávamos, podemos agora excluir as outras colunas do dataframe. A memória do seu pc agradece.

src_read = src_read.drop(['IP', "DIRETORIO"], axis=1)

03_rel

Que tal adicionar o dataframe a uma variável? Isso ajuda caso cometa algum erro no meio do caminho, não precisa importar tudo novamente, você pode voltar ao ponto do src_read.

directorys = src_read['IPDIRETORIO']

UFA…   Não acabou ainda, vamos juntar tudo para que possamos excluir os arquivos:

from Class.EXTENSION_AND_DROP_FILES import seleciona_diretorio
from Class.EXTENSION_AND_DROP_FILES import quebra_extensao
from Class.EXTENSION_AND_DROP_FILES import busca_extensao

src_read = seleciona_diretorio.servidor_diretorio()

src_read["IPDIRETORIO"] = '\\\\'+ src_read.iloc[:,0] + src_read["DIRETORIO"]

directorys = src_read['IPDIRETORIO']

def Find_files(*args):
# Lista todos os arquivos em todos os diretórios
     for item in args:
         quebra_extensao.quebra(item)

E por fim, aplicaremos a classe ao dataframe:

src_read['IPDIRETORIO'].apply(Find_files)

Agora é só conferir na tabela de log:

04_rel

Por hoje é isso.

Temos um “robô” versátil, eficaz e escalavel podendo facilmente mudar as classes para outras tarefas com poucas alterações.

Existem maneiras mais fáceis? Obviamente. Mas, qual seria a graça?

;D

cloud_big_data_0

Publicado em Administração Servidores, Python
Um comentário em “PYTHON – Exclui arquivos de logs de transação
  1. Gabi disse:

    Parabens!👏👏👏

    Curtir

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s

Arquivos
Follow SQL DATA BLOG on WordPress.com
%d blogueiros gostam disto: