UTS(SQLALCHEMY)_Alya Fitri Nurhaliza_205150201111047.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "54646f0c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: sqlalchemy in c:\\python310\\lib\\site-packages (1.4.44)\n",
      "Requirement already satisfied: greenlet!=0.4.17 in c:\\python310\\lib\\site-packages (from sqlalchemy) (2.0.1)\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING: Ignoring invalid distribution -ip (c:\\python310\\lib\\site-packages)\n",
      "WARNING: Ignoring invalid distribution -ip (c:\\python310\\lib\\site-packages)\n",
      "WARNING: Ignoring invalid distribution -ip (c:\\python310\\lib\\site-packages)\n",
      "WARNING: Ignoring invalid distribution -ip (c:\\python310\\lib\\site-packages)\n",
      "WARNING: Ignoring invalid distribution -ip (c:\\python310\\lib\\site-packages)\n",
      "WARNING: Ignoring invalid distribution -ip (c:\\python310\\lib\\site-packages)\n",
      "\n",
      "[notice] A new release of pip available: 22.3 -> 22.3.1\n",
      "[notice] To update, run: python.exe -m pip install --upgrade pip\n"
     ]
    }
   ],
   "source": [
    "pip install sqlalchemy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "1b15ee1c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: psycopg2 in c:\\python310\\lib\\site-packages (2.9.5)\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING: Ignoring invalid distribution -ip (c:\\python310\\lib\\site-packages)\n",
      "WARNING: Ignoring invalid distribution -ip (c:\\python310\\lib\\site-packages)\n",
      "WARNING: Ignoring invalid distribution -ip (c:\\python310\\lib\\site-packages)\n",
      "WARNING: Ignoring invalid distribution -ip (c:\\python310\\lib\\site-packages)\n",
      "WARNING: Ignoring invalid distribution -ip (c:\\python310\\lib\\site-packages)\n",
      "WARNING: Ignoring invalid distribution -ip (c:\\python310\\lib\\site-packages)\n",
      "\n",
      "[notice] A new release of pip available: 22.3 -> 22.3.1\n",
      "[notice] To update, run: python.exe -m pip install --upgrade pip\n"
     ]
    }
   ],
   "source": [
    "pip install psycopg2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "48c7e10e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlalchemy as db\n",
    "import psycopg2\n",
    "import pandas as pd\n",
    "from sqlalchemy.dialects.postgresql import insert"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "f13ea077",
   "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>Unnamed: 0</th>\n",
       "      <th>bike_id</th>\n",
       "      <th>bike_name</th>\n",
       "      <th>region_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>799</td>\n",
       "      <td>GRID 086</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1356</td>\n",
       "      <td>GRID 182</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>5032</td>\n",
       "      <td>M 2264</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>5046</td>\n",
       "      <td>GRID 402 FIRMWARE RESCUE</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>10563</td>\n",
       "      <td>GRID 2007</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>12749</td>\n",
       "      <td>T723</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>12763</td>\n",
       "      <td>T755</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>12766</td>\n",
       "      <td>T756</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>12786</td>\n",
       "      <td>T731</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>12845</td>\n",
       "      <td>T819</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10</td>\n",
       "      <td>12866</td>\n",
       "      <td>T837</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11</td>\n",
       "      <td>12869</td>\n",
       "      <td>T839</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>12</td>\n",
       "      <td>12872</td>\n",
       "      <td>T833</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>13</td>\n",
       "      <td>12881</td>\n",
       "      <td>T782</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>14</td>\n",
       "      <td>13042</td>\n",
       "      <td>T939</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>15</td>\n",
       "      <td>13053</td>\n",
       "      <td>T986</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>16</td>\n",
       "      <td>13054</td>\n",
       "      <td>T987</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>17</td>\n",
       "      <td>13069</td>\n",
       "      <td>T996</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>18</td>\n",
       "      <td>13095</td>\n",
       "      <td>T961</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>19</td>\n",
       "      <td>13103</td>\n",
       "      <td>T933</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>20</td>\n",
       "      <td>17318</td>\n",
       "      <td>GRID 2025</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Unnamed: 0  bike_id                 bike_name  region_id\n",
       "0            0      799                 GRID 086          81\n",
       "1            1     1356                  GRID 182         80\n",
       "2            2     5032                    M 2264         81\n",
       "3            3     5046  GRID 402 FIRMWARE RESCUE         80\n",
       "4            4    10563                 GRID 2007         80\n",
       "5            5    12749                      T723         80\n",
       "6            6    12763                      T755         80\n",
       "7            7    12766                      T756         80\n",
       "8            8    12786                      T731         80\n",
       "9            9    12845                      T819         81\n",
       "10          10    12866                      T837         81\n",
       "11          11    12869                      T839         81\n",
       "12          12    12872                      T833         80\n",
       "13          13    12881                      T782         81\n",
       "14          14    13042                      T939         80\n",
       "15          15    13053                      T986         81\n",
       "16          16    13054                      T987         81\n",
       "17          17    13069                      T996         80\n",
       "18          18    13095                      T961         80\n",
       "19          19    13103                      T933         81\n",
       "20          20    17318                 GRID 2025         81"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_bike = pd.read_csv(\"bike_info.csv\")\n",
    "df_bike"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "5cf4b00d",
   "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>Unnamed: 0</th>\n",
       "      <th>station_id</th>\n",
       "      <th>station_name</th>\n",
       "      <th>region_id</th>\n",
       "      <th>address</th>\n",
       "      <th>num_bikes_available</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>290</td>\n",
       "      <td>P127 Central Ave / Roosevelt St</td>\n",
       "      <td>80</td>\n",
       "      <td>1000-1034 North Central Avenue, Phoenix</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>297</td>\n",
       "      <td>P131 Central Ave / Willetta St  (Sponsored by ...</td>\n",
       "      <td>80</td>\n",
       "      <td>1401 North Central Avenue, Phoenix</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>299</td>\n",
       "      <td>P113 11th St. / Washington St</td>\n",
       "      <td>80</td>\n",
       "      <td>1010-1098 East Washington Street, Phoenix</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>305</td>\n",
       "      <td>P116 6th St. / Monroe St (Sponsored by SRP)</td>\n",
       "      <td>80</td>\n",
       "      <td>567-599 East Monroe Street, Arizona State Univ...</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>309</td>\n",
       "      <td>P123 5th Ave / Fillmore St</td>\n",
       "      <td>80</td>\n",
       "      <td>604, North 5th Avenue, Phoenix, Arizona</td>\n",
       "      <td>6</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>80</td>\n",
       "      <td>3264</td>\n",
       "      <td>P168 5th St. and Washington</td>\n",
       "      <td>80</td>\n",
       "      <td>1-115 South 5th Street, Phoenix</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>81</td>\n",
       "      <td>3525</td>\n",
       "      <td>P196 2nd St. and Roosevelt</td>\n",
       "      <td>80</td>\n",
       "      <td>1021 North 2nd Street, Phoenix</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>82</td>\n",
       "      <td>3582</td>\n",
       "      <td>T21 Forest Ave/ Lemon St. (Gammage)</td>\n",
       "      <td>81</td>\n",
       "      <td>1201 South Forest Avenue, Tempe</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>83</td>\n",
       "      <td>3583</td>\n",
       "      <td>T32 North Tempe Multigen Center</td>\n",
       "      <td>81</td>\n",
       "      <td>1555 North Bridalwreath Street, Tempe</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>84</td>\n",
       "      <td>3590</td>\n",
       "      <td>T06 McClintock Dr / Rio Salado Pkwy</td>\n",
       "      <td>81</td>\n",
       "      <td>1807-1809 East Rio Salado Parkway, Tempe</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>85 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Unnamed: 0  station_id                                       station_name  \\\n",
       "0            0         290                    P127 Central Ave / Roosevelt St   \n",
       "1            1         297  P131 Central Ave / Willetta St  (Sponsored by ...   \n",
       "2            2         299                      P113 11th St. / Washington St   \n",
       "3            3         305        P116 6th St. / Monroe St (Sponsored by SRP)   \n",
       "4            4         309                         P123 5th Ave / Fillmore St   \n",
       "..         ...         ...                                                ...   \n",
       "80          80        3264                        P168 5th St. and Washington   \n",
       "81          81        3525                         P196 2nd St. and Roosevelt   \n",
       "82          82        3582                T21 Forest Ave/ Lemon St. (Gammage)   \n",
       "83          83        3583                    T32 North Tempe Multigen Center   \n",
       "84          84        3590                T06 McClintock Dr / Rio Salado Pkwy   \n",
       "\n",
       "    region_id                                            address  \\\n",
       "0          80            1000-1034 North Central Avenue, Phoenix   \n",
       "1          80                 1401 North Central Avenue, Phoenix   \n",
       "2          80          1010-1098 East Washington Street, Phoenix   \n",
       "3          80  567-599 East Monroe Street, Arizona State Univ...   \n",
       "4          80            604, North 5th Avenue, Phoenix, Arizona   \n",
       "..        ...                                                ...   \n",
       "80         80                    1-115 South 5th Street, Phoenix   \n",
       "81         80                     1021 North 2nd Street, Phoenix   \n",
       "82         81                    1201 South Forest Avenue, Tempe   \n",
       "83         81              1555 North Bridalwreath Street, Tempe   \n",
       "84         81           1807-1809 East Rio Salado Parkway, Tempe   \n",
       "\n",
       "    num_bikes_available  \n",
       "0                     9  \n",
       "1                     6  \n",
       "2                     8  \n",
       "3                     5  \n",
       "4                     6  \n",
       "..                  ...  \n",
       "80                    4  \n",
       "81                    0  \n",
       "82                    4  \n",
       "83                    5  \n",
       "84                    4  \n",
       "\n",
       "[85 rows x 6 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_info = pd.read_csv(\"station_info.csv\")\n",
    "df_info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "b63112fb",
   "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>Unnamed: 0</th>\n",
       "      <th>region_id</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>81</td>\n",
       "      <td>Tempe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>80</td>\n",
       "      <td>Phoenix</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  region_id     name\n",
       "0           0         81    Tempe\n",
       "1           1         80  Phoenix"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_region = pd.read_csv(\"system_regions.csv\")\n",
    "df_region"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "bfa74d23",
   "metadata": {},
   "outputs": [],
   "source": [
    "pg_engine = db.create_engine(\"postgresql://postgres:alya_2412@localhost:5432/postgres\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "0e7f8322",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<connection object at 0x000001C5AFCCE8A0; dsn: 'user=postgres password=xxx dbname=postgres host=localhost port=5432', closed: 0>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "psycopg2.connect(dbname=\"postgres\", user=\"postgres\", password=\"alya_2412\", host=\"localhost\",port =\"5432\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "807aaa9c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "21"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_bike.to_sql('bike_info', pg_engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "1e8df9a5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "85"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_info.to_sql('station_info', pg_engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "ecd04c33",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_region.to_sql('region_info', pg_engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "0b3d8794",
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = pg_engine.connect()\n",
    "metadata = db.MetaData()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "b98b0c90",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x1c5b4f03be0>"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#insert region\n",
    "pg_engine.execute(\"INSERT INTO region_info (index, region_id, name) VALUES ('2','82', 'Tucson')\")\n",
    "pg_engine.execute(\"INSERT INTO region_info (index, region_id, name) VALUES ('3','83', 'mesa')\")\n",
    "pg_engine.execute(\"INSERT INTO region_info (index, region_id, name) VALUES ('4','84', 'Sedona')\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "775d3c40",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x1c5b4f02e60>"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Update Region\n",
    "pg_engine.execute(\"UPDATE region_info SET name='mesa' WHERE region_id='82'\")\n",
    "pg_engine.execute(\"UPDATE region_info SET name='Tuscon' WHERE region_id='84'\")\n",
    "pg_engine.execute(\"UPDATE region_info SET name='sedona' WHERE region_id='83'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "d1fa6167",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x1c5b4f7b310>"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pg_engine.execute(\"DELETE FROM region_info WHERE region_id='82'\")\n",
    "pg_engine.execute(\"DELETE FROM region_info WHERE region_id='83'\")\n",
    "pg_engine.execute(\"DELETE FROM region_info WHERE region_id='84'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "bfbbd443",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x1c5b4f7bdc0>"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#insert Bike\n",
    "pg_engine.execute(\"INSERT INTO bike_info (index, bike_id, bike_name, region_id) VALUES ('21','14361', 'GRID 2026', '80')\")\n",
    "pg_engine.execute(\"INSERT INTO bike_info (index, bike_id, bike_name, region_id) VALUES ('22','12351', 'GRID 2027', '81')\")\n",
    "pg_engine.execute(\"INSERT INTO bike_info (index, bike_id, bike_name, region_id) VALUES ('23','14361', 'GRID 2026', '82')\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "86bfa552",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x1c5b4f7b790>"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Update Bike\n",
    "pg_engine.execute(\"UPDATE bike_info SET region_id='82' WHERE bike_name='T996'\")\n",
    "pg_engine.execute(\"UPDATE bike_info SET region_id='83' WHERE bike_name='T961'\")\n",
    "pg_engine.execute(\"UPDATE bike_info SET region_id='84' WHERE bike_name='T933'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "4ec8ce71",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x1c5b4f7be20>"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Delete Bike\n",
    "pg_engine.execute(\"DELETE FROM bike_info WHERE bike_id='17318'\")\n",
    "pg_engine.execute(\"DELETE FROM bike_info WHERE bike_id='13054'\")\n",
    "pg_engine.execute(\"DELETE FROM bike_info WHERE bike_id='13053'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "eba5eb24",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x1c5b4f7b850>"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#insert station\n",
    "pg_engine.execute(\"INSERT INTO station_info (index, station_id, station_name, region_id, address, num_bikes_available) VALUES ('85','3591', 'Tucson Amtrak Station', '82', '400 N Toole Ave, Tucson', '10')\")\n",
    "pg_engine.execute(\"INSERT INTO station_info (index, station_id, station_name, region_id, address, num_bikes_available) VALUES ('86','35100', 'Mesa Dr. / Main St.', '83', 'Dr and main St bus is, Mesa', '1')\")\n",
    "pg_engine.execute(\"INSERT INTO station_info (index, station_id, station_name, region_id, address, num_bikes_available) VALUES ('87','35120', 'Flagstaff Amtrak', '84', '1 E Rte 66, Flagstaff', '5')\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "5cb2421b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x1c5b4f7add0>"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pg_engine.execute(\"UPDATE station_info SET station_id='3582' WHERE  num_bikes_available='5'\")\n",
    "pg_engine.execute(\"UPDATE station_info SET station_id='3583' WHERE  num_bikes_available='4'\")\n",
    "pg_engine.execute(\"UPDATE station_info SET station_id='3590' WHERE  num_bikes_available='5'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "b051d5bf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x1c5b4f039a0>"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pg_engine.execute(\"DELETE FROM station_info WHERE station_name='P147 7th Ave / Glenrosa Ave'\")\n",
    "pg_engine.execute(\"DELETE FROM station_info WHERE station_name='P122 Grand Ave / McKinley St'\")\n",
    "pg_engine.execute(\"DELETE FROM station_info WHERE station_name='P138 Encanto Park'\")"
   ]
  }
 ],
 "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.10.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}