Connecting to OCHIN DB - python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "693c3fd5",
   "metadata": {},
   "source": [
    "# Connect to the OCHIN DB through python"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "061d0d32",
   "metadata": {},
   "source": [
    "This notebook will walk you through how to connect to the OCHIN DB using python. \n",
    "Before you begin, make sure that you have access to the data and check to make sure the `db-credentials.txt` file is located in your home directory. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac3a3654",
   "metadata": {},
   "source": [
    "## Install ODBC drivers as necessary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ba9be3c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sh\n",
    "\n",
    "sudo su \n",
    "\n",
    "#Download appropriate package for the OS version\n",
    "#Choose only ONE of the following, corresponding to your OS version\n",
    "\n",
    "#RHEL 7 and Oracle Linux 7\n",
    "curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo\n",
    "\n",
    "#RHEL 8 and Oracle Linux 8\n",
    "#curl https://packages.microsoft.com/config/rhel/8/prod.repo > /etc/yum.repos.d/mssql-release.repo\n",
    "\n",
    "#RHEL 9\n",
    "#curl https://packages.microsoft.com/config/rhel/9.0/prod.repo > /etc/yum.repos.d/mssql-release.repo\n",
    "\n",
    "exit\n",
    "\n",
    "sudo yum remove unixODBC-utf16 unixODBC-utf16-devel #to avoid conflicts\n",
    "sudo ACCEPT_EULA=Y yum install -y msodbcsql17\n",
    "# optional: for bcp and sqlcmd\n",
    "sudo ACCEPT_EULA=Y yum install -y mssql-tools\n",
    "echo 'export PATH=\"$PATH:/opt/mssql-tools/bin\"' >> ~/.bashrc\n",
    "source ~/.bashrc\n",
    "# optional: for unixODBC development headers\n",
    "sudo yum install -y unixODBC-devel"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "adf3958b",
   "metadata": {},
   "source": [
    "## Import python packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4fc7c8f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pyodbc\n",
    "print(\"List of ODBC drivers:\")\n",
    "dlist = pyodbc.drivers()\n",
    "for drvr in dlist:\n",
    "    print('\\t', drvr)\n",
    "\n",
    "print(\"End of list\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7e2e9226",
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install pandasql \n",
    "import pandas as pd\n",
    "from pandasql import sqldf\n",
    "import pyodbc"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "366e398f",
   "metadata": {},
   "source": [
    "## Read and parse your db credentials\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "88e15dbc",
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "\n",
    "file_path = '/home/ec2-user/SageMaker/db-credentials.txt'\n",
    "\n",
    "with open(file_path, 'r') as file:\n",
    "    # Code to parse the data will go here\n",
    "    file_contents = file.read()\n",
    "\n",
    "# Remove newlines and extra spaces\n",
    "cleaned_string = file_contents.replace('\\n', '').strip()\n",
    "\n",
    "# Extract variable-value pairs using regular expressions\n",
    "pattern = r'\"([^\"]+)\": \"([^\"]+)\"'\n",
    "pairs = re.findall(pattern, cleaned_string)\n",
    "\n",
    "parsed_data = {}\n",
    "\n",
    "for variable, data in pairs:\n",
    "    parsed_data[variable] = data\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5bd605c0",
   "metadata": {},
   "source": [
    "## Connect to the database\n",
    "Remember that you may have access to multiple databases. \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "debad411",
   "metadata": {},
   "source": [
    "### Connecting to the \"raw\" data\n",
    "The raw database contains the patient data. You have read-only access to this database.\n",
    "\n",
    "- For AIM AHEAD Year 1 programs, the Raw database is named FellowsSample.\n",
    "- For AIM AHEAD Year 2 programs, the Raw database is named AAOCHIN2023.\n",
    "\n",
    "Within the Raw database, you must open the **View** specific to your project. Your OCHIN Project ID is provided to you when you receive confirmation of your OCHIN DB Login Activation. \n",
    "\n",
    "Once you have opened your View, you will see the Tables which contain the subset of data specific to your project. \n",
    "\n",
    "- Tables containing patient-level data specific to your project will be prefixed with your OCHIN Project ID.\n",
    "- Tables that contain metadata shared amongst all projects are prefixed with \"common\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d5a723cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "### This example shows how to view tables in the AAOCHIN2023 database. \n",
    "raw_db = 'AAOCHIN2023' \n",
    "raw_connection_string = \"DRIVER={ODBC Driver 17 for SQL Server};\" + \\\n",
    "                    \"SERVER=\" + parsed_data['host'] + ',' + parsed_data['port'] + ';' + \\\n",
    "                    \"DATABASE=\" + raw_db + ';' + \\\n",
    "                    \"UID=\" + parsed_data['username'] + ';' + \\\n",
    "                    \"PWD={\" + parsed_data['password'] + \"};\"\n",
    "raw_conn = pyodbc.connect(raw_connection_string, trusted_connection = 'no')\n",
    "raw_cursor = raw_conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b18aecf6",
   "metadata": {},
   "outputs": [],
   "source": [
    "### We can print all tables available in the database views we have access to like so: \n",
    "raw_cursor.execute('''\n",
    "SELECT name AS TABLE_NAME\n",
    "FROM sys.views\n",
    "''')\n",
    "for row in raw_cursor:\n",
    "    print(row[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "23b5b04e",
   "metadata": {},
   "outputs": [],
   "source": [
    "### We can view the top 10 entries in the CONCEPT_DIMENSION table like so:\n",
    "### Note that the CONCEPT_DIMENSION table contains metadata shared amongst all projects, \n",
    "### and therefore must be prefixed with \"common\"\n",
    "raw_cursor.execute('''\n",
    "SELECT TOP 10 *\n",
    "FROM common.CONCEPT_DIMENSION;\n",
    "\n",
    "''')\n",
    "\n",
    "results = raw_cursor.fetchall()\n",
    "\n",
    "# Get the column names from the cursor description\n",
    "columns = [column[0] for column in raw_cursor.description]\n",
    "\n",
    "# Create a DataFrame from the fetched results and column names\n",
    "results_df = pd.DataFrame.from_records(results, columns=columns)\n",
    "results_df['NAME_CHAR'] = results_df['NAME_CHAR'].astype('category')\n",
    "results_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4d7f23c1",
   "metadata": {},
   "source": [
    "### Connecting to your project database\n",
    "Your project database is unique to your project and named after your OCHIN Project ID. You have read-write access to this database.\n",
    "\n",
    "Your project database is initially empty. All temporary tables or aggregate results for your project should be saved here. You can store up to 50GB in your project database. This is not a lot! The full Raw database has over 1TB of data. If you copy data directly from the Raw database to your project database, you will quickly run out of space.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "58f7d6c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "### This example shows how to view tables in your project specific database. \n",
    "proj_db = 'S000' # Change this to your project ID\n",
    "proj_connection_string = \"DRIVER={ODBC Driver 17 for SQL Server};\" + \\\n",
    "                    \"SERVER=\" + parsed_data['host'] + ',' + parsed_data['port'] + ';' + \\\n",
    "                    \"DATABASE=\" + raw_db + ';' + \\\n",
    "                    \"UID=\" + parsed_data['username'] + ';' + \\\n",
    "                    \"PWD={\" + parsed_data['password'] + \"};\"\n",
    "proj_conn = pyodbc.connect(proj_connection_string, trusted_connection = 'no')\n",
    "proj_cursor = proj_conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "86baae8f",
   "metadata": {},
   "outputs": [],
   "source": [
    "### We can print all tables in the project database like so:\n",
    "### Note that this database will be empty unless you have saved tables here. \n",
    "proj_cursor.execute('''\n",
    "SELECT name AS TABLE_NAME\n",
    "FROM sys.tables\n",
    "''')\n",
    "for row in raw_cursor:\n",
    "    print(row[0])"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "conda_python3",
   "language": "python",
   "name": "conda_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.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}