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.
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_jsonEsses 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.dbEste 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.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.
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.
.exitAgora, crie um arquivo Python chamado json_extractor.py.
nano json_extractor.pyCole 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'].Salve o arquivo e saia do nano.
Agora, execute o script Python.
python3 json_extractor.pySaída Esperada:
Dell
Intel i7Este 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.
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.pyModifique 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.pySaí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.
Nesta etapa, você aprenderá como atualizar valores dentro dos campos JSON.
Abra o arquivo json_extractor.py.
nano json_extractor.pyModifique 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.pySaí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.
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.
