Como podemos manipular os dados utilizando as operações CRUD MySQL com python? Agora, iremos criar os métodos de acesso aos dados com operações CRUD.
Você pode acessar o primeiro artigo desse projeto em: Conectando ao MySQL com Python
O que é CRUD?
CRUD é o acrônimo utilizado para referenciar as operações básicas para acesso (geralmente) a banco de dados. Presentes em qualquer API.
Através delas conseguimos manipular e gerenciar os dados persistidos em um SGBD, de maneira eficaz.
A granularidade dos dados manipulados vai depender do contexto do seu programa.
Se precisar entender o que é uma API, sugiro nossa playlist no youtube sobre o assunto: Simplificando API.
CRUD é um acrônimo para: Create, Read, Uptade e Delete. Essas são as operações básicas de qualquer programa de acesso aos dados de uma aplicação.
Apesar de ser algo bem intuitivo, vamos ilustrar o que seria cada operação.
CREATE – é ato de criar um objeto utilizando o programa.
Esse objeto pode ser uma instância de uma tabela, uma nova tabela no banco de dados, um formulário, ou qualquer outro objeto de dados manipulado pelo seu programa.
READ – Ato de recurperar os dados para leitura. (Bem simples)
UPDATE – Ato de atualizar um objeto atráves do programa. Novamente, o objeto depende do contexto do programa. Exs: linha, tabela, agregação, arquivos….
DELETE – Ato de remover o objeto de dados através do programa
Recaptulando o código da classe MySQLDatabase
Relembrando que estamos utilizando o paradigma de orientação a objeto. Sendo assim, nossa classe até agora está como segue:
import os
import mysql.connector as mysql_connector
from dotenv import load_dotenv
load_dotenv()
class MySQLDatabase:
def __init__(self):
self._host = os.getenv("HOST")
self._username = os.getenv("USERNAME")
self._passwd = os.getenv("PASSWD")
self._database = os.getenv("DATABASE")
self.conn = self._connecting()
def _connecting(self):
return mysql_connector.connect(
user=self._username,
password=self._passwd,
host=self._host,
database=self._database,
)
def get_database_name(self):
return self._database
def get_database_user(self):
return self._username
def closing(self):
if self.conn.is_connected():
self.conn.close()Além disso, temos os métodos get_database_tables() e _querying() descritos no primero tutorial.
Para faciliar nossa vida, vou deixar o código do método _querying() aqui. Se precisar checar a explicação pode acessar o artigo que já foi mencionado antes.
Conectando ao MySQL com Python
def _querying(self, query: str):
if (not self.conn.is_connected()) or self.conn is None:
self.conn = self._connecting()
cursor = self.conn.cursor(dictionary=True)
cursor.execute(query)
result = cursor.fetchall()
cursor.close()
return resultVamos começar pelo Getter – Tables!
O que é um Getter em programação? Bom…. get está associado a “pegar”, em recuperar algum recurso do servidor/programa.
Em nosso caso getter são todos os métodos de uma classe que recuperação informação.
Na outra mão estão os setters. Esses “setam”, adicionam um novo dado utilizando o programa.
Esses termos estão muito associados a POO, com manipulação de dados dentro do programa.
Contudo, em nosso caso temos apenas um getter. Os demais se encaixam no que chamamos de métodos READ (CRUD).
Vamos entender cada parte do método…
O papel desse métod está em retornar as tabelas de um banco de dados específico. O banco de dados no caso, foi definido durante a conexão.
def get_database_tables(self):
dict_of_tables = self._querying('SHOW tables;')
print(f'List of tables on {self._database} database!\n')
for table in dict_of_tables:
print(' '.join(['-', table['Tables_in_'+self._database]]))
return dict_of_tablesPara isso, utilizamos o método privado _querying() passando como parâmetro a query SHOW tables;
Após a execução da query pelo cursos no método, recebemos o dict (dicionário) com os dados resultantes.
Depois disso, temos apenas um tratamento dos dados para melhor visualização do usuário. Printamos os dados e retornamos o dict.
Sim… você não precisa usar os dois. Aqui ficou para fins didáticos. Em seu caso, veja qual faz mais sentido, printar ou retornar o dict.
Ok… essa parte é para retornar infos adicionais sobre o banco de dados. Agora, vamos para o CRUD!
Vamos codar…. Criando o CRUD com Python
A granularidade dos dados que iremos manipular com nossas operações CRUD consite em linhas, instâncias de uma tabela do BD presente no MySQL.
Para as operações de leitura o método anterior, _querying(), é o bastante.
Contudo, quando precisamos utilizar condicionais, para busca ou atualização dos dados, precisaremos criar outro método. Neste caso, o _execute_query_with_dict().
Além de outros métodos de suporte as 4 operações básicas do CRUD.
Métodos de suporte ao CRUD
Como passamos parâmetros de uma condição em SQL utilizando o python? Utilizando um dict. Para isso vamos utilizar o próximo método.
O primeiro que iremos descrever será o _execute_query_with_dict(). Para que possamos criar filtros com SQL, o python precisa entender o que está sendo passado.
Sendo assim, imagine a query simples a seguir:
SELECT name, email FROM users WHERE ID = 8;Agora estamos passando o valor 8 como elemento de filtro para encontrar o usuário dentro da tabela users.
Você vai perceber que o método só pode ser chamado por outro método dentro da classe. Isso acontece pelo modificar e acesso na assinatura do métodos.
Usamos o underscore (_) para designar que um método está protegido.
Como atualizar os dados do BD pelo Python?
Sendo assim, quando esse método de suporte (da nossa classe) receber seus parâmetros ele irá alocar um cursor para essa query.
Posteriormente, vai executar a query enviando os parâmetro através do cursor.
#support methods
def _execute_query_with_dict(self, query: str, attr: dict):
try:
cursor = self.conn.cursor(dictionary=True)
cursor.execute(query, params= attr)
except TypeError as err:
raise (f'An error occur during the insert '
f'operation on {self._database}\n Message:'
f'{err}')Mas como criamos o parametro de dicionário que será usado pelo método?
Podemos passar esse parâmetro na “mão”, como no exemplo abaixo. Esse será usado em outro método mais a frente.
update_query = "UPDATE users SET name = %(name)s, email = %(email)s WHERE id = %(id)s"Neste caso, temos as chaves e placeholders que vão definir onde a lib deve buscar os dados passados por parâmetro e a quais atributos vincular.
Chaves e Placeholders na Query com Python
Observe esse trecho da query acima. As chaves aqui designadas são: name e email. Os placeholders devem possuir um padrão atrelado ao nome da chave.
Esse padrão é: %(_chave_)s, onde _chave_ deve ser substituido pelo nome do atributo.
name = %(name)s, email = %(email)s Extamente como no exemplo.
Para criar o placeholder do id que será buscado, é feito o mesmo processo. id = %(id)s.
Dessa forma, quando passarmos e executarmos a query pelo cursor, a lib conseguirá identificar o que deve substituir para que a query seja executada com sucesso.
A outra maneira está na criação de um método que identifica as chaves e define os placeholders.
OBS: Se você estiver lidando com um cenário específico pode definir os parâmetros e requisitos do seu metódo de acordo com a tabela.
Mas nesse caso, você precisará personalizar um novo método para cada tabela.
Agora… vamos entender o método!
def _returning_key_list_and_placeholders(self, attr:dict):
keys_list = ', '.join([key for key in attr.keys()])
placeholder = ', '.join([f'%({key})s' for key in attr.keys()])
return keys_list, placeholderNeste caso, vamos receber um dicinário com as chaves e seus valores relacionados. Após a transformação dos dados, o método retornará duas strings.
A key_list, que são os atributos; e a string placeholder, que são a estrutura que falamos anteriormente.
Vamos usar um exemplo pra facilitar o entendimento. Com o dict abaixo, criaremos uma nova instância no banco de dados.
O método a ser chamados (descrito mais a frente no artigo) será o create_line(). Além do dict, será passado ainda a tabela a ser atualizada.
Por sua vez o método CRUD create_line() chama o método, que estamos explicando, _returning_key_list_and_placeholders. O resultado desse método será:
keys_list = 'customerNumber, customerName, contactLastName, contactFirstName, phone, addressLine1, city, postalCode, country, salesRepEmployeeNumber, creditLimit'
placeholder = '%(customerNumber)s, %(customerName)s, %(contactLastName)s,
%(contactFirstName)s, %(phone)s, %(addressLine1)s, %(city)s, %(postalCode)s,
%(country)s, %(salesRepEmployeeNumber)s, %(creditLimit)s'Com essas duas strings poderemos criar a QUERY SQL. Logo o método _execute_query_with_dict irá receber a query e o dict com os dados a serem persistidos.
Sobre essa passo falaremos na sessão CRUD create_line.
Is on Database? Método de suporte
Antes de continuarmos com o CRUD, ha outros dois métodos de suporte utilizados pelos metodos principais da nossa classe: _is_on_database e _get_list_of_databases_tables().
Vamos lá… _is_on_database é intuitivo. No trecho de código abaixo vocề vai perceber que estamos checando se a tabela existe no banco de dados (ao qual estamos conectados).
def _is_on_database(self, table_name:str) -> None:
if table_name not in self._get_list_of_database_tables():
raise f'Table not found in the {self._database} database'Simplismente checamos se o nome da tabela (table_name) está ou não na lista de tabelas do banco de dados usando o método _get_list_of_database_tables.
Esse método, _get_list_of_database_tables, foi descrito no artigo anterior: Conectando ao MySQL com Python
Listando tabelas do banco de dados
Agora, vamos entener a listagem das tabelas. O que na verdade é bem simples.
Apensa enviamos a string “SHOW tables;” como parâmetro do nosso método _querying().
Lembre-se o método _querying_with_dict() é utilizado quando queremos enviar dados específicos para nossa tabela.
No caso, em situações como atualização, removação, inserção ou modificação da instância no BD.
Ainda também em casos de buscas por macth por algum atributo da tabela (ex: ID).
def _get_list_of_database_tables(self):
dict_of_tables = self._querying('SHOW tables;')
return [table['Tables_in_' + self._database] for table in dict_of_tables]Uma vez recuperada a lista de tabelas, vamos concatenar os nomes das tabelas usando a key ‘Tables_in_nomedb’ no dict recebido.
CREATE – CRUD MySQL Python
Muito bem… chegamos na inserção de linhas em uma tabela. Na linguagem CRUD, o método CREATE.
Vamos entender o método!
def create_line(self, attr: dict, table_name: str):
self._is_on_database(table_name) #checando a existência da tabela
key_list, placeholders = self._returning_key_list_and_placeholders(attr)
insertion_query = f"INSERT INTO {table_name} ({key_list}) VALUES ({placeholders})"
try:
self._execute_query_with_dict(insertion_query, attr)
except Exception as err:
raise f'Message: {err}'
self.conn.commit()
return TrueLembra daquelas duas variáveis (strings) com os atributos e o placeholder? Então, elas serão usadas aqui para construir a QUERY, com esse pedaço de código:
insertion_query = f"INSERT INTO {table_name} ({key_list}) VALUES ({placeholders})"Faremos o tratamento de exceções na execução da query, ou seja, na chamada do metodo _execute_query_with_dict.
Caso haja algum erro na construção ou execução da query, o except recebe a exceção através da variável err e retorna um raise para o usuário.
raise f'Message: {err}'Muito bem, feito! Na verdade não…
Como lidamos com transações em banco de dados, é necessário que ao final de toda ação seja realizado um commit.
Em outras palavras, você confirma com o BD que as modificações feitas devem duráveis. Assim, uma noa instância é inserida.
Isso acontece chamando o método commit() que está associado a conexão (não ao cursor). Neste caso, o trecho abaixo:
self.conn.commit() # conexão relacionada a classe READ – CRUD MySQL Python
OK, e quando precisamos recuperar dados através da leitura? O READ, das operações CRUD, pode estar relacionada a qualquer granularidade dos dados.
No método desc_table(), vamos verificar os metadados de uma tabela qualquer. Logo ao passarmos a tabela customer como parâmetro teremos como resultado as infos de campos e tipos de dados associados.
def desc_table(self, table):
return self._querying(' '.join(['DESC', table]))Em nosso exemplo (contido no main) recuperamos os metadados da tabela customers e printamos utilizando o método ppprint.
O pprint é muito utilizado em estruturas de dicionários e JSON, deixando-a mais apresentavel com a estrutura hierárquica que os dados possuem.
O método read_table(), recebe o nome da tabela alvo da leitura, verifica se a mesma existe no BD, para então chamar o método get_lines_from_table.
def read_table(self, table_name):
self._is_on_database(table_name)
return self.get_lines_from_table(table=table_name)Dentro o get_lines_from_table que criamos a query para leitura dos dados da tabela.
Resumindo, antes de chamar o método _querying, que cria o cursos e executa a query, verificamos se o usuário passou um número máximo de linhas a serem recuperadas.
Como isso? Em SQL podemos usar a cláusula LIMIT para definir o máximo de instâncias retornadas. Por exemplo:
SELECT * FROM customers LIMIT 2; Assim, apenas duas instâncias (linhas), contendo todos os atributos, da tabela customers são retornadas.
Após a execução do método _querying e recebimento dos dados na variável result, há duas ações.
Printamos na tela os dados do dict que recebemo e retornamos a variável. Perceba que está abordagem foi utilizada com fim didático.
Você irá verificar qual a mais adequada para seu cenário.
# método de leitura
def get_lines_from_table(self, table, limit=False, number_of_lines=10):
query = ' '.join(['SELECT * FROM', table])
query = query if limit is False else ' '.join([query, 'limit', str(number_of_lines)])
result = self._querying(query)
for element in result:
keys = element.keys()
for k in keys:
print(k+':', element[k])
print('\n')
return resultO que falta agora?
A atualização dos dados de alguma tabela e ainda a remoção de dados. Continuaremos com a atualização na próxima sessão.
UPDATE – CRUD MySQL Python
A atualização geralmente possui uma condicional associada. Em nosso caso, para simplificação iremos implementar apenas a busca pelo ID.
Fique a vontade para criar suas varições, busca por nome, cidade e qualquer outro atributo que seja pertinente.
def update_users_by_id(self, id, data: dict):
"""
Vamos receber um conjunto prédefinido de campos a serem atualizados
:param id:
:param data:
:return:
"""
data["id"] = id
update_query = "UPDATE users SET name = %(name)s, email = %(email)s WHERE id = %(id)s"
self._execute_query_with_dict(update_query, data)
self.conn.commit()A ideia é similar ao que já fizemos anteriormente.
Precisamos dos placeholders para criar a query, além de chamarmos o método _execute_query_with_dict() para que possamos passar os parâmetros da nossa condiconal.
OBS: Para melhorar esse método, adicione o tratamento de exceções na execução da query.
Relembrando, como estamos fazendo uma modificação, precisamos “commitar” a ação ao final do método.
DELETE – CRUD MySQL Python
Chegamos ao último método da nossa classe com operações CRUD.
Tanto para update quanto para o delete a ideia é a mesma. O que iremos modificar são as ações principais.
No UPDATE, utilizamos a query: UPDATE <table> set <atributos> WHERE <condicional>
Aqui precisamos, deletar a instânca que for compatível com a condicional proposta. Sendo assim, teriamos a sequinte query:
DELETE FROM <table> WHERE <condicional>Logo, fazendo essa modificação teremos a mesma lógica de antes.
Primeiro, verifico se a tabela existe, crio a query, executo com o método _execute_query_with_dict(), para por fim comitar a ação.
OBS: Novamente, para melhorar o código adicione o tratamento de exceções para execução da query.
def delete_instance(self, table_name: str, condition: str, value):
self._is_on_database(table_name)
delete_query = f"DELETE FROM {table_name} WHERE {condition} = %s"
self._execute_query_with_dict(delete_query, value)
self.conn.commit()Feito isso, temos todas as operações básicas de acesso aos dados persistidos no banco de dados MySQL.
Ok, e como testamos tudo isso?
Rodando o Programa principal – Main
Com certeza você quer ver esse código rodando. Então, vamos para um código principal que teste nossos métodos.
Aqui, basicamente importamos a classe e usamos os métodos disponíveis nela. A conexão com o BD e os demais dados estão protegidos.
A beleza dessa abordagem é que você só pode usar aquilo que foi implementado na classe.
Não deixando brecha para utilização de outros métodos disponíveis no lib pelo usuário através do main.
import pprint
from Database import MySQLDatabase
if __name__ == '__main__':
print('Running')
try:
db = MySQLDatabase()
print(f"Starting the connection...\nStatus: {db.conn.is_connected()}")
except ConnectionError as err:
raise f"Error during the connection. Message: {err}"
tables = db.get_database_tables()
print("\n-> Sample of the database table")
db.get_lines_from_table('customers', limit=True, number_of_lines=1)
description = db.desc_table('customers')
pprint.pprint(description)
new_instance = {"customerNumber": 105,
"customerName": "Atelier",
"contactLastName": "Schmitt",
"contactFirstName": "Carine",
"phone": "40322555",
"addressLine1": "54 rue Royale",
"city": "Nantes",
"postalCode": "44000",
"country": "France",
"salesRepEmployeeNumber": 1370,
"creditLimit": 21000.00}
if db.create_line(new_instance, 'customers'):
print("Inserção bem sucedida")
db.delete_instance('customers', 'customerNumber', [105])Acredito que o código ficou auto-explicativo. Sendo assim, caso você tenha alguma dúvida passe em nosso canal e deixe nos comentários.
Por lá conseguimos sanar as dúvidas dos projetos criados.
E você que gosta de assitir ao vídeo do projeto com hands on. Em outras palavras, vamos codar juntos o código. Seguem os vídeos que contemplam esse artigo.
Até a próxima!
Veja Mais:
Conectando ao MySQL com Python
Python: minhas portas TCP e UDP abertas
Projeto para Portifólio: Sua primeira API com Python e MySQL
Principais Bibliotecas Python para Data Science: Manipulação e Visualização
Videos do projeto no canal Simplificando Redes
Juliana Mascarenhas
Data Scientist and Master in Computer Modeling by LNCC.
Computer Engineer
