sqlite_util.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "4290b7a9-de9a-4bad-a261-6fca2cfd9830",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "8dd07d17-03b1-481a-bf76-891a262fcaab",
   "metadata": {},
   "outputs": [],
   "source": [
    "DATABASE_PATH = \"./data/air-quality-data/fortaleza_trama_data_hour.sqlite\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "3a24a15d-01ec-43df-a0b7-722d30b9bb3a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Tabelas no banco de dados:\n",
      "- trama_moqabase_hour\n",
      "- metadata\n",
      "- trama_moqabase_hour_normalized\n",
      "\n",
      "Estrutura da tabela 'trama_moqabase_hour':\n",
      "- date (tipo: TEXT)\n",
      "- time (tipo: TEXT)\n",
      "- moqa_id (tipo: TEXT)\n",
      "- internal_temp (tipo: REAL)\n",
      "- external_temp (tipo: REAL)\n",
      "- humidity (tipo: REAL)\n",
      "- pressure (tipo: REAL)\n",
      "- pm1 (tipo: REAL)\n",
      "- pm25 (tipo: REAL)\n",
      "- pm10 (tipo: REAL)\n",
      "- pms_log (tipo: REAL)\n",
      "- bme_log (tipo: REAL)\n",
      "- latitude (tipo: REAL)\n",
      "- longitude (tipo: REAL)\n",
      "\n",
      "Primeiros 10 registros da tabela 'trama_moqabase_hour':\n",
      "         date      time moqa_id  internal_temp  external_temp   humidity  \\\n",
      "0  2024-08-01  07:00:00  A5BBFC      29.275969      25.457152  55.517520   \n",
      "1  2024-08-01  08:00:00  A5BBFC      30.967749      27.273467  48.863613   \n",
      "2  2024-08-01  09:00:00  A5BBFC      31.919670      28.814042  45.266362   \n",
      "3  2024-08-01  10:00:00  A5BBFC      33.363878      29.678385  43.889363   \n",
      "4  2024-08-01  11:00:00  A5BBFC      34.342411      29.788842  41.941384   \n",
      "5  2024-08-01  12:00:00  A5BBFC      34.332083      29.363001  42.978628   \n",
      "6  2024-08-01  13:00:00  A5BBFC      34.203396      29.814623  42.887275   \n",
      "7  2024-08-01  14:00:00  A5BBFC      34.272449      29.781431  43.186069   \n",
      "8  2024-08-01  15:00:00  A5BBFC      33.231760      29.393529  45.273697   \n",
      "9  2024-08-01  16:00:00  A5BBFC      33.061875      29.753586  47.446059   \n",
      "\n",
      "      pressure       pm1      pm25      pm10  pms_log  bme_log  latitude  \\\n",
      "0  1016.343469  3.849359  7.536859  8.878846      0.0      0.0 -3.718418   \n",
      "1  1016.721152  2.315094  5.303145  6.734906      0.0      0.0 -3.718418   \n",
      "2  1017.075963  2.449359  6.385577  8.280129      0.0      0.0 -3.718418   \n",
      "3  1017.247231  1.949359  4.906410  6.337820      0.0      0.0 -3.718418   \n",
      "4  1016.925111  1.484615  3.854808  5.138462      0.0      0.0 -3.718418   \n",
      "5  1016.398630  1.408654  3.726282  4.791987      0.0      0.0 -3.718418   \n",
      "6  1015.457076  1.056191  3.323810  4.707936      0.0      0.0 -3.718418   \n",
      "7  1014.409721  0.889423  3.201602  4.532692      0.0      0.0 -3.718418   \n",
      "8  1013.723730  0.815064  2.959936  4.345513      0.0      0.0 -3.718418   \n",
      "9  1013.714066  0.763141  2.858013  4.202564      0.0      0.0 -3.718418   \n",
      "\n",
      "   longitude  \n",
      "0 -38.465755  \n",
      "1 -38.465755  \n",
      "2 -38.465755  \n",
      "3 -38.465755  \n",
      "4 -38.465755  \n",
      "5 -38.465755  \n",
      "6 -38.465755  \n",
      "7 -38.465755  \n",
      "8 -38.465755  \n",
      "9 -38.465755  \n"
     ]
    }
   ],
   "source": [
    "def explore_database(db_path):\n",
    "    # Conectar ao banco de dados SQLite\n",
    "    conn = sqlite3.connect(db_path)\n",
    "    cursor = conn.cursor()\n",
    "\n",
    "    # Listar todas as tabelas no banco de dados\n",
    "    cursor.execute(\"SELECT name FROM sqlite_master WHERE type='table';\")\n",
    "    tables = cursor.fetchall()\n",
    "    print(\"Tabelas no banco de dados:\")\n",
    "    for table in tables:\n",
    "        print(f\"- {table[0]}\")\n",
    "\n",
    "    # Explorar estrutura da tabela principal\n",
    "    if tables:\n",
    "        main_table = tables[0][0]  # Seleciona a primeira tabela encontrada\n",
    "        print(f\"\\nEstrutura da tabela '{main_table}':\")\n",
    "        cursor.execute(f\"PRAGMA table_info({main_table});\")\n",
    "        columns = cursor.fetchall()\n",
    "        for col in columns:\n",
    "            print(f\"- {col[1]} (tipo: {col[2]})\")\n",
    "\n",
    "        # Carregar os primeiros 10 dados da tabela principal\n",
    "        print(f\"\\nPrimeiros 10 registros da tabela '{main_table}':\")\n",
    "        df = pd.read_sql_query(f\"SELECT * FROM {main_table} LIMIT 10;\", conn)\n",
    "        print(df)\n",
    "    else:\n",
    "        print(\"Nenhuma tabela encontrada no banco de dados.\")\n",
    "\n",
    "    # Fechar a conexão\n",
    "    conn.close()\n",
    "\n",
    "# Chamada da função\n",
    "explore_database(DATABASE_PATH)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "5c905769-d861-4bab-8f86-43de6a619979",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "36 linhas deletadas com os IDs indesejados.\n"
     ]
    }
   ],
   "source": [
    "def delete_unwanted_ids(db_path, unwanted_ids):\n",
    "    \"\"\"\n",
    "    Deleta as linhas do banco de dados onde moqa_id corresponde a um dos IDs indesejados.\n",
    "    \n",
    "    Args:\n",
    "        db_path (str): Caminho para o banco de dados SQLite.\n",
    "        unwanted_ids (list): Lista de IDs indesejados.\n",
    "    \"\"\"\n",
    "    try:\n",
    "        # Conectar ao banco de dados\n",
    "        conn = sqlite3.connect(db_path)\n",
    "        cursor = conn.cursor()\n",
    "        \n",
    "        # Deletar linhas com moqa_id indesejado\n",
    "        placeholders = \", \".join(\"?\" for _ in unwanted_ids)  # Para usar no SQL IN\n",
    "        query = f\"DELETE FROM trama_moqabase_hour_normalized WHERE moqa_id IN ({placeholders});\"\n",
    "        cursor.execute(query, unwanted_ids)\n",
    "        \n",
    "        # Confirmar alterações\n",
    "        conn.commit()\n",
    "        print(f\"{cursor.rowcount} linhas deletadas com os IDs indesejados.\")\n",
    "        \n",
    "    except sqlite3.Error as e:\n",
    "        print(f\"Erro ao acessar o banco de dados: {e}\")\n",
    "    finally:\n",
    "        # Fechar conexão\n",
    "        conn.close()\n",
    "\n",
    "# Lista de IDs indesejados\n",
    "unwanted_ids = [\"JJJAAA\", \"JJJBBB\", \"JJJCCC\", \"JJJDDD\", \"JJJEEE\", \"NOVAT\"]\n",
    "\n",
    "# Chamar a função\n",
    "delete_unwanted_ids(DATABASE_PATH, unwanted_ids)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "24f16a57-d6cc-47bb-84ef-03af767fd8e2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Valores únicos na coluna 'moqa_id':\n",
      "A5BBFC\n",
      "AV1CD2\n",
      "AV4STA\n",
      "AV5CEC\n",
      "DC40AC\n",
      "DC663C\n",
      "DCB604\n",
      "DD31EC\n",
      "DD32D4\n",
      "DD3468\n",
      "DD34F0\n",
      "DD56F0\n",
      "DDCA1C\n"
     ]
    }
   ],
   "source": [
    "def get_unique_values(db_path, table_name, column_name):\n",
    "    \"\"\"\n",
    "    Obtém todos os valores únicos de uma coluna específica em uma tabela do banco de dados.\n",
    "    \n",
    "    Args:\n",
    "        db_path (str): Caminho para o banco de dados SQLite.\n",
    "        table_name (str): Nome da tabela.\n",
    "        column_name (str): Nome da coluna.\n",
    "    \n",
    "    Returns:\n",
    "        list: Lista de valores únicos encontrados.\n",
    "    \"\"\"\n",
    "    try:\n",
    "        # Conectar ao banco de dados\n",
    "        conn = sqlite3.connect(db_path)\n",
    "        cursor = conn.cursor()\n",
    "        \n",
    "        # Consultar valores únicos\n",
    "        query = f\"SELECT DISTINCT {column_name} FROM {table_name};\"\n",
    "        cursor.execute(query)\n",
    "        unique_values = [row[0] for row in cursor.fetchall()]\n",
    "        \n",
    "        print(f\"Valores únicos na coluna '{column_name}':\")\n",
    "        for value in unique_values:\n",
    "            print(value)\n",
    "        \n",
    "        return unique_values\n",
    "        \n",
    "    except sqlite3.Error as e:\n",
    "        print(f\"Erro ao acessar o banco de dados: {e}\")\n",
    "        return []\n",
    "    finally:\n",
    "        # Fechar conexão\n",
    "        conn.close()\n",
    "\n",
    "# Chamar a função para obter os valores únicos de moqa_id\n",
    "unique_moqa_ids = get_unique_values(DATABASE_PATH, \"trama_moqabase_hour_normalized\", \"moqa_id\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e291c5fa-1d95-4a7b-9204-bd2551a0f952",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}