{ "cells": [ { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\pymysql\\_auth.py:9: CryptographyDeprecationWarning: Python 3.7 is no longer supported by the Python core team and support for it is deprecated in cryptography. A future release of cryptography will remove support for Python 3.7.\n", " from cryptography.hazmat.backends import default_backend\n" ] } ], "source": [ "import pymysql\n", "\n", "# Função para conectar ao banco de dados MySQL\n", "def conectar_banco():\n", " try:\n", " conexao = pymysql.connect(\n", " host=\"localhost\", # Ex: \"localhost\"\n", " user=\"captura_sites2\", # Ex: \"root\"\n", " password=\"Cristin@10\", # Senha do seu banco de dados\n", " database=\"sites_pesquisa\"\n", " )\n", " print(\"Conexão bem-sucedida!\")\n", " return conexao\n", " except pymysql.MySQLError as e:\n", " print(f\"Erro ao conectar ao banco de dados: {e}\")\n", " return None" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[nltk_data] Downloading package stopwords to\n", "[nltk_data] C:\\Users\\Cliente\\AppData\\Roaming\\nltk_data...\n", "[nltk_data] Package stopwords is already up-to-date!\n" ] } ], "source": [ "import requests\n", "from bs4 import BeautifulSoup\n", "from sentence_transformers import SentenceTransformer, util\n", "import nltk\n", "import spacy\n", "import re\n", "from nltk.corpus import stopwords\n", "\n", "# Baixar stopwords em português do nltk\n", "nltk.download('stopwords')\n", "stop_words = set(stopwords.words('portuguese'))\n", "\n", "# Carregar modelo do spaCy para português\n", "nlp = spacy.load(\"pt_core_news_sm\")\n", "\n", "# Função para coletar links do site\n", "def coletar_links(site):\n", " resposta = requests.get(site)\n", " soup = BeautifulSoup(resposta.content, 'html.parser')\n", " links = []\n", " for link in soup.find_all('a', href=True):\n", " url = link['href']\n", " if url.startswith('http'):\n", " links.append(url)\n", " return links\n", "\n", "# Função para extrair frases da página HTML\n", "def extrair_frases(site):\n", " resposta = requests.get(site)\n", " soup = BeautifulSoup(resposta.content, 'html.parser')\n", " texto = soup.get_text()\n", " frases = texto.split('.')\n", " return [frase.strip() for frase in frases if frase.strip()]\n", "\n", "# Função para remover caracteres especiais, stopwords e fazer lematização de uma frase\n", "def limpar_e_lematizar_frase(frase):\n", " # Remover caracteres especiais\n", " frase = re.sub(r'[^\\w\\s]', '', frase)\n", " # Processar a frase com spaCy\n", " doc = nlp(frase)\n", " # Remover stopwords e fazer lematização\n", " frase_limpa = ' '.join([token.lemma_ for token in doc if token.text.lower() not in stop_words])\n", " return frase_limpa\n", "\n", "# Função para limpar e lematizar uma lista de frases\n", "def limpar_e_lematizar_frases(frases):\n", " return [limpar_e_lematizar_frase(frase) for frase in frases]\n", "\n", "# Função para contar a similaridade de frases\n", "def contar_similaridade(frases_html, frases_categoria):\n", " # Carregar modelo pré-treinado\n", " modelo = SentenceTransformer('paraphrase-MiniLM-L6-v2')\n", " # Calcular embeddings das frases\n", " embeddings_html = modelo.encode(frases_html, convert_to_tensor=True)\n", " embeddings_categoria = modelo.encode(frases_categoria, convert_to_tensor=True)\n", " # Calcular similaridade\n", " similaridade = util.pytorch_cos_sim(embeddings_html, embeddings_categoria)\n", " return similaridade\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Conexão bem-sucedida!\n", "MS - Campo Grande\n" ] } ], "source": [ "conexao = conectar_banco()\n", "cursor = conexao.cursor()\n", "\n", "# Executar o SELECT para obter os sites principais com processado = 'N' e município específico\n", "cursor.execute('''\n", " SELECT uf, municipio, site\n", " FROM tb_municipio\n", " WHERE processado = 'N'\n", " AND uf IN ('RR','RS','SC','SE','SP','TO')\n", " ORDER BY uf, municipio;\n", "''')\n", "\n", "# Obter todos os resultados\n", "resultados = cursor.fetchall()\n", "\n", "# Alimentar o array sites_principais\n", "sites_principais = [(linha[0], linha[1], linha[2]) for linha in resultados]\n", "\n", "# Executar o SELECT para obter as categorias e subcategorias\n", "cursor.execute('''\n", "SELECT a.cod_categoria cod_categoria, \n", " b.cod_sub_categoria cod_sub_categoria,\n", " a.descricao descricao_cat, \n", " b.descricao descricao_sub,\n", " b.frase frase\n", " FROM tb_categoria a, tb_sub_categoria b\n", " WHERE a.cod_categoria = b.cod_categoria\n", " ORDER by a.cod_categoria, b.cod_sub_categoria;\n", "''')\n", "\n", "# Obter todos os resultados\n", "resultados = cursor.fetchall()\n", "\n", "# Organizar os dados no formato desejado\n", "categorias_subcategorias = {}\n", "for linha in resultados:\n", " cod_categoria = linha[0]\n", " cod_sub_categoria = linha[1]\n", " descricao_cat = linha[2]\n", " descricao_sub = linha[3]\n", " frase = linha[4]\n", " \n", " if descricao_cat not in categorias_subcategorias:\n", " categorias_subcategorias[descricao_cat] = {}\n", " \n", " if descricao_sub not in categorias_subcategorias[descricao_cat]:\n", " categorias_subcategorias[descricao_cat][descricao_sub] = {\n", " 'cod_categoria': cod_categoria,\n", " 'cod_sub_categoria': cod_sub_categoria,\n", " 'frases': []\n", " }\n", " \n", " categorias_subcategorias[descricao_cat][descricao_sub]['frases'].append(frase)\n", "\n", "# Limpar e lematizar categorias e subcategorias\n", "categorias_limpas = {}\n", "for categoria, subcategorias in categorias_subcategorias.items():\n", " categorias_limpas[categoria] = {}\n", " for subcategoria, dados in subcategorias.items():\n", " categorias_limpas[categoria][subcategoria] = {\n", " 'cod_categoria': dados['cod_categoria'],\n", " 'cod_sub_categoria': dados['cod_sub_categoria'],\n", " 'frases': limpar_e_lematizar_frases(dados['frases'])\n", " }\n", "\n", "# Iterar sobre os sites principais\n", "for uf, municipio, site_principal in sites_principais:\n", " print(uf + ' - ' + municipio)\n", " # Coletar links do site principal\n", " links = coletar_links(site_principal)\n", " #print(links)\n", " # Iterar sobre links coletados\n", " for link in links:\n", " try:\n", " # Extrair frases da página HTML\n", " frases_html = extrair_frases(link)\n", " # Limpar e lematizar frases da página HTML\n", " frases_html_limpas = limpar_e_lematizar_frases(frases_html)\n", " # Inicializar contador de frases para cada subcategoria\n", " for categoria, subcategorias in categorias_limpas.items():\n", " for subcategoria, dados in subcategorias.items():\n", " contagem = {frase: 0 for frase in dados['frases']}\n", " similaridade = contar_similaridade(frases_html_limpas, dados['frases'])\n", " # Contar ocorrências de frases similares\n", " for i, frase_html in enumerate(frases_html_limpas):\n", " for j, frase_banco in enumerate(dados['frases']):\n", " if similaridade[i][j] > 0.7: # Limiar de similaridade\n", " if frase_html not in contagem:\n", " contagem[frase_html] = 0\n", " contagem[frase_html] += 1\n", " # Armazenar resultados na tabela do banco de dados\n", " for frase_html, count in contagem.items():\n", " if count > 0: # Verificar se a frase foi encontrada\n", " try:\n", " cursor.execute(''' \n", " INSERT INTO tb_registros_processados(\n", " Cod_Categoria, \n", " Cod_Subcategoria, \n", " link_url, \n", " frase_html, \n", " contagem,\n", " municipio,\n", " uf\n", " ) \n", " VALUES (%s, %s, %s, %s, %s, %s, %s) ''', \n", " (\n", " dados['cod_categoria'], \n", " dados['cod_sub_categoria'], \n", " link, \n", " frase_html, # Inserir a frase HTML\n", " count,\n", " municipio, # Inserir o município\n", " uf # Inserir a UF\n", " )\n", " ) \n", " conexao.commit()\n", " except Exception as e:\n", " print(f'Erro ao inserir no banco: {e}')\n", " cursor.execute(''' \n", " INSERT INTO tb_trata_erro(\n", " link, \n", " cod_erro\n", " ) \n", " VALUES (%s, %s) ''', \n", " (\n", " link, # Salvar o link\n", " str(e) # Salvar o erro como string\n", " )\n", " ) \n", " conexao.commit()\n", " except Exception as e:\n", " print(f'Erro ao processar o link {link}: {e}')\n", " cursor.execute(''' \n", " INSERT INTO tb_trata_erro(\n", " link, \n", " cod_erro\n", " ) \n", " VALUES (%s, %s) ''', \n", " (\n", " link, # Salvar o link\n", " str(e) # Salvar o erro como string\n", " )\n", " ) \n", " conexao.commit()\n", "\n", " # Atualizar a coluna 'processado' para 'S' após processar todos os links do site principal\n", " cursor.execute('''\n", " UPDATE tb_municipio\n", " SET processado = 'S'\n", " WHERE uf = %s AND municipio = %s;\n", " ''', (uf, municipio))\n", " conexao.commit()\n", " \n", "# Fechar a conexão\n", "conexao.close()\n", "\n", "print('Processo concluído')\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python [conda env:base] *", "language": "python", "name": "conda-base-py" }, "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.7" } }, "nbformat": 4, "nbformat_minor": 4 }