project_2/02_from_csv_to_PostgreSQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "#  Hispasonic (data dumping; From csv to PostgreSQL)\n",
    "\n",
    "\n",
    "\n",
    "<br>\n",
    "\n",
    "\n",
    "\n",
    "Data migration stands as a pivotal step in the analytics process, and the transfer of information from a `CSV` file to a `PostgreSQL` database not only proves critical but marks the genesis of a transformative journey in the realm of data analytics. This process, an essential component of **ETL (Extraction, Transformation, Loading)**, doesn't just involve data transfer; it harnesses the advantages offered by `PostgreSQL`, a potent and versatile database.\n",
    "\n",
    "<div style=\"text-align:center\">\n",
    "    <img src=\"images/csv_postgre.png\" alt=\"2hand\" style=\"width:350px;\"/>\n",
    "</div>\n",
    "\n",
    "At the initial stage, data collection and cleansing within a `CSV` file lay the groundwork for analysis. While this file holds value, it operates within limitations in terms of management and processing. This is precisely where the data dump to `PostgreSQL` assumes a vital role. The robustness and scalability of `PostgreSQL` provide a conducive environment to store this preprocessed data, allowing for subsequent efficient and precise analysis.\n",
    "\n",
    "The rationale behind this transition lies in `PostgreSQL's` ability to handle **substantial data volumes and support intricate queries**. Migrating to this relational **database enables deeper analysis, yields meaningful insights, and facilitates informed decision-making**.\n",
    "\n",
    "This primary step, data dumping, is merely the commencement of a series of processes within the data analysis lifecycle. It's an integral part of the **ETL** phase, where information is extracted, transformed to suit analytical needs, and ultimately loaded into a robust repository like `PostgreSQL`, laying the foundation for comprehensive and accurate analysis.\n",
    "\n",
    "In this article, we'll delve into the significance of this initial process, dissect the advantages of PostgreSQL as the destination for this data, and comprehend how this transition forms the fundamental pillar for unlocking the potential of information in the world of data analytics.\"\n",
    "\n",
    "---\n",
    "\n",
    "The aim to be achieved are:\n",
    "\n",
    "- Establish communication with postgreSQL through jupyter notebooks.\n",
    "\n",
    "- Dump the file `.csv` into the **PostgreSQL database**.\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import datetime as dt\n",
    "from sqlalchemy import create_engine, func, MetaData, Table\n",
    "import pymysql\n",
    "import psycopg2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next step is enable `load_ext sql`. This is not a standard command in Python, but a directive of the so-called `\"IPython Magic Commands\"`. \n",
    "\n",
    "These commands provide additional functionality within the IPython/Jupyter Notebook environment that are not part of the standard Python feature set. \n",
    "\n",
    "In this case, `'load_ext sql'` is used to load an extension that **allows SQL code to be executed directly inside a Jupyter Notebook cell**. This extension enables the execution of SQL queries in a Python kernel, allowing you to interact with a database directly from a code cell. \n",
    "\n",
    "To use `'load_ext sql'`, you must have the corresponding extension installed. Depending on the configuration of your Jupyter Notebook environment, you may need to install a specific extension that supports SQL execution within Python code cells."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Ensure that the `db_user` has **super user** permissions to:\n",
    "\n",
    "    $sudo -i -u postgres\n",
    "    postgres@R7:~$ psql\n",
    "    \n",
    "    psql (15.5 (Ubuntu 15.5-1.pgdg22.04+1))\n",
    "    Digite «help» para obtener ayuda.\n",
    "    \n",
    "    postgres=# \\conninfo\n",
    "    Está conectado a la base de datos «postgres» como el usuario «postgres» a través del socket en «/var/run/postgresql» port «5432».\n",
    "    \n",
    "    postgres=# \\du\n",
    "                                         Lista de roles\n",
    "     Nombre de rol |                         Atributos                          | Miembro de \n",
    "    ---------------+------------------------------------------------------------+------------\n",
    "     jupyter       | Crear BD                                                   | {}\n",
    "     postgres      | Superusuario, Crear rol, Crear BD, Replicación, Ignora RLS | {}\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The `jupyter user` should have **super user** privileges for executing actions.\n",
    "\n",
    "    postgres=# ALTER ROLE jupyter SUPERUSER;\n",
    "    ALTER ROLE\n",
    "    \n",
    "    postgres=# \\du\n",
    "                                         Lista de roles\n",
    "     Nombre de rol |                         Atributos                          | Miembro de \n",
    "    ---------------+------------------------------------------------------------+------------\n",
    "     jupyter       | Superusuario, Crear BD                                     | {}\n",
    "     postgres      | Superusuario, Crear rol, Crear BD, Replicación, Ignora RLS | {}\n",
    "    \n",
    "    postgres=# \n",
    "    \n",
    "<br>\n",
    "\n",
    "Because we're going to make use of the database from Jupyter using Python.\n",
    "\n",
    "It is necessary to know that the connection would not be possible without the use of an external library. Fortunately, there are two available:\n",
    "\n",
    "\n",
    "- `psycopg2`: It is a PostgreSQL database adapter for Python that provides a direct interface for working with PostgreSQL. It is highly efficient and is used directly to interact with the database, execute queries, handle transactions, etc. It is widely used due to its speed and functionality.\n",
    "\n",
    "\n",
    "- `SQLAlchemy`: It is an object-relational mapping (ORM) library that provides a higher abstraction layer than psycopg2. It allows you to work with the database using Python objects and classes instead of directly writing SQL queries. SQLAlchemy is more flexible and offers different tools for interacting with the database.\n",
    "\n",
    "This is how we can connect to the database using `psycopg2`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['hpw20231220_postgre.csv']"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from os import listdir\n",
    "from os.path import isfile, join\n",
    "\n",
    "ruta = 'csv'\n",
    "listado_csv = [f for f in listdir(ruta) if isfile(join(ruta, f))] # Comprueba los htmls\n",
    "listado_csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>buy</th>\n",
       "      <th>change</th>\n",
       "      <th>sell</th>\n",
       "      <th>price</th>\n",
       "      <th>gift</th>\n",
       "      <th>search</th>\n",
       "      <th>repair</th>\n",
       "      <th>parts</th>\n",
       "      <th>synt_brand</th>\n",
       "      <th>description</th>\n",
       "      <th>city</th>\n",
       "      <th>published</th>\n",
       "      <th>expire</th>\n",
       "      <th>date_scrapped</th>\n",
       "      <th>seen</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>200</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>korg</td>\n",
       "      <td>korg 05r w módulo</td>\n",
       "      <td>Madrid</td>\n",
       "      <td>2023/07/27</td>\n",
       "      <td>2024/06/14</td>\n",
       "      <td>2023/12/20</td>\n",
       "      <td>456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>360</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>waldorf</td>\n",
       "      <td>waldorf pulse rack</td>\n",
       "      <td>Barcelona</td>\n",
       "      <td>2023/12/18</td>\n",
       "      <td>2024/06/15</td>\n",
       "      <td>2023/12/20</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>495</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>roland</td>\n",
       "      <td>sampler s750 roland</td>\n",
       "      <td>Madrid</td>\n",
       "      <td>2023/09/30</td>\n",
       "      <td>2024/06/17</td>\n",
       "      <td>2023/12/20</td>\n",
       "      <td>440</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>roland</td>\n",
       "      <td>roland rd 2000</td>\n",
       "      <td>Madrid</td>\n",
       "      <td>2023/11/19</td>\n",
       "      <td>2024/06/12</td>\n",
       "      <td>2023/12/20</td>\n",
       "      <td>286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>340</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>studiologic</td>\n",
       "      <td>studiologic numa compact 2</td>\n",
       "      <td>Castellón</td>\n",
       "      <td>2023/12/16</td>\n",
       "      <td>2024/06/13</td>\n",
       "      <td>2023/12/20</td>\n",
       "      <td>124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>650</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>kurzweil</td>\n",
       "      <td>kurzweil pc3 le6</td>\n",
       "      <td>Madrid</td>\n",
       "      <td>2023/11/05</td>\n",
       "      <td>2024/06/17</td>\n",
       "      <td>2023/12/20</td>\n",
       "      <td>252</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>425</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>korg</td>\n",
       "      <td>korg drumlogue</td>\n",
       "      <td>Bizkaia</td>\n",
       "      <td>2023/01/24</td>\n",
       "      <td>2024/06/16</td>\n",
       "      <td>2023/12/20</td>\n",
       "      <td>1530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>roland</td>\n",
       "      <td>sonidos roland jupiter x xm series y zenology ...</td>\n",
       "      <td>Pontevedra</td>\n",
       "      <td>2022/07/15</td>\n",
       "      <td>2024/06/16</td>\n",
       "      <td>2023/12/20</td>\n",
       "      <td>1744</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>doepfer</td>\n",
       "      <td>doepfer dual quantizer vintage edition</td>\n",
       "      <td>Madrid</td>\n",
       "      <td>2023/11/29</td>\n",
       "      <td>2024/06/13</td>\n",
       "      <td>2023/12/20</td>\n",
       "      <td>82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>75</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>m-audio</td>\n",
       "      <td>controlador m-audio 5 octavas evolution mk461c</td>\n",
       "      <td>Madrid</td>\n",
       "      <td>2023/02/15</td>\n",
       "      <td>2024/06/16</td>\n",
       "      <td>2023/12/20</td>\n",
       "      <td>629</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   buy  change  sell  price  gift  search  repair  parts   synt_brand  \\\n",
       "0    0       0     1    200     0       0       0      0         korg   \n",
       "1    0       0     1    360     0       0       0      0      waldorf   \n",
       "2    0       0     1    495     0       0       0      0       roland   \n",
       "3    0       1     0      0     0       0       0      0       roland   \n",
       "4    0       0     1    340     0       0       0      0  studiologic   \n",
       "5    0       0     1    650     0       0       0      0     kurzweil   \n",
       "6    0       0     1    425     0       0       0      0         korg   \n",
       "7    0       0     1     22     0       0       0      0       roland   \n",
       "8    0       0     1     90     0       0       0      0      doepfer   \n",
       "9    0       0     1     75     0       0       0      0      m-audio   \n",
       "\n",
       "                                         description        city   published  \\\n",
       "0                                 korg 05r w módulo       Madrid  2023/07/27   \n",
       "1                                waldorf pulse rack    Barcelona  2023/12/18   \n",
       "2                               sampler s750 roland       Madrid  2023/09/30   \n",
       "3                                    roland rd 2000       Madrid  2023/11/19   \n",
       "4                        studiologic numa compact 2    Castellón  2023/12/16   \n",
       "5                                  kurzweil pc3 le6       Madrid  2023/11/05   \n",
       "6                                    korg drumlogue      Bizkaia  2023/01/24   \n",
       "7  sonidos roland jupiter x xm series y zenology ...  Pontevedra  2022/07/15   \n",
       "8            doepfer dual quantizer vintage edition       Madrid  2023/11/29   \n",
       "9    controlador m-audio 5 octavas evolution mk461c       Madrid  2023/02/15   \n",
       "\n",
       "       expire date_scrapped  seen  \n",
       "0  2024/06/14    2023/12/20   456  \n",
       "1  2024/06/15    2023/12/20    88  \n",
       "2  2024/06/17    2023/12/20   440  \n",
       "3  2024/06/12    2023/12/20   286  \n",
       "4  2024/06/13    2023/12/20   124  \n",
       "5  2024/06/17    2023/12/20   252  \n",
       "6  2024/06/16    2023/12/20  1530  \n",
       "7  2024/06/16    2023/12/20  1744  \n",
       "8  2024/06/13    2023/12/20    82  \n",
       "9  2024/06/16    2023/12/20   629  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"csv/hpw20231220_postgre.csv\")\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Configure the connection to the database thru sqlalchemy\n",
    "\n",
    "db_user = 'jupyter'\n",
    "db_password = 'jjxx33pp'\n",
    "db_name = 'postgres'\n",
    "db_host = 'localhost'  # Change this if the database is on another host\n",
    "db_port = '5432'       # Change this if the PostgreSQL port is different\n",
    "\n",
    "# Create the connection string\n",
    "connection_string = f'postgresql://{db_user}:{db_password}@{db_host}:{db_port}/{db_name}'\n",
    "\n",
    "# Create the SQLAlchemy engine\n",
    "engine = create_engine(connection_string)\n",
    "conn = engine.connect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Comprobemos que la base de datos está vacia."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The 'hispasonic' table has 1 rows.\n"
     ]
    }
   ],
   "source": [
    "# Obtenemos la metadata y la tabla\n",
    "metadata = MetaData(bind=engine)\n",
    "hispasonic_table = Table('hispasonic', metadata, autoload=True, autoload_with=engine)\n",
    "\n",
    "# Consulta para contar la cantidad de filas en la tabla\n",
    "count_query = hispasonic_table.select().with_only_columns([func.count()])\n",
    "\n",
    "# Ejecutar la consulta y obtener el resultado\n",
    "count = engine.execute(count_query).scalar()\n",
    "\n",
    "# Verificar si la tabla está vacía\n",
    "if count == 0:\n",
    "    print(\"The 'hispasonic' table is empty\")\n",
    "else:\n",
    "    print(f\"The 'hispasonic' table has {count} rows.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data successfully exported to PostgreSQL.stgreSQL.\n"
     ]
    }
   ],
   "source": [
    "conn = engine.connect()\n",
    "\n",
    "# Export the dfFrame to the \"hispasonic\" table in the database\n",
    "df.to_sql('hispasonic', engine, if_exists='append', index=False)\n",
    "\n",
    "print(\"Data successfully exported to PostgreSQL.stgreSQL.\")\n",
    "# Cerrar la conexión\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>buy</th>\n",
       "      <th>change</th>\n",
       "      <th>sell</th>\n",
       "      <th>price</th>\n",
       "      <th>gift</th>\n",
       "      <th>search</th>\n",
       "      <th>repair</th>\n",
       "      <th>parts</th>\n",
       "      <th>synt_brand</th>\n",
       "      <th>description</th>\n",
       "      <th>city</th>\n",
       "      <th>published</th>\n",
       "      <th>expire</th>\n",
       "      <th>date_scrapped</th>\n",
       "      <th>seen</th>\n",
       "      <th>anon_user</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>200</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>korg</td>\n",
       "      <td>korg 05r w módulo</td>\n",
       "      <td>Madrid</td>\n",
       "      <td>2023/07/27</td>\n",
       "      <td>2024/06/14</td>\n",
       "      <td>2023/12/20</td>\n",
       "      <td>456</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>360</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>waldorf</td>\n",
       "      <td>waldorf pulse rack</td>\n",
       "      <td>Barcelona</td>\n",
       "      <td>2023/12/18</td>\n",
       "      <td>2024/06/15</td>\n",
       "      <td>2023/12/20</td>\n",
       "      <td>88</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>495</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>roland</td>\n",
       "      <td>sampler s750 roland</td>\n",
       "      <td>Madrid</td>\n",
       "      <td>2023/09/30</td>\n",
       "      <td>2024/06/17</td>\n",
       "      <td>2023/12/20</td>\n",
       "      <td>440</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>roland</td>\n",
       "      <td>roland rd 2000</td>\n",
       "      <td>Madrid</td>\n",
       "      <td>2023/11/19</td>\n",
       "      <td>2024/06/12</td>\n",
       "      <td>2023/12/20</td>\n",
       "      <td>286</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>340</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>studiologic</td>\n",
       "      <td>studiologic numa compact 2</td>\n",
       "      <td>Castellón</td>\n",
       "      <td>2023/12/16</td>\n",
       "      <td>2024/06/13</td>\n",
       "      <td>2023/12/20</td>\n",
       "      <td>124</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>267</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>600</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>korg</td>\n",
       "      <td>korg cx-3 mki analógico 1979 restaurado</td>\n",
       "      <td>Valencia</td>\n",
       "      <td>2023/10/06</td>\n",
       "      <td>2024/06/17</td>\n",
       "      <td>2023/12/20</td>\n",
       "      <td>657</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1100</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>synthstrom</td>\n",
       "      <td>synthstrom deluge oled</td>\n",
       "      <td>Madrid</td>\n",
       "      <td>2023/12/20</td>\n",
       "      <td>2024/06/17</td>\n",
       "      <td>2023/12/20</td>\n",
       "      <td>90</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>269</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>behringer</td>\n",
       "      <td>behringer eurorack system-100 121 + 150</td>\n",
       "      <td>Barcelona</td>\n",
       "      <td>2023/12/06</td>\n",
       "      <td>2024/06/16</td>\n",
       "      <td>2023/12/20</td>\n",
       "      <td>158</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>270</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>190</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>korg</td>\n",
       "      <td>korg n5 fallo iluminación pantalla</td>\n",
       "      <td>Madrid</td>\n",
       "      <td>2023/03/31</td>\n",
       "      <td>2024/06/16</td>\n",
       "      <td>2023/12/20</td>\n",
       "      <td>702</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>271</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1450</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>nord electro</td>\n",
       "      <td>4 sw 73</td>\n",
       "      <td>Guadalajara</td>\n",
       "      <td>2023/10/27</td>\n",
       "      <td>2024/06/16</td>\n",
       "      <td>2023/12/20</td>\n",
       "      <td>197</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>272 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     buy  change  sell  price  gift  search  repair  parts     synt_brand  \\\n",
       "0      0       0     1    200     0       0       0      0           korg   \n",
       "1      0       0     1    360     0       0       0      0        waldorf   \n",
       "2      0       0     1    495     0       0       0      0         roland   \n",
       "3      0       1     0      0     0       0       0      0         roland   \n",
       "4      0       0     1    340     0       0       0      0    studiologic   \n",
       "..   ...     ...   ...    ...   ...     ...     ...    ...            ...   \n",
       "267    0       0     1    600     0       0       0      0           korg   \n",
       "268    0       0     1   1100     0       0       0      0     synthstrom   \n",
       "269    0       0     1     90     0       0       0      0      behringer   \n",
       "270    0       0     1    190     0       0       0      0           korg   \n",
       "271    0       0     1   1450     0       0       0      0  nord electro    \n",
       "\n",
       "                                  description         city   published  \\\n",
       "0                          korg 05r w módulo        Madrid  2023/07/27   \n",
       "1                         waldorf pulse rack     Barcelona  2023/12/18   \n",
       "2                        sampler s750 roland        Madrid  2023/09/30   \n",
       "3                             roland rd 2000        Madrid  2023/11/19   \n",
       "4                 studiologic numa compact 2     Castellón  2023/12/16   \n",
       "..                                        ...          ...         ...   \n",
       "267  korg cx-3 mki analógico 1979 restaurado      Valencia  2023/10/06   \n",
       "268                   synthstrom deluge oled        Madrid  2023/12/20   \n",
       "269  behringer eurorack system-100 121 + 150     Barcelona  2023/12/06   \n",
       "270       korg n5 fallo iluminación pantalla        Madrid  2023/03/31   \n",
       "271                                  4 sw 73   Guadalajara  2023/10/27   \n",
       "\n",
       "         expire date_scrapped  seen anon_user  \n",
       "0    2024/06/14    2023/12/20   456      None  \n",
       "1    2024/06/15    2023/12/20    88      None  \n",
       "2    2024/06/17    2023/12/20   440      None  \n",
       "3    2024/06/12    2023/12/20   286      None  \n",
       "4    2024/06/13    2023/12/20   124      None  \n",
       "..          ...           ...   ...       ...  \n",
       "267  2024/06/17    2023/12/20   657      None  \n",
       "268  2024/06/17    2023/12/20    90      None  \n",
       "269  2024/06/16    2023/12/20   158      None  \n",
       "270  2024/06/16    2023/12/20   702      None  \n",
       "271  2024/06/16    2023/12/20   197      None  \n",
       "\n",
       "[272 rows x 16 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Establecer una nueva conexión\n",
    "conn = engine.connect()\n",
    "\n",
    "# Realizar operaciones en la base de datos...\n",
    "def consult(text):\n",
    "    # Realiza la consulta\n",
    "    consult = text\n",
    "    return pd.read_sql(consult, engine)\n",
    "\n",
    "#consult(\"DELETE FROM hispasonic;\")\n",
    "\n",
    "consult(\"SELECT * FROM hispasonic;\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that the dataframe has been imported into the database correctly. Working with Jupyter's database isn't exactly convenient, so what you usually do is use software like:\n",
    "\n",
    "\"In the world of data analysis, there are several specialized tools for data visualization, and some of the most commonly used ones include:\n",
    "\n",
    "1. **Tableau:** It's a popular tool for creating interactive visualizations and dashboards without the need for coding. It allows connecting multiple data sources and offers a wide range of visualization options.\n",
    "\n",
    "2. **Power BI:** Developed by Microsoft, it's a powerful tool for creating interactive reports and dashboards. It enables data connection and visualization from various sources, along with analysis and modeling capabilities.\n",
    "\n",
    "3. **QlikView and Qlik Sense:** These tools allow interactive data visualization and dashboard creation. They provide data discovery and associative analysis capabilities.\n",
    "\n",
    "4. **Google Data Studio:** It's a free tool from Google that enables the creation of visually appealing reports and dashboards. It integrates well with other Google tools like Google Analytics and Google Sheets.\n",
    "\n",
    "5. **Plotly:** Although it's a Python library, it also has an online graphical interface version that allows creating interactive visualizations directly on the web.\n",
    "\n",
    "6. **D3.js:** It's a JavaScript library for creating advanced and customized visualizations. While it requires programming knowledge, it offers a high degree of flexibility and customization.\n",
    "\n",
    "These tools enable the creation of interactive and appealing data visualizations, offering different levels of complexity and flexibility based on the data analyst's needs and the specific project requirements.\""
   ]
  }
 ],
 "metadata": {
  "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.8.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}