Skip to main content

Banco de dados com Mysql



Python com Mysql​

Para usar o Python com Mysql vou usar o Pymsql e o container com Mysql que foi desmontrado na sessão anterior.

# Crie um ambiente virtual:
python3 -m venv mysql

# Entre no ambiente virtual:
cd mysql

# Ative o ambiente virtual:
source bin/activate

# Instale o pymsql:
pip3 install pymysql

Se você subiu o Mysql com método de autenticação para sha256_password ou caching_sha2_password, será necessário instalar o pacote abaixo:

pip3 install cryptography

Crie o arquivo do python, vou chamar de main.py:

# Crie o arquivo:
> main.py

# Crie o shebang do python:
echo '#!/usr/bin/env python3' > main.py

# Dê permissão de execução para o arquivo criado:
chmod +x main.py


Começando nosso código​

Vamos começar com algo simples no código, nao vou me atentar muito na parte de segurança, começarei com hard coded e vamos aprimorando. Vejamos uma forma simples de como conectar no banco:

main.py
#!/usr/bin/env python3

import pymysql

# Cria a conexão com o banco de dados:
conn = pymysql.connect(
    host="localhost",
    user="usuario",
    password="senha123",
    database="dbtest"
)

# Cria um cursor (objeto usado para executar os comandos SQL na conexão criada acima):
cursor = conn.cursor()

cursor.execute("SELECT DATABASE()")
print(cursor.fetchone())

cursor.close()
conn.close()

Como nosso pymysql.connect é um context manager, já que a biblioteca implementa os métodos __enter__ e __exit__, podemos usar o with, o que garante que a conexão seja fechada automaticamente ao final do bloco, com isso nao vamos precisar fechar.

main.py
#!/usr/bin/env python3

import pymysql

# Cria a conexão com o banco de dados:
conn = pymysql.connect(
    host="localhost",
    user="usuario",
    password="senha123",
    database="dbtest"
)

# Abre uma conexão com o banco e garante que será fechada automaticamente:
with conn:

    # Cria um cursor para executar comandos SQL e fecha automaticamente após o uso:
    with conn.cursor() as cursor:

        cursor.execute("SELECT DATABASE()")
        print(cursor.fetchone())


Agora vamos buscar as variáveis do arquivo .env, assim ficará mais simples quando migrarmos para container, vale lembrar que o módulo python-dotenv só será usado agora e nao deve ser levado para o container:

.env
MYSQL_ROOT_PASSWORD='root'
MYSQL_DATABASE='dbtest'
MYSQL_USER='usuario'
MYSQL_PASSWORD='senha123'
MYSQL_HOST='localhost'
TZ='America/Sao_Paulo'

main.py
#!/usr/bin/env python3

import pymysql

### Esse módulo nao será necessário quando passar para container:
from dotenv import load_dotenv
from pathlib import Path
import os

### Caminho manual para o '.env':
dotenv_path = Path('/home/fulano/Nextcloud/docker/mysql/.env')
load_dotenv(dotenv_path=dotenv_path)

# Agora você pode acessar as variáveis normalmente
db_user = os.environ("MYSQL_USER")
db_pass = os.environ("MYSQL_PASSWORD")
db_name = os.environ("MYSQL_DATABASE")
db_host = os.environ("MYSQL_HOST")

# Cria a conexão com o banco de dados:
conn = pymysql.connect(
    host=db_host,
    user=db_user,
    password=db_pass,
    database=db_name
)

# Abre uma conexão com o banco e garante que será fechada automaticamente:
with conn:

    # Cria um cursor para executar comandos SQL e fecha automaticamente após o uso:
    with conn.cursor() as cursor:

        cursor.execute("SELECT DATABASE()")
        print(cursor.fetchone())

Use os.getenv() quando você quer evitar erro se a variável não existir. Ele retorna None se a variável não estiver definida. Mas você pode usar os.environ["VAR"] quando a variável é obrigatória e você quer que o programa pare com erro (KeyError) se ela não estiver no ambiente.


Ainda podemos deixar mais simples e bonito o código:

main.py
#!/usr/bin/env python3

import pymysql

### Esse módulo nao será necessário quando passar para container:
from dotenv import load_dotenv
from pathlib import Path
import os

### Caminho manual para o '.env':
dotenv_path = Path('/home/bruno/Nextcloud/docker/mysql/.env')
load_dotenv(dotenv_path=dotenv_path)

# Cria a conexão com o banco de dados:
conn = pymysql.connect(
    host=os.environ["MYSQL_HOST"],
    user=os.environ["MYSQL_USER"],
    password=os.environ["MYSQL_PASSWORD"],
    database=os.environ["MYSQL_DATABASE"]
)

# Abre uma conexão com o banco e garante que será fechada automaticamente:
with conn:

    # Cria um cursor para executar comandos SQL e fecha automaticamente após o uso:
    with conn.cursor() as cursor:

        cursor.execute("SELECT DATABASE()")
        print(cursor.fetchone())


Criando uma tabela​

Para criar uma tabela com pymysql, você usa um comando CREATE TABLE dentro de um cursor, como no exemplo abaixo:

main.py
#!/usr/bin/env python3

import pymysql

### Esse módulo nao será necessário quando passar para container:
from dotenv import load_dotenv
from pathlib import Path
import os

### Caminho manual para o '.env':
dotenv_path = Path('/home/bruno/Nextcloud/docker/mysql/.env')
load_dotenv(dotenv_path=dotenv_path)

# Cria a conexão com o banco de dados:
conn = pymysql.connect(
    host=os.environ["MYSQL_HOST"],
    user=os.environ["MYSQL_USER"],
    password=os.environ["MYSQL_PASSWORD"],
    database=os.environ["MYSQL_DATABASE"]
)

# Abre uma conexão com o banco e garante que será fechada automaticamente:
with conn:

    # Cria um cursor para executar comandos SQL e fecha automaticamente após o uso:
    with conn.cursor() as cursor:

        cursor.execute("""
                CREATE TABLE IF NOT EXISTS produtos (
                    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                    nome VARCHAR(100) NOT NULL,
                    preco DECIMAL(10,2) NOT NULL
                )
                """)
        # Aplica no banco:
        conn.commit()


Criando tabelas no container​

Como não é comum o programador criar tabelas dinamicamente via código, porque isso mistura responsabilidades, o código deveria se concentrar na lógica da aplicação, não na estrutura do banco. Por isso, é uma prática comum já deixar o banco pronto para uso assim que o container sobe.


Vamos fazer isso criando um script SQL com os comandos CREATE TABLE e instruindo o container do MySQL a executá-lo automaticamente. Teremos uma tabela chamada produtos com os seguintes campos:

  • id: Será o 'id' do produto e esse campo será chave primária;
  • nome: Nome do produto;
  • valor: Preço do produto;

Também teremos uma segunda tabela chamada funcionarios com os seguintes campos:

  • id: Será o 'id' do funcionario e esse campo será chave primária;
  • nome: Nome do funcionário;
  • data_nascimento: Data de nascimento;

E por fim, teremos uma tabela chamada vendas com os seguintes campos:

  • id: Será o 'id' da venda e esse campo será chave primária;
  • id_funcionario: ID do funcionário que fez a venda. Deve corresponder ao ID da tabela de funcionarios;
  • quantidade: Número de vendas realizada;
  • mes: Registra em qual mês a venda ocorreu (de 1 a 12);
  • id_produto: Mostra o que foi vendido. Deve corresponder ao ID da tabela de produtos;

Crie um arquivo init.sql no mesmo diretório onde está o container, ele deve ter o seguinte conteúdo:

init.sql
CREATE TABLE IF NOT EXISTS produtos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    valor DECIMAL(10, 2) NOT NULL
);

CREATE TABLE IF NOT EXISTS funcionarios (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    data_nascimento DATE NOT NULL
);

CREATE TABLE IF NOT EXISTS vendas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    mes INT NOT NULL CHECK (mes BETWEEN 1 AND 12),
    id_produto INT NOT NULL,
    quantidade INT NOT NULL,
    id_funcionario INT NOT NULL,
    FOREIGN KEY (id_produto) REFERENCES produtos(id),
    FOREIGN KEY (id_funcionario) REFERENCES funcionarios(id)
);

FOREIGN KEY

Uma FOREIGN KEY (chave estrangeira) é uma regra de integridade referencial usada em bancos de dados relacionais para garantir que um valor em uma tabela corresponda a um valor existente em outra tabela.


Na prática, ela cria um vínculo entre duas tabelas, uma coluna em uma tabela faz referência à chave primária de outra tabela. Isso garante que os dados estejam sempre consistentes.


Agora atualize o docker-compose.yml para incluir o script sql:

    volumes:
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
      - mysql_data:/var/lib/mysql

Agora delete e suba novamente o container:

# Pare o ambiente que a gente subiu:
docker compose down -v

# Suba novamente:
docker compose up -d


Inserindo dados na tabela​

Vamos ver como inserir dados na tabela:

main.py
#!/usr/bin/env python3

import pymysql

### Esse módulo nao será necessário quando passar para container:
from dotenv import load_dotenv
from pathlib import Path
import os

### Caminho manual para o '.env':
dotenv_path = Path('/home/bruno/Nextcloud/docker/mysql/.env')
load_dotenv(dotenv_path=dotenv_path)

# Cria a conexão com o banco de dados:
conn = pymysql.connect(
    host=os.environ["MYSQL_HOST"],
    user=os.environ["MYSQL_USER"],
    password=os.environ["MYSQL_PASSWORD"],
    database=os.environ["MYSQL_DATABASE"]
)

# Abre uma conexão com o banco e garante que será fechada automaticamente:
with conn:

    # Cria um cursor para executar comandos SQL e fecha automaticamente após o uso:
    with conn.cursor() as cursor:

        # Os 'cursors' abaixo criam um pacote, que não fará nada no servidor até que 'commit()' seja usado:
        result = cursor.execute("INSERT INTO produtos (nome, valor) VALUES (%s, %s)", ("Caneta", 1.99))
        result += cursor.execute("INSERT INTO produtos (nome, valor) VALUES (%s, %s)", ("Caderno", 12.50))

        # Inserir funcionário
        result += cursor.execute("INSERT INTO funcionarios (nome, data_nascimento) VALUES (%s, %s)", ("João Silva", "1990-03-12"))
        result += cursor.execute("INSERT INTO funcionarios (nome, data_nascimento) VALUES (%s, %s)", ("Maria Oliveira", "2000-03-12"))

        print(f'Quantidade de linhas afetadas para o commit na criação dos produtos e funcionários: {result}')
        # Aplica os "executes" acima no banco:
        conn.commit()

        # Zera o nosso result:
        result = 0


        ## Agora vamos efetuar o resto do cadastro que dependia dos ID de produto e ID de funcionários:
        
        # Ober o id do João:
        cursor.execute("SELECT id FROM funcionarios WHERE nome = %s", ("João Silva",))
        id_funcionario = cursor.fetchone()[0]

        # Obter o id do produto caneta:
        cursor.execute("SELECT id FROM produtos WHERE nome = %s", ("Caneta",))
        id_caneta = cursor.fetchone()[0]

        # Obter o id do produto caderno:
        cursor.execute("SELECT id FROM produtos WHERE nome = %s", ("Caderno",))
        id_caderno = cursor.fetchone()[0]


        # Cadastrar a venda:
        result += cursor.execute("INSERT INTO vendas (mes, id_produto, quantidade, id_funcionario) VALUES (%s, %s, %s, %s)", (2, id_caneta, 3, id_funcionario))
        result += cursor.execute("INSERT INTO vendas (mes, id_produto, quantidade, id_funcionario) VALUES (%s, %s, %s, %s)", (2, id_caderno, 1, id_funcionario))

        print(f'Quantidade de linhas afetadas para o commit no insert de dados na tabela: {result}')

        # Commita no banco:
        conn.commit()
        print("Dados inseridos com sucesso.")


Inserindo dados na tabela usando dicionário​

As vezes você recebe um dicionário e quer inserir os dados desse dict na tabela, para isso vamos usar o placeholders nomeados com %(chave)s ao invés de %s.

main.py
#!/usr/bin/env python3

import pymysql

### Esse módulo nao será necessário quando passar para container:
from dotenv import load_dotenv
from pathlib import Path
import os

### Caminho manual para o '.env':
dotenv_path = Path('/home/bruno/Nextcloud/docker/mysql/.env')
load_dotenv(dotenv_path=dotenv_path)

# Cria a conexão com o banco de dados:
conn = pymysql.connect(
    host=os.environ["MYSQL_HOST"],
    user=os.environ["MYSQL_USER"],
    password=os.environ["MYSQL_PASSWORD"],
    database=os.environ["MYSQL_DATABASE"]
)

dict_produtos = {
    'valor_produto': 2.50,
    'nome_produto': "Borracha"
}

# Abre uma conexão com o banco e garante que será fechada automaticamente:
with conn:

    # Cria um cursor para executar comandos SQL e fecha automaticamente após o uso:
    with conn.cursor() as cursor:

        # Os 'cursors' abaixo criam um pacote, que não fará nada no servidor até que 'commit()' seja usado:
        result = cursor.execute("INSERT INTO produtos (nome, valor) VALUES (%(nome_produto)s, %(valor_produto)s)", dict_produtos)

        print(f'Quantidade de linhas afetadas para o commit na criação dos produtos e funcionários: {result}')
        # Aplica os "executes" acima no banco:
        conn.commit()

        print("Dados inseridos com sucesso.")

Perceba que os nomes das chaves no dicionário são diferentes dos nomes das colunas da tabela. Isso mostra que o que realmente importa é que os placeholders na consulta SQL correspondam exatamente às chaves do dicionário, a ordem dos dados não influencia, o que influencia é a ordem em (nome, valor), você deve fornecer os dados nessa ordem..


Agora vamos inserir vários dados vindos de um dict grande. Para isso vamos usar executemany e o dicionário deve ficar dentro de uma tupla.

main.py
#!/usr/bin/env python3

import pymysql

### Esse módulo nao será necessário quando passar para container:
from dotenv import load_dotenv
from pathlib import Path
import os

### Caminho manual para o '.env':
dotenv_path = Path('/home/bruno/Nextcloud/docker/mysql/.env')
load_dotenv(dotenv_path=dotenv_path)

# Cria a conexão com o banco de dados:
conn = pymysql.connect(
    host=os.environ["MYSQL_HOST"],
    user=os.environ["MYSQL_USER"],
    password=os.environ["MYSQL_PASSWORD"],
    database=os.environ["MYSQL_DATABASE"]
)

dict_produtos = (
    { 'valor_produto': 2.50, 'nome_produto': "Borracha" },
    { 'valor_produto': 3.50, 'nome_produto': "Lapis" },
    { 'valor_produto': 32.75, 'nome_produto': "grampeador" },
    { 'valor_produto': 5.60, 'nome_produto': "Corretivo" },
)

# Abre uma conexão com o banco e garante que será fechada automaticamente:
with conn:

    # Cria um cursor para executar comandos SQL e fecha automaticamente após o uso:
    with conn.cursor() as cursor:

        # Os 'cursors' abaixo criam um pacote, que não fará nada no servidor até que 'commit()' seja usado:
        result = cursor.executemany("INSERT INTO produtos (nome, valor) VALUES (%(nome_produto)s, %(valor_produto)s)", dict_produtos)

        print(f'Quantidade de linhas afetadas para o commit na criação dos produtos e funcionários: {result}')
        # Aplica os "executes" acima no banco:
        conn.commit()

        print("Dados inseridos com sucesso.")


Lendo dados da tabela​

Vamos ver como ler dados da tabela.

main.py
#!/usr/bin/env python3

import pymysql

### Esse módulo nao será necessário quando passar para container:
from dotenv import load_dotenv
from pathlib import Path
import os

### Caminho manual para o '.env':
dotenv_path = Path('/home/bruno/Nextcloud/docker/mysql/.env')
load_dotenv(dotenv_path=dotenv_path)

# Cria a conexão com o banco de dados:
conn = pymysql.connect(
    host=os.environ["MYSQL_HOST"],
    user=os.environ["MYSQL_USER"],
    password=os.environ["MYSQL_PASSWORD"],
    database=os.environ["MYSQL_DATABASE"]
)

# Abre uma conexão com o banco e garante que será fechada automaticamente:
with conn:

    # Cria um cursor para executar comandos SQL e fecha automaticamente após o uso:
    with conn.cursor() as cursor:

        # Os 'cursors' abaixo criam um pacote, que não fará nada no servidor até que 'commit()' seja usado:
        cursor.execute("select * from produtos")

        # Pegar todos os valores:
        read = cursor.fetchall()

        ## Pegar apenas um valor:
        #read = cursor.fetchone()

        for row in read:
            print(*row)

Se você quiser, pode usar o método cursor.mogrify() para gerar a string SQL final com os valores já interpolados, mas sem executar a consulta, ou seja, você irá ver a string SQL completa, para ver como está sendo fornecida ao banco de dados, mas de uma forma que ela não seja executada.



Deletar dados da tabela​

Para deletar dados de uma tabela, vamos usar o comando SQL DELETE FROM com segurança via placeholders, como em qualquer execute().

main.py
#!/usr/bin/env python3

import pymysql

### Esse módulo nao será necessário quando passar para container:
from dotenv import load_dotenv
from pathlib import Path
import os

### Caminho manual para o '.env':
dotenv_path = Path('/home/bruno/Nextcloud/docker/mysql/.env')
load_dotenv(dotenv_path=dotenv_path)

# Cria a conexão com o banco de dados:
conn = pymysql.connect(
    host=os.environ["MYSQL_HOST"],
    user=os.environ["MYSQL_USER"],
    password=os.environ["MYSQL_PASSWORD"],
    database=os.environ["MYSQL_DATABASE"]
)

# Abre uma conexão com o banco e garante que será fechada automaticamente:
with conn:

    # Cria um cursor para executar comandos SQL e fecha automaticamente após o uso:
    with conn.cursor() as cursor:

        id_deletar = int(5)

        # Os 'cursors' abaixo criam um pacote, que não fará nada no servidor até que 'commit()' seja usado:
        cursor.execute("delete from produtos WHERE id = %s", (id_deletar,))

        # Aplica o delete no banco:
        conn.commit()

        # Os 'cursors' abaixo criam um pacote, que não fará nada no servidor até que 'commit()' seja usado:
        cursor.execute("select * from produtos")
        
        # Pegar todos os valores:
        read = cursor.fetchall()

        # Exibe os dados da tabela:
        for row in read:
            print(*row)


Atualizar dados da tabela​

Para atualizar dados de uma tabela, vamos usar o comando SQL UPDATE com segurança via placeholders, como em qualquer execute().

main.py
#!/usr/bin/env python3

import pymysql

### Esse módulo nao será necessário quando passar para container:
from dotenv import load_dotenv
from pathlib import Path
import os

### Caminho manual para o '.env':
dotenv_path = Path('/home/bruno/Nextcloud/docker/mysql/.env')
load_dotenv(dotenv_path=dotenv_path)

# Cria a conexão com o banco de dados:
conn = pymysql.connect(
    host=os.environ["MYSQL_HOST"],
    user=os.environ["MYSQL_USER"],
    password=os.environ["MYSQL_PASSWORD"],
    database=os.environ["MYSQL_DATABASE"]
)

# Abre uma conexão com o banco e garante que será fechada automaticamente:
with conn:

    # Cria um cursor para executar comandos SQL e fecha automaticamente após o uso:
    with conn.cursor() as cursor:

        modificar = {
            "produto_id": 2,
            "valor": 72.35
        }

        # Os 'cursors' abaixo criam um pacote, que não fará nada no servidor até que 'commit()' seja usado:
        cursor.execute("UPDATE produtos SET valor = %(valor)s WHERE id = %(produto_id)s", modificar)

        # Aplica o delete no banco:
        conn.commit()

        # Os 'cursors' abaixo criam um pacote, que não fará nada no servidor até que 'commit()' seja usado:
        cursor.execute("select * from produtos")
        
        # Pegar todos os valores:
        read = cursor.fetchall()

        # Exibe os dados da tabela:
        for row in read:
            print(*row)


Outros Cursors​

O pymysql oferece diferentes tipos de cursors além do padrão. Esses cursors controlam como os resultados são retornados quando você faz consultas. Isso é útil para adaptar o comportamento do cursor ao tipo de dado que você quer manipular.


Para mudar o cursor faz o seguinte, no connect() adicione cursorclass=pymysql.cursors.CURSOR_NAME:

conn = pymysql.connect(
    host=os.environ["MYSQL_HOST"],
    user=os.environ["MYSQL_USER"],
    password=os.environ["MYSQL_PASSWORD"],
    database=os.environ["MYSQL_DATABASE"],
    cursorclass=pymysql.cursors.DictCursor
)

pymysql.cursors.Cursor​

Retorna cada linha como uma tupla, esse é o padrão.

(1, 'Caneta', 2.50)


pymysql.cursors.DictCursor​

Retorna cada linha como um dicionário, com os nomes das colunas como chaves:

{'id': 1, 'nome': 'Caneta', 'valor': 2.50}

Uso:

with conn.cursor(pymysql.cursors.DictCursor) as cursor:
    cursor.execute("SELECT * FROM produtos")
    for linha in cursor.fetchall():
        print(linha['nome'], linha['valor'])


pymysql.cursors.SSCursor e pymysql.cursors.SSDictCursor​

Esses são cursores "server-side", usados para grandes volumes de dados. Eles não carregam todos os resultados de uma vez, mas um por um, direto do servidor.

  • SSCursor: retorna tuplas, como o padrão.
  • SSDictCursor: retorna dicionários, como o DictCursor.

Uso:

with conn.cursor(pymysql.cursors.SSDictCursor) as cursor:
    cursor.execute("SELECT * FROM produtos")
    for linha in cursor:
        print(linha)

Esses são úteis para consultas muito grandes (milhares ou milhões de linhas) onde o uso de memória importa.



Trabalhando com linhas​

Os atributos rowcount, rownumber e lastrowid são propriedades de cursores em bibliotecas como pymysql e servem para obter informações sobre o resultado ou efeito de operações SQL. Cada um tem uma função específica.



cursor.rowcount​

Retorna a quantidade de linhas afetadas pela última operação SQL (como INSERT, UPDATE, DELETE, ou até SELECT). Exemplo:

cursor.execute("DELETE FROM produtos WHERE valor < %s", (1.00,))
print(cursor.rowcount)  # Exibe quantas linhas foram deletadas

É útil para saber se uma operação teve efeito.



cursor.rownumber​

Essa propriedade é comum no psycopg2, mas não existe no pymysql. Ela indica o índice da próxima linha que será retornada num fetch. Pode ser útil ao iterar manualmente, mas não é muito usado.



cursor.lastrowid​

Retorna o ID da última linha inserida em uma tabela com chave primária AUTO_INCREMENT. Exemplo:

cursor.execute("INSERT INTO produtos (nome, valor) VALUES (%s, %s)", ("Lápis", 1.00))
print(cursor.lastrowid)  # ID gerado automaticamente para esse produto

É muito útil para associar esse novo ID a outras tabelas ou para saber o que acabou de ser criado.