{ "cells": [ { "cell_type": "code", "execution_count": 1, "id": "63c7d688", "metadata": {}, "outputs": [], "source": [ "import sqlite3\n", "import pandas as pd\n", "\n", "# Caminho absoluto do seu banco\n", "caminho_banco = r\"C:\\Users\\joao.herculano\\GRUPO GINSENG\\Assistência Suprimentos - 2025\\CODIGOS\\Planilha online devolução comercial\\dados.db\"\n", "\n", "# Conectar e ler a tabela\n", "conn = sqlite3.connect(caminho_banco)\n", "df = pd.read_sql(\"SELECT * FROM dados_promocao\", conn)\n", "\n", "# Mostrar os dados\n", "\n", "\n", "conn.close()\n", "\n" ] }, { "cell_type": "code", "execution_count": 2, "id": "056492cb", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
IGUAL?NOVO SUPERVSUPERVISORANALISTACANALUFchavePDVDESCRIÇÃO PDVPRODUTO LANÇAMENTO...ignorarMETA SELLINPV GINSENGsugest compilado novoSUGESTÃO ABASTECIMENTOSUGESTÃO COMERCIALnova quantidadeSUGESTÃO COMERCIAL originalQUANTIDADE DEFINITIVAqtd aprovados bahia
01Jessica/MaryJessica/MaryVACDAL209988698320998CD SERRARIA86983...030071943020005555NaN
11FERNANDA VIEIRAFERNANDA VIEIRADARLINVDBA209938698320993ER CANDEIAS86983...0136110191100110011003311001100.0
21Anna SchellyAnna SchellyLAISVDAL209978698320997ER PITANGUINHA86983...01438889130013000303030NaN
31Anna SchellyAnna SchellyLAISVDAL209968698320996ER ANTARES86983...0742756800800800101010NaN
41FERNANDA VIEIRAFERNANDA VIEIRALUCASVDBA209948698320994ER SIMOES FILHO86983...09127198007508002525800800.0
\n", "

5 rows × 39 columns

\n", "
" ], "text/plain": [ " IGUAL? NOVO SUPERV SUPERVISOR ANALISTA CANAL UF chave \\\n", "0 1 Jessica/Mary Jessica/Mary VA CD AL 2099886983 \n", "1 1 FERNANDA VIEIRA FERNANDA VIEIRA DARLIN VD BA 2099386983 \n", "2 1 Anna Schelly Anna Schelly LAIS VD AL 2099786983 \n", "3 1 Anna Schelly Anna Schelly LAIS VD AL 2099686983 \n", "4 1 FERNANDA VIEIRA FERNANDA VIEIRA LUCAS VD BA 2099486983 \n", "\n", " PDV DESCRIÇÃO PDV PRODUTO LANÇAMENTO ... ignorar META SELLIN \\\n", "0 20998 CD SERRARIA 86983 ... 0 3007 \n", "1 20993 ER CANDEIAS 86983 ... 0 1361 \n", "2 20997 ER PITANGUINHA 86983 ... 0 1438 \n", "3 20996 ER ANTARES 86983 ... 0 742 \n", "4 20994 ER SIMOES FILHO 86983 ... 0 912 \n", "\n", " PV GINSENG sugest compilado novo SUGESTÃO ABASTECIMENTO SUGESTÃO COMERCIAL \\\n", "0 1943 0 2000 5 \n", "1 1019 1100 1100 1100 \n", "2 889 1300 1300 0 \n", "3 756 800 800 800 \n", "4 719 800 750 800 \n", "\n", " nova quantidade SUGESTÃO COMERCIAL original QUANTIDADE DEFINITIVA \\\n", "0 5 5 5 \n", "1 3 3 1100 \n", "2 30 30 30 \n", "3 10 10 10 \n", "4 25 25 800 \n", "\n", " qtd aprovados bahia \n", "0 NaN \n", "1 1100.0 \n", "2 NaN \n", "3 NaN \n", "4 800.0 \n", "\n", "[5 rows x 39 columns]" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.head()" ] }, { "cell_type": "code", "execution_count": 19, "id": "12fd47f7", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "np.float64(289.0)" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df['SUGESTÃO COMERCIAL'].sum()" ] }, { "cell_type": "code", "execution_count": 34, "id": "36b255d1", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Lendo arquivo Excel...\n", "Abas disponíveis: ['Planilha1 (2)', 'Planilha1', 'faltando', 'Sheet1']\n", "\\nArquivo lido com sucesso!\n", "Dimensões: 2508 linhas x 39 colunas\n", "\\nColunas encontradas:\n", "1. IGUAL?\n", "2. NOVO SUPERV\n", "3. SUPERVISOR\n", "4. ANALISTA\n", "5. CANAL\n", "6. UF\n", "7. chave\n", "8. PDV\n", "9. DESCRIÇÃO PDV\n", "10. PRODUTO LANÇAMENTO\n", "11. DESCRIÇÃO DO LANÇAMENTO\n", "12. MARCA\n", "13. CATEGORIA\n", "14. % CONSUMIDOR\n", "15. MECANICA CONSUMIDOR\n", "16. % REVENDEDOR\n", "17. MECANICA REVENDEDOR\n", "18. IAF\n", "19. FOCO\n", "20. PRODUTO SIMILAR\n", "21. DESCRIÇÃO SIMILAR\n", "22. CICLO SIMILAR\n", "23. VENDAS CICLO LANÇAMENTO\n", "24. C-4\n", "25. C-3\n", "26. C-2\n", "27. C-1\n", "28. VENDAS CICLO ATUAL\n", "29. PICO VENDAS SIMILAR ULTIMOS 6 CICLOS\n", "30. ignorar\n", "31. META SELLIN\n", "32. PV GINSENG\n", "33. sugest compilado novo\n", "34. SUGESTÃO ABASTECIMENTO\n", "35. SUGESTÃO COMERCIAL\n", "36. nova quantidade\n", "37. SUGESTÃO COMERCIAL original\n", "38. QUANTIDADE DEFINITIVA\n", "39. qtd aprovados bahia\n", "\\nPrimeiras 3 linhas:\n", " IGUAL? NOVO SUPERV SUPERVISOR ANALISTA CANAL UF chave \\\n", "0 True Jessica/Mary Jessica/Mary VA CD AL 2099886983 \n", "1 True FERNANDA VIEIRA FERNANDA VIEIRA DARLIN VD BA 2099386983 \n", "2 True Anna Schelly Anna Schelly LAIS VD AL 2099786983 \n", "\n", " PDV DESCRIÇÃO PDV PRODUTO LANÇAMENTO ... ignorar META SELLIN \\\n", "0 20998 CD SERRARIA 86983 ... 0 3007 \n", "1 20993 ER CANDEIAS 86983 ... 0 1361 \n", "2 20997 ER PITANGUINHA 86983 ... 0 1438 \n", "\n", " PV GINSENG sugest compilado novo SUGESTÃO ABASTECIMENTO SUGESTÃO COMERCIAL \\\n", "0 1943 0 2000 5 \n", "1 1019 1100 1100 1100 \n", "2 889 1300 1300 30 \n", "\n", " nova quantidade SUGESTÃO COMERCIAL original QUANTIDADE DEFINITIVA \\\n", "0 5 5 5 \n", "1 3 3 1100 \n", "2 30 30 30 \n", "\n", " qtd aprovados bahia \n", "0 NaN \n", "1 1100.0 \n", "2 NaN \n", "\n", "[3 rows x 39 columns]\n", "\\nConectando ao banco de dados...\n", "Inserindo dados na tabela 'dados_promocao'...\n", "Dados inseridos com sucesso!\n", "2508 registros foram inseridos na tabela 'dados_promocao'\n", "\\nVerificação - Primeiras 5 linhas da tabela:\n", " IGUAL? NOVO SUPERV SUPERVISOR ANALISTA CANAL UF chave \\\n", "0 1 Jessica/Mary Jessica/Mary VA CD AL 2099886983 \n", "1 1 FERNANDA VIEIRA FERNANDA VIEIRA DARLIN VD BA 2099386983 \n", "2 1 Anna Schelly Anna Schelly LAIS VD AL 2099786983 \n", "3 1 Anna Schelly Anna Schelly LAIS VD AL 2099686983 \n", "4 1 FERNANDA VIEIRA FERNANDA VIEIRA LUCAS VD BA 2099486983 \n", "\n", " PDV DESCRIÇÃO PDV PRODUTO LANÇAMENTO ... ignorar META SELLIN \\\n", "0 20998 CD SERRARIA 86983 ... 0 3007 \n", "1 20993 ER CANDEIAS 86983 ... 0 1361 \n", "2 20997 ER PITANGUINHA 86983 ... 0 1438 \n", "3 20996 ER ANTARES 86983 ... 0 742 \n", "4 20994 ER SIMOES FILHO 86983 ... 0 912 \n", "\n", " PV GINSENG sugest compilado novo SUGESTÃO ABASTECIMENTO SUGESTÃO COMERCIAL \\\n", "0 1943 0 2000 5 \n", "1 1019 1100 1100 1100 \n", "2 889 1300 1300 30 \n", "3 756 800 800 10 \n", "4 719 800 750 800 \n", "\n", " nova quantidade SUGESTÃO COMERCIAL original QUANTIDADE DEFINITIVA \\\n", "0 5 5 5 \n", "1 3 3 1100 \n", "2 30 30 30 \n", "3 10 10 10 \n", "4 25 25 800 \n", "\n", " qtd aprovados bahia \n", "0 NaN \n", "1 1100.0 \n", "2 NaN \n", "3 NaN \n", "4 800.0 \n", "\n", "[5 rows x 39 columns]\n", "\\nProcesso concluído com sucesso!\n" ] } ], "source": [ "import sqlite3\n", "import pandas as pd\n", "\n", "import sqlite3\n", "import pandas as pd\n", "\n", "# Caminhos\n", "arquivo_excel = r\"C:\\\\Users\\\\joao.herculano\\\\GRUPO GINSENG\\\\Assistência Suprimentos - 2025\\\\SUPRIMENTOS\\\\BD_LANÇAMENTOS\\\\BOT\\\\Bot - C13\\\\LANÇAMENTOS\\\\Lançamento C2025.13_ com abast.xlsx\"\n", "caminho_banco = r\"C:\\\\Users\\\\joao.herculano\\\\GRUPO GINSENG\\\\Assistência Suprimentos - 2025\\\\CODIGOS\\\\Planilha online devolução comercial\\\\dados.db\"\n", "\n", "print(\"Lendo arquivo Excel...\")\n", "\n", "# Ler o arquivo Excel\n", "try:\n", " # Primeiro, vamos ver quais abas existem\n", " excel_file = pd.ExcelFile(arquivo_excel)\n", " print(f\"Abas disponíveis: {excel_file.sheet_names}\")\n", " \n", " # Ler a primeira aba (ou você pode especificar qual aba quer)\n", " df = pd.read_excel(arquivo_excel, sheet_name='Sheet1') # sheet_name=0 pega a primeira aba\n", " \n", " print(f\"\\\\nArquivo lido com sucesso!\")\n", " print(f\"Dimensões: {df.shape[0]} linhas x {df.shape[1]} colunas\")\n", " print(f\"\\\\nColunas encontradas:\")\n", " for i, col in enumerate(df.columns):\n", " print(f\"{i+1}. {col}\")\n", " \n", " print(f\"\\\\nPrimeiras 3 linhas:\")\n", " print(df.head(3))\n", " \n", " # Conectar ao banco\n", " print(\"\\\\nConectando ao banco de dados...\")\n", " conn = sqlite3.connect(caminho_banco)\n", " \n", " # Subir os dados para a tabela dados_promocao\n", " print(\"Inserindo dados na tabela 'dados_promocao'...\")\n", " df.to_sql('dados_promocao', conn, if_exists='replace', index=False)\n", " \n", " print(f\"Dados inseridos com sucesso!\")\n", " print(f\"{len(df)} registros foram inseridos na tabela 'dados_promocao'\")\n", " \n", " # Verificar se os dados foram inseridos\n", " df_verificacao = pd.read_sql(\"SELECT * FROM dados_promocao LIMIT 5\", conn)\n", " print(f\"\\\\nVerificação - Primeiras 5 linhas da tabela:\")\n", " print(df_verificacao)\n", " \n", " conn.close()\n", " print(\"\\\\nProcesso concluído com sucesso!\")\n", " \n", "except FileNotFoundError:\n", " print(f\"Erro: Arquivo não encontrado no caminho especificado.\")\n", " print(f\"Verifique se o arquivo existe em: {arquivo_excel}\")\n", " \n", "except Exception as e:\n", " print(f\"Erro ao processar: {e}\")\n" ] }, { "cell_type": "code", "execution_count": null, "id": "852a3508", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Tabela 'usuarios' criada com sucesso!\n", "\n", "Dados inseridos:\n", "Email: anna.schelly@ginseng.com, Senha: anna123, Supervisor: Anna Schelly\n", "Email: jessica.mary@ginseng.com, Senha: jessica123, Supervisor: Jessica/Mary\n", "Email: carla.melo@ginseng.com, Senha: carla123, Supervisor: Carla Melo\n", "Email: ana.lima@ginseng.com, Senha: ana123, Supervisor: Ana Lima\n", "Email: taciana.andrade@ginseng.com, Senha: taciana123, Supervisor: Taciana Andrade\n", "Email: efigenia.herculano@ginseng.com, Senha: efigenia123, Supervisor: Efigênia Herculano\n" ] } ], "source": [ "import sqlite3\n", "\n", "# Caminho do seu banco\n", "caminho_banco = r\"C:\\Users\\joao.herculano\\GRUPO GINSENG\\Assistência Suprimentos - 2025\\CODIGOS\\Planilha online devolução comercial\\dados.db\"\n", "\n", "# Dados dos usuários\n", "usuarios = {\n", " \"anna.schelly@ginseng.com\": {\"senha\": \"anna123\", \"supervisor\": \"Anna Schelly\"},\n", " \"jessica.mary@ginseng.com\": {\"senha\": \"jessica123\", \"supervisor\": \"Jessica/Mary\"},\n", " \"carla.melo@ginseng.com\": {\"senha\": \"carla123\", \"supervisor\": \"Carla Melo\"},\n", " \"ana.lima@ginseng.com\": {\"senha\": \"ana123\", \"supervisor\": \"Ana Lima\"},\n", " \"taciana.andrade@ginseng.com\": {\"senha\": \"taciana123\", \"supervisor\": \"Taciana Andrade\"},\n", " \"efigenia.herculano@ginseng.com\": {\"senha\": \"efigenia123\", \"supervisor\": \"Efigênia Herculano\"},\n", "}\n", "\n", "# Conectar ao banco\n", "conn = sqlite3.connect(caminho_banco)\n", "cursor = conn.cursor()\n", "\n", "# Criar a tabela usuarios\n", "cursor.execute('''\n", "CREATE TABLE IF NOT EXISTS usuarios (\n", " email TEXT NOT NULL,\n", " senha TEXT NOT NULL,\n", " supervisor TEXT NOT NULL\n", ")\n", "''')\n", "\n", "# Inserir os dados\n", "for email, dados in usuarios.items():\n", " cursor.execute('''\n", " INSERT OR REPLACE INTO usuarios (email, senha, supervisor)\n", " VALUES (?, ?, ?)\n", " ''', (email, dados['senha'], dados['supervisor']))\n", "\n", "# Confirmar as mudanças\n", "conn.commit()\n", "\n", "# Verificar se os dados foram inseridos\n", "cursor.execute(\"SELECT * FROM usuarios\")\n", "resultados = cursor.fetchall()\n", "\n", "print(\"Tabela 'usuarios' criada com sucesso!\")\n", "print(\"\\nDados inseridos:\")\n", "for linha in resultados:\n", " print(f\"Email: {linha[0]}, Senha: {linha[1]}, Supervisor: {linha[2]}\")\n", "\n", "conn.close()" ] }, { "cell_type": "code", "execution_count": 6, "id": "b3c35879", "metadata": {}, "outputs": [], "source": [ "# Conectar ao banco\n", "conn = sqlite3.connect(caminho_banco)\n", "cursor = conn.cursor()\n", "\n", "\n", "# Inserir os dados\n", "\n", "cursor.execute('''\n", "INSERT OR REPLACE INTO usuarios (email, senha, supervisor)\n", "VALUES (?, ?, ?)\n", "''', ('joao.teste@ginseng.com.br','teste123', 'Ana Lima'))\n", "\n", "# Confirmar as mudanças\n", "conn.commit()" ] }, { "cell_type": "code", "execution_count": 7, "id": "fa34c1aa", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
emailsenhasupervisor
0anna.schelly@ginseng.comanna123Anna Schelly
1jessica.mary@ginseng.comjessica123Jessica/Mary
2carla.melo@ginseng.comcarla123Carla Melo
3ana.lima@ginseng.comana123Ana Lima
4taciana.andrade@ginseng.comtaciana123Taciana Andrade
5efigenia.herculano@ginseng.comefigenia123Efigênia Herculano
6joao.teste@ginseng.com.brteste123Jessica/Mary
7joao.teste@ginseng.com.brteste123Anna Schelly
8joao.teste@ginseng.com.brteste123Taciana Andrade
9joao.teste@ginseng.com.brteste123Carla Melo
10joao.teste@ginseng.com.brteste123Ana Lima
\n", "
" ], "text/plain": [ " email senha supervisor\n", "0 anna.schelly@ginseng.com anna123 Anna Schelly\n", "1 jessica.mary@ginseng.com jessica123 Jessica/Mary\n", "2 carla.melo@ginseng.com carla123 Carla Melo\n", "3 ana.lima@ginseng.com ana123 Ana Lima\n", "4 taciana.andrade@ginseng.com taciana123 Taciana Andrade\n", "5 efigenia.herculano@ginseng.com efigenia123 Efigênia Herculano\n", "6 joao.teste@ginseng.com.br teste123 Jessica/Mary\n", "7 joao.teste@ginseng.com.br teste123 Anna Schelly\n", "8 joao.teste@ginseng.com.br teste123 Taciana Andrade\n", "9 joao.teste@ginseng.com.br teste123 Carla Melo\n", "10 joao.teste@ginseng.com.br teste123 Ana Lima" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Conectar e ler a tabela\n", "conn = sqlite3.connect(caminho_banco)\n", "df1 = pd.read_sql(\"SELECT * FROM usuarios\", conn)\n", "\n", "# Mostrar os dados\n", "\n", "conn.close()\n", "\n", "df1.head(20)" ] }, { "cell_type": "code", "execution_count": 4, "id": "8539e1fe", "metadata": {}, "outputs": [], "source": [ "import sqlite3\n", "\n", "caminho_banco = r\"C:\\Users\\joao.herculano\\GRUPO GINSENG\\Assistência Suprimentos - 2025\\CODIGOS\\Planilha online devolução comercial\\dados.db\"\n", "\n", "script = \"\"\"\n", "DROP TABLE IF EXISTS usuarios;\n", "\n", "CREATE TABLE usuarios (\n", " email TEXT NOT NULL,\n", " senha TEXT NOT NULL,\n", " supervisor TEXT NOT NULL,\n", " PRIMARY KEY (email, supervisor)\n", ");\n", "\n", "\"\"\"\n", "\n", "conn = sqlite3.connect(caminho_banco)\n", "cursor = conn.cursor()\n", "cursor.executescript(script)\n", "conn.commit()\n", "conn.close()\n" ] }, { "cell_type": "code", "execution_count": null, "id": "8e901290", "metadata": {}, "outputs": [], "source": [ "conn = sqlite3.connect(caminho_banco)\n", "cursor = conn.cursor()\n", "\n", "cursor.execute('''\n", "DROP TABLE IF EXISTS dados_promocao''')\n", "\n", "# Confirmar as mudanças\n", "conn.commit()\n", "conn.close()" ] }, { "cell_type": "code", "execution_count": 6, "id": "56d08212", "metadata": {}, "outputs": [], "source": [ "conn = sqlite3.connect(caminho_banco)\n", "cursor = conn.cursor()\n", "\n", "cursor.execute(\n", " '''INSERT INTO usuarios (email, senha, supervisor) VALUES\n", " ('anna.schelly@ginseng.com', 'anna123', 'Anna Schelly'),\n", " ('jessica.mary@ginseng.com', 'jessica123', 'Jessica/Mary'),\n", " ('carla.melo@ginseng.com', 'carla123', 'Carla Melo'),\n", " ('ana.lima@ginseng.com', 'ana123', 'Ana Lima'),\n", " ('taciana.andrade@ginseng.com', 'taciana123', 'Taciana Andrade'),\n", " ('efigenia.herculano@ginseng.com', 'efigenia123', 'Efigênia Herculano'),\n", " ('joao.teste@ginseng.com.br', 'teste123', 'Jessica/Mary'),\n", " ('joao.teste@ginseng.com.br', 'teste123', 'Anna Schelly');''')\n", "\n", "# Confirmar as mudanças\n", "conn.commit()\n", "conn.close()\n", "\n" ] }, { "cell_type": "code", "execution_count": 8, "id": "ac883a70", "metadata": {}, "outputs": [], "source": [ "conn = sqlite3.connect(caminho_banco)\n", "cursor = conn.cursor()\n", "\n", "cursor.execute('''\n", " INSERT INTO usuarios (email, senha, supervisor)\n", " VALUES (?, ?, ?)\n", " ''', ('joao.teste@ginseng.com.br', 'teste123', 'Taciana Andrade'))\n", "\n", "# Confirmar as mudanças\n", "conn.commit()\n", "conn.close()" ] }, { "cell_type": "code", "execution_count": null, "id": "535247e1", "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "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.13.2" } }, "nbformat": 4, "nbformat_minor": 5 }