LabEx
EntrarJunte-se

Processamento de JSON no SQLite

SQLiteBeginner
Pratique Agora

Introdução

Neste laboratório, você aprenderá como processar dados JSON dentro do SQLite. Explorará como armazenar, extrair, filtrar e atualizar dados JSON dentro de um banco de dados SQLite. Este laboratório oferece uma introdução prática ao trabalho com dados JSON no SQLite, uma habilidade cada vez mais valiosa na gestão de dados moderna.

Criar um Banco de Dados e Tabela

Nesta etapa, você criará um banco de dados SQLite e uma tabela para armazenar dados JSON. SQLite é um banco de dados leve que armazena dados em um único arquivo, facilitando o gerenciamento.

Primeiro, abra seu terminal. O caminho padrão é /home/labex/project.

Agora, vamos criar um diretório para armazenar nosso banco de dados.

mkdir sqlite_json
cd sqlite_json

Esses comandos criam um diretório chamado sqlite_json e, em seguida, alteram o diretório atual para ele. Isso manterá seus arquivos de projeto organizados.

Em seguida, crie um banco de dados SQLite chamado mydatabase.db.

sqlite3 mydatabase.db

Este comando abre o shell SQLite, conectando-se ao banco de dados mydatabase.db. Se o arquivo do banco de dados não existir, o SQLite o criará.

Agora, crie uma tabela chamada products com colunas para id, name e details. A coluna details armazenará dados JSON como texto.

CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    details TEXT
);

Este comando SQL cria a tabela products:

  • id: Um inteiro único que se incrementa automaticamente para cada novo produto.
  • name: O nome do produto (por exemplo, Laptop, Smartphone).
  • details: Um campo de texto para armazenar os dados JSON do produto.

Inserir Dados JSON

Nesta etapa, você inserirá dados JSON na tabela products.

Vamos inserir dois registros de exemplo na tabela products.

INSERT INTO products (name, details) VALUES (
    'Laptop',
    '{"brand": "Dell", "model": "XPS 13", "specs": {"cpu": "Intel i7", "memory": "16GB", "storage": "512GB SSD"}}'
);

INSERT INTO products (name, details) VALUES (
    'Smartphone',
    '{"brand": "Samsung", "model": "Galaxy S21", "specs": {"display": "6.2 inch", "camera": "12MP", "storage": "128GB"}}'
);

Essas instruções INSERT adicionam duas linhas à tabela products. A coluna details contém dados JSON como uma string de texto.

Para verificar se os dados foram inseridos corretamente, execute a seguinte consulta:

SELECT * FROM products;

Saída Esperada:

1|Laptop|{"brand": "Dell", "model": "XPS 13", "specs": {"cpu": "Intel i7", "memory": "16GB", "storage": "512GB SSD"}}
2|Smartphone|{"brand": "Samsung", "model": "Galaxy S21", "specs": {"display": "6.2 inch", "camera": "12MP", "storage": "128GB"}}

Esta saída confirma que os dados JSON foram armazenados com sucesso na tabela products.

Extrair Campos JSON com uma Função Personalizada

Como o SQLite não possui funções JSON integradas, você criará uma função Python personalizada para extrair dados das strings JSON.

Primeiro, saia do shell do SQLite.

.exit

Agora, crie um arquivo Python chamado json_extractor.py.

nano json_extractor.py

Cole o seguinte código Python no arquivo json_extractor.py:

## Importar as bibliotecas necessárias
import sqlite3
import json

## Definir uma função para extrair um valor de uma string JSON usando um caminho
def json_extract(json_str, path):
    try:
        ## Analisar a string JSON em um dicionário Python
        json_data = json.loads(json_str)
        ## Dividir o caminho em componentes (por exemplo, 'specs.cpu' torna-se ['specs', 'cpu'])
        path_components = path.split('.')
        ## Começar com o objeto JSON completo
        value = json_data
        ## Percorrer o objeto JSON usando os componentes do caminho
        for component in path_components:
            ## Obter o valor para o componente atual
            value = value.get(component)
        ## Retornar o valor final
        return value
    ## Lidar com erros se o JSON for inválido ou o caminho não existir
    except (json.JSONDecodeError, AttributeError, TypeError):
        return None

## Definir uma função para conectar ao banco de dados e registrar a função personalizada
def connect_db(db_path):
    ## Conectar ao banco de dados SQLite no caminho fornecido
    conn = sqlite3.connect(db_path)
    ## Registrar a função Python 'json_extract' como uma função SQL personalizada
    ## "json_extract" é o nome em SQL, 2 é o número de argumentos, json_extract é a função Python
    conn.create_function("json_extract", 2, json_extract)
    ## Retornar a conexão do banco de dados
    return conn

## Este bloco é executado quando o script é executado diretamente
if __name__ == '__main__':
    ## Conectar ao banco de dados e registrar a função
    conn = connect_db('mydatabase.db')
    ## Criar um objeto cursor para executar consultas SQL
    cursor = conn.cursor()

    ## Usar a função SQL personalizada para extrair a 'brand' (marca) da coluna 'details'
    cursor.execute("SELECT json_extract(details, 'brand') FROM products WHERE name = 'Laptop'")
    ## Buscar o resultado e imprimi-lo
    print(cursor.fetchone()[0])

    ## Usar a função SQL personalizada para extrair a 'cpu' do objeto aninhado 'specs'
    cursor.execute("SELECT json_extract(details, 'specs.cpu') FROM products WHERE name = 'Laptop'")
    ## Buscar o resultado e imprimi-lo
    print(cursor.fetchone()[0])

    ## Fechar a conexão com o banco de dados
    conn.close()

Este código Python define uma função json_extract que recebe uma string JSON e um caminho como entrada e retorna o valor nesse caminho. Ele também inclui uma função connect_db para conectar ao banco de dados SQLite e registrar a função json_extract.

  • json.loads(json_str): Esta linha analisa a string JSON em um dicionário Python.
  • path.split('.'): Isso divide o caminho em uma lista de componentes. Por exemplo, 'specs.cpu' se torna ['specs', 'cpu'].
  • O loop itera pelos componentes do caminho, acessando valores aninhados nos dados JSON.

Salve o arquivo e saia do nano.

Agora, execute o script Python.

python3 json_extractor.py

Saída Esperada:

Dell
Intel i7

Este script se conecta ao banco de dados, registra a função json_extract e, em seguida, a utiliza para extrair a marca e a CPU do Laptop.

Filtrar Dados Usando Consultas JSON

Nesta etapa, você usará a função personalizada json_extract para filtrar dados com base em valores dentro dos campos JSON.

Abra o arquivo json_extractor.py novamente.

nano json_extractor.py

Modifique o arquivo json_extractor.py para incluir uma função para consultar o banco de dados:

## Importar as bibliotecas necessárias
import sqlite3
import json

## Definir uma função para extrair um valor de uma string JSON usando um caminho
def json_extract(json_str, path):
    try:
        ## Analisar a string JSON em um dicionário Python
        json_data = json.loads(json_str)
        ## Dividir o caminho em componentes (por exemplo, 'specs.cpu' torna-se ['specs', 'cpu'])
        path_components = path.split('.')
        ## Começar com o objeto JSON completo
        value = json_data
        ## Percorrer o objeto JSON usando os componentes do caminho
        for component in path_components:
            ## Obter o valor para o componente atual
            value = value.get(component)
        ## Retornar o valor final
        return value
    ## Lidar com erros se o JSON for inválido ou o caminho não existir
    except (json.JSONDecodeError, AttributeError, TypeError):
        return None

## Definir uma função para conectar ao banco de dados e registrar a função personalizada
def connect_db(db_path):
    ## Conectar ao banco de dados SQLite no caminho fornecido
    conn = sqlite3.connect(db_path)
    ## Registrar a função Python 'json_extract' como uma função SQL personalizada
    conn.create_function("json_extract", 2, json_extract)
    ## Retornar a conexão com o banco de dados
    return conn

## Definir uma função para filtrar produtos com base em um campo JSON
def filter_products(db_path, json_path, value):
    ## Conectar ao banco de dados
    conn = connect_db(db_path)
    ## Criar um objeto cursor
    cursor = conn.cursor()
    ## Criar a consulta SQL usando uma f-string para filtrar por um valor JSON
    query = f"SELECT * FROM products WHERE json_extract(details, '{json_path}') = '{value}'"
    ## Executar a consulta
    cursor.execute(query)
    ## Buscar todos os resultados correspondentes
    results = cursor.fetchall()
    ## Fechar a conexão com o banco de dados
    conn.close()
    ## Retornar os resultados
    return results

## Este bloco é executado quando o script é executado diretamente
if __name__ == '__main__':
    ## Exemplo de uso:
    ## Filtrar produtos onde a marca é 'Dell'
    dell_products = filter_products('mydatabase.db', 'brand', 'Dell')
    print("Produtos com a marca 'Dell':", dell_products)

    ## Filtrar produtos onde a CPU é 'Intel i7'
    intel_products = filter_products('mydatabase.db', 'specs.cpu', 'Intel i7')
    print("Produtos com CPU 'Intel i7':", intel_products)

Este código adiciona uma função filter_products que recebe um caminho de banco de dados, um caminho JSON e um valor como entrada. Em seguida, ele se conecta ao banco de dados, registra a função json_extract e executa uma consulta para encontrar todos os produtos onde o valor no caminho JSON especificado corresponde ao valor fornecido.

Salve o arquivo e saia do nano.

Agora, execute o script Python.

python3 json_extractor.py

Saída Esperada:

Produtos com a marca 'Dell': [(1, 'Laptop', '{"brand": "Dell", "model": "XPS 13", "specs": {"cpu": "Intel i7", "memory": "16GB", "storage": "512GB SSD"}}')]
Produtos com CPU 'Intel i7': [(1, 'Laptop', '{"brand": "Dell", "model": "XPS 13", "specs": {"cpu": "Intel i7", "memory": "16GB", "storage": "512GB SSD"}}')]

Esta saída mostra os produtos que correspondem aos critérios especificados.

Atualizar Valores JSON

Nesta etapa, você aprenderá como atualizar valores dentro dos campos JSON.

Abra o arquivo json_extractor.py.

nano json_extractor.py

Modifique o arquivo json_extractor.py para incluir funções para atualizar o JSON e o banco de dados:

## Importar as bibliotecas necessárias
import sqlite3
import json

## Definir uma função para extrair um valor de uma string JSON usando um caminho
def json_extract(json_str, path):
    try:
        ## Analisar a string JSON em um dicionário Python
        json_data = json.loads(json_str)
        ## Dividir o caminho em componentes
        path_components = path.split('.')
        ## Começar com o objeto JSON completo
        value = json_data
        ## Percorrer o objeto JSON
        for component in path_components:
            value = value.get(component)
        return value
    except (json.JSONDecodeError, AttributeError, TypeError):
        return None

## Definir uma função para conectar ao banco de dados e registrar a função personalizada
def connect_db(db_path):
    ## Conectar ao banco de dados
    conn = sqlite3.connect(db_path)
    ## Registrar a função SQL personalizada
    conn.create_function("json_extract", 2, json_extract)
    return conn

## Definir uma função para filtrar produtos com base em um campo JSON
def filter_products(db_path, json_path, value):
    ## Conectar ao banco de dados
    conn = connect_db(db_path)
    ## Criar um objeto cursor
    cursor = conn.cursor()
    ## Criar a consulta SQL para filtrar por um valor JSON
    query = f"SELECT * FROM products WHERE json_extract(details, '{json_path}') = '{value}'"
    ## Executar a consulta
    cursor.execute(query)
    ## Buscar todos os resultados correspondentes
    results = cursor.fetchall()
    ## Fechar a conexão
    conn.close()
    return results

## Definir uma função para atualizar um valor dentro de uma string JSON
def update_json_value(json_str, path, new_value):
    try:
        ## Analisar a string JSON em um dicionário Python
        json_data = json.loads(json_str)
        ## Dividir o caminho em componentes
        path_components = path.split('.')
        ## Navegar até o pai do valor de destino
        target = json_data
        for i in range(len(path_components) - 1):
            target = target.get(path_components[i])

        ## Atualizar o valor de destino
        target[path_components[-1]] = new_value
        ## Converter o dicionário Python de volta para uma string JSON
        return json.dumps(json_data)
    except (json.JSONDecodeError, AttributeError, TypeError):
        ## Se ocorrer um erro, retornar a string JSON original
        return json_str

## Definir uma função para atualizar os detalhes de um produto no banco de dados
def update_product_details(db_path, product_name, json_path, new_value):
    ## Conectar ao banco de dados
    conn = sqlite3.connect(db_path)
    ## Criar um objeto cursor
    cursor = conn.cursor()

    ## Obter os detalhes atuais para o produto especificado
    ## O '?' é um placeholder para prevenir injeção de SQL
    cursor.execute("SELECT details FROM products WHERE name = ?", (product_name,))
    result = cursor.fetchone()
    ## Se o produto não existir, fechar a conexão e retornar False
    if not result:
        conn.close()
        return False

    ## Obter a string de detalhes JSON atual
    current_details = result[0]

    ## Atualizar a string JSON com o novo valor
    updated_details = update_json_value(current_details, json_path, new_value)

    ## Atualizar o banco de dados com a nova string JSON
    cursor.execute("UPDATE products SET details = ? WHERE name = ?", (updated_details, product_name))
    ## Confirmar as alterações no banco de dados
    conn.commit()
    ## Fechar a conexão
    conn.close()
    return True

## Este bloco é executado quando o script é executado diretamente
if __name__ == '__main__':
    ## Exemplo de uso: Atualizar a memória do laptop para 32GB
    update_product_details('mydatabase.db', 'Laptop', 'specs.memory', '32GB')
    print("Memória do laptop atualizada para 32GB")

    ## Verificar a atualização buscando os dados novamente
    conn = sqlite3.connect('mydatabase.db')
    cursor = conn.cursor()
    cursor.execute("SELECT details FROM products WHERE name = 'Laptop'")
    ## Buscar os detalhes atualizados
    updated_details = cursor.fetchone()[0]
    print("Detalhes atualizados do Laptop:", updated_details)
    ## Fechar a conexão
    conn.close()

Este código adiciona duas funções:

  • update_json_value: Esta função recebe uma string JSON, um caminho e um novo valor como entrada. Ela analisa a string JSON, atualiza o valor no caminho especificado e retorna a string JSON atualizada.
  • update_product_details: Esta função recebe um caminho de banco de dados, um nome de produto, um caminho JSON e um novo valor como entrada. Ela se conecta ao banco de dados, recupera os dados JSON atuais para o produto, atualiza o valor no caminho especificado usando update_json_value e, em seguida, atualiza o banco de dados com os dados JSON modificados.

Salve o arquivo e saia do nano.

Agora, execute o script Python.

python3 json_extractor.py

Saída Esperada:

Memória do laptop atualizada para 32GB
Detalhes atualizados do Laptop: {"brand": "Dell", "model": "XPS 13", "specs": {"cpu": "Intel i7", "memory": "32GB", "storage": "512GB SSD"}}

Esta saída confirma que a memória do Laptop foi atualizada para 32GB no banco de dados.

Resumo

Neste laboratório, você aprendeu como processar dados JSON dentro do SQLite. Você começou criando um banco de dados e uma tabela para armazenar dados JSON. Em seguida, você aprendeu como inserir dados JSON na tabela. Você criou uma função Python personalizada para extrair campos específicos dos dados JSON e usou essa função para filtrar dados com base em valores dentro dos campos JSON. Por fim, você aprendeu como atualizar valores dentro dos campos JSON usando uma função Python personalizada. Essas habilidades fornecem uma base para gerenciar dados JSON de forma eficaz dentro de bancos de dados SQLite.

Relacionados SQLite Cursos
SQLite para Iniciantes

SQLite para Iniciantes

databasesqlite
SQLite Intermédio a Avançado

SQLite Intermédio a Avançado

databasesqlite