python_CRUD.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b30c6f2c",
   "metadata": {
    "id": "VWTl5xa_fqQi"
   },
   "outputs": [],
   "source": [
    "import traceback\n",
    "import ipywidgets as widgets\n",
    "from IPython.display import display\n",
    "from ipywidgets import interact\n",
    "import pandas as pd\n",
    "import psycopg2 as pg\n",
    "import sqlalchemy\n",
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.sql.expression import text"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3567c443",
   "metadata": {
    "id": "_2Ba9N2ofqQo"
   },
   "source": [
    "### Conexão com o banco de dados"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "fdf1a02a",
   "metadata": {
    "id": "41_t9knVfqQq"
   },
   "outputs": [],
   "source": [
    "cnx = 'postgresql://postgres:postgres@localhost/crud-pessoa'\n",
    "eng = sqlalchemy.create_engine(cnx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "ee6ca528",
   "metadata": {
    "id": "q0QxEx4qfqQs"
   },
   "outputs": [],
   "source": [
    "con = pg.connect(host='localhost', dbname= 'crud-pessoa', user='postgres', password= 'postgres')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "2ab33370",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "cpf: 123  nome: João\n"
     ]
    }
   ],
   "source": [
    "with eng.connect() as conn:\n",
    "    result = conn.execute(text(\"SELECT * FROM pessoa WHERE cpf = ':cpf'\"), {\"cpf\": 123})\n",
    "    for row in result:\n",
    "        print(f\"cpf: {row.cpf}  nome: {row.nome}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0d1df865",
   "metadata": {
    "id": "FnAU494YfqQs"
   },
   "source": [
    "### Definição do Formulário do CRUD\n",
    "\n",
    "Os componentes são salvos em variáveis dos widgets. o campo value da variável acessa o valor que foi preenchido. Por exemplo, na célula abaixo é definido um componente pnome do tipo widgets.Text. O comando pnome.value vai acessar o valor que foi preenchido nessa caixa de texto. Isso serve para todos os componentes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "5221e19d",
   "metadata": {
    "id": "JEd0SWKPfqQt"
   },
   "outputs": [],
   "source": [
    "#campos de texto\n",
    "\n",
    "#declare esta variável para usar na consulta de campos em branco\n",
    "flag=''\n",
    "\n",
    "nome = widgets.Text(\n",
    "    value='',\n",
    "    placeholder='Digite o nome',\n",
    "    description='Nome: ',\n",
    "    disabled=False\n",
    ")\n",
    "\n",
    "\n",
    "cpf = widgets.Text(\n",
    "    value='',\n",
    "    placeholder='Digite seu CPF',\n",
    "    description='CPF: ',\n",
    "    disabled=False\n",
    ")\n",
    "\n",
    "datanasc = widgets.DatePicker(\n",
    "    description='Data de Nascimento:',\n",
    "    disabled=False\n",
    ")\n",
    "\n",
    "endereco = widgets.Text(\n",
    "    value='',\n",
    "    placeholder='Digite seu endereço completo',\n",
    "    description='Endereço: ',\n",
    "    disabled=False\n",
    ")\n",
    "\n",
    "# campo com opções enumeradas (radio button)\n",
    "\n",
    "sexo = widgets.RadioButtons(\n",
    "    options=['M', 'F', 'Não Informado'],\n",
    "    description='Sexo:',\n",
    "    disabled=False\n",
    ")\n",
    "\n",
    "salario = widgets.FloatText(\n",
    "    value = 0,\n",
    "    description='Salario: ',\n",
    "    disabled=False\n",
    ")\n",
    "\n",
    "# campo numérico (para float use FloatText)\n",
    "\n",
    "#dnr = widgets.IntText(\n",
    "#    value = 0,\n",
    "#    description='Numero Departamento: ',\n",
    "#    disabled=False\n",
    "#)\n",
    "\n",
    "#rg = widgets.Text(\n",
    "#    value='',\n",
    "#    placeholder='Digite o RG',\n",
    "#    description='RG: ',\n",
    "#    disabled=False\n",
    "#)\n",
    "\n",
    "# botão com descrição \n",
    "\n",
    "button_ins = widgets.Button(description=\"Inserir\", value='')\n",
    "button_del = widgets.Button(description=\"Deletar\", value='Preencha o CPF para deletar')\n",
    "button_find_cpf = widgets.Button(description=\"Consultar CPF\", value='Preencha o CPF para consultar')\n",
    "button_find_all = widgets.Button(description=\"Listar tudo\")\n",
    "\n",
    "\n",
    "# ação que vai ser executada ao clicar no botão\n",
    "# inserir\n",
    "def on_button_insert(b):\n",
    "    try:\n",
    "        cursor= con.cursor()\n",
    "        cursor.execute(\"insert into pessoa(nome, cpf, nascimento, sexo, salario) VALUES (%s, %s, %s, %s, %s)\", \n",
    "                    (nome.value, cpf.value, datanasc.value,\n",
    "                     sexo.value, salario.value))\n",
    "        cursor.query\n",
    "        con.commit()\n",
    "        \n",
    "        df = pd.read_sql(\"select * from pessoa\", cnx)\n",
    "        output.clear_output()\n",
    "        display(nome, cpf, datanasc, endereco, sexo, salario, button_ins, button_del, button_find_cpf, button_find_all, df)\n",
    "\n",
    "    except Exception as e:\n",
    "        cursor.execute(\"ROLLBACK\")\n",
    "        output.clear_output()        \n",
    "        display(nome, cpf, datanasc, endereco, sexo, salario, button_ins, button_del, button_find_cpf, button_find_all)\n",
    "        display(\"Não foi possível realizar a inserção. Verifique restrições.\")\n",
    "        display(e)\n",
    "        traceback.print_exc()\n",
    "        \n",
    "    finally:\n",
    "        cursor.close()\n",
    "        \n",
    "        # inserir\n",
    "def on_button_delete(b):\n",
    "    try:\n",
    "        cursor= con.cursor()\n",
    "        cursor.execute(f\"delete from pessoa where cpf='{cpf.value}'\")\n",
    "        rows_deleted = cursor.rowcount\n",
    "        con.commit()\n",
    "        df = pd.read_sql(\"select * from pessoa\", cnx)\n",
    "        output.clear_output()        \n",
    "        display(nome, cpf, datanasc, endereco, sexo, salario, button_ins, button_del, button_find_cpf, button_find_all, df)\n",
    "    except Exception as e:\n",
    "        cursor.execute(\"ROLLBACK\")\n",
    "        output.clear_output()        \n",
    "        display(nome, cpf, datanasc, endereco, sexo, salario, button_ins, button_del, button_find_cpf, button_find_all)\n",
    "        display(\"Não foi possível realizar deletar. Verifique restrições.\")\n",
    "        display(e)\n",
    "        traceback.print_exc()\n",
    "    finally:\n",
    "        cursor.close()\n",
    "        \n",
    "# consultar        \n",
    "# neste exemplo o método de consulta usa o dataframe do pandas como retorno. Note que a flag é usada para ignorar quando um \n",
    "# campo for null (condição é sempre verdadeira). Veja que para cpf, que é uma string, foi usado '{cpf.value})' como parametro\n",
    "# e para dnr que é numérico, foi usado {dnr.value} (sem aspas simples).\n",
    "def on_button_find_cpf(b):\n",
    "    try:        \n",
    "        query = f\"select * from pessoa where cpf = '{cpf.value}'\"\n",
    "        \n",
    "        df = pd.read_sql_query(query, cnx)\n",
    "        output.clear_output()\n",
    "        display(nome, cpf, datanasc, endereco, sexo, salario, button_ins, button_del, button_find_cpf, button_find_all, df)      \n",
    "    except Exception as e:\n",
    "        output.clear_output()        \n",
    "        display(nome, cpf, datanasc, endereco, sexo, salario, button_ins, button_del, button_find_cpf, button_find_all)\n",
    "        display(\"Não foi possível realizar a consulta. \")\n",
    "        display(e)\n",
    "        traceback.print_exc()\n",
    "\n",
    "def on_button_find_all(b):\n",
    "    try:        \n",
    "        query = f\"select * from pessoa\"\n",
    "        \n",
    "        df = pd.read_sql_query(query, cnx)\n",
    "        output.clear_output()\n",
    "        display(nome, cpf, datanasc, endereco, sexo, salario, button_ins, button_del, button_find_cpf, button_find_all, df)     \n",
    "    except Exception as e:\n",
    "        output.clear_output()        \n",
    "        display(nome, cpf, datanasc, endereco, sexo, salario, button_ins, button_del, button_find_cpf, button_find_all)\n",
    "        display(\"Não foi possível realizar a consulta. \")\n",
    "        display(e)\n",
    "        traceback.print_exc()\n",
    "        \n",
    "\n",
    "# definição que determinado botão vai executar a ação\n",
    "\n",
    "# botão de inserir \n",
    "button_ins.on_click(on_button_insert)\n",
    "# deletar\n",
    "button_del.on_click(on_button_delete)\n",
    "#botão de consultar\n",
    "button_find_cpf.on_click(on_button_find_cpf)\n",
    "#botão de consultar\n",
    "button_find_all.on_click(on_button_find_all)\n",
    "\n",
    "\n",
    "#saída da tela\n",
    "output = widgets.Output()\n",
    "\n",
    "with output:\n",
    "    output.clear_output()   \n",
    "    display(nome, cpf, datanasc, endereco, sexo, salario, button_ins, button_del, button_find_cpf, button_find_all)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "a84de140",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "a62040cc9b11429d889fdf223ab7c5c0",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output(outputs=({'output_type': 'display_data', 'data': {'text/plain': \"Text(value='', description='Nome: ', p…"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "81622ae8",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c93a24c6",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [],
   "name": "CRUD-Funcionario.ipynb",
   "provenance": []
  },
  "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.10.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}