one_time_historical.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import Library"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "import pandas as pd\n",
    "import sqlalchemy as sa\n",
    "import json\n",
    "from datetime import datetime\n",
    "\n",
    "from webdriver_manager.chrome import ChromeDriverManager\n",
    "from selenium import webdriver\n",
    "from selenium.webdriver.common.by import By\n",
    "from selenium.webdriver.chrome.options import Options\n",
    "from selenium.webdriver.chrome.service import Service\n",
    "\n",
    "from sqlalchemy import MetaData, Table, Column, String, DateTime, Text, Float\n",
    "\n",
    "from creds import creds"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Establish Connection to Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dest_engine = sa.create_engine(f\"postgresql+psycopg2://{creds['user']}:{creds['pass']}@{creds['host']}:{creds['port']}/{creds['db']}\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Drop all tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dest_engine.connect().execute('DROP SCHEMA public CASCADE; CREATE SCHEMA public;')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def createStockListTable():\n",
    "    engine = dest_engine\n",
    "    metadata = MetaData(bind=engine)\n",
    "    stocks = Table('saham', metadata,\n",
    "                       Column('Code', String, primary_key=True),\n",
    "                       Column('Name', Text,\n",
    "                              nullable=False, unique=True),\n",
    "                       Column('ListingDate', DateTime),\n",
    "                       Column('Shares',Float ),\n",
    "                       Column('ListingBoard', String),\n",
    "                       Column('Sector', String),\n",
    "                       )\n",
    "    stocks.drop(checkfirst=True)\n",
    "    stocks.create()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extract Data from Source"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def getstocklistData():\n",
    "    DaftarSaham = pd.DataFrame()\n",
    "    \n",
    "    URL = 'https://www.idx.co.id/primary/StockData/GetSecuritiesStock?start=0&length=9999&code=&sector=Energy&board=&language=id-id'\n",
    "        \n",
    "    data = get_scrap_data(URL)\n",
    "\n",
    "    # Mengubah json ke dalam bentuk DataFrame\n",
    "    df = pd.DataFrame(data['data'])\n",
    "\n",
    "    # Mengubah format tanggal menjadi datetime\n",
    "    df['ListingDate'] = [datetime.strptime(\n",
    "        x[:10], '%Y-%m-%d') for x in df['ListingDate']]\n",
    "    df['Sector'] = \"Energy\"\n",
    "\n",
    "    DaftarSaham = pd.concat([DaftarSaham, df], ignore_index=True)\n",
    "\n",
    "    # urut berdasarkan kode saham\n",
    "    DaftarSaham = DaftarSaham.sort_values(by='Code').reset_index(drop=True)\n",
    "\n",
    "    \n",
    "    return DaftarSaham"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load Data into Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def insertStockListData():\n",
    "    data = getstocklistData()\n",
    "    \n",
    "    with dest_engine.connect() as conn:\n",
    "        data.to_sql('saham', conn, if_exists='append', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9.13 ('env': venv)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "version": "3.9.13"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "521131dee84837cf57376ea2eff34b9372a65e992ace276547287e00ae149fae"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}