notebook.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "## API GOOGLE"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "source": [
    "import requests\n",
    "import pandas as pd\n",
    "import json"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "source": [
    "def consumir_api(url, busca=''):\n",
    "    url = str(url)\n",
    "    busca = str(busca)\n",
    "    resposta = requests.get(url+busca).json()\n",
    "    return resposta\n",
    "\n",
    "\n",
    "def head(resposta, qtd_resposta=10):\n",
    "    limpo = resposta['items'][:qtd_resposta]\n",
    "    return limpo\n",
    "\n",
    "def valida_chave_existe(dict_book : dict, key : str):\n",
    "    if key in dict_book:\n",
    "        dict_livro['descricao'] = livro['description']\n",
    "    else:\n",
    "        dict_livro['descricao'] = ''\n",
    "# 'title', authors, 'pageCount', 'maturityRating', 'language', 'infoLink', 'description'\n",
    "def limpa_requisicao_livro(livro):\n",
    "    \n",
    "    dict_livro = {\n",
    "        'titulo' : livro['title'],\n",
    "        'autores' : '; '.join(livro['authors']),\n",
    "        'num_paginas' : livro['pageCount'],\n",
    "        'faixa_etaria' : livro['maturityRating'],\n",
    "        'idioma' : livro['language'],\n",
    "        'link' : livro['infoLink'],\n",
    "    }\n",
    "\n",
    "\n",
    "    ## Tratando descrição\n",
    "    if 'description' in livro:\n",
    "        dict_livro['descricao'] = livro['description']\n",
    "    else:\n",
    "        dict_livro['descricao'] = ''\n",
    "\n",
    "    ## Tratando descrição ↑\n",
    "\n",
    "    ## Tratando iSBN\n",
    "\n",
    "    if 'industryIdentifiers' in livro:\n",
    "        for isbns in livro['industryIdentifiers']:\n",
    "            if isbns['type'] == 'ISBN_13':\n",
    "                dict_livro['isbn'] = {'tipo_isbn': isbns['type'], 'valor': isbns['identifier']}\n",
    "            elif isbns['type'] == 'ISBN_10':\n",
    "                dict_livro['isbn'] = {'tipo_isbn': isbns['type'], 'valor': isbns['identifier']}\n",
    "            else:\n",
    "                {'tipo_isbn':None}\n",
    "    else:\n",
    "        {'tipo_isbn':None}\n",
    "\n",
    "    ## Tratando iSBN ↑\n",
    "\n",
    "\n",
    "    ## Tratando Thumb\n",
    "\n",
    "    if 'imageLinks' in livro:\n",
    "        if 'smallThumbnail' in livro['imageLinks']:\n",
    "            dict_livro['imagem'] = livro['imageLinks']['smallThumbnail']\n",
    "        else:\n",
    "            dict_livro['imagem'] = 'https://raw.githubusercontent.com/kilerhg/TodoBook/main/imagens/livro_sem_capa.png'\n",
    "    else:\n",
    "        dict_livro['imagem'] = 'https://raw.githubusercontent.com/kilerhg/TodoBook/main/imagens/livro_sem_capa.png'\n",
    "\n",
    "    ## Tratando Thumb ↑\n",
    "\n",
    "    return dict_livro"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "' MAIN '\n",
    "\n",
    "url = 'https://www.googleapis.com/books/v1/volumes?q='\n",
    "busca = 'senhor'\n",
    "\n",
    "\n",
    "dados = consumir_api(url,busca)\n",
    "dados_limpo = head(dados)\n",
    "\n",
    "lista_livros = []\n",
    "for livro_sujo in dados_limpo:\n",
    "    livro_limpo = livro_sujo['volumeInfo']\n",
    "    try:\n",
    "        livro_tratado = limpa_requisicao_livro(livro=livro_limpo)\n",
    "    except:\n",
    "        livro_tratado = None\n",
    "    if livro_tratado:\n",
    "        lista_livros.append(livro_tratado)\n",
    "link_livros = [livro['imagem'] for livro in lista_livros]"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "dados_limpo[0]['volumeInfo'].keys()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "len(lista_livros)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "link_livros"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "dados_limpo"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "dados_limpo[1]['volumeInfo']['imageLinks']['smallThumbnail']"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "livro = dados_limpo[2]['volumeInfo']\r\n",
    "print(livro)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "valores = dados_limpo[2]['volumeInfo']\r\n",
    "dicionario_livro_limpo = {}\r\n",
    "for coluna in interessantes:\r\n",
    "    dicionario_livro_limpo[coluna] = valores[coluna]"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "livro['industryIdentifiers'][0]['identifier']"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "livro = dados_limpo[1]['volumeInfo']\r\n",
    "\r\n",
    "dict_livro = {\r\n",
    "    'titulo' : livro['title'],\r\n",
    "    'autores' : '; '.join(livro['authors']),\r\n",
    "    'descricao' : livro['description'],\r\n",
    "    'num_paginas' : livro['pageCount'],\r\n",
    "    'faixa_etaria' : livro['maturityRating'],\r\n",
    "    'idioma' : livro['language'],\r\n",
    "    'link' : livro['infoLink'],\r\n",
    "}\r\n",
    "\r\n",
    "## Tratando iSBN\r\n",
    "\r\n",
    "if 'industryIdentifiers' in livro:\r\n",
    "    for isbns in livro['industryIdentifiers']:\r\n",
    "        if isbns['type'] == 'ISBN_13':\r\n",
    "            dict_livro['isbn'] = {'tipo_isbn': isbns['type'], 'valor': isbns['identifier']}\r\n",
    "        elif isbns['type'] == 'ISBN_10':\r\n",
    "            dict_livro['isbn'] = {'tipo_isbn': isbns['type'], 'valor': isbns['identifier']}\r\n",
    "        else:\r\n",
    "            dict_livro['isbn'] = None\r\n",
    "else:\r\n",
    "    dict_livro['isbn'] = None\r\n",
    "\r\n",
    "## Tratando iSBN ↑\r\n",
    "\r\n",
    "## Tratando Thumb\r\n",
    "\r\n",
    "if 'imageLinks' in livro:\r\n",
    "    if 'smallThumbnail' in livro['imageLinks']:\r\n",
    "        dict_livro['imagem'] = livro['imageLinks']['smallThumbnail']\r\n",
    "    else:\r\n",
    "        dict_livro['imagem'] = 'https://raw.githubusercontent.com/kilerhg/TodoBook/main/imagens/livro_sem_capa.png'\r\n",
    "else:\r\n",
    "    dict_livro['imagem'] = 'https://raw.githubusercontent.com/kilerhg/TodoBook/main/imagens/livro_sem_capa.png'\r\n",
    "\r\n",
    "## Tratando Thumb ↑\r\n"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "dict_livro['isbn']['valor']"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "dicionario_livro_limpo\r\n",
    "\r\n",
    "# '' : livro['imageLinks'],\r\n",
    "# #  https://raw.githubusercontent.com/kilerhg/TodoBook/main/imagens/livro_sem_capa.png\r\n",
    "# 'ISBN' : livro['industryIdentifiers'],\r\n",
    "\r\n"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "dados_limpo[5]['volumeInfo']"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "interessantes = ['title', 'authors', 'description', 'industryIdentifiers', 'maturityRating', 'language', 'infoLink']\r\n",
    "interessantes_opc = ['publisher', 'imageLinks']"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "livro = dados_limpo[0]['volumeInfo']\r\n",
    "for coluna in interessantes:\r\n",
    "    limpo = livro['volumeInfo']\r\n",
    "    if coluna in limpo:\r\n",
    "        print(limpo[coluna])"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "for livro['volumeInfo'] in dados_limpo:\r\n",
    "    print('-=-=-=')\r\n",
    "    print(dados_limpo)\r\n",
    "    for coluna in interessantes:\r\n",
    "        if coluna in livro['volumeInfo']:\r\n",
    "            print(livro[coluna])"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## DB"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "source": [
    "import sqlite3\n",
    "import os\n",
    "from werkzeug.security import generate_password_hash, check_password_hash\n",
    "\n",
    "def connect_db(name='database'):\n",
    "\n",
    "    if os.path.isfile(f'./{name}.db'):\n",
    "        db = sqlite3.connect(f\"{name}.db\")\n",
    "        cursor = db.cursor()\n",
    "    else:\n",
    "        db = sqlite3.connect(f\"{name}.db\")\n",
    "        cursor = db.cursor()\n",
    "\n",
    "        sql_query_generate_status_book = '''\n",
    "            CREATE TABLE status_book (\n",
    "                id_status INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n",
    "                description TEXT(50)\n",
    "            );'''\n",
    "\n",
    "        sql_query_generate_library = '''\n",
    "            CREATE TABLE library (\n",
    "                id_register INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n",
    "                id_user INTEGER NOT NULL,\n",
    "                book_unique_key TEXT(40),\n",
    "                id_status_book INTEGER,\n",
    "                percent_book FLOAT(3),\n",
    "                FOREIGN KEY(id_user) REFERENCES users(id)\n",
    "                FOREIGN KEY(id_status_book) REFERENCES status_book(id_status)\n",
    "            );'''\n",
    "\n",
    "        sql_query_insert_status_book = '''\n",
    "            insert into status_book(id_status, description) values \n",
    "            ('0', 'Not Stated'), \n",
    "            ('1', 'Reading'),\n",
    "            ('2', 'Readed');'''\n",
    "\n",
    "        cursor.execute(sql_query_generate_users)\n",
    "        cursor.execute(sql_query_generate_status_book)\n",
    "        cursor.execute(sql_query_generate_library)\n",
    "\n",
    "        cursor.execute(sql_query_insert_status_book)\n",
    "        db.commit()\n",
    "\n",
    "        \n",
    "    \n",
    "    return db, cursor\n",
    "\n",
    "def insert_user(db, cursor, username : str, email : str, pwd : str):\n",
    "    hash_password = generate_password_hash(pwd)\n",
    "    query_insert = f\"\"\"\n",
    "    insert into users(username, email, password) \n",
    "    values ('{username}', '{email}', '{hash_password}');\"\"\"\n",
    "    try:\n",
    "        cursor.execute(query_insert)\n",
    "    except sqlite3.IntegrityError:\n",
    "        return 'E-mail ja cadastrado'\n",
    "\n",
    "    db.commit()\n",
    "\n",
    "def login_user(cursor, email, pwd):\n",
    "    query_login = f'''\n",
    "    select password from users where email = '{email}';\n",
    "    '''\n",
    "    \n",
    "    cursor.execute(query_login)\n",
    "\n",
    "\n",
    "    hash_password = cursor.fetchone()\n",
    "\n",
    "    if hash_password:\n",
    "        if check_password_hash(hash_password[0], pwd):\n",
    "            return True\n",
    "        else:\n",
    "            return 'senha incorreta'\n",
    "    else:\n",
    "        return 'email não existente'\n",
    "\n",
    "def get_id_by_email(cursor, email : str):\n",
    "    query = f'''\n",
    "        select (id) from users\n",
    "        where email = '{email}';\n",
    "    '''\n",
    "    cursor.execute(query)\n",
    "    value = cursor.fetchone()\n",
    "\n",
    "    if value:\n",
    "        value = value[0]\n",
    "    else:\n",
    "        value = None\n",
    "\n",
    "    return value\n",
    "        \n",
    "\n",
    "def update_password_by_id(cursor, db, id_user, password : str):\n",
    "    query = f'''\n",
    "    UPDATE users\n",
    "    set password = '{password}'\n",
    "    where id = '{id_user}'\n",
    "    '''\n",
    "    cursor.execute(query)\n",
    "    db.commit()\n",
    "\n",
    "def update_email_by_id(cursor, db, id_user, email : str):\n",
    "    query = f'''\n",
    "    UPDATE users\n",
    "    set email = '{email}'\n",
    "    where id = '{id_user}'\n",
    "    '''\n",
    "    cursor.execute(query)\n",
    "    db.commit()\n",
    "\n",
    "def update_username_by_id(cursor, db, id_user, username : str):\n",
    "    query = f'''\n",
    "    UPDATE users\n",
    "    set username = '{username}'\n",
    "    where id = '{id_user}'\n",
    "    '''\n",
    "    cursor.execute(query)\n",
    "    db.commit()\n",
    "\n",
    "\n",
    "db, cursor = connect_db()\n",
    "\n",
    "\n",
    "\n",
    "# login_user(cursor=cursor, email=email, pwd=password)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "source": [
    "username='lucas nunes'\n",
    "email='lucas@gmail.com'\n",
    "password ='123'\n"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "'''\n",
    "To Do account:\n",
    "\n",
    "* Register\n",
    "* Login\n",
    "* Reset Password\n",
    "\n",
    "'''"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "source": [
    "insert_user(db=db, cursor=cursor, username=username, email=email, pwd=password)"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "'E-mail ja cadastrado'"
      ]
     },
     "metadata": {},
     "execution_count": 13
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "source": [
    "login_user(cursor=cursor, email=email, pwd='kiler')"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "metadata": {},
     "execution_count": 16
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "source": [
    "password_new = generate_password_hash('kiler')\n",
    "email_new = 'kilerhg@gmail.com'\n",
    "username_new = 'kikil'"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "source": [
    "id_user = get_id_by_email(cursor=cursor, email=email_new)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "source": [
    "id_user"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "metadata": {},
     "execution_count": 23
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "source": [
    "update_password_by_id(cursor=cursor, db=db, id_user=id_user, password=password_new)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "source": [
    "update_email_by_id(cursor=cursor, db=db, id_user=id_user, email=email_new)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "source": [
    "update_username_by_id(cursor=cursor, db=db, id_user=id_user, username=username_new)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "\n",
    " \n"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## New DB"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "source": [
    "import sqlite3\n",
    "import os\n",
    "\n",
    "def connect_db(name='database'):\n",
    "\n",
    "    if os.path.isfile(f'./{name}.db'):\n",
    "        db = sqlite3.connect(f\"{name}.db\")\n",
    "        cursor = db.cursor()\n",
    "    else:\n",
    "        db = sqlite3.connect(f\"{name}.db\")\n",
    "        cursor = db.cursor()\n",
    "\n",
    "        sql_query_generate_status_book = '''\n",
    "            CREATE TABLE status_book (\n",
    "                id_status INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n",
    "                description TEXT(50)\n",
    "            );'''\n",
    "\n",
    "        sql_query_generate_library = '''\n",
    "            CREATE TABLE library (\n",
    "                id_register INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n",
    "                id_user INTEGER NOT NULL UNIQUE,\n",
    "                book_unique_key TEXT(40),\n",
    "                id_status_book INTEGER,\n",
    "                percent_book FLOAT(3),\n",
    "                FOREIGN KEY(id_status_book) REFERENCES status_book(id_status)\n",
    "            );'''\n",
    "\n",
    "        sql_query_insert_status_book = '''\n",
    "            insert into status_book(id_status, description) values \n",
    "            ('0', 'Not Stated'), \n",
    "            ('1', 'Reading'),\n",
    "            ('2', 'Readed');'''\n",
    "\n",
    "        cursor.execute(sql_query_generate_status_book)\n",
    "        cursor.execute(sql_query_generate_library)\n",
    "\n",
    "        cursor.execute(sql_query_insert_status_book)\n",
    "        db.commit()\n",
    "    \n",
    "    return db, cursor\n",
    "\n",
    "db, cursor = connect_db()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "source": [
    "def insert_book_into_library(db, cursor, id_user, google_book_id):\n",
    "    sql_query_insert_book_into_library = f'''\n",
    "            insert into library(id_user, book_unique_key, id_status_book, percent_book) values \n",
    "            ('{id_user}', '{google_book_id}', '0', '0');'''\n",
    "    cursor.execute(sql_query_insert_book_into_library)\n",
    "    db.commit()\n",
    "\n",
    "def get_id_register(cursor, id_user, google_book_id):\n",
    "    sql_query_get_id_register = f'''\n",
    "            select id_register from library\n",
    "            where id_user = '{id_user}'\n",
    "            and book_unique_key = '{google_book_id}';'''\n",
    "    cursor.execute(sql_query_get_id_register)\n",
    "    register_id = cursor.fetchone()\n",
    "    if register_id:\n",
    "        register_id = register_id[0]\n",
    "    else:\n",
    "        register_id = None\n",
    "    return register_id\n",
    "\n",
    "def update_status_book_by_id(cursor, db, new_status_book, id_register):\n",
    "    sql_query_update_status_book = f'''\n",
    "            update library\n",
    "            set id_status_book = {new_status_book}\n",
    "            where id_register={id_register}'''\n",
    "    cursor.execute(sql_query_update_status_book)\n",
    "    db.commit()\n",
    "\n",
    "def update_percent_book_by_id(cursor, db, new_percent, id_register):\n",
    "    sql_query_update_percent_book = f'''\n",
    "            update library\n",
    "            set percent_book = {new_percent}\n",
    "            where id_register = {id_register};'''\n",
    "    cursor.execute(sql_query_update_percent_book)\n",
    "    db.commit()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "source": [
    "google_book_id = 'oMBADwAAQBAJ'\n",
    "id_user = '1.170687577971458e+20'\n",
    "new_status_book = '3'\n",
    "new_percent = '10'\n",
    "id_register = '1'\n",
    "\n",
    "sql_query_insert_book_into_library = f'''\n",
    "            insert into library(id_user, book_unique_key, id_status_book, percent_book) values \n",
    "            ('{id_user}', '{google_book_id}', '0', '0');'''\n",
    "\n",
    "sql_query_update_status_book = f'''\n",
    "            update library\n",
    "            set id_status_book = {new_status_book}\n",
    "            where id_register={id_register}'''\n",
    "\n",
    "sql_query_update_percent_book = f'''\n",
    "            update library\n",
    "            set percent_book = {new_percent}\n",
    "            where id_register = {id_register};'''"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "source": [
    "sql_query_get_id_register = f'''\n",
    "            select id_register from library\n",
    "            where id_user = '{id_user}'\n",
    "            and book_unique_key = '{google_book_id}';'''"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "source": [
    "cursor.execute('select * from library')\n",
    "valores = cursor.fetchall()\n",
    "print(valores)"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "[(3, 1.170687577971458e+20, 'GOm_AwAAQBAJ', 0, 0.0)]\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "source": [
    "id_user = 1.170687577971458e+20\n",
    "\n",
    "cursor.execute(f'''\n",
    "    select book_unique_key from library\n",
    "    where id_user = '{id_user}'\n",
    ";'''\n",
    ")\n",
    "valores = cursor.fetchall()\n",
    "if valores:\n",
    "    valores = [book[0] for book in valores]\n",
    "else:\n",
    "    valores = []"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "source": [
    "valores"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "['oMBADwAAQBAJ', 'GOm_AwAAQBAJ']"
      ]
     },
     "metadata": {},
     "execution_count": 89
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "'''\n",
    "' MAIN '\n",
    "\n",
    "url = 'https://www.googleapis.com/books/v1/volumes?q='\n",
    "busca = 'senhor'\n",
    "\n",
    "\n",
    "dados = consumir_api(url,busca)\n",
    "dados_limpo = head(dados)\n",
    "\n",
    "lista_livros = []\n",
    "for livro_sujo in dados_limpo:\n",
    "    livro_limpo = livro_sujo['volumeInfo']\n",
    "    try:\n",
    "        livro_tratado = limpa_requisicao_livro(livro=livro_limpo)\n",
    "    except:\n",
    "        livro_tratado = None\n",
    "    if livro_tratado:\n",
    "        lista_livros.append(livro_tratado)\n",
    "link_livros = [livro['imagem'] for livro in lista_livros]\n",
    "'''\n"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "source": [
    "def get_id_books_by_user_id(id_user):\n",
    "    cursor.execute(f'''\n",
    "        select book_unique_key from library\n",
    "        where id_user = '{id_user}'\n",
    "    ;'''\n",
    "    )\n",
    "    valores = cursor.fetchall()\n",
    "    if valores:\n",
    "        valores = [book[0] for book in valores]\n",
    "    else:\n",
    "        valores = []\n",
    "    return valores\n",
    "\n",
    "def get_books_by_id(list_books_id : list):\n",
    "    base_url = 'https://www.googleapis.com/books/v1/volumes/'\n",
    "    list_book_library = []\n",
    "    for book in list_books_id:\n",
    "        link_book = base_url+book\n",
    "        dados = consumir_api(link_book)\n",
    "        dados_limpos = limpa_requisicao_livro(livro=dados['volumeInfo'])\n",
    "        dados_limpos['book_id'] = dados['id']\n",
    "        list_book_library.append(dados_limpos)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "source": [
    "base_url = 'https://www.googleapis.com/books/v1/volumes/'\n",
    "list_book_library = []\n",
    "for book in valores:\n",
    "    link_book = base_url+book\n",
    "    dados = consumir_api(link_book)\n",
    "    dados_limpos = limpa_requisicao_livro(livro=dados['volumeInfo'])\n",
    "    dados_limpos['book_id'] = dados['id']\n",
    "    list_book_library.append(dados_limpos)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "source": [
    "list_book_library[1]\n"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "{'titulo': 'Teste Ergométrico no Paciente Hipertenso',\n",
       " 'autores': 'Augusto Heitor Xavier de Brito',\n",
       " 'num_paginas': 64,\n",
       " 'faixa_etaria': 'NOT_MATURE',\n",
       " 'idioma': 'pt',\n",
       " 'link': 'https://play.google.com/store/books/details?id=GOm_AwAAQBAJ&source=gbs_api',\n",
       " 'descricao': '<p>Desde que foi introduzida no Brasil, a ergometria clínica experimentou muitos e variados caminhos. Como parte dessa evolução, a hipertensão arterial passou a figurar em primeiro lugar entre todas as atuais indicações para o teste ergométrico em quase todos os centros mundiais. A única restrição era a hipertensão dita grave, aquela com PAD de repouso igual ou maior que 120mmHg em obediência às atuais diretrizes que regem a utilização do método. Ergometrista com mais de 35 anos de prática ininterrupta e<br>cerca de 80 mil exames realizados, o Dr. Augusto Xavier de Brito descreve neste livro sua vasta experiência com o teste ergométrico no paciente hipertenso com qualquer nível de PA pré-esforço, demonstrando a grande utilidade do método para a avaliação clínica da doença, sem quaisquer riscos<br>adicionais para os pacientes. <br></p>',\n",
       " 'isbn': {'tipo_isbn': 'ISBN_13', 'valor': '9788577710836'},\n",
       " 'imagem': 'http://books.google.com/books/publisher/content?id=GOm_AwAAQBAJ&printsec=frontcover&img=1&zoom=5&edge=curl&imgtk=AFLRE70bbUM0T3_8VvA5z5SQSqFcVSzBMkAn-Gr-07BjOC7bm-MkhcuiXcxceuoJgm7_ZqrgNJ-MVaGfQ3WiMuX-N-KcOQ84E6-8_M6uzOfkoqVJ0lBXRn6m-2iHQvg5RnMTetCPpk1u&source=gbs_api',\n",
       " 'book_id': 'GOm_AwAAQBAJ'}"
      ]
     },
     "metadata": {},
     "execution_count": 94
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "source": [],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "source": [
    "dados_limpos"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "{'titulo': 'Teste de desempenho com JMeter 3',\n",
       " 'autores': 'Bayo Erinle',\n",
       " 'num_paginas': 160,\n",
       " 'faixa_etaria': 'NOT_MATURE',\n",
       " 'idioma': 'en',\n",
       " 'link': 'https://play.google.com/store/books/details?id=oMBADwAAQBAJ&source=gbs_api',\n",
       " 'descricao': 'O JMeter é uma aplicação Java projetado para carregar e testar o desempenho de aplicações web. Este livro é um ótimo ponto de partida para conhecê-lo. Ele aborda os novos recursos introduzidos com o JMeter3 e permite conhecer a fundo as novas técnicas necessárias para a avaliação do desempenho de um site. O livro começa com os aspectos básicos do teste de desempenho e nos guia pelo registro do primeiro cenário de teste, antes de se aprofundar no JMeter. Além disso, ensina a configurar o JMeter e os navegadores para nos ajudarem a registrar planos de teste. Ao avançar, você aprenderá a capturar o envio de formulários no JMeter, examinará o gerenciamento de sessões com a ferramenta e verá como se beneficiar de alguns dos componentes fornecidos pelo JMeter para a manipulação de sessões HTTP de aplicações web. Também aprenderá como o JMeter pode ajudar a monitorar testes em tempo real. Examinaremos detalhadamente a execução de testes distribuídos e veremos como nos beneficiar dos recursos do JMeter para realizá-los. Você também conhecerá algumas dicas e melhores práticas relacionadas ao teste de desempenho. Ao terminar o livro, saberá extrair o máximo do poder fornecido pelo Apache JMeter. Você aprenderá: por que o teste de desempenho é necessário e a configurar o JMeter a registrar e testar com JMeter a manipular entradas de formulário no JMeter e analisar os resultados durante o teste a gerenciar sessões de usuário em aplicações web no contexto de um teste do JMeter a monitorar os resultados do JMeter em tempo real a executar testes distribuídos com JMeter dicas e melhores práticas úteis para o trabalho com JMeter',\n",
       " 'isbn': {'tipo_isbn': 'ISBN_13', 'valor': '9788575226414'},\n",
       " 'imagem': 'http://books.google.com/books/publisher/content?id=oMBADwAAQBAJ&printsec=frontcover&img=1&zoom=5&edge=curl&imgtk=AFLRE71zE7OpIm-sWlrTBVklYx2az6gdIEUgvd9sR7AoMvdpSvA80hkbwRE6xz8iBvqwagXSBYPTM9EpfqiUZlV-yS5OPU-3Z4qNDlp6BNRn8QZ7NwEBQ8tcMLU6m4_ju-xfCoDnRTMh&source=gbs_api',\n",
       " 'book_id': 'oMBADwAAQBAJ'}"
      ]
     },
     "metadata": {},
     "execution_count": 79
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "source": [
    "# cursor.execute(sql_query_get_id_register)\n",
    "id_register = '2'\n",
    "print(valores)"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "[(2, 1.170687577971458e+20, 'oMBADwAAQBAJ', 0, 0.0), (3, 1.170687577971458e+20, 'GOm_AwAAQBAJ', 0, 0.0)]\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "source": [
    "cursor.execute(sql_query_insert_book_into_library)\n",
    "db.commit()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "[(1, '', '123', 0, 0.0)]\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "source": [
    "cursor.execute(sql_query_update_status_book)\n",
    "db.commit()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "source": [
    "cursor.execute(sql_query_update_percent_book)\n",
    "db.commit()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "source": [
    "sql_query_remove_book_into_library = f'''\n",
    "delete from library\n",
    "where id_register='{id_register}'\n",
    "'''\n",
    "cursor.execute(sql_query_remove_book_into_library)\n",
    "db.commit()"
   ],
   "outputs": [],
   "metadata": {}
  }
 ],
 "metadata": {
  "kernelspec": {
   "name": "python3",
   "display_name": "Python 3.8.5 64-bit ('venv': venv)"
  },
  "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.8.5"
  },
  "interpreter": {
   "hash": "15e166b57557d3a4cbacb9ec2aba57f7f8edfc90bd893c0116c7b5b44fadd40a"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}