data-ingestion-and-preparation/read-external-db.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reading from External Databases\n",
    "\n",
    "Learn how to ingest data from external databases using SQLAlchemy, PyMySQL, pandas, and V3IO Frames.\n",
    "\n",
    "- [Overview](#readextdb-overview)\n",
    "  - [Creating an SQLAlchemy Engine](#readextdb-sqlalchemy-engine-create)\n",
    "- [Initialization](#readextdb-init)\n",
    "  - [Importing Packages](#readextdb-import-packages)\n",
    "  - [Creating a Frames Client](#readextdb-create-frames-client)\n",
    "- [Reading from a MySQL Database](#readextdb-mysql)\n",
    "  - [Reading Data in Chunks Using SQLAlchemy](#readextdb-mysql-chunks-sqlalchemy)\n",
    "  - [Reading a Table as a Bulk Operation Using PyMySQL](#readextdb-mysql-bulk-pymysql)\n",
    "  - [Reading Data in Chunks Using PyMySQL](#readextdb-mysql-chunks-pymysql)\n",
    "- [Reading from a PostgreSQL Database](#readextdb-postgresql)\n",
    "- [Reading from an Oracle Database](#readextdb-oracle)\n",
    "- [Reading from a Microsoft SQL Server Database](#readextdb-mssql)\n",
    "- [Cleanup](#readextdb-cleanup)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-overview\"></a>\n",
    "## Overview\n",
    "\n",
    "This tutorial walks you through the steps for\n",
    "\n",
    "- <a id=\"readextdb-overview-db-connect\"></a>**Connecting to an external database** using either of the following methods:\n",
    "  - <a id=\"readextdb-sqlalchemy\"></a>Creating an SQLAlchemy engine.\n",
    "    [SQLAlchemy](https://www.sqlalchemy.org/) is a Python SQL toolkit and Object Relational Mapper, which gives application developers the full power and flexibility of SQL.\n",
    "    For more information, see the [SQLAlchemy documentation](https://docs.sqlalchemy.org).\n",
    "  - <a id=\"readextdb-pymysql\"></a>Creating a database connection using the [PyMySQL](https://github.com/PyMySQL/PyMySQL) client library (**pymysql**).\n",
    "    For more information, see the [PyMySQL documentation](https://pymysql.readthedocs.io/).\n",
    "\n",
    "\n",
    "- <a id=\"readextdb-overview-load-data\"></a>**Loading data from the external database** to pandas DataFrames.\n",
    "  You can read from an external database using the pandas [`read_sql_query` method](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_sql_query.html) or [`read_sql` wrapper](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_sql.html) (which calls `read_sql_query` when called with an SQL query, and `read_sql_table` when called with a database table name).\n",
    "  Both methods accept an SQL query object, a database object (either an SQLAlchemy engine or a PyMySQL connection), and an optional chunk-size parameter for chunked reads (see more information in the [MySQL](#readextdb-mysql) section).\n",
    "\n",
    "\n",
    "- <a id=\"readextdb-overview-ingest-to-platform\"></a>**Ingesting the data** into the Iguazio Data Science Platform (**\"the platform\"**) using V3IO Frames (**\"Frames\"**).\n",
    "  [Frames](https://github.com/v3io/frames) is a multi-model open-source data-access library, developed by Iguazio, which provides a unified high-performance DataFrame API for working with data in the platform's data store.\n",
    "  Frames currently supports the NoSQL (key-value) and time-series (TSDB) data models via its `nosql`|`kv` and `tsdb` backends.\n",
    "  The tutorial demonstrates how to use Frames to write the database data from the pandas DataFrames to the platform's data containers, thus persisting the data in the platform's data store.\n",
    "  The tutorial also shows how to use Frames to read data from the platform's data store into pandas DataFrames.\n",
    "  For more information about Frames, see to the [**frames**](frames.ipynb) tutorial."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-sqlalchemy-engine-create\"></a>\n",
    "### Creating an SQLAlchemy Engine\n",
    "\n",
    "Most of the examples in this tutorial use SQLAlchemy to connect to the external database.\n",
    "This requires calling the SQLAlchemy `create_engine` function to create an `Engine` object for a specific database URL:\n",
    "```python\n",
    "engine = create_engine('<database URL>')\n",
    "```\n",
    "\n",
    "The typical form of a database URL that's used by the SQLAlchemy engine is `<dialect>+<driver>://<username>:<password>@<host>:<port>/<database>`.\n",
    "The engine creates a `Dialect` object that's tailored towards a database instance (`DB_INSTANCE`) for the specified database URL.\n",
    "The engine also creates a `Pool` object for establishing a DBAPI connection at the database IP address (`<host>:<port>` &mdash; `DB_HOST:DB_PORT`) when a connection request is first received.\n",
    "The connection to the DBAPI is done using the driver whose name is specified in the database URL (`<driver>`); the driver name can be omitted to use the default driver for the specified dialect.\n",
    "\n",
    "The following example creates a MySQL SQLAlchemy engine for the database URL`mysql+pymysql://scott:tiger@localhost/foo`:\n",
    "```python\n",
    "engine = create_engine('mysql+pymysql://scott:tiger@localhost/foo')\n",
    "```\n",
    "\n",
    "For more information, see the SQLAlchemy [Engine Configuration](https://docs.sqlalchemy.org/en/latest/core/engines.html#engine-configuration) documentation, and especially the [Database URLs](https://docs.sqlalchemy.org/en/latest/core/engines.html#database-urls) section."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-init\"></a>\n",
    "## Initialization\n",
    "\n",
    "Start out by performing initialization steps that are required for executing all code examples in this tutorial &mdash; importing required packages and creating a Frames client."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-import-packages\"></a>\n",
    "### Importing Packages\n",
    "\n",
    "Import Python packages used in this tutorial."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import v3io_frames as v3f\n",
    "from sqlalchemy.engine import create_engine"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-create-frames-client\"></a>\n",
    "### Creating a Frames Client\n",
    "\n",
    "Create a Frames `Client` object (named `client`), which references data in the platform's \"users\" data container.<br>\n",
    "(User authentication is done by using the platform access key that's stored in the `V3IO_ACCESS_KEY` environment variable, which is automatically defined for the platform's Jupyter Notebook service.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 4 µs, sys: 0 ns, total: 4 µs\n",
      "Wall time: 7.87 µs\n"
     ]
    }
   ],
   "source": [
    "%time\n",
    "client = v3f.Client('framesd:8081', container='users')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-mysql\"></a>\n",
    "## Reading from a MySQL Database\n",
    "\n",
    "This section demonstrates different ways of ingesting data from an external MySQL database into the platform's data store.\n",
    "All the examples use a pandas SQL query to read data from a MySQL database into pandas DataFrames, and Frames to write the data to the platform's NoSQL store and then read it back for verification.\n",
    "The examples differ in the methods used for performing the read and write operations:\n",
    "\n",
    "- <a id=\"readextdb-mysql-read-chunks\"></a>**Chunked reads (\"data streaming\")** &mdash; the [first](#readextdb-mysql-chunks-sqlalchemy) and [third](#readextdb-mysql-chunks-pymysql) examples both read the data from the MySQL database into a pandas DataFrames iterator in chunks, essentially streaming the data.\n",
    "  One example connects to the database using an SQLAlchemy engine and the other using a PyMySQL connection.\n",
    "  Many pandas data engines (including SQL, CSV, and the Frames NoSQL backend) support data chunking.\n",
    "  With chunking, the driver forms a continuous iterator for reading or writing data sequentially in chunks.\n",
    "  Working in chunks is a common approach for handling big data sets that cannot fit into the available memory resources, such as pandas DataFrames.\n",
    "  Chunked reads are performed by setting the `chunksize` parameter of the pandas SQL-query method (`read_sql_query` or `read_sql`) to the number of rows (items) to read in each chunk (the chunk size).\n",
    "  When this parameter is set, the read method returns the data in a pandas DataFrames iterator.\n",
    "  This iterator can be passed as-is to a DataFrame write command, such as the Frames `write` method.\n",
    "- <a id=\"readextdb-mysql-read-bulk\"></a>**Bulk reads** &mdash; the [second example](#readextdb-read-mysql-bulk-pymysql) reads a table from the MySQL database into a single pandas DataFrame as a bulk operation.\n",
    "\n",
    "All the examples use a public MySQL database named [Rfam](https://rfam.readthedocs.io/en/latest/database.html); the database URL is `mysql+pymysql://rfamro@mysql-rfam-public.ebi.ac.uk:4497/Rfam`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-mysql-init\"></a>\n",
    "### Initialization"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-mysql-create-query-obj\"></a>\n",
    "#### Create an SQL Query Object\n",
    "\n",
    "Start out by creating a query object that identifies the data to download from the database.\n",
    "This object is used in all of the MySQL database examples."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 3 µs, sys: 1 µs, total: 4 µs\n",
      "Wall time: 9.3 µs\n"
     ]
    }
   ],
   "source": [
    "%time\n",
    "\n",
    "mysql_query = 'select rfam_acc,rfam_id,auto_wiki,description,author,seed_source FROM family'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-mysql-create-pymysql-db-connection\"></a>\n",
    "#### Create a PyMySQL Connection to the MySQL Database\n",
    "\n",
    "Install the PyMySQL Python MySQL client library (**pymysql**), and then use the library's `connect` method to create a database connection to the MySQL database.\n",
    "This step is required for the examples that don't use the SQLAlchemy engine.\n",
    "\n",
    "> **AWS Note:** If you're running the code in an AWS cloud with the persisted data and software package for eventual consistency, note that the creation of the database connection might take a bit of time to complete."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2 µs, sys: 0 ns, total: 2 µs\n",
      "Wall time: 5.72 µs\n"
     ]
    }
   ],
   "source": [
    "import pymysql\n",
    "%time\n",
    "\n",
    "conn = pymysql.connect(\n",
    "    host=os.getenv('DB_HOST','mysql-rfam-public.ebi.ac.uk'),\n",
    "    port=int(4497),\n",
    "    user=os.getenv('DB_USER','rfamro'),\n",
    "    passwd=os.getenv('DB_PASSWORD',''),\n",
    "    db=os.getenv('DB_NAME','Rfam'),\n",
    "    charset='utf8mb4')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-mysql-chunks-sqlalchemy\"></a>\n",
    "### Reading Data in Chunks Using SQLAlchemy\n",
    "\n",
    "This example reads data from a MySQL database into a pandas DataFrame iterator, in chunks, using an SQLAlchemy database engine; writes the DataFrames to a platform NoSQL table, in chunks, using Frames; and reads from the NoSQL table using Frames."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-mysql-chunks-sqlalchemy-create-engine\"></a>\n",
    "#### Create a MySQL SQLAlchemy Engine\n",
    "\n",
    "Create an SQLAlchemy engine using the [MySQL dialect](https://docs.sqlalchemy.org/en/latest/core/engines.html#mysql).\n",
    "\n",
    "The MySQL dialect uses `mysql-python` as the default DBAPI.\n",
    "However, the following example sets the driver in the database URL to `pymysql` to use the PyMySQL DBAPI (database URL = `mysql+pymysql://rfamro@mysql-rfam-public.ebi.ac.uk:4497/Rfam`)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 3 µs, sys: 0 ns, total: 3 µs\n",
      "Wall time: 5.25 µs\n"
     ]
    }
   ],
   "source": [
    "import pymysql\n",
    "%time\n",
    "\n",
    "mysql_URL = 'mysql+pymysql://rfamro@mysql-rfam-public.ebi.ac.uk:4497/Rfam'\n",
    "engine = create_engine(mysql_URL)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-mysql-chunks-sqlalchemy-read-from-db\"></a>\n",
    "#### Read from the MySQL Database in Chunks (\"Streaming\")\n",
    "\n",
    "Use the pandas DataFrames `read_sql` method with the query and SQLAlchemy engine objects that you created in the previous steps to read data from the MySQL database, in chunks (\"streaming\"), into a pandas DataFrames iterator."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 3 µs, sys: 0 ns, total: 3 µs\n",
      "Wall time: 5.72 µs\n"
     ]
    }
   ],
   "source": [
    "%time\n",
    "\n",
    "CHUNK_SIZE = 100000\n",
    "\n",
    "all_df = pd.read_sql(mysql_query,engine,chunksize = CHUNK_SIZE)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-mysql-chunks-sqlalchemy-frames-write\"></a>\n",
    "#### Write the pandas DataFrames to the Platform's NoSQL Store Using Frames\n",
    "\n",
    "Use Frames to write the pandas DataFrames that were read in the previous step to the platform's NoSQL store as individual data chunks."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 3 µs, sys: 1e+03 ns, total: 4 µs\n",
      "Wall time: 5.72 µs\n"
     ]
    }
   ],
   "source": [
    "%time\n",
    "\n",
    "mysql_table = os.path.join(os.getenv('V3IO_USERNAME')+'/examples/family')\n",
    "backend = 'kv'\n",
    "\n",
    "for df in all_df:\n",
    "    df = df.reset_index()\n",
    "    out = client.write(backend,mysql_table, df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-mysql-chunks-sqlalchemy-read-from-platform\"></a>\n",
    "#### Read from the Platform's NoSQL Store Using Frames\n",
    "\n",
    "Use Frames to read the platform NoSQL table that you created in the previous step."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 4 µs, sys: 0 ns, total: 4 µs\n",
      "Wall time: 8.34 µs\n"
     ]
    },
    {
     "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>author</th>\n",
       "      <th>auto_wiki</th>\n",
       "      <th>description</th>\n",
       "      <th>index</th>\n",
       "      <th>rfam_acc</th>\n",
       "      <th>rfam_id</th>\n",
       "      <th>seed_source</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>idx</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2787</th>\n",
       "      <td>Argasinska J</td>\n",
       "      <td>2554</td>\n",
       "      <td>Bacillus sRNA 1</td>\n",
       "      <td>2787</td>\n",
       "      <td>RF02888</td>\n",
       "      <td>BtsR1</td>\n",
       "      <td>Argasinska J</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>366</th>\n",
       "      <td>Moxon SJ</td>\n",
       "      <td>1644</td>\n",
       "      <td>Insertion sequence IS1222 ribosomal frameshift...</td>\n",
       "      <td>366</td>\n",
       "      <td>RF00383</td>\n",
       "      <td>IS1222_FSE</td>\n",
       "      <td>PMID:15126494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>362</th>\n",
       "      <td>Moxon SJ</td>\n",
       "      <td>2397</td>\n",
       "      <td>ydaO/yuaA leader</td>\n",
       "      <td>362</td>\n",
       "      <td>RF00379</td>\n",
       "      <td>ydaO-yuaA</td>\n",
       "      <td>Barrick JE, Breaker RR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1915</th>\n",
       "      <td>Boursnell C</td>\n",
       "      <td>1287</td>\n",
       "      <td>microRNA mir-1253</td>\n",
       "      <td>1915</td>\n",
       "      <td>RF02006</td>\n",
       "      <td>mir-1253</td>\n",
       "      <td>Predicted; ClustalW2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>881</th>\n",
       "      <td>Wilkinson A</td>\n",
       "      <td>1287</td>\n",
       "      <td>microRNA mir-582</td>\n",
       "      <td>881</td>\n",
       "      <td>RF00927</td>\n",
       "      <td>mir-582</td>\n",
       "      <td>miRBase; Wilkinson A</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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2305</th>\n",
       "      <td>Osuch I, Eberhardt R</td>\n",
       "      <td>1269</td>\n",
       "      <td>Pseudomonas sRNA P34</td>\n",
       "      <td>2305</td>\n",
       "      <td>RF02405</td>\n",
       "      <td>P34</td>\n",
       "      <td>INFERNAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>997</th>\n",
       "      <td>Gardner PP</td>\n",
       "      <td>1322</td>\n",
       "      <td>Saccharomyces telomerase</td>\n",
       "      <td>997</td>\n",
       "      <td>RF01050</td>\n",
       "      <td>Sacc_telomerase</td>\n",
       "      <td>Published; PMID:15242611</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2327</th>\n",
       "      <td>Eberhardt R</td>\n",
       "      <td>2341</td>\n",
       "      <td>Streptococcus sRNA SpF10</td>\n",
       "      <td>2327</td>\n",
       "      <td>RF02427</td>\n",
       "      <td>SpF10_sRNA</td>\n",
       "      <td>Eberhardt R</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1284</th>\n",
       "      <td>Wilkinson A</td>\n",
       "      <td>1265</td>\n",
       "      <td>CRISPR RNA direct repeat element</td>\n",
       "      <td>1284</td>\n",
       "      <td>RF01352</td>\n",
       "      <td>CRISPR-DR43</td>\n",
       "      <td>Predicted; WAR; Wilkinson A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>713</th>\n",
       "      <td>Wilkinson A</td>\n",
       "      <td>1936</td>\n",
       "      <td>microRNA mir-208</td>\n",
       "      <td>713</td>\n",
       "      <td>RF00749</td>\n",
       "      <td>mir-208</td>\n",
       "      <td>miRBase; Wilkinson A</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3024 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    author  auto_wiki  \\\n",
       "idx                                     \n",
       "2787          Argasinska J       2554   \n",
       "366               Moxon SJ       1644   \n",
       "362               Moxon SJ       2397   \n",
       "1915           Boursnell C       1287   \n",
       "881            Wilkinson A       1287   \n",
       "...                    ...        ...   \n",
       "2305  Osuch I, Eberhardt R       1269   \n",
       "997             Gardner PP       1322   \n",
       "2327           Eberhardt R       2341   \n",
       "1284           Wilkinson A       1265   \n",
       "713            Wilkinson A       1936   \n",
       "\n",
       "                                            description  index rfam_acc  \\\n",
       "idx                                                                       \n",
       "2787                                    Bacillus sRNA 1   2787  RF02888   \n",
       "366   Insertion sequence IS1222 ribosomal frameshift...    366  RF00383   \n",
       "362                                    ydaO/yuaA leader    362  RF00379   \n",
       "1915                                  microRNA mir-1253   1915  RF02006   \n",
       "881                                    microRNA mir-582    881  RF00927   \n",
       "...                                                 ...    ...      ...   \n",
       "2305                               Pseudomonas sRNA P34   2305  RF02405   \n",
       "997                            Saccharomyces telomerase    997  RF01050   \n",
       "2327                           Streptococcus sRNA SpF10   2327  RF02427   \n",
       "1284                   CRISPR RNA direct repeat element   1284  RF01352   \n",
       "713                                    microRNA mir-208    713  RF00749   \n",
       "\n",
       "              rfam_id                  seed_source  \n",
       "idx                                                 \n",
       "2787            BtsR1                 Argasinska J  \n",
       "366        IS1222_FSE                PMID:15126494  \n",
       "362         ydaO-yuaA       Barrick JE, Breaker RR  \n",
       "1915         mir-1253         Predicted; ClustalW2  \n",
       "881           mir-582         miRBase; Wilkinson A  \n",
       "...               ...                          ...  \n",
       "2305              P34                     INFERNAL  \n",
       "997   Sacc_telomerase     Published; PMID:15242611  \n",
       "2327       SpF10_sRNA                  Eberhardt R  \n",
       "1284      CRISPR-DR43  Predicted; WAR; Wilkinson A  \n",
       "713           mir-208         miRBase; Wilkinson A  \n",
       "\n",
       "[3024 rows x 7 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%time\n",
    "\n",
    "client.read(backend, mysql_table)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-mysql-bulk-pymysql\"></a>\n",
    "### Reading a Table as a Bulk Operation Using PyMySQL\n",
    "\n",
    "This example reads a table from the MySQL database into a pandas DataFrame as a bulk operation, using a PyMySQL database connection; writes the contents of the DataFrame to a platform NoSQL table using Frames; and reads from the NoSQL table using Frames."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-mysql-bulk-pymysql-read-from-db\"></a>\n",
    "#### Read from the Database as a Bulk Operation\n",
    "\n",
    "Use the pandas DataFrames `read_sql_query` method with the query and PyMySQL database-connection objects that you created in the previous steps to read a table from the MySQL database, as a bulk operation, into a pandas DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2 µs, sys: 0 ns, total: 2 µs\n",
      "Wall time: 5.72 µs\n"
     ]
    }
   ],
   "source": [
    "%time\n",
    "\n",
    "# Read from the external database\n",
    "df = pd.read_sql_query(mysql_query, conn) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 3 µs, sys: 0 ns, total: 3 µs\n",
      "Wall time: 5.72 µs\n"
     ]
    },
    {
     "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>rfam_acc</th>\n",
       "      <th>rfam_id</th>\n",
       "      <th>auto_wiki</th>\n",
       "      <th>description</th>\n",
       "      <th>author</th>\n",
       "      <th>seed_source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3014</th>\n",
       "      <td>RF03116</td>\n",
       "      <td>aCoV-5UTR</td>\n",
       "      <td>2707</td>\n",
       "      <td>Alphacoronavirus 5'UTR</td>\n",
       "      <td>Lamkiewicz K</td>\n",
       "      <td>Lamkiewicz K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3015</th>\n",
       "      <td>RF03117</td>\n",
       "      <td>bCoV-5UTR</td>\n",
       "      <td>2707</td>\n",
       "      <td>Betacoronavirus 5'UTR</td>\n",
       "      <td>Lamkiewicz K</td>\n",
       "      <td>Lamkiewicz K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3016</th>\n",
       "      <td>RF03118</td>\n",
       "      <td>gCoV-5UTR</td>\n",
       "      <td>2707</td>\n",
       "      <td>Gammacoronavirus 5'UTR</td>\n",
       "      <td>Lamkiewicz K</td>\n",
       "      <td>Lamkiewicz K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3017</th>\n",
       "      <td>RF03119</td>\n",
       "      <td>dCoV-5UTR</td>\n",
       "      <td>2707</td>\n",
       "      <td>Deltacoronavirus 5'UTR</td>\n",
       "      <td>Lamkiewicz K</td>\n",
       "      <td>Lamkiewicz K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3018</th>\n",
       "      <td>RF03120</td>\n",
       "      <td>Sarbecovirus-5UTR</td>\n",
       "      <td>2707</td>\n",
       "      <td>Sarbecovirus 5'UTR</td>\n",
       "      <td>Lamkiewicz K</td>\n",
       "      <td>Lamkiewicz K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3019</th>\n",
       "      <td>RF03121</td>\n",
       "      <td>aCoV-3UTR</td>\n",
       "      <td>2708</td>\n",
       "      <td>Alphacoronavirus 3'UTR</td>\n",
       "      <td>Lamkiewicz K</td>\n",
       "      <td>Kevin Lamkiewicz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3020</th>\n",
       "      <td>RF03122</td>\n",
       "      <td>bCoV-3UTR</td>\n",
       "      <td>2708</td>\n",
       "      <td>Betacoronavirus 3'UTR</td>\n",
       "      <td>Lamkiewicz K</td>\n",
       "      <td>Lamkiewicz K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3021</th>\n",
       "      <td>RF03123</td>\n",
       "      <td>gCoV-3UTR</td>\n",
       "      <td>2708</td>\n",
       "      <td>Gammacoronavirus 3'UTR</td>\n",
       "      <td>Lamkiewicz K</td>\n",
       "      <td>Lamkiewicz K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3022</th>\n",
       "      <td>RF03124</td>\n",
       "      <td>dCoV-3UTR</td>\n",
       "      <td>2708</td>\n",
       "      <td>Deltacoronavirus 3'UTR</td>\n",
       "      <td>Lamkiewicz K</td>\n",
       "      <td>Lamkiewicz K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3023</th>\n",
       "      <td>RF03125</td>\n",
       "      <td>Sarbecovirus-3UTR</td>\n",
       "      <td>2708</td>\n",
       "      <td>Sarbecovirus 3'UTR</td>\n",
       "      <td>Lamkiewicz K</td>\n",
       "      <td>Lamkiewicz K</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     rfam_acc            rfam_id  auto_wiki             description  \\\n",
       "3014  RF03116          aCoV-5UTR       2707  Alphacoronavirus 5'UTR   \n",
       "3015  RF03117          bCoV-5UTR       2707   Betacoronavirus 5'UTR   \n",
       "3016  RF03118          gCoV-5UTR       2707  Gammacoronavirus 5'UTR   \n",
       "3017  RF03119          dCoV-5UTR       2707  Deltacoronavirus 5'UTR   \n",
       "3018  RF03120  Sarbecovirus-5UTR       2707      Sarbecovirus 5'UTR   \n",
       "3019  RF03121          aCoV-3UTR       2708  Alphacoronavirus 3'UTR   \n",
       "3020  RF03122          bCoV-3UTR       2708   Betacoronavirus 3'UTR   \n",
       "3021  RF03123          gCoV-3UTR       2708  Gammacoronavirus 3'UTR   \n",
       "3022  RF03124          dCoV-3UTR       2708  Deltacoronavirus 3'UTR   \n",
       "3023  RF03125  Sarbecovirus-3UTR       2708      Sarbecovirus 3'UTR   \n",
       "\n",
       "            author       seed_source  \n",
       "3014  Lamkiewicz K      Lamkiewicz K  \n",
       "3015  Lamkiewicz K      Lamkiewicz K  \n",
       "3016  Lamkiewicz K      Lamkiewicz K  \n",
       "3017  Lamkiewicz K      Lamkiewicz K  \n",
       "3018  Lamkiewicz K      Lamkiewicz K  \n",
       "3019  Lamkiewicz K  Kevin Lamkiewicz  \n",
       "3020  Lamkiewicz K      Lamkiewicz K  \n",
       "3021  Lamkiewicz K      Lamkiewicz K  \n",
       "3022  Lamkiewicz K      Lamkiewicz K  \n",
       "3023  Lamkiewicz K      Lamkiewicz K  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%time\n",
    "\n",
    "# Display the last 10 row (items) of the read DataFrame\n",
    "df.tail(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-mysql-bulk-pymysql-write-to-platform\"></a>\n",
    "#### Write the Table to the Platform's NoSQL Store Using Frames\n",
    "\n",
    "Use Frames to write the pandas DataFrame that was read in the previous step to the platform's NoSQL store."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 3 µs, sys: 0 ns, total: 3 µs\n",
      "Wall time: 5.48 µs\n"
     ]
    }
   ],
   "source": [
    "%time\n",
    "\n",
    "mysql_table_1 = os.path.join(os.getenv('V3IO_USERNAME')+'/examples/family1')\n",
    "\n",
    "client.write(backend, mysql_table_1, df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-mysql-bulk-pymysql-read-from-platform\"></a>\n",
    "#### Read from the Platform's NoSQL Store Using Frames\n",
    "\n",
    "Use Frames to read the platform NoSQL table that you created in the previous step."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 3 µs, sys: 0 ns, total: 3 µs\n",
      "Wall time: 7.39 µs\n"
     ]
    },
    {
     "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>author</th>\n",
       "      <th>auto_wiki</th>\n",
       "      <th>description</th>\n",
       "      <th>rfam_acc</th>\n",
       "      <th>rfam_id</th>\n",
       "      <th>seed_source</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>idx</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2852</th>\n",
       "      <td>Weinberg Z</td>\n",
       "      <td>2601</td>\n",
       "      <td>DUF3577 RNA</td>\n",
       "      <td>RF02954</td>\n",
       "      <td>DUF3577</td>\n",
       "      <td>Weinberg Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2433</th>\n",
       "      <td>Eberhardt R</td>\n",
       "      <td>2348</td>\n",
       "      <td>Hepatitis A virus (HAV) cis-acting replication...</td>\n",
       "      <td>RF02533</td>\n",
       "      <td>HAV_CRE</td>\n",
       "      <td>PMID:18684812</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>267</th>\n",
       "      <td>Moxon SJ</td>\n",
       "      <td>1550</td>\n",
       "      <td>Small nucleolar RNA SNORD49</td>\n",
       "      <td>RF00277</td>\n",
       "      <td>SNORD49</td>\n",
       "      <td>Moxon SJ, INFERNAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>825</th>\n",
       "      <td>Wilkinson A</td>\n",
       "      <td>1287</td>\n",
       "      <td>microRNA mir-BART17</td>\n",
       "      <td>RF00863</td>\n",
       "      <td>mir-BART17</td>\n",
       "      <td>miRBase; Wilkinson A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2640</th>\n",
       "      <td>Argasinska J</td>\n",
       "      <td>2518</td>\n",
       "      <td>Soft rot Enterobacteriaceae Fwd 6 3'UTR</td>\n",
       "      <td>RF02740</td>\n",
       "      <td>Fwd6_3p_UTR</td>\n",
       "      <td>Argasinska J</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>1814</th>\n",
       "      <td>Boursnell C</td>\n",
       "      <td>1287</td>\n",
       "      <td>microRNA mir-142</td>\n",
       "      <td>RF01896</td>\n",
       "      <td>mir-142</td>\n",
       "      <td>Predicted; ClustalW2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2818</th>\n",
       "      <td>Corbino K, Weinberg Z</td>\n",
       "      <td>2571</td>\n",
       "      <td>Fusobacteriales-1 RNA</td>\n",
       "      <td>RF02920</td>\n",
       "      <td>Fusobacteriales-1</td>\n",
       "      <td>Weinberg Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2682</th>\n",
       "      <td>Argasinska J</td>\n",
       "      <td>2437</td>\n",
       "      <td>CpoB/ybgF ICR thermometer</td>\n",
       "      <td>RF02782</td>\n",
       "      <td>CpoB_ybgF_thermometer</td>\n",
       "      <td>Argasinska J</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2323</th>\n",
       "      <td>Eberhardt R</td>\n",
       "      <td>2451</td>\n",
       "      <td>Burkholderia sRNA Bp1_Cand871_SIPHT</td>\n",
       "      <td>RF02423</td>\n",
       "      <td>Bp1_781</td>\n",
       "      <td>Predicted; CMfinder</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>174</th>\n",
       "      <td>Griffiths-Jones SR, Daub J</td>\n",
       "      <td>1463</td>\n",
       "      <td>Small nucleolar RNA SNORD113/SNORD114 family</td>\n",
       "      <td>RF00181</td>\n",
       "      <td>SNORD113</td>\n",
       "      <td>PMID:12045206</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3024 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          author  auto_wiki  \\\n",
       "idx                                           \n",
       "2852                  Weinberg Z       2601   \n",
       "2433                 Eberhardt R       2348   \n",
       "267                     Moxon SJ       1550   \n",
       "825                  Wilkinson A       1287   \n",
       "2640                Argasinska J       2518   \n",
       "...                          ...        ...   \n",
       "1814                 Boursnell C       1287   \n",
       "2818       Corbino K, Weinberg Z       2571   \n",
       "2682                Argasinska J       2437   \n",
       "2323                 Eberhardt R       2451   \n",
       "174   Griffiths-Jones SR, Daub J       1463   \n",
       "\n",
       "                                            description rfam_acc  \\\n",
       "idx                                                                \n",
       "2852                                        DUF3577 RNA  RF02954   \n",
       "2433  Hepatitis A virus (HAV) cis-acting replication...  RF02533   \n",
       "267                         Small nucleolar RNA SNORD49  RF00277   \n",
       "825                                 microRNA mir-BART17  RF00863   \n",
       "2640            Soft rot Enterobacteriaceae Fwd 6 3'UTR  RF02740   \n",
       "...                                                 ...      ...   \n",
       "1814                                   microRNA mir-142  RF01896   \n",
       "2818                              Fusobacteriales-1 RNA  RF02920   \n",
       "2682                          CpoB/ybgF ICR thermometer  RF02782   \n",
       "2323                Burkholderia sRNA Bp1_Cand871_SIPHT  RF02423   \n",
       "174        Small nucleolar RNA SNORD113/SNORD114 family  RF00181   \n",
       "\n",
       "                    rfam_id           seed_source  \n",
       "idx                                                \n",
       "2852                DUF3577            Weinberg Z  \n",
       "2433                HAV_CRE         PMID:18684812  \n",
       "267                 SNORD49    Moxon SJ, INFERNAL  \n",
       "825              mir-BART17  miRBase; Wilkinson A  \n",
       "2640            Fwd6_3p_UTR          Argasinska J  \n",
       "...                     ...                   ...  \n",
       "1814                mir-142  Predicted; ClustalW2  \n",
       "2818      Fusobacteriales-1            Weinberg Z  \n",
       "2682  CpoB_ybgF_thermometer          Argasinska J  \n",
       "2323                Bp1_781   Predicted; CMfinder  \n",
       "174                SNORD113         PMID:12045206  \n",
       "\n",
       "[3024 rows x 6 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%time\n",
    "\n",
    "client.read(backend, mysql_table_1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-mysql-chunks-pymysql\"></a>\n",
    "### Reading Data in Chunks Using PyMySQL\n",
    "\n",
    "This example reads data from a MySQL database into a pandas DataFrame iterator in chunks, using a PyMySQL database connection; writes the DataFrames iterator to a platform NoSQL table using Frames; and reads from the NoSQL table using Frames."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-mysql-chunks-pymysql-read-from-db\"></a>\n",
    "#### Read from the MySQL Database in Chunks (\"Streaming\")\n",
    "\n",
    "Use the pandas DataFrames `read_sql` method with the query and PyMySQL database-connection objects that you created in the previous steps to read data from the MySQL database, in chunks (\"streaming\"), into a pandas DataFrames iterator."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2 µs, sys: 1e+03 ns, total: 3 µs\n",
      "Wall time: 5.72 µs\n"
     ]
    }
   ],
   "source": [
    "%time\n",
    "\n",
    "mysql_table_2 = os.path.join(os.getenv('V3IO_USERNAME')+'/examples/family2')\n",
    "CHUNK_SIZE = 1000\n",
    "\n",
    "dfIterator = pd.read_sql(mysql_query, conn, chunksize=CHUNK_SIZE)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-mysql-chunks-pymysql-write-to-platform\"></a>\n",
    "#### Write the pandas DataFrames Iterator to the Platform's NoSQL Store Using Frames\n",
    "\n",
    "Use Frames to write the pandas DataFrames iterator that was read in the previous step to the platform's NoSQL store."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "client.write(backend, mysql_table_2, dfIterator)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-mysql-chunks-pymysql-read-from-platform\"></a>\n",
    "#### Read from the Platform's NoSQL Store Using Frames\n",
    "\n",
    "Use Frames to read the platform NoSQL table that you created in the previous step."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 3 µs, sys: 0 ns, total: 3 µs\n",
      "Wall time: 5.48 µs\n"
     ]
    },
    {
     "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>author</th>\n",
       "      <th>auto_wiki</th>\n",
       "      <th>description</th>\n",
       "      <th>rfam_acc</th>\n",
       "      <th>rfam_id</th>\n",
       "      <th>seed_source</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>idx</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1529</th>\n",
       "      <td>Osuch I</td>\n",
       "      <td>1264</td>\n",
       "      <td>small nucleolar RNA snoR23</td>\n",
       "      <td>RF01598</td>\n",
       "      <td>snoR23</td>\n",
       "      <td>INFERNAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>496</th>\n",
       "      <td>Barrick JE</td>\n",
       "      <td>1258</td>\n",
       "      <td>ybhL leader</td>\n",
       "      <td>RF00520</td>\n",
       "      <td>ybhL</td>\n",
       "      <td>Barrick JE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>320</th>\n",
       "      <td>Moxon SJ, Daub J</td>\n",
       "      <td>1600</td>\n",
       "      <td>Small nucleolar RNA SNORA3/SNORA45 family</td>\n",
       "      <td>RF00334</td>\n",
       "      <td>SNORA3</td>\n",
       "      <td>INFERNAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>851</th>\n",
       "      <td>Wilkinson A</td>\n",
       "      <td>1986</td>\n",
       "      <td>microRNA MIR854</td>\n",
       "      <td>RF00893</td>\n",
       "      <td>MIR854</td>\n",
       "      <td>miRBase; Wilkinson A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1368</th>\n",
       "      <td>Osuch I</td>\n",
       "      <td>1264</td>\n",
       "      <td>small nucleolar RNA snR10</td>\n",
       "      <td>RF01437</td>\n",
       "      <td>S_pombe_snR10</td>\n",
       "      <td>INFERNAL</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>1052</th>\n",
       "      <td>Wilkinson A</td>\n",
       "      <td>1651</td>\n",
       "      <td>Pseudoknot of upstream pseudoknot domain (UPD)...</td>\n",
       "      <td>RF01111</td>\n",
       "      <td>SBWMV2_UPD-PKk</td>\n",
       "      <td>Pseudobase</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1804</th>\n",
       "      <td>Eberhardt R</td>\n",
       "      <td>2228</td>\n",
       "      <td>Heat shock gene hsromega conserved region 1</td>\n",
       "      <td>RF01885</td>\n",
       "      <td>HSR-omega_1</td>\n",
       "      <td>Predicted; WAR; Eberhardt R</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1015</th>\n",
       "      <td>Wilkinson A</td>\n",
       "      <td>1651</td>\n",
       "      <td>Pseudoknot of upstream pseudoknot domain (UPD)...</td>\n",
       "      <td>RF01072</td>\n",
       "      <td>TMV_UPD-PK3</td>\n",
       "      <td>Pseudobase</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>368</th>\n",
       "      <td>Moxon SJ</td>\n",
       "      <td>1646</td>\n",
       "      <td>Infectious bronchitis virus D-RNA</td>\n",
       "      <td>RF00385</td>\n",
       "      <td>IBV_D-RNA</td>\n",
       "      <td>PMID:11119581</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2853</th>\n",
       "      <td>Weinberg Z</td>\n",
       "      <td>2602</td>\n",
       "      <td>EGFOA RNA</td>\n",
       "      <td>RF02955</td>\n",
       "      <td>EGFOA</td>\n",
       "      <td>Weinberg Z</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3024 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                author  auto_wiki  \\\n",
       "idx                                 \n",
       "1529           Osuch I       1264   \n",
       "496         Barrick JE       1258   \n",
       "320   Moxon SJ, Daub J       1600   \n",
       "851        Wilkinson A       1986   \n",
       "1368           Osuch I       1264   \n",
       "...                ...        ...   \n",
       "1052       Wilkinson A       1651   \n",
       "1804       Eberhardt R       2228   \n",
       "1015       Wilkinson A       1651   \n",
       "368           Moxon SJ       1646   \n",
       "2853        Weinberg Z       2602   \n",
       "\n",
       "                                            description rfam_acc  \\\n",
       "idx                                                                \n",
       "1529                         small nucleolar RNA snoR23  RF01598   \n",
       "496                                         ybhL leader  RF00520   \n",
       "320           Small nucleolar RNA SNORA3/SNORA45 family  RF00334   \n",
       "851                                     microRNA MIR854  RF00893   \n",
       "1368                          small nucleolar RNA snR10  RF01437   \n",
       "...                                                 ...      ...   \n",
       "1052  Pseudoknot of upstream pseudoknot domain (UPD)...  RF01111   \n",
       "1804        Heat shock gene hsromega conserved region 1  RF01885   \n",
       "1015  Pseudoknot of upstream pseudoknot domain (UPD)...  RF01072   \n",
       "368                   Infectious bronchitis virus D-RNA  RF00385   \n",
       "2853                                          EGFOA RNA  RF02955   \n",
       "\n",
       "             rfam_id                  seed_source  \n",
       "idx                                                \n",
       "1529          snoR23                     INFERNAL  \n",
       "496             ybhL                   Barrick JE  \n",
       "320           SNORA3                     INFERNAL  \n",
       "851           MIR854         miRBase; Wilkinson A  \n",
       "1368   S_pombe_snR10                     INFERNAL  \n",
       "...              ...                          ...  \n",
       "1052  SBWMV2_UPD-PKk                   Pseudobase  \n",
       "1804     HSR-omega_1  Predicted; WAR; Eberhardt R  \n",
       "1015     TMV_UPD-PK3                   Pseudobase  \n",
       "368        IBV_D-RNA                PMID:11119581  \n",
       "2853           EGFOA                   Weinberg Z  \n",
       "\n",
       "[3024 rows x 6 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%time\n",
    "\n",
    "client.read(backend, mysql_table_2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-postgresql\"></a>\n",
    "## Reading from a PostgreSQL Database\n",
    "\n",
    "This example uses a pandas `read_sql` query with an SQLAlchemy engine for the [PostgreSQL dialect](https://docs.sqlalchemy.org/en/latest/core/engines.html#postgresql) to load data from an external PostgreSQL database into a pandas DataFrame; and then uses Frames to write the data to the platform's NoSQL store and read it back for verification.\n",
    "\n",
    "The PostgreSQL dialect uses `psycopg2` as the default DBAPI, so you can optionally omit the name of the DBAPI driver from the database URL that you pass in the `create_engine` call.\n",
    "For example:\n",
    "```python\n",
    "# Default DBAPI\n",
    "engine = create_engine('postgresql://scott:tiger@localhost/mydatabase')\n",
    "\n",
    "# DBAPI = psycopg2\n",
    "engine = create_engine('postgresql+psycopg2://scott:tiger@localhost/mydatabase')\n",
    "```\n",
    "\n",
    "> **Note:** Before you run the code, you must edit the definition of the database-URL variable (`postgresql_URL`) to use a valid PostgreSQL database.\n",
    "> You can also optionally edit the SQL query (`postgresql_query`) to use a custom read filter."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-postgresql-sqlalchemy-create-engine\"></a>\n",
    "#### Create a PostgreSQL SQLAlchemy Engine\n",
    "\n",
    "Create an SQLAlchemy engine using the PostgreSQL dialect."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg2\n",
    "\n",
    "# !! TODO !! Replace the <...> placeholders in the database URL\n",
    "postgresql_URL = 'postgresql+psycopg2://<username>:<password>@<host>:<port>/<database>'\n",
    "engine = create_engine(postgresql_URL)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-postgresql-sqlachemy-read-from-db\"></a>\n",
    "#### Read from the PostgreSQL Database\n",
    "\n",
    "Create an SQL query object and use the pandas DataFrames `read_sql` method with this object and the SQLAlchemy engine object that you created in the previous step to read data from the PostgreSQL database into a pandas DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create an SQL query object\n",
    "postgresql_query = 'select * from table'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read from the external database\n",
    "df = pd.read_sql(postgresql_query,engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Display the contents of the read DataFrame\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-postgresql-sqlalchemy-write-to-platform\"></a>\n",
    "#### Write the Data to the Platform's NoSQL Store Using Frames\n",
    "\n",
    "Use Frames to write the pandas DataFrame that was read in the previous step to the platform's NoSQL store."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "postgresql_table = os.path.join(os.getenv('V3IO_USERNAME')+'/examples/postgresql_table')\n",
    "backend = 'kv'\n",
    "client.write(backend, postgresql_table, df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-postgresql-sqlalchemy-read-from-platform\"></a>\n",
    "#### Read from the Platform's NoSQL Store Using Frames\n",
    "\n",
    "Use Frames to read the platform NoSQL table that you created in the previous step."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "client.read(backend, postgresql_table)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-oracle\"></a>\n",
    "## Reading from an Oracle Database\n",
    "\n",
    "This example uses a pandas `read_sql` query with an SQLAlchemy engine for the [Oracle dialect](https://docs.sqlalchemy.org/en/latest/core/engines.html#oracle) to load data from an external Oracle database into a pandas DataFrame; and then uses Frames to write the data to the platform's NoSQL store and read it back for verification.\n",
    "\n",
    "The Oracle dialect uses `cx_oracle` as the default DBAPI, so you can optionally omit the name of the DBAPI driver from the database URL that you pass in the `create_engine` call.\n",
    "For example:\n",
    "```python\n",
    "# Default DBAPI\n",
    "engine = create_engine('oracle://scott:tiger@localhost/mydatabase')\n",
    "\n",
    "# DBAPI = cx_oracle\n",
    "engine = create_engine('oracle+cx_oracle://scott:tiger@localhost/mydatabase')\n",
    "```\n",
    "\n",
    "> **Note:** Before you run the code, you must edit the definition of the database-URL variable (`oracle_URL`) to use a valid Oracle database.\n",
    "> You can also optionally edit the SQL query (`oracle_query`) to use a custom read filter."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-oracle-sqlalchemy-create-engine\"></a>\n",
    "#### Create an Oracle SQLAlchemy Engine\n",
    "\n",
    "Create an SQLAlchemy engine using the Oracle dialect."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import cx_oracle\n",
    "\n",
    "# !! TODO !! Replace the <...> placeholders in the database URL\n",
    "oracle_URL = 'oracle://<username>:<password>@<host>:<port>/<database>'\n",
    "engine = create_engine(oracle_URL)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-oracle-sqlachemy-read-from-db\"></a>\n",
    "#### Read from the Oracle Database\n",
    "\n",
    "Create an SQL query object and use the pandas DataFrames `read_sql` method with this object and the SQLAlchemy engine object that you created in the previous step to read data from the Oracle database into a pandas DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create an SQL query object\n",
    "oracle_query = 'select * from table'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read from the external database\n",
    "df = pd.read_sql(oracle_query,engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Display the contents of the read DataFrame\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-oracle-sqlalchemy-write-to-platform\"></a>\n",
    "#### Write the Data to the Platform's NoSQL Store Using Frames\n",
    "\n",
    "Use Frames to write the pandas DataFrame that was read in the previous step to the platform's NoSQL store."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "oracle_table = os.path.join(os.getenv('V3IO_USERNAME')+'/examples/oracle_table')\n",
    "backend = 'kv'\n",
    "client.write(backend, oracle_table, df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-oracle-sqlalchemy-read-from-platform\"></a>\n",
    "#### Read from the Platform's NoSQL Store Using Frames\n",
    "\n",
    "Use Frames to read the platform NoSQL table that you created in the previous step."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "client.read(backend, oracle_table)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-mssql\"></a>\n",
    "## Reading from a Microsoft SQL Server Database\n",
    "\n",
    "This example uses a pandas `read_sql` query with an SQLAlchemy engine for the [Microsoft SQL Server dialect](https://docs.sqlalchemy.org/en/latest/core/engines.html#microsoft-sql-server) to load data from an external SQL server database into a pandas DataFrame; and then uses Frames to write the data to the platform's NoSQL store and read it back for verification.\n",
    "\n",
    "The Microsoft SQL Server dialect uses `pymssql` as the default DBAPI, so you can optionally omit the name of the DBAPI driver from the database URL that you pass in the `create_engine` call.\n",
    "For example:\n",
    "```python\n",
    "# Default DBAPI\n",
    "engine = create_engine('mssql://scott:tiger@localhost/mydatabase')\n",
    "\n",
    "# DBAPI = pymssql\n",
    "engine = create_engine('mssql+pymssql://scott:tiger@localhost/mydatabase')\n",
    "```\n",
    "\n",
    "> **Note:** Before you run the code, you must edit the definition of the database-URL variable (`mssql_URL`) to use a valid Microsoft SQL Server database.\n",
    "> You can also optionally edit the SQL query (`mssql_query`) to use a custom read filter."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-mssql-sqlalchemy-create-engine\"></a>\n",
    "#### Create a Microsoft SQL Server SQLAlchemy Engine\n",
    "\n",
    "Create an SQLAlchemy engine using the Microsoft SQL Server dialect."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymssql\n",
    "\n",
    "# !! TODO !! Replace the <...> placeholders in the database URL\n",
    "mssql_URL = 'mssql+pymssql://<username>:<password>@<host>:<port>/<database>'\n",
    "engine = create_engine(mssql_URL)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-mssql-sqlachemy-read-from-db\"></a>\n",
    "#### Read from the SQL Server Database\n",
    "\n",
    "Create an SQL query object and use the pandas DataFrames `read_sql` method with this object and the SQLAlchemy engine object that you created in the previous step to read data from the Oracle database into a pandas DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create an SQL query object\n",
    "mssql_query = 'select * from table'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read from the external database\n",
    "df = pd.read_sql(mssql_query,engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Display the contents of the read DataFrame\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-mssql-sqlalchemy-write-to-platform\"></a>\n",
    "#### Write the Data to the Platform's NoSQL Store Using Frames\n",
    "\n",
    "Use Frames to write the pandas DataFrame that was read in the previous step to the platform's NoSQL store."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mssql_table = os.path.join(os.getenv('V3IO_USERNAME')+'/examples/mssql_table')\n",
    "backend = 'kv'\n",
    "client.write(backend, mssql_table, df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-mssql-sqlalchemy-read-from-platform\"></a>\n",
    "#### Read from the Platform's NoSQL Store Using Frames\n",
    "\n",
    "Use Frames to read the platform NoSQL table that you created in the previous step."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "client.read(backend, mssql_table)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"readextdb-cleanup\"></a>\n",
    "## Cleanup\n",
    "\n",
    "Use the Frames `delete` client method to delete data that was ingested into the platform's NoSQL store as part of this tutorial."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "from v3io_frames import frames_pb2 as fpb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "client.delete(backend, mysql_table, if_missing=fpb.IGNORE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "client.delete(backend, mysql_table_1, if_missing=fpb.IGNORE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "client.delete(backend, mysql_table_2, if_missing=fpb.IGNORE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "client.delete(backend, postgresql_table, if_missing=fpb.IGNORE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "client.delete(backend, oracle_table, if_missing=fpb.IGNORE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "client.delete(backend, mssql_table, if_missing=fpb.IGNORE)"
   ]
  }
 ],
 "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}