Oracle connect by cx_Oracle.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "eacbd767",
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "import cx_Oracle\n",
    "import configparser\n",
    "import concurrent.futures\n",
    "import matplotlib\n",
    "import matplotlib.pyplot as plt\n",
    "from IPython.display import display"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "12e26f85",
   "metadata": {},
   "source": [
    "```tnsnames.ora```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7165238a",
   "metadata": {},
   "source": [
    "```ORCLCDB=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=51521))\n",
    "    (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCLCDB.localdomain)))     \n",
    "ORCLPDB1=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=51521))\n",
    "    (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCLPDB1.localdomain)))```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "044118db",
   "metadata": {},
   "outputs": [],
   "source": [
    "host = 'localhost'\n",
    "port = 51521\n",
    "service_name = 'ORCLPDB1.localdomain'\n",
    "user = r'test'\n",
    "password = r'test'\n",
    "dsn_tns = cx_Oracle.makedsn(host, port, service_name=service_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "529d2c71",
   "metadata": {},
   "outputs": [],
   "source": [
    "dsn = \"\"\"\n",
    "(DESCRIPTION =\n",
    "    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 51521))\n",
    "    (CONNECT_DATA =\n",
    "      (SERVER = DEDICATED)\n",
    "      (SERVICE_NAME = ORCLPDB1.localdomain)\n",
    "    )\n",
    "  )\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "7521d16b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Database version: 12.2.0.1.0\n",
      "Client version: (21, 6, 0, 0, 0)\n",
      "\n",
      "(DESCRIPTION =\n",
      "    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 51521))\n",
      "    (CONNECT_DATA =\n",
      "      (SERVER = DEDICATED)\n",
      "      (SERVICE_NAME = ORCLPDB1.localdomain)\n",
      "    )\n",
      "  )\n",
      "\n"
     ]
    }
   ],
   "source": [
    "connection = None\n",
    "\n",
    "try:\n",
    "    connection = cx_Oracle.connect(user=user, password=password, dsn=dsn)\n",
    "    print('Database version:', connection.version)\n",
    "    print('Client version:', cx_Oracle.clientversion())\n",
    "    print(connection.dsn)\n",
    "except cx_Oracle.Error as error:\n",
    "    print(\"Whoops!\")\n",
    "    print(err);\n",
    "    sys.exit(1);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "0a82a2ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"\"\"\n",
    "SELECT\n",
    "    id,\n",
    "    age,\n",
    "    sex,\n",
    "    job,\n",
    "    housing,\n",
    "    saving_accounts,\n",
    "    checking_account,\n",
    "    credit_amount,\n",
    "    duration,\n",
    "    purpose\n",
    "FROM\n",
    "    german_credit_data\n",
    "WHERE id BETWEEN :param1 AND :param2\n",
    "ORDER BY\n",
    "    id\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "496d302c",
   "metadata": {},
   "outputs": [],
   "source": [
    "def Query(connection, sql, p1, p2):\n",
    "    cursor = connection.cursor()\n",
    "    cursor.execute(sql, param1 = p1, param2 = p2)\n",
    "    \n",
    "    column_list = []\n",
    "    for column in cursor.description:\n",
    "        column_list.append(column[0])\n",
    "        \n",
    "    data = []\n",
    "    \n",
    "    while True:\n",
    "        row = cursor.fetchone()\n",
    "        if row is None:\n",
    "            break\n",
    "        else:\n",
    "            data.append(row)\n",
    "            \n",
    "    cursor.close()\n",
    "\n",
    "    return pd.DataFrame(data, columns=column_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "b49b2d8e",
   "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>ID</th>\n",
       "      <th>AGE</th>\n",
       "      <th>SEX</th>\n",
       "      <th>JOB</th>\n",
       "      <th>HOUSING</th>\n",
       "      <th>SAVING_ACCOUNTS</th>\n",
       "      <th>CHECKING_ACCOUNT</th>\n",
       "      <th>CREDIT_AMOUNT</th>\n",
       "      <th>DURATION</th>\n",
       "      <th>PURPOSE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>67</td>\n",
       "      <td>male</td>\n",
       "      <td>2</td>\n",
       "      <td>own</td>\n",
       "      <td>NA</td>\n",
       "      <td>little</td>\n",
       "      <td>1169</td>\n",
       "      <td>6</td>\n",
       "      <td>radio/TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>22</td>\n",
       "      <td>female</td>\n",
       "      <td>2</td>\n",
       "      <td>own</td>\n",
       "      <td>little</td>\n",
       "      <td>moderate</td>\n",
       "      <td>5951</td>\n",
       "      <td>48</td>\n",
       "      <td>radio/TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>49</td>\n",
       "      <td>male</td>\n",
       "      <td>1</td>\n",
       "      <td>own</td>\n",
       "      <td>little</td>\n",
       "      <td>NA</td>\n",
       "      <td>2096</td>\n",
       "      <td>12</td>\n",
       "      <td>education</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>45</td>\n",
       "      <td>male</td>\n",
       "      <td>2</td>\n",
       "      <td>free</td>\n",
       "      <td>little</td>\n",
       "      <td>little</td>\n",
       "      <td>7882</td>\n",
       "      <td>42</td>\n",
       "      <td>furniture/equipment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>53</td>\n",
       "      <td>male</td>\n",
       "      <td>2</td>\n",
       "      <td>free</td>\n",
       "      <td>little</td>\n",
       "      <td>little</td>\n",
       "      <td>4870</td>\n",
       "      <td>24</td>\n",
       "      <td>car</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>35</td>\n",
       "      <td>male</td>\n",
       "      <td>1</td>\n",
       "      <td>free</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>9055</td>\n",
       "      <td>36</td>\n",
       "      <td>education</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>53</td>\n",
       "      <td>male</td>\n",
       "      <td>2</td>\n",
       "      <td>own</td>\n",
       "      <td>quite rich</td>\n",
       "      <td>NA</td>\n",
       "      <td>2835</td>\n",
       "      <td>24</td>\n",
       "      <td>furniture/equipment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>35</td>\n",
       "      <td>male</td>\n",
       "      <td>3</td>\n",
       "      <td>rent</td>\n",
       "      <td>little</td>\n",
       "      <td>moderate</td>\n",
       "      <td>6948</td>\n",
       "      <td>36</td>\n",
       "      <td>car</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>61</td>\n",
       "      <td>male</td>\n",
       "      <td>1</td>\n",
       "      <td>own</td>\n",
       "      <td>rich</td>\n",
       "      <td>NA</td>\n",
       "      <td>3059</td>\n",
       "      <td>12</td>\n",
       "      <td>radio/TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>28</td>\n",
       "      <td>male</td>\n",
       "      <td>3</td>\n",
       "      <td>own</td>\n",
       "      <td>little</td>\n",
       "      <td>moderate</td>\n",
       "      <td>5234</td>\n",
       "      <td>30</td>\n",
       "      <td>car</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10</td>\n",
       "      <td>25</td>\n",
       "      <td>female</td>\n",
       "      <td>2</td>\n",
       "      <td>rent</td>\n",
       "      <td>little</td>\n",
       "      <td>moderate</td>\n",
       "      <td>1295</td>\n",
       "      <td>12</td>\n",
       "      <td>car</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ID  AGE     SEX  JOB HOUSING SAVING_ACCOUNTS CHECKING_ACCOUNT  \\\n",
       "0    0   67    male    2     own              NA           little   \n",
       "1    1   22  female    2     own          little         moderate   \n",
       "2    2   49    male    1     own          little               NA   \n",
       "3    3   45    male    2    free          little           little   \n",
       "4    4   53    male    2    free          little           little   \n",
       "5    5   35    male    1    free              NA               NA   \n",
       "6    6   53    male    2     own      quite rich               NA   \n",
       "7    7   35    male    3    rent          little         moderate   \n",
       "8    8   61    male    1     own            rich               NA   \n",
       "9    9   28    male    3     own          little         moderate   \n",
       "10  10   25  female    2    rent          little         moderate   \n",
       "\n",
       "    CREDIT_AMOUNT  DURATION              PURPOSE  \n",
       "0            1169         6             radio/TV  \n",
       "1            5951        48             radio/TV  \n",
       "2            2096        12            education  \n",
       "3            7882        42  furniture/equipment  \n",
       "4            4870        24                  car  \n",
       "5            9055        36            education  \n",
       "6            2835        24  furniture/equipment  \n",
       "7            6948        36                  car  \n",
       "8            3059        12             radio/TV  \n",
       "9            5234        30                  car  \n",
       "10           1295        12                  car  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df = pd.DataFrame()\n",
    "\n",
    "with concurrent.futures.ThreadPoolExecutor() as executor:\n",
    "    future = executor.submit(Query, connection, sql, 0, 10)\n",
    "    df = future.result()\n",
    "    \n",
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "dc27dbda",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"\"\"\n",
    "SELECT\n",
    "    id,\n",
    "    age,\n",
    "    sex,\n",
    "    job,\n",
    "    housing,\n",
    "    saving_accounts,\n",
    "    checking_account,\n",
    "    credit_amount,\n",
    "    duration,\n",
    "    purpose\n",
    "FROM\n",
    "    german_credit_data\n",
    "WHERE id BETWEEN 0 AND 10\n",
    "ORDER BY\n",
    "    id\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "7bca9d96",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_sql(sql, con=connection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "c269179d",
   "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>ID</th>\n",
       "      <th>AGE</th>\n",
       "      <th>SEX</th>\n",
       "      <th>JOB</th>\n",
       "      <th>HOUSING</th>\n",
       "      <th>SAVING_ACCOUNTS</th>\n",
       "      <th>CHECKING_ACCOUNT</th>\n",
       "      <th>CREDIT_AMOUNT</th>\n",
       "      <th>DURATION</th>\n",
       "      <th>PURPOSE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>67</td>\n",
       "      <td>male</td>\n",
       "      <td>2</td>\n",
       "      <td>own</td>\n",
       "      <td>NA</td>\n",
       "      <td>little</td>\n",
       "      <td>1169</td>\n",
       "      <td>6</td>\n",
       "      <td>radio/TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>22</td>\n",
       "      <td>female</td>\n",
       "      <td>2</td>\n",
       "      <td>own</td>\n",
       "      <td>little</td>\n",
       "      <td>moderate</td>\n",
       "      <td>5951</td>\n",
       "      <td>48</td>\n",
       "      <td>radio/TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>49</td>\n",
       "      <td>male</td>\n",
       "      <td>1</td>\n",
       "      <td>own</td>\n",
       "      <td>little</td>\n",
       "      <td>NA</td>\n",
       "      <td>2096</td>\n",
       "      <td>12</td>\n",
       "      <td>education</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>45</td>\n",
       "      <td>male</td>\n",
       "      <td>2</td>\n",
       "      <td>free</td>\n",
       "      <td>little</td>\n",
       "      <td>little</td>\n",
       "      <td>7882</td>\n",
       "      <td>42</td>\n",
       "      <td>furniture/equipment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>53</td>\n",
       "      <td>male</td>\n",
       "      <td>2</td>\n",
       "      <td>free</td>\n",
       "      <td>little</td>\n",
       "      <td>little</td>\n",
       "      <td>4870</td>\n",
       "      <td>24</td>\n",
       "      <td>car</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>35</td>\n",
       "      <td>male</td>\n",
       "      <td>1</td>\n",
       "      <td>free</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>9055</td>\n",
       "      <td>36</td>\n",
       "      <td>education</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>53</td>\n",
       "      <td>male</td>\n",
       "      <td>2</td>\n",
       "      <td>own</td>\n",
       "      <td>quite rich</td>\n",
       "      <td>NA</td>\n",
       "      <td>2835</td>\n",
       "      <td>24</td>\n",
       "      <td>furniture/equipment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>35</td>\n",
       "      <td>male</td>\n",
       "      <td>3</td>\n",
       "      <td>rent</td>\n",
       "      <td>little</td>\n",
       "      <td>moderate</td>\n",
       "      <td>6948</td>\n",
       "      <td>36</td>\n",
       "      <td>car</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>61</td>\n",
       "      <td>male</td>\n",
       "      <td>1</td>\n",
       "      <td>own</td>\n",
       "      <td>rich</td>\n",
       "      <td>NA</td>\n",
       "      <td>3059</td>\n",
       "      <td>12</td>\n",
       "      <td>radio/TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>28</td>\n",
       "      <td>male</td>\n",
       "      <td>3</td>\n",
       "      <td>own</td>\n",
       "      <td>little</td>\n",
       "      <td>moderate</td>\n",
       "      <td>5234</td>\n",
       "      <td>30</td>\n",
       "      <td>car</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10</td>\n",
       "      <td>25</td>\n",
       "      <td>female</td>\n",
       "      <td>2</td>\n",
       "      <td>rent</td>\n",
       "      <td>little</td>\n",
       "      <td>moderate</td>\n",
       "      <td>1295</td>\n",
       "      <td>12</td>\n",
       "      <td>car</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ID  AGE     SEX  JOB HOUSING SAVING_ACCOUNTS CHECKING_ACCOUNT  \\\n",
       "0    0   67    male    2     own              NA           little   \n",
       "1    1   22  female    2     own          little         moderate   \n",
       "2    2   49    male    1     own          little               NA   \n",
       "3    3   45    male    2    free          little           little   \n",
       "4    4   53    male    2    free          little           little   \n",
       "5    5   35    male    1    free              NA               NA   \n",
       "6    6   53    male    2     own      quite rich               NA   \n",
       "7    7   35    male    3    rent          little         moderate   \n",
       "8    8   61    male    1     own            rich               NA   \n",
       "9    9   28    male    3     own          little         moderate   \n",
       "10  10   25  female    2    rent          little         moderate   \n",
       "\n",
       "    CREDIT_AMOUNT  DURATION              PURPOSE  \n",
       "0            1169         6             radio/TV  \n",
       "1            5951        48             radio/TV  \n",
       "2            2096        12            education  \n",
       "3            7882        42  furniture/equipment  \n",
       "4            4870        24                  car  \n",
       "5            9055        36            education  \n",
       "6            2835        24  furniture/equipment  \n",
       "7            6948        36                  car  \n",
       "8            3059        12             radio/TV  \n",
       "9            5234        30                  car  \n",
       "10           1295        12                  car  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "07215a9c",
   "metadata": {},
   "outputs": [],
   "source": [
    "connection.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:.conda-ORACLE] *",
   "language": "python",
   "name": "conda-env-.conda-ORACLE-py"
  },
  "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}