Get Data From DB_CD_Audit_MB_CV.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import cx_Oracle\n",
    "import pickle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "ip = '172.16.80.74'\n",
    "port = '1521'\n",
    "SID = 'RPTDB'\n",
    "\n",
    "dsn_tns = cx_Oracle.makedsn(ip, port, SID)\n",
    "connection = cx_Oracle.connect('ANLYUSR', 'ANLYUSR$', dsn_tns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "CV = ['TBL_TRN_CV_METADATA',\n",
    "'TBL_TRN_CV_LOCATIONPROFILE',\n",
    "'TBL_TRN_CV_CREDITCARDACCOUNT',\n",
    "'TBL_TRN_CV_LOANACCOUNT',\n",
    "'TBL_TRN_CV_LOANS',\n",
    "'TBL_TRN_CV_CREDITSCORE_FRAUD',\n",
    "'TBL_TRN_CV_BANKACCOUNT',\n",
    "'TBL_TRN_CV_EMAILIDS',\n",
    "'TBL_TRN_CV_INCOME',\n",
    "'TBL_TRN_CV_ALTERNATECONTACT',\n",
    "'TBL_TRN_CV_ACCOUNTS',\n",
    "'TBL_TRN_CV_UTILITYACCOUNT',\n",
    "'TBL_TRN_CV_CREDITCARD',\n",
    "'TBL_TRN_MST_CV_DETAILS']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "---------------------------TBL_TRN_CV_METADATA----DONE-------------------\n",
      "---------------------------TBL_TRN_CV_LOCATIONPROFILE----DONE-------------------\n",
      "---------------------------TBL_TRN_CV_CREDITCARDACCOUNT----DONE-------------------\n",
      "---------------------------TBL_TRN_CV_LOANACCOUNT----DONE-------------------\n",
      "---------------------------TBL_TRN_CV_LOANS----DONE-------------------\n",
      "---------------------------TBL_TRN_CV_CREDITSCORE_FRAUD----DONE-------------------\n",
      "---------------------------TBL_TRN_CV_BANKACCOUNT----DONE-------------------\n",
      "---------------------------TBL_TRN_CV_EMAILIDS----DONE-------------------\n",
      "---------------------------TBL_TRN_CV_INCOME----DONE-------------------\n",
      "---------------------------TBL_TRN_CV_ALTERNATECONTACT----DONE-------------------\n",
      "---------------------------TBL_TRN_CV_ACCOUNTS----DONE-------------------\n",
      "---------------------------TBL_TRN_CV_UTILITYACCOUNT----DONE-------------------\n",
      "---------------------------TBL_TRN_CV_CREDITCARD----DONE-------------------\n",
      "---------------------------TBL_TRN_MST_CV_DETAILS----DONE-------------------\n"
     ]
    }
   ],
   "source": [
    "for i in CV:\n",
    "    query = \"SELECT TB3.PROPNO,sum(status) as {} \\\n",
    "    FROM \\\n",
    "    (SELECT TB1.PROPNO,decode(TB2.PROPNO,NULL,0,1) as status \\\n",
    "    FROM \\\n",
    "    (SELECT PROPNO \\\n",
    "    FROM TBL_TRN_CUSTOMER_DETAILS CUS \\\n",
    "    WHERE CUS.AGMT_DATE > TO_DATE('31-OCT-18', 'DD-MON-YY') \\\n",
    "    AND CUS.PORTFOLIO='CD') TB1 LEFT OUTER JOIN \\\n",
    "    (SELECT PROPNO FROM BIUSR.{} \\\n",
    "    WHERE CREATED_DATE > TO_DATE('31-OCT-18', 'DD-MON-YY') \\\n",
    "    AND SUBSTR(PROPNO,5,2) = 'CD') TB2 \\\n",
    "    ON TB1.PROPNO = TB2.PROPNO) TB3 \\\n",
    "    GROUP BY TB3.PROPNO\".format(i,i)\n",
    "\n",
    "    Data = pd.read_sql(query, con=connection, chunksize=10000)\n",
    "    \n",
    "    data = pd.concat([chunk for chunk in Data])\n",
    "    \n",
    "    if i == 'TBL_TRN_CV_METADATA':\n",
    "        DF_TBL_TRN_CV_METADATA = data\n",
    "    elif i == 'TBL_TRN_CV_LOCATIONPROFILE':\n",
    "        DF_TBL_TRN_CV_LOCATIONPROFILE = data\n",
    "    elif i == 'TBL_TRN_CV_CREDITCARDACCOUNT':\n",
    "        DF_TBL_TRN_CV_CREDITCARDACCOUNT = data\n",
    "    elif i == 'TBL_TRN_CV_LOANACCOUNT':\n",
    "        DF_TBL_TRN_CV_LOANACCOUNT = data\n",
    "    elif i == 'TBL_TRN_CV_LOANS':\n",
    "        DF_TBL_TRN_CV_LOANS = data\n",
    "    elif i == 'TBL_TRN_CV_CREDITSCORE_FRAUD':\n",
    "        DF_TBL_TRN_CV_CREDITSCORE_FRAUD = data\n",
    "    elif i == 'TBL_TRN_CV_BANKACCOUNT':\n",
    "        DF_TBL_TRN_CV_BANKACCOUNT = data\n",
    "    elif i == 'TBL_TRN_CV_EMAILIDS':\n",
    "        DF_TBL_TRN_CV_EMAILIDS = data\n",
    "    elif i == 'TBL_TRN_CV_INCOME':\n",
    "        DF_TBL_TRN_CV_INCOME = data\n",
    "    elif i == 'TBL_TRN_CV_ALTERNATECONTACT':\n",
    "        DF_TBL_TRN_CV_ALTERNATECONTACT = data\n",
    "    elif i == 'TBL_TRN_CV_ACCOUNTS':\n",
    "        DF_TBL_TRN_CV_ACCOUNTS = data\n",
    "    elif i == 'TBL_TRN_CV_UTILITYACCOUNT':\n",
    "        DF_TBL_TRN_CV_UTILITYACCOUNT = data\n",
    "    elif i == 'TBL_TRN_CV_CREDITCARD':\n",
    "        DF_TBL_TRN_CV_CREDITCARD = data\n",
    "    elif i == 'TBL_TRN_MST_CV_DETAILS':\n",
    "        DF_TBL_TRN_MST_CV_DETAILS = data\n",
    "    print(\"---------------------------\"+i+\"----DONE-------------------\")\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "DF_Data = pd.merge(DF_TBL_TRN_CV_METADATA, DF_TBL_TRN_CV_LOCATIONPROFILE, on='PROPNO', how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "DF_Data = pd.merge(DF_Data, DF_TBL_TRN_CV_CREDITCARDACCOUNT, on='PROPNO', how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "DF_Data = pd.merge(DF_Data, DF_TBL_TRN_CV_LOANACCOUNT, on='PROPNO', how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "DF_Data = pd.merge(DF_Data, DF_TBL_TRN_CV_LOANS, on='PROPNO', how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "DF_Data = pd.merge(DF_Data, DF_TBL_TRN_CV_CREDITSCORE_FRAUD, on='PROPNO', how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "DF_Data = pd.merge(DF_Data, DF_TBL_TRN_CV_BANKACCOUNT, on='PROPNO', how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "DF_Data = pd.merge(DF_Data, DF_TBL_TRN_CV_EMAILIDS, on='PROPNO', how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "DF_Data = pd.merge(DF_Data, DF_TBL_TRN_CV_INCOME, on='PROPNO', how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "DF_Data = pd.merge(DF_Data, DF_TBL_TRN_CV_ALTERNATECONTACT, on='PROPNO', how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "DF_Data = pd.merge(DF_Data, DF_TBL_TRN_CV_ACCOUNTS, on='PROPNO', how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "DF_Data = pd.merge(DF_Data, DF_TBL_TRN_CV_UTILITYACCOUNT, on='PROPNO', how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "DF_Data = pd.merge(DF_Data, DF_TBL_TRN_CV_CREDITCARD, on='PROPNO', how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "DF_Data = pd.merge(DF_Data, DF_TBL_TRN_MST_CV_DETAILS, on='PROPNO', how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>PROPNO</th>\n",
       "      <th>TBL_TRN_CV_METADATA</th>\n",
       "      <th>TBL_TRN_CV_LOCATIONPROFILE</th>\n",
       "      <th>TBL_TRN_CV_CREDITCARDACCOUNT</th>\n",
       "      <th>TBL_TRN_CV_LOANACCOUNT</th>\n",
       "      <th>TBL_TRN_CV_LOANS</th>\n",
       "      <th>TBL_TRN_CV_CREDITSCORE_FRAUD</th>\n",
       "      <th>TBL_TRN_CV_BANKACCOUNT</th>\n",
       "      <th>TBL_TRN_CV_EMAILIDS</th>\n",
       "      <th>TBL_TRN_CV_INCOME</th>\n",
       "      <th>TBL_TRN_CV_ALTERNATECONTACT</th>\n",
       "      <th>TBL_TRN_CV_ACCOUNTS</th>\n",
       "      <th>TBL_TRN_CV_UTILITYACCOUNT</th>\n",
       "      <th>TBL_TRN_CV_CREDITCARD</th>\n",
       "      <th>TBL_TRN_MST_CV_DETAILS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3000CD0043323</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>0</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>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3000CD0048516</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3000CD0049443</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>0</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>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3000CD0049455</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3000CD0049677</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>0</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>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          PROPNO  TBL_TRN_CV_METADATA  TBL_TRN_CV_LOCATIONPROFILE  \\\n",
       "0  3000CD0043323                    0                           0   \n",
       "1  3000CD0048516                    1                           1   \n",
       "2  3000CD0049443                    0                           0   \n",
       "3  3000CD0049455                    1                           1   \n",
       "4  3000CD0049677                    0                           0   \n",
       "\n",
       "   TBL_TRN_CV_CREDITCARDACCOUNT  TBL_TRN_CV_LOANACCOUNT  TBL_TRN_CV_LOANS  \\\n",
       "0                             0                       0                 0   \n",
       "1                             1                       1                 0   \n",
       "2                             0                       0                 0   \n",
       "3                             1                       1                 0   \n",
       "4                             0                       0                 0   \n",
       "\n",
       "   TBL_TRN_CV_CREDITSCORE_FRAUD  TBL_TRN_CV_BANKACCOUNT  TBL_TRN_CV_EMAILIDS  \\\n",
       "0                             0                       0                    0   \n",
       "1                             1                       1                    1   \n",
       "2                             0                       0                    0   \n",
       "3                             1                       1                    0   \n",
       "4                             0                       0                    0   \n",
       "\n",
       "   TBL_TRN_CV_INCOME  TBL_TRN_CV_ALTERNATECONTACT  TBL_TRN_CV_ACCOUNTS  \\\n",
       "0                  0                            0                    0   \n",
       "1                  1                            1                    0   \n",
       "2                  0                            0                    0   \n",
       "3                  1                            1                    0   \n",
       "4                  0                            0                    0   \n",
       "\n",
       "   TBL_TRN_CV_UTILITYACCOUNT  TBL_TRN_CV_CREDITCARD  TBL_TRN_MST_CV_DETAILS  \n",
       "0                          0                      0                       0  \n",
       "1                          1                      0                       1  \n",
       "2                          0                      0                       0  \n",
       "3                          1                      0                       1  \n",
       "4                          0                      0                       0  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "DF_Data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "DF_Data.to_csv(\"D:\\CD\\CD_MB_CV_6_months.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}