N23) Conexion a PostgreSQL- SQLAlchemy.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "ed383200",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting psycopg2\n",
      "  Downloading psycopg2-2.9.3-cp39-cp39-win_amd64.whl (1.2 MB)\n",
      "Installing collected packages: psycopg2\n",
      "Successfully installed psycopg2-2.9.3\n"
     ]
    }
   ],
   "source": [
    "!pip install psycopg2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "0c1da9bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from sqlalchemy import create_engine, text\n",
    "import psycopg2\n",
    "pd.options.display.max_rows = 10\n",
    "hostname= 'localhost'\n",
    "database= 'Semana6_DE'\n",
    "username= 'postgres'\n",
    "pwd='david9.25.38'\n",
    "port_id= '5432'\n",
    "import psycopg2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "94fb9636",
   "metadata": {},
   "source": [
    "# SQLalchemy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "46898885",
   "metadata": {},
   "outputs": [],
   "source": [
    "engine= create_engine(\"postgresql://postgres:contraseña@localhost:5432/Semana6_DE\")\n",
    "#engine= create_engine(string)\n",
    "# Caso redshift\n",
    "#engine= conn = create_engine('postgresql://username:password@yoururl.com:5439/yourdatabase')\n",
    "ruta_archivos='C:/Users/Windows/Downloads/Caso_practico_SQL_repsuestas/Caso_practico_SQL_respuestas/data/csvs/'\n",
    "df = pd.read_csv(ruta_archivos+'product.csv').to_sql('product', engine, if_exists='replace', index=False)\n",
    "df = pd.read_csv(ruta_archivos+'productreview.csv').to_sql('productreview', engine, if_exists='replace', index=False)\n",
    "df = pd.read_csv(ruta_archivos+'productmodelproductdescriptionculture.csv').to_sql('productmodelproductdescriptionculture', engine, if_exists='replace', index=False)\n",
    "df = pd.read_csv(ruta_archivos+'productdescription.csv').to_sql('productdescription', engine, if_exists='replace', index=False)\n",
    "df = pd.read_csv(ruta_archivos+'salesorderdetail.csv').to_sql('salesorderdetail', engine, if_exists='replace', index=False)\n",
    "df = pd.read_csv(ruta_archivos+'productcategory.csv').to_sql('productcategory', engine, if_exists='replace', index=False)\n",
    "df = pd.read_csv(ruta_archivos+'productsubcategory.csv').to_sql('productsubcategory', engine, if_exists='replace', index=False)\n",
    "df = pd.read_csv(ruta_archivos+'salesperson.csv').to_sql('salesperson', engine, if_exists='replace', index=False)\n",
    "df = pd.read_csv(ruta_archivos+'salesorderheader.csv').to_sql('salesorderheader', engine, if_exists='replace', index=False)\n",
    "df = pd.read_csv(ruta_archivos+'salesterritory.csv').to_sql('salesterritory', engine, if_exists='replace', index=False)\n",
    "df = pd.read_csv(ruta_archivos+'countryregioncurrency.csv').to_sql('countryregioncurrency', engine, if_exists='replace', index=False)\n",
    "df = pd.read_csv(ruta_archivos+'currencyrate.csv').to_sql('currencyrate', engine, if_exists='replace', index=False)\n",
    "# funcion para ejecutar comandos en python\n",
    "def runQuery(sql):\n",
    "    result = engine.connect().execute((text(sql)))\n",
    "    return pd.DataFrame(result.fetchall(), columns=result.keys())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e2255a16",
   "metadata": {},
   "source": [
    "**Pregunta 1**\n",
    "\n",
    "Usando las tablas ```product``` y ```productreview```, JOIN y clasifica los productos de acuerdo con su calificación promedio de revisión. ¿Cuáles son los nombres y las identificaciones de los 5 productos principales"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "93c64bcf",
   "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>productid</th>\n",
       "      <th>name</th>\n",
       "      <th>avgrating</th>\n",
       "      <th>num_ratings</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>798</td>\n",
       "      <td>Road-550-W Yellow, 40</td>\n",
       "      <td>5.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>709</td>\n",
       "      <td>Mountain Bike Socks, M</td>\n",
       "      <td>5.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>937</td>\n",
       "      <td>HL Mountain Pedal</td>\n",
       "      <td>3.00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   productid                    name avgrating  num_ratings\n",
       "0        798   Road-550-W Yellow, 40      5.00            1\n",
       "1        709  Mountain Bike Socks, M      5.00            1\n",
       "2        937       HL Mountain Pedal      3.00            2"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query1=\"\"\"SELECT product.productid, name, round(avg(rating), 2) as avgrating, count(*) as num_ratings\n",
    "FROM product inner join productreview\n",
    "ON productreview.productid = product.productid\n",
    "GROUP BY product.productid, name\n",
    "ORDER BY avgrating DESC\"\"\"\n",
    "runQuery(query1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "843cd004",
   "metadata": {},
   "source": [
    "**Pregunta 2**\n",
    "\n",
    "Para su decepción, ¡solo hay tres productos con calificaciones y solo cuatro reseñas en total! Esto no es lo suficientemente cerca como para realizar un análisis de la correlación entre las revisiones y las ventas totales.\n",
    "\n",
    "Sin embargo, su gerente quiere la descripción en inglés de estos productos para una próxima venta. ¡Utilice la documentación proporcionada anteriormente si necesita ayuda para navegar por la estructura para extraer esto!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "9f9ff9d5",
   "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>name</th>\n",
       "      <th>description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Road-550-W Yellow, 40</td>\n",
       "      <td>Same technology as all of our Road series bike...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>HL Mountain Pedal</td>\n",
       "      <td>Stainless steel; designed to shed mud easily.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mountain Bike Socks, M</td>\n",
       "      <td>Combination of natural and synthetic fibers st...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     name                                        description\n",
       "0   Road-550-W Yellow, 40  Same technology as all of our Road series bike...\n",
       "1       HL Mountain Pedal      Stainless steel; designed to shed mud easily.\n",
       "2  Mountain Bike Socks, M  Combination of natural and synthetic fibers st..."
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query2=\"\"\"\n",
    "SELECT \"name\",\n",
    "       description\n",
    "FROM productdescription pd\n",
    "INNER JOIN productmodelproductdescriptionculture pm ON pm.productdescriptionid=pd.productdescriptionid\n",
    "INNER JOIN product ON product.productmodelid = pm.productmodelid\n",
    "WHERE productid IN (798,709,937)\n",
    "  AND cultureid = 'en'\n",
    "\"\"\"\n",
    "runQuery(query2)"
   ]
  }
 ],
 "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.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}