813 lines
28 KiB
Plaintext
813 lines
28 KiB
Plaintext
{
|
||
"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": [
|
||
"<div>\n",
|
||
"<style scoped>\n",
|
||
" .dataframe tbody tr th:only-of-type {\n",
|
||
" vertical-align: middle;\n",
|
||
" }\n",
|
||
"\n",
|
||
" .dataframe tbody tr th {\n",
|
||
" vertical-align: top;\n",
|
||
" }\n",
|
||
"\n",
|
||
" .dataframe thead th {\n",
|
||
" text-align: right;\n",
|
||
" }\n",
|
||
"</style>\n",
|
||
"<table border=\"1\" class=\"dataframe\">\n",
|
||
" <thead>\n",
|
||
" <tr style=\"text-align: right;\">\n",
|
||
" <th></th>\n",
|
||
" <th>IGUAL?</th>\n",
|
||
" <th>NOVO SUPERV</th>\n",
|
||
" <th>SUPERVISOR</th>\n",
|
||
" <th>ANALISTA</th>\n",
|
||
" <th>CANAL</th>\n",
|
||
" <th>UF</th>\n",
|
||
" <th>chave</th>\n",
|
||
" <th>PDV</th>\n",
|
||
" <th>DESCRIÇÃO PDV</th>\n",
|
||
" <th>PRODUTO LANÇAMENTO</th>\n",
|
||
" <th>...</th>\n",
|
||
" <th>ignorar</th>\n",
|
||
" <th>META SELLIN</th>\n",
|
||
" <th>PV GINSENG</th>\n",
|
||
" <th>sugest compilado novo</th>\n",
|
||
" <th>SUGESTÃO ABASTECIMENTO</th>\n",
|
||
" <th>SUGESTÃO COMERCIAL</th>\n",
|
||
" <th>nova quantidade</th>\n",
|
||
" <th>SUGESTÃO COMERCIAL original</th>\n",
|
||
" <th>QUANTIDADE DEFINITIVA</th>\n",
|
||
" <th>qtd aprovados bahia</th>\n",
|
||
" </tr>\n",
|
||
" </thead>\n",
|
||
" <tbody>\n",
|
||
" <tr>\n",
|
||
" <th>0</th>\n",
|
||
" <td>1</td>\n",
|
||
" <td>Jessica/Mary</td>\n",
|
||
" <td>Jessica/Mary</td>\n",
|
||
" <td>VA</td>\n",
|
||
" <td>CD</td>\n",
|
||
" <td>AL</td>\n",
|
||
" <td>2099886983</td>\n",
|
||
" <td>20998</td>\n",
|
||
" <td>CD SERRARIA</td>\n",
|
||
" <td>86983</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>3007</td>\n",
|
||
" <td>1943</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>2000</td>\n",
|
||
" <td>5</td>\n",
|
||
" <td>5</td>\n",
|
||
" <td>5</td>\n",
|
||
" <td>5</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>1</th>\n",
|
||
" <td>1</td>\n",
|
||
" <td>FERNANDA VIEIRA</td>\n",
|
||
" <td>FERNANDA VIEIRA</td>\n",
|
||
" <td>DARLIN</td>\n",
|
||
" <td>VD</td>\n",
|
||
" <td>BA</td>\n",
|
||
" <td>2099386983</td>\n",
|
||
" <td>20993</td>\n",
|
||
" <td>ER CANDEIAS</td>\n",
|
||
" <td>86983</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>1361</td>\n",
|
||
" <td>1019</td>\n",
|
||
" <td>1100</td>\n",
|
||
" <td>1100</td>\n",
|
||
" <td>1100</td>\n",
|
||
" <td>3</td>\n",
|
||
" <td>3</td>\n",
|
||
" <td>1100</td>\n",
|
||
" <td>1100.0</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>2</th>\n",
|
||
" <td>1</td>\n",
|
||
" <td>Anna Schelly</td>\n",
|
||
" <td>Anna Schelly</td>\n",
|
||
" <td>LAIS</td>\n",
|
||
" <td>VD</td>\n",
|
||
" <td>AL</td>\n",
|
||
" <td>2099786983</td>\n",
|
||
" <td>20997</td>\n",
|
||
" <td>ER PITANGUINHA</td>\n",
|
||
" <td>86983</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>1438</td>\n",
|
||
" <td>889</td>\n",
|
||
" <td>1300</td>\n",
|
||
" <td>1300</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>30</td>\n",
|
||
" <td>30</td>\n",
|
||
" <td>30</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>3</th>\n",
|
||
" <td>1</td>\n",
|
||
" <td>Anna Schelly</td>\n",
|
||
" <td>Anna Schelly</td>\n",
|
||
" <td>LAIS</td>\n",
|
||
" <td>VD</td>\n",
|
||
" <td>AL</td>\n",
|
||
" <td>2099686983</td>\n",
|
||
" <td>20996</td>\n",
|
||
" <td>ER ANTARES</td>\n",
|
||
" <td>86983</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>742</td>\n",
|
||
" <td>756</td>\n",
|
||
" <td>800</td>\n",
|
||
" <td>800</td>\n",
|
||
" <td>800</td>\n",
|
||
" <td>10</td>\n",
|
||
" <td>10</td>\n",
|
||
" <td>10</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>4</th>\n",
|
||
" <td>1</td>\n",
|
||
" <td>FERNANDA VIEIRA</td>\n",
|
||
" <td>FERNANDA VIEIRA</td>\n",
|
||
" <td>LUCAS</td>\n",
|
||
" <td>VD</td>\n",
|
||
" <td>BA</td>\n",
|
||
" <td>2099486983</td>\n",
|
||
" <td>20994</td>\n",
|
||
" <td>ER SIMOES FILHO</td>\n",
|
||
" <td>86983</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>912</td>\n",
|
||
" <td>719</td>\n",
|
||
" <td>800</td>\n",
|
||
" <td>750</td>\n",
|
||
" <td>800</td>\n",
|
||
" <td>25</td>\n",
|
||
" <td>25</td>\n",
|
||
" <td>800</td>\n",
|
||
" <td>800.0</td>\n",
|
||
" </tr>\n",
|
||
" </tbody>\n",
|
||
"</table>\n",
|
||
"<p>5 rows × 39 columns</p>\n",
|
||
"</div>"
|
||
],
|
||
"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": [
|
||
"<div>\n",
|
||
"<style scoped>\n",
|
||
" .dataframe tbody tr th:only-of-type {\n",
|
||
" vertical-align: middle;\n",
|
||
" }\n",
|
||
"\n",
|
||
" .dataframe tbody tr th {\n",
|
||
" vertical-align: top;\n",
|
||
" }\n",
|
||
"\n",
|
||
" .dataframe thead th {\n",
|
||
" text-align: right;\n",
|
||
" }\n",
|
||
"</style>\n",
|
||
"<table border=\"1\" class=\"dataframe\">\n",
|
||
" <thead>\n",
|
||
" <tr style=\"text-align: right;\">\n",
|
||
" <th></th>\n",
|
||
" <th>email</th>\n",
|
||
" <th>senha</th>\n",
|
||
" <th>supervisor</th>\n",
|
||
" </tr>\n",
|
||
" </thead>\n",
|
||
" <tbody>\n",
|
||
" <tr>\n",
|
||
" <th>0</th>\n",
|
||
" <td>anna.schelly@ginseng.com</td>\n",
|
||
" <td>anna123</td>\n",
|
||
" <td>Anna Schelly</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>1</th>\n",
|
||
" <td>jessica.mary@ginseng.com</td>\n",
|
||
" <td>jessica123</td>\n",
|
||
" <td>Jessica/Mary</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>2</th>\n",
|
||
" <td>carla.melo@ginseng.com</td>\n",
|
||
" <td>carla123</td>\n",
|
||
" <td>Carla Melo</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>3</th>\n",
|
||
" <td>ana.lima@ginseng.com</td>\n",
|
||
" <td>ana123</td>\n",
|
||
" <td>Ana Lima</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>4</th>\n",
|
||
" <td>taciana.andrade@ginseng.com</td>\n",
|
||
" <td>taciana123</td>\n",
|
||
" <td>Taciana Andrade</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>5</th>\n",
|
||
" <td>efigenia.herculano@ginseng.com</td>\n",
|
||
" <td>efigenia123</td>\n",
|
||
" <td>Efigênia Herculano</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>6</th>\n",
|
||
" <td>joao.teste@ginseng.com.br</td>\n",
|
||
" <td>teste123</td>\n",
|
||
" <td>Jessica/Mary</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>7</th>\n",
|
||
" <td>joao.teste@ginseng.com.br</td>\n",
|
||
" <td>teste123</td>\n",
|
||
" <td>Anna Schelly</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>8</th>\n",
|
||
" <td>joao.teste@ginseng.com.br</td>\n",
|
||
" <td>teste123</td>\n",
|
||
" <td>Taciana Andrade</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>9</th>\n",
|
||
" <td>joao.teste@ginseng.com.br</td>\n",
|
||
" <td>teste123</td>\n",
|
||
" <td>Carla Melo</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>10</th>\n",
|
||
" <td>joao.teste@ginseng.com.br</td>\n",
|
||
" <td>teste123</td>\n",
|
||
" <td>Ana Lima</td>\n",
|
||
" </tr>\n",
|
||
" </tbody>\n",
|
||
"</table>\n",
|
||
"</div>"
|
||
],
|
||
"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
|
||
}
|