getting-started/read-external-db.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reading from External Databases\n",
    "*Using SQL Alchemy Python library and Iguazio Frames library*\n",
    "\n",
    "1. [Overview](#Overview)\n",
    "2. [Set Up](#Set-Up)\n",
    "3. [Reading From MySQL](#Reading-From-MySQL)\n",
    "4. [Reading From PostgreSQL](#Reading-From-PostgreSQL)\n",
    "5. [Reading From Oracle](#Reading-From-Oracle)\n",
    "6. [Reading From SQL Server](#Reading-From-SQL-Server)\n",
    "7. [Cleanup](#Cleanup)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Overview"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this notebook,  let's walk through how to: <br>\n",
    "* Use SQL Alchemy Python library to Load Data from External Databases\n",
    "* Use Iguazio Frames library for High-Performance Data Access.\n",
    "\n",
    "**SQL Alchemy**\n",
    "\n",
    "SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL.  <br>\n",
    "\n",
    "For more details read [SQL Alchemy](https://www.sqlalchemy.org/) <br>\n",
    "\n",
    "**Iguazio Frames**\n",
    "\n",
    "Iguazio `v3io_frames` (Frames) is a streaming oriented multi-model (generic) data API that enables high-speed data loading and storing. <br>\n",
    "Frames currently support Iguazio Key/Value, Time-Series, and Streaming data models (called backends), additional backends will be added. <br>\n",
    "\n",
    "For more details refer to [Frames](frames.ipynb) <br>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Set UP\n",
    "Import all packages used in this exercise. <br>"
   ]
  },
  {
   "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": [
    "## Create an Iguazio Frames' client\n",
    "* Specify Data Container to _`users`_\n",
    "* Assign the Data Container Access Key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2 µs, sys: 1e+03 ns, total: 3 µs\n",
      "Wall time: 7.39 µs\n"
     ]
    }
   ],
   "source": [
    "%time\n",
    "client = v3f.Client('framesd:8081', container='users')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Initiate a SQL Alchemy engine\n",
    "The engine creates a `Dialect` object tailored towards the database instance (DB_INSTANCE), as well as a `Pool` object that will establish a `DBAPI` connection at database's host:port (DB_HOST:DB_PORT) when a connection request is first received. <br>\n",
    "\n",
    "**Database URL** : <br>\n",
    "The typical form of a database URL is:\n",
    "*dialect+driver://username:password@host:port/database* <br>\n",
    "e.g. <br>\n",
    "`mysql+pymysql://scott:tiger@localhost/foo` <br>\n",
    "\n",
    "For more details read [SQL Alchemy Database URL](https://docs.sqlalchemy.org/en/latest/core/engines.html#database-urls)\n",
    "\n",
    "**Engine Initialization template** : <br>\n",
    "*engine = create_engine('DATABASE_URL')* <br>\n",
    "e.g. <br>\n",
    "`engine = create_engine('mysql+pymysql://scott:tiger@localhost/foo')` <br>\n",
    "\n",
    "For more details read [SQL Alchemy Engine Configuration](https://docs.sqlalchemy.org/en/latest/core/engines.html#engine-configuration)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reading From MySQL\n",
    "In this MySQL experiment, the codes demonstrate how to load data from external databases\n",
    "in the following two methods:\n",
    "\n",
    "* A generic example of reading data in chunks using Python library SQLAlchemy\n",
    "* A specific example of reading a table from mysql as a bulk operation\n",
    "\n",
    "For more details read [MySQL dialet](https://docs.sqlalchemy.org/en/latest/core/engines.html#mysql) <br>\n",
    "\n",
    "In the example below we are using a public MySQL database called [Rfam](https://rfam.readthedocs.io/en/latest/database.html). <br>\n",
    "\n",
    "The idea is to read data by chunks and then write it to a NoSQL table in Iguazio Data Container.  Working in chunks is a divide and conquer algorithm for handling big datasets that cannot fit into the available memory resources. <br>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading Data in Chunks Using Python library SQLAlchemy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Initiate a SQL Alchemy Engine for MySQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2 µs, sys: 0 ns, total: 2 µs\n",
      "Wall time: 4.77 µs\n"
     ]
    }
   ],
   "source": [
    "%time\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": [
    "### Prepare a SQL Query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2 µs, sys: 1 µs, total: 3 µs\n",
      "Wall time: 5.96 µs\n"
     ]
    }
   ],
   "source": [
    "%time\n",
    "query = 'select rfam_acc,rfam_id,auto_wiki,description,author,seed_source FROM family'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create a Pandas DataFrame to Retreive Data with Specified Chunk size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2 µs, sys: 1e+03 ns, total: 3 µs\n",
      "Wall time: 7.15 µs\n"
     ]
    }
   ],
   "source": [
    "%time\n",
    "\n",
    "CHUNK_SIZE = 100000\n",
    "\n",
    "all_df = pd.read_sql(query,engine,chunksize = CHUNK_SIZE)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Use Iguazio Frames to Persist Each Data Chunks into an Iguazio KV store"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2 µs, sys: 1e+03 ns, total: 3 µs\n",
      "Wall time: 4.53 µs\n"
     ]
    }
   ],
   "source": [
    "%time\n",
    "\n",
    "tablename = 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, tablename, df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Use Iguazio Frames to Read from Iguazio KV Store"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 1 µs, sys: 0 ns, total: 1 µs\n",
      "Wall time: 5.01 µ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_id</th>\n",
       "      <th>seed_source</th>\n",
       "      <th>author</th>\n",
       "      <th>auto_wiki</th>\n",
       "      <th>description</th>\n",
       "      <th>index</th>\n",
       "      <th>rfam_acc</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>__name</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>1272</th>\n",
       "      <td>CRISPR-DR27</td>\n",
       "      <td>Predicted; WAR; Wilkinson A</td>\n",
       "      <td>Wilkinson A</td>\n",
       "      <td>1265.0</td>\n",
       "      <td>CRISPR RNA direct repeat element</td>\n",
       "      <td>1272.0</td>\n",
       "      <td>RF01339</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1982</th>\n",
       "      <td>STnc590</td>\n",
       "      <td>Predicted; CMfinder; Eberhardt R</td>\n",
       "      <td>Eberhardt R</td>\n",
       "      <td>1236.0</td>\n",
       "      <td>Salmonella enterica sRNA STnc590</td>\n",
       "      <td>1982.0</td>\n",
       "      <td>RF02072</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2599</th>\n",
       "      <td>Avalong_thermometer</td>\n",
       "      <td>Argasinska J</td>\n",
       "      <td>Argasinska J</td>\n",
       "      <td>2510.0</td>\n",
       "      <td>Avalong 5' UTR thermometer</td>\n",
       "      <td>2599.0</td>\n",
       "      <td>RF02698</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008</th>\n",
       "      <td>DGCR5</td>\n",
       "      <td>Predicted; CMfinder; Eberhardt R</td>\n",
       "      <td>Eberhardt R</td>\n",
       "      <td>2507.0</td>\n",
       "      <td>DiGeorge syndrome critical region gene 5 conse...</td>\n",
       "      <td>2008.0</td>\n",
       "      <td>RF02098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2165</th>\n",
       "      <td>adapt33_3</td>\n",
       "      <td>Predicted; CMfinder</td>\n",
       "      <td>Eberhardt R</td>\n",
       "      <td>2310.0</td>\n",
       "      <td>Adaptive response 33 conserved region 3</td>\n",
       "      <td>2165.0</td>\n",
       "      <td>RF02257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2496</th>\n",
       "      <td>EBv-sisRNA-1</td>\n",
       "      <td>Moss at al; PMID:23937650</td>\n",
       "      <td>Argasinska J</td>\n",
       "      <td>2419.0</td>\n",
       "      <td>Epstein-Barr virus stable intronic sequence RNA 1</td>\n",
       "      <td>2496.0</td>\n",
       "      <td>RF02595</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2513</th>\n",
       "      <td>BSR1350</td>\n",
       "      <td>Argasinska J</td>\n",
       "      <td>Argasinska J</td>\n",
       "      <td>2371.0</td>\n",
       "      <td>Brucella sRNA 1350</td>\n",
       "      <td>2513.0</td>\n",
       "      <td>RF02612</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>716</th>\n",
       "      <td>mir-12</td>\n",
       "      <td>miRBase; Wilkinson A</td>\n",
       "      <td>Wilkinson A</td>\n",
       "      <td>1287.0</td>\n",
       "      <td>microRNA mir-12</td>\n",
       "      <td>716.0</td>\n",
       "      <td>RF00751</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2089</th>\n",
       "      <td>ST7-AS1_2</td>\n",
       "      <td>Predicted; CMfinder; Eberhardt R</td>\n",
       "      <td>Eberhardt R</td>\n",
       "      <td>2490.0</td>\n",
       "      <td>ST7 antisense RNA 1 conserved region 2</td>\n",
       "      <td>2089.0</td>\n",
       "      <td>RF02180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2177</th>\n",
       "      <td>HPnc0580</td>\n",
       "      <td>Predicted; MAFFT</td>\n",
       "      <td>Burge SW</td>\n",
       "      <td>1236.0</td>\n",
       "      <td>Heliobacter pylori small RNA HPnc0580</td>\n",
       "      <td>2177.0</td>\n",
       "      <td>RF02269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2202</th>\n",
       "      <td>TtnuCD18</td>\n",
       "      <td>Eberhardt R</td>\n",
       "      <td>Eberhardt R</td>\n",
       "      <td>1264.0</td>\n",
       "      <td>Tetrahymena snoRNA TtnuCD18</td>\n",
       "      <td>2202.0</td>\n",
       "      <td>RF02294</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2356</th>\n",
       "      <td>Virus_CITE_1</td>\n",
       "      <td>Eberhardt R</td>\n",
       "      <td>Eberhardt R</td>\n",
       "      <td>2342.0</td>\n",
       "      <td>Dianthovirus RNA2 cap-independent translation ...</td>\n",
       "      <td>2356.0</td>\n",
       "      <td>RF02455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2678</th>\n",
       "      <td>OppA_thermometer</td>\n",
       "      <td>Argasinska J</td>\n",
       "      <td>Argasinska J</td>\n",
       "      <td>2437.0</td>\n",
       "      <td>OppA 5' UTR thermometer</td>\n",
       "      <td>2678.0</td>\n",
       "      <td>RF02777</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1865</th>\n",
       "      <td>KCNQ1OT1_3</td>\n",
       "      <td>Predicted; ClustalW; Eberhardt R</td>\n",
       "      <td>Eberhardt R</td>\n",
       "      <td>1277.0</td>\n",
       "      <td>KCNQ1 overlapping transcript 1 conserved region 3</td>\n",
       "      <td>1865.0</td>\n",
       "      <td>RF01948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2358</th>\n",
       "      <td>Virus_CITE_2</td>\n",
       "      <td>Eberhardt R</td>\n",
       "      <td>Eberhardt R</td>\n",
       "      <td>2342.0</td>\n",
       "      <td>Tombusvirus 3' cap-independent translation ele...</td>\n",
       "      <td>2358.0</td>\n",
       "      <td>RF02457</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>577</th>\n",
       "      <td>SNORD100</td>\n",
       "      <td>INFERNAL</td>\n",
       "      <td>Daub J</td>\n",
       "      <td>1843.0</td>\n",
       "      <td>Small nucleolar RNA SNORD100</td>\n",
       "      <td>577.0</td>\n",
       "      <td>RF00609</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2092</th>\n",
       "      <td>ST7-OT3_1</td>\n",
       "      <td>Predicted; CMfinder; Eberhardt R</td>\n",
       "      <td>Eberhardt R</td>\n",
       "      <td>2475.0</td>\n",
       "      <td>ST7 overlapping transcript 3 conserved region 1</td>\n",
       "      <td>2092.0</td>\n",
       "      <td>RF02183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2903</th>\n",
       "      <td>GP20-b</td>\n",
       "      <td>Weinberg Z</td>\n",
       "      <td>Weinberg Z</td>\n",
       "      <td>2628.0</td>\n",
       "      <td>GP20-b RNA</td>\n",
       "      <td>2903.0</td>\n",
       "      <td>RF03003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2944</th>\n",
       "      <td>Proteo-phage-1</td>\n",
       "      <td>Weinberg Z</td>\n",
       "      <td>Weinberg Z</td>\n",
       "      <td>2666.0</td>\n",
       "      <td>Proteo-phage-1 RNA</td>\n",
       "      <td>2944.0</td>\n",
       "      <td>RF03044</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>250</th>\n",
       "      <td>IFN_gamma</td>\n",
       "      <td>PMID:11832212</td>\n",
       "      <td>Moxon SJ</td>\n",
       "      <td>1535.0</td>\n",
       "      <td>Interferon gamma 5' UTR regulatory element</td>\n",
       "      <td>250.0</td>\n",
       "      <td>RF00259</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>760</th>\n",
       "      <td>mir-48</td>\n",
       "      <td>miRBase; Wilkinson A; WormBase</td>\n",
       "      <td>Burge S, Wilkinson A</td>\n",
       "      <td>1959.0</td>\n",
       "      <td>microRNA mir-48</td>\n",
       "      <td>760.0</td>\n",
       "      <td>RF00796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>828</th>\n",
       "      <td>MIR169_5</td>\n",
       "      <td>miRBase; Wilkinson A</td>\n",
       "      <td>Wilkinson A</td>\n",
       "      <td>1287.0</td>\n",
       "      <td>microRNA MIR169_5</td>\n",
       "      <td>828.0</td>\n",
       "      <td>RF00865</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2471</th>\n",
       "      <td>BM-sr0117</td>\n",
       "      <td>Argasinska J</td>\n",
       "      <td>Argasinska J</td>\n",
       "      <td>2371.0</td>\n",
       "      <td>Brucella melitensis small RNA 0117</td>\n",
       "      <td>2471.0</td>\n",
       "      <td>RF02570</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1002</th>\n",
       "      <td>preQ1-II</td>\n",
       "      <td>Published; PMID:18305186; Weinberg Z, Meyer MM...</td>\n",
       "      <td>Weinberg Z, Gardner PP</td>\n",
       "      <td>2039.0</td>\n",
       "      <td>preQ1-II (pre queuosine) riboswitch</td>\n",
       "      <td>1002.0</td>\n",
       "      <td>RF01054</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>743</th>\n",
       "      <td>MIR474</td>\n",
       "      <td>miRBase; Wilkinson A</td>\n",
       "      <td>Wilkinson A</td>\n",
       "      <td>2406.0</td>\n",
       "      <td>microRNA MIR474</td>\n",
       "      <td>743.0</td>\n",
       "      <td>RF00779</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2802</th>\n",
       "      <td>AaHKsRNA22</td>\n",
       "      <td>Argasinska J</td>\n",
       "      <td>Argasinska J</td>\n",
       "      <td>2559.0</td>\n",
       "      <td>Aggregatibacter sRNA 22</td>\n",
       "      <td>2802.0</td>\n",
       "      <td>RF02902</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1659</th>\n",
       "      <td>Termite-flg</td>\n",
       "      <td>Published; PMID:20230605;</td>\n",
       "      <td>Weinberg Z</td>\n",
       "      <td>2131.0</td>\n",
       "      <td>Termite-flg RNA</td>\n",
       "      <td>1659.0</td>\n",
       "      <td>RF01729</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112</th>\n",
       "      <td>S15</td>\n",
       "      <td>Ji Y</td>\n",
       "      <td>Bateman A</td>\n",
       "      <td>1406.0</td>\n",
       "      <td>Ribosomal S15 leader</td>\n",
       "      <td>112.0</td>\n",
       "      <td>RF00114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>410</th>\n",
       "      <td>SNORA38</td>\n",
       "      <td>Moxon SJ, INFERNAL</td>\n",
       "      <td>Moxon SJ</td>\n",
       "      <td>1687.0</td>\n",
       "      <td>Small nucleolar RNA SNORA38</td>\n",
       "      <td>410.0</td>\n",
       "      <td>RF00428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>673</th>\n",
       "      <td>mir-197</td>\n",
       "      <td>miRBase; Wilkinson A</td>\n",
       "      <td>Wilkinson A</td>\n",
       "      <td>1287.0</td>\n",
       "      <td>microRNA mir-197</td>\n",
       "      <td>673.0</td>\n",
       "      <td>RF00707</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>644</th>\n",
       "      <td>mir-140</td>\n",
       "      <td>miRBase; Wilkinson A</td>\n",
       "      <td>Wilkinson A</td>\n",
       "      <td>1287.0</td>\n",
       "      <td>microRNA mir-140</td>\n",
       "      <td>644.0</td>\n",
       "      <td>RF00678</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>891</th>\n",
       "      <td>mir-653</td>\n",
       "      <td>miRBase; Wilkinson A</td>\n",
       "      <td>Wilkinson A</td>\n",
       "      <td>1287.0</td>\n",
       "      <td>microRNA mir-653</td>\n",
       "      <td>891.0</td>\n",
       "      <td>RF00937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1782</th>\n",
       "      <td>RsaF</td>\n",
       "      <td>Published; PMID:19786493</td>\n",
       "      <td>Moore B</td>\n",
       "      <td>2199.0</td>\n",
       "      <td>RNA Staph. aureus F</td>\n",
       "      <td>1782.0</td>\n",
       "      <td>RF01858</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1906</th>\n",
       "      <td>mir-995</td>\n",
       "      <td>Predicted; ClustalW2</td>\n",
       "      <td>Boursnell C</td>\n",
       "      <td>1287.0</td>\n",
       "      <td>microRNA mir-995</td>\n",
       "      <td>1906.0</td>\n",
       "      <td>RF01996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2779</th>\n",
       "      <td>MH_s10</td>\n",
       "      <td>Argasinska J</td>\n",
       "      <td>Argasinska J</td>\n",
       "      <td>2551.0</td>\n",
       "      <td>Mesorhizobail RNA 10</td>\n",
       "      <td>2779.0</td>\n",
       "      <td>RF02879</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>311</th>\n",
       "      <td>SNORD53_SNORD92</td>\n",
       "      <td>Moxon SJ, INFERNAL</td>\n",
       "      <td>Moxon SJ, Daub J</td>\n",
       "      <td>1591.0</td>\n",
       "      <td>Small nucleolar RNA SNORD53/SNORD92</td>\n",
       "      <td>311.0</td>\n",
       "      <td>RF00325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>468</th>\n",
       "      <td>U1_yeast</td>\n",
       "      <td>Zwieb C, The uRNA database, PMID:9016512</td>\n",
       "      <td>Griffiths-Jones SR</td>\n",
       "      <td>1742.0</td>\n",
       "      <td>Yeast U1 spliceosomal RNA</td>\n",
       "      <td>468.0</td>\n",
       "      <td>RF00488</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>356</th>\n",
       "      <td>RNaseP_arch</td>\n",
       "      <td>Brown JW, The Ribonuclease P Database, PMID:98...</td>\n",
       "      <td>Griffiths-Jones SR</td>\n",
       "      <td>2441.0</td>\n",
       "      <td>Archaeal RNase P</td>\n",
       "      <td>356.0</td>\n",
       "      <td>RF00373</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1437</th>\n",
       "      <td>Afu_264</td>\n",
       "      <td>INFERNAL</td>\n",
       "      <td>Osuch I</td>\n",
       "      <td>1264.0</td>\n",
       "      <td>A. fumigatus snoRNA Afu_264</td>\n",
       "      <td>1437.0</td>\n",
       "      <td>RF01505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1590</th>\n",
       "      <td>ceN88</td>\n",
       "      <td>INFERNAL</td>\n",
       "      <td>Osuch I</td>\n",
       "      <td>1264.0</td>\n",
       "      <td>Nematode snoRNA ceN88</td>\n",
       "      <td>1590.0</td>\n",
       "      <td>RF01660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2232</th>\n",
       "      <td>TtnuHACA17</td>\n",
       "      <td>Eberhardt R</td>\n",
       "      <td>Eberhardt R</td>\n",
       "      <td>1264.0</td>\n",
       "      <td>Tetrahymena snoRNA TtnuHACA17</td>\n",
       "      <td>2232.0</td>\n",
       "      <td>RF02324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2885</th>\n",
       "      <td>ftsZ-DE</td>\n",
       "      <td>Weinberg Z</td>\n",
       "      <td>Weinberg Z</td>\n",
       "      <td>2622.0</td>\n",
       "      <td>ftsZ-DE RNA</td>\n",
       "      <td>2885.0</td>\n",
       "      <td>RF02985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>SNORD26</td>\n",
       "      <td>Griffiths-Jones SR, INFERNAL</td>\n",
       "      <td>Griffiths-Jones SR</td>\n",
       "      <td>1381.0</td>\n",
       "      <td>Small nucleolar RNA SNORD26</td>\n",
       "      <td>86.0</td>\n",
       "      <td>RF00087</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>770</th>\n",
       "      <td>mir-350</td>\n",
       "      <td>miRBase; Wilkinson A</td>\n",
       "      <td>Wilkinson A</td>\n",
       "      <td>1962.0</td>\n",
       "      <td>microRNA mir-350</td>\n",
       "      <td>770.0</td>\n",
       "      <td>RF00806</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1293</th>\n",
       "      <td>CRISPR-DR47</td>\n",
       "      <td>Predicted; WAR; Wilkinson A</td>\n",
       "      <td>Wilkinson A</td>\n",
       "      <td>1265.0</td>\n",
       "      <td>CRISPR RNA direct repeat element</td>\n",
       "      <td>1293.0</td>\n",
       "      <td>RF01360</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1311</th>\n",
       "      <td>CRISPR-DR66</td>\n",
       "      <td>Predicted; WAR; Wilkinson A</td>\n",
       "      <td>Wilkinson A</td>\n",
       "      <td>1265.0</td>\n",
       "      <td>CRISPR RNA direct repeat element</td>\n",
       "      <td>1311.0</td>\n",
       "      <td>RF01379</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1861</th>\n",
       "      <td>mir-2518</td>\n",
       "      <td>Predicted; ClustalW2</td>\n",
       "      <td>Boursnell C</td>\n",
       "      <td>1287.0</td>\n",
       "      <td>microRNA mir-2518</td>\n",
       "      <td>1861.0</td>\n",
       "      <td>RF01944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>mir-192</td>\n",
       "      <td>Griffiths-Jones SR</td>\n",
       "      <td>Moxon SJ</td>\n",
       "      <td>2375.0</td>\n",
       "      <td>mir-192/215 microRNA precursor</td>\n",
       "      <td>126.0</td>\n",
       "      <td>RF00130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>693</th>\n",
       "      <td>bantam</td>\n",
       "      <td>miRBase; Wilkinson A</td>\n",
       "      <td>Wilkinson A</td>\n",
       "      <td>1922.0</td>\n",
       "      <td>microRNA bantam</td>\n",
       "      <td>693.0</td>\n",
       "      <td>RF00727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1251</th>\n",
       "      <td>CRISPR-DR5</td>\n",
       "      <td>Predicted; WAR; Wilkinson A</td>\n",
       "      <td>Wilkinson A</td>\n",
       "      <td>1265.0</td>\n",
       "      <td>CRISPR RNA direct repeat element</td>\n",
       "      <td>1251.0</td>\n",
       "      <td>RF01318</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1573</th>\n",
       "      <td>ceN54</td>\n",
       "      <td>INFERNAL</td>\n",
       "      <td>Osuch I</td>\n",
       "      <td>1264.0</td>\n",
       "      <td>Caenorhabditis snoRNA ceN54</td>\n",
       "      <td>1573.0</td>\n",
       "      <td>RF01642</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>455</th>\n",
       "      <td>snosnR69</td>\n",
       "      <td>Moxon SJ</td>\n",
       "      <td>Moxon SJ</td>\n",
       "      <td>1729.0</td>\n",
       "      <td>Small nucleolar RNA snR69</td>\n",
       "      <td>455.0</td>\n",
       "      <td>RF00475</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>773</th>\n",
       "      <td>mir-241</td>\n",
       "      <td>miRBase; Wilkinson A</td>\n",
       "      <td>Wilkinson A</td>\n",
       "      <td>1963.0</td>\n",
       "      <td>microRNA mir-241</td>\n",
       "      <td>773.0</td>\n",
       "      <td>RF00809</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2674</th>\n",
       "      <td>TrxA_thermometer</td>\n",
       "      <td>Argasinska J</td>\n",
       "      <td>Argasinska J</td>\n",
       "      <td>2437.0</td>\n",
       "      <td>TrxA 5' UTR thermometer</td>\n",
       "      <td>2674.0</td>\n",
       "      <td>RF02773</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1001</th>\n",
       "      <td>Deinococcus_Y_RNA</td>\n",
       "      <td>Gardner PP</td>\n",
       "      <td>Gardner PP</td>\n",
       "      <td>1317.0</td>\n",
       "      <td>Deinococcus radiodurans Y RNA</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>RF01053</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2498</th>\n",
       "      <td>shuA_thermo</td>\n",
       "      <td>Kouse et al, PMID:23704938</td>\n",
       "      <td>Argasinska J</td>\n",
       "      <td>2420.0</td>\n",
       "      <td>shuA/chuA 5' UTR thermoregulator</td>\n",
       "      <td>2498.0</td>\n",
       "      <td>RF02597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2915</th>\n",
       "      <td>Transposase-2</td>\n",
       "      <td>Weinberg Z</td>\n",
       "      <td>Weinberg Z</td>\n",
       "      <td>2649.0</td>\n",
       "      <td>Transposase-2 RNA</td>\n",
       "      <td>2915.0</td>\n",
       "      <td>RF03015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1768</th>\n",
       "      <td>SmY</td>\n",
       "      <td>Published; PMID:19106623</td>\n",
       "      <td>Jones T, Moore B, Daub J, Marz M</td>\n",
       "      <td>2215.0</td>\n",
       "      <td>SmY spliceosomal RNA</td>\n",
       "      <td>1768.0</td>\n",
       "      <td>RF01844</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2791</th>\n",
       "      <td>AgrB</td>\n",
       "      <td>Argasinska J</td>\n",
       "      <td>Argasinska J</td>\n",
       "      <td>2254.0</td>\n",
       "      <td>ArsR-gov region gene B</td>\n",
       "      <td>2791.0</td>\n",
       "      <td>RF02891</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3012</th>\n",
       "      <td>Staphylococcus-1</td>\n",
       "      <td>Weinberg Z</td>\n",
       "      <td>Weinberg Z</td>\n",
       "      <td>2701.0</td>\n",
       "      <td>Staphylococcus-1 RNA</td>\n",
       "      <td>3012.0</td>\n",
       "      <td>RF03112</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3016 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    rfam_id  \\\n",
       "__name                        \n",
       "1272            CRISPR-DR27   \n",
       "1982                STnc590   \n",
       "2599    Avalong_thermometer   \n",
       "2008                  DGCR5   \n",
       "2165              adapt33_3   \n",
       "2496           EBv-sisRNA-1   \n",
       "2513                BSR1350   \n",
       "716                  mir-12   \n",
       "2089              ST7-AS1_2   \n",
       "2177               HPnc0580   \n",
       "2202               TtnuCD18   \n",
       "2356           Virus_CITE_1   \n",
       "2678       OppA_thermometer   \n",
       "1865             KCNQ1OT1_3   \n",
       "2358           Virus_CITE_2   \n",
       "577                SNORD100   \n",
       "2092              ST7-OT3_1   \n",
       "2903                 GP20-b   \n",
       "2944         Proteo-phage-1   \n",
       "250               IFN_gamma   \n",
       "760                  mir-48   \n",
       "828                MIR169_5   \n",
       "2471              BM-sr0117   \n",
       "1002               preQ1-II   \n",
       "743                  MIR474   \n",
       "2802             AaHKsRNA22   \n",
       "1659            Termite-flg   \n",
       "112                     S15   \n",
       "410                 SNORA38   \n",
       "673                 mir-197   \n",
       "...                     ...   \n",
       "644                 mir-140   \n",
       "891                 mir-653   \n",
       "1782                   RsaF   \n",
       "1906                mir-995   \n",
       "2779                 MH_s10   \n",
       "311         SNORD53_SNORD92   \n",
       "468                U1_yeast   \n",
       "356             RNaseP_arch   \n",
       "1437                Afu_264   \n",
       "1590                  ceN88   \n",
       "2232             TtnuHACA17   \n",
       "2885                ftsZ-DE   \n",
       "86                  SNORD26   \n",
       "770                 mir-350   \n",
       "1293            CRISPR-DR47   \n",
       "1311            CRISPR-DR66   \n",
       "1861               mir-2518   \n",
       "126                 mir-192   \n",
       "693                  bantam   \n",
       "1251             CRISPR-DR5   \n",
       "1573                  ceN54   \n",
       "455                snosnR69   \n",
       "773                 mir-241   \n",
       "2674       TrxA_thermometer   \n",
       "1001      Deinococcus_Y_RNA   \n",
       "2498            shuA_thermo   \n",
       "2915          Transposase-2   \n",
       "1768                    SmY   \n",
       "2791                   AgrB   \n",
       "3012       Staphylococcus-1   \n",
       "\n",
       "                                              seed_source  \\\n",
       "__name                                                      \n",
       "1272                          Predicted; WAR; Wilkinson A   \n",
       "1982                     Predicted; CMfinder; Eberhardt R   \n",
       "2599                                         Argasinska J   \n",
       "2008                     Predicted; CMfinder; Eberhardt R   \n",
       "2165                                  Predicted; CMfinder   \n",
       "2496                            Moss at al; PMID:23937650   \n",
       "2513                                         Argasinska J   \n",
       "716                                  miRBase; Wilkinson A   \n",
       "2089                     Predicted; CMfinder; Eberhardt R   \n",
       "2177                                     Predicted; MAFFT   \n",
       "2202                                          Eberhardt R   \n",
       "2356                                          Eberhardt R   \n",
       "2678                                         Argasinska J   \n",
       "1865                     Predicted; ClustalW; Eberhardt R   \n",
       "2358                                          Eberhardt R   \n",
       "577                                              INFERNAL   \n",
       "2092                     Predicted; CMfinder; Eberhardt R   \n",
       "2903                                           Weinberg Z   \n",
       "2944                                           Weinberg Z   \n",
       "250                                         PMID:11832212   \n",
       "760                        miRBase; Wilkinson A; WormBase   \n",
       "828                                  miRBase; Wilkinson A   \n",
       "2471                                         Argasinska J   \n",
       "1002    Published; PMID:18305186; Weinberg Z, Meyer MM...   \n",
       "743                                  miRBase; Wilkinson A   \n",
       "2802                                         Argasinska J   \n",
       "1659                            Published; PMID:20230605;   \n",
       "112                                                  Ji Y   \n",
       "410                                    Moxon SJ, INFERNAL   \n",
       "673                                  miRBase; Wilkinson A   \n",
       "...                                                   ...   \n",
       "644                                  miRBase; Wilkinson A   \n",
       "891                                  miRBase; Wilkinson A   \n",
       "1782                             Published; PMID:19786493   \n",
       "1906                                 Predicted; ClustalW2   \n",
       "2779                                         Argasinska J   \n",
       "311                                    Moxon SJ, INFERNAL   \n",
       "468              Zwieb C, The uRNA database, PMID:9016512   \n",
       "356     Brown JW, The Ribonuclease P Database, PMID:98...   \n",
       "1437                                             INFERNAL   \n",
       "1590                                             INFERNAL   \n",
       "2232                                          Eberhardt R   \n",
       "2885                                           Weinberg Z   \n",
       "86                           Griffiths-Jones SR, INFERNAL   \n",
       "770                                  miRBase; Wilkinson A   \n",
       "1293                          Predicted; WAR; Wilkinson A   \n",
       "1311                          Predicted; WAR; Wilkinson A   \n",
       "1861                                 Predicted; ClustalW2   \n",
       "126                                    Griffiths-Jones SR   \n",
       "693                                  miRBase; Wilkinson A   \n",
       "1251                          Predicted; WAR; Wilkinson A   \n",
       "1573                                             INFERNAL   \n",
       "455                                              Moxon SJ   \n",
       "773                                  miRBase; Wilkinson A   \n",
       "2674                                         Argasinska J   \n",
       "1001                                           Gardner PP   \n",
       "2498                           Kouse et al, PMID:23704938   \n",
       "2915                                           Weinberg Z   \n",
       "1768                             Published; PMID:19106623   \n",
       "2791                                         Argasinska J   \n",
       "3012                                           Weinberg Z   \n",
       "\n",
       "                                  author  auto_wiki  \\\n",
       "__name                                                \n",
       "1272                         Wilkinson A     1265.0   \n",
       "1982                         Eberhardt R     1236.0   \n",
       "2599                        Argasinska J     2510.0   \n",
       "2008                         Eberhardt R     2507.0   \n",
       "2165                         Eberhardt R     2310.0   \n",
       "2496                        Argasinska J     2419.0   \n",
       "2513                        Argasinska J     2371.0   \n",
       "716                          Wilkinson A     1287.0   \n",
       "2089                         Eberhardt R     2490.0   \n",
       "2177                            Burge SW     1236.0   \n",
       "2202                         Eberhardt R     1264.0   \n",
       "2356                         Eberhardt R     2342.0   \n",
       "2678                        Argasinska J     2437.0   \n",
       "1865                         Eberhardt R     1277.0   \n",
       "2358                         Eberhardt R     2342.0   \n",
       "577                               Daub J     1843.0   \n",
       "2092                         Eberhardt R     2475.0   \n",
       "2903                          Weinberg Z     2628.0   \n",
       "2944                          Weinberg Z     2666.0   \n",
       "250                             Moxon SJ     1535.0   \n",
       "760                 Burge S, Wilkinson A     1959.0   \n",
       "828                          Wilkinson A     1287.0   \n",
       "2471                        Argasinska J     2371.0   \n",
       "1002              Weinberg Z, Gardner PP     2039.0   \n",
       "743                          Wilkinson A     2406.0   \n",
       "2802                        Argasinska J     2559.0   \n",
       "1659                          Weinberg Z     2131.0   \n",
       "112                            Bateman A     1406.0   \n",
       "410                             Moxon SJ     1687.0   \n",
       "673                          Wilkinson A     1287.0   \n",
       "...                                  ...        ...   \n",
       "644                          Wilkinson A     1287.0   \n",
       "891                          Wilkinson A     1287.0   \n",
       "1782                             Moore B     2199.0   \n",
       "1906                         Boursnell C     1287.0   \n",
       "2779                        Argasinska J     2551.0   \n",
       "311                     Moxon SJ, Daub J     1591.0   \n",
       "468                   Griffiths-Jones SR     1742.0   \n",
       "356                   Griffiths-Jones SR     2441.0   \n",
       "1437                             Osuch I     1264.0   \n",
       "1590                             Osuch I     1264.0   \n",
       "2232                         Eberhardt R     1264.0   \n",
       "2885                          Weinberg Z     2622.0   \n",
       "86                    Griffiths-Jones SR     1381.0   \n",
       "770                          Wilkinson A     1962.0   \n",
       "1293                         Wilkinson A     1265.0   \n",
       "1311                         Wilkinson A     1265.0   \n",
       "1861                         Boursnell C     1287.0   \n",
       "126                             Moxon SJ     2375.0   \n",
       "693                          Wilkinson A     1922.0   \n",
       "1251                         Wilkinson A     1265.0   \n",
       "1573                             Osuch I     1264.0   \n",
       "455                             Moxon SJ     1729.0   \n",
       "773                          Wilkinson A     1963.0   \n",
       "2674                        Argasinska J     2437.0   \n",
       "1001                          Gardner PP     1317.0   \n",
       "2498                        Argasinska J     2420.0   \n",
       "2915                          Weinberg Z     2649.0   \n",
       "1768    Jones T, Moore B, Daub J, Marz M     2215.0   \n",
       "2791                        Argasinska J     2254.0   \n",
       "3012                          Weinberg Z     2701.0   \n",
       "\n",
       "                                              description   index rfam_acc  \n",
       "__name                                                                      \n",
       "1272                     CRISPR RNA direct repeat element  1272.0  RF01339  \n",
       "1982                     Salmonella enterica sRNA STnc590  1982.0  RF02072  \n",
       "2599                           Avalong 5' UTR thermometer  2599.0  RF02698  \n",
       "2008    DiGeorge syndrome critical region gene 5 conse...  2008.0  RF02098  \n",
       "2165              Adaptive response 33 conserved region 3  2165.0  RF02257  \n",
       "2496    Epstein-Barr virus stable intronic sequence RNA 1  2496.0  RF02595  \n",
       "2513                                   Brucella sRNA 1350  2513.0  RF02612  \n",
       "716                                       microRNA mir-12   716.0  RF00751  \n",
       "2089               ST7 antisense RNA 1 conserved region 2  2089.0  RF02180  \n",
       "2177                Heliobacter pylori small RNA HPnc0580  2177.0  RF02269  \n",
       "2202                          Tetrahymena snoRNA TtnuCD18  2202.0  RF02294  \n",
       "2356    Dianthovirus RNA2 cap-independent translation ...  2356.0  RF02455  \n",
       "2678                              OppA 5' UTR thermometer  2678.0  RF02777  \n",
       "1865    KCNQ1 overlapping transcript 1 conserved region 3  1865.0  RF01948  \n",
       "2358    Tombusvirus 3' cap-independent translation ele...  2358.0  RF02457  \n",
       "577                          Small nucleolar RNA SNORD100   577.0  RF00609  \n",
       "2092      ST7 overlapping transcript 3 conserved region 1  2092.0  RF02183  \n",
       "2903                                           GP20-b RNA  2903.0  RF03003  \n",
       "2944                                   Proteo-phage-1 RNA  2944.0  RF03044  \n",
       "250            Interferon gamma 5' UTR regulatory element   250.0  RF00259  \n",
       "760                                       microRNA mir-48   760.0  RF00796  \n",
       "828                                     microRNA MIR169_5   828.0  RF00865  \n",
       "2471                   Brucella melitensis small RNA 0117  2471.0  RF02570  \n",
       "1002                  preQ1-II (pre queuosine) riboswitch  1002.0  RF01054  \n",
       "743                                       microRNA MIR474   743.0  RF00779  \n",
       "2802                              Aggregatibacter sRNA 22  2802.0  RF02902  \n",
       "1659                                      Termite-flg RNA  1659.0  RF01729  \n",
       "112                                  Ribosomal S15 leader   112.0  RF00114  \n",
       "410                           Small nucleolar RNA SNORA38   410.0  RF00428  \n",
       "673                                      microRNA mir-197   673.0  RF00707  \n",
       "...                                                   ...     ...      ...  \n",
       "644                                      microRNA mir-140   644.0  RF00678  \n",
       "891                                      microRNA mir-653   891.0  RF00937  \n",
       "1782                                  RNA Staph. aureus F  1782.0  RF01858  \n",
       "1906                                     microRNA mir-995  1906.0  RF01996  \n",
       "2779                                 Mesorhizobail RNA 10  2779.0  RF02879  \n",
       "311                   Small nucleolar RNA SNORD53/SNORD92   311.0  RF00325  \n",
       "468                             Yeast U1 spliceosomal RNA   468.0  RF00488  \n",
       "356                                      Archaeal RNase P   356.0  RF00373  \n",
       "1437                          A. fumigatus snoRNA Afu_264  1437.0  RF01505  \n",
       "1590                                Nematode snoRNA ceN88  1590.0  RF01660  \n",
       "2232                        Tetrahymena snoRNA TtnuHACA17  2232.0  RF02324  \n",
       "2885                                          ftsZ-DE RNA  2885.0  RF02985  \n",
       "86                            Small nucleolar RNA SNORD26    86.0  RF00087  \n",
       "770                                      microRNA mir-350   770.0  RF00806  \n",
       "1293                     CRISPR RNA direct repeat element  1293.0  RF01360  \n",
       "1311                     CRISPR RNA direct repeat element  1311.0  RF01379  \n",
       "1861                                    microRNA mir-2518  1861.0  RF01944  \n",
       "126                        mir-192/215 microRNA precursor   126.0  RF00130  \n",
       "693                                       microRNA bantam   693.0  RF00727  \n",
       "1251                     CRISPR RNA direct repeat element  1251.0  RF01318  \n",
       "1573                          Caenorhabditis snoRNA ceN54  1573.0  RF01642  \n",
       "455                             Small nucleolar RNA snR69   455.0  RF00475  \n",
       "773                                      microRNA mir-241   773.0  RF00809  \n",
       "2674                              TrxA 5' UTR thermometer  2674.0  RF02773  \n",
       "1001                        Deinococcus radiodurans Y RNA  1001.0  RF01053  \n",
       "2498                     shuA/chuA 5' UTR thermoregulator  2498.0  RF02597  \n",
       "2915                                    Transposase-2 RNA  2915.0  RF03015  \n",
       "1768                                 SmY spliceosomal RNA  1768.0  RF01844  \n",
       "2791                               ArsR-gov region gene B  2791.0  RF02891  \n",
       "3012                                 Staphylococcus-1 RNA  3012.0  RF03112  \n",
       "\n",
       "[3016 rows x 7 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%time\n",
    "\n",
    "client.read(backend, tablename)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading a Table from MySQL as a Bulk Operation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Install pymysql\n",
    "pymysql is Python MySQL client library"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create a Database Connection to MySQL\n",
    "\n",
    "Reading from MySQL as a bulk operation using pandas DataFrames.\n",
    "\n",
    "**NOTE** If this notebook runs in AWS Cloud: AWS S3 provides **eventual consistency**. Therefore, it takes time for users using the persisted data and software package."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2 µs, sys: 1 µs, total: 3 µs\n",
      "Wall time: 7.15 µs\n"
     ]
    }
   ],
   "source": [
    "import pymysql\n",
    "\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": [
    "### Read Data into a Pandas DataFrame\n",
    "\n",
    "Pandas DataFrame natively supports for reading and writing to various SQL databases. <br>\n",
    "\n",
    "Use [pd.read_sql_query()](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_sql_query.html) or [pd.read_sql()](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_sql.html) to read the database table into a DataFrame.   Once we have a DataFrame object we can manipulate it and store it into Iguazio KV store or Time-Series tables. <br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2 µs, sys: 1e+03 ns, total: 3 µs\n",
      "Wall time: 5.25 µs\n"
     ]
    }
   ],
   "source": [
    "%time\n",
    "\n",
    "df = pd.read_sql_query(query, conn) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Print Last 10 line in Pandas DF"
   ]
  },
  {
   "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"
     ]
    },
    {
     "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>3006</th>\n",
       "      <td>RF03106</td>\n",
       "      <td>RT-11</td>\n",
       "      <td>2572</td>\n",
       "      <td>RT-11 RNA</td>\n",
       "      <td>Weinberg Z</td>\n",
       "      <td>Weinberg Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3007</th>\n",
       "      <td>RF03107</td>\n",
       "      <td>saliva-tongue-1</td>\n",
       "      <td>2696</td>\n",
       "      <td>saliva-tongue-1 RNA</td>\n",
       "      <td>Weinberg Z</td>\n",
       "      <td>Weinberg Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3008</th>\n",
       "      <td>RF03108</td>\n",
       "      <td>Methylosinus-1</td>\n",
       "      <td>2697</td>\n",
       "      <td>Methylosinus-1 RNA</td>\n",
       "      <td>Weinberg Z</td>\n",
       "      <td>Weinberg Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3009</th>\n",
       "      <td>RF03109</td>\n",
       "      <td>Thermales-rpoB</td>\n",
       "      <td>2698</td>\n",
       "      <td>Thermales-rpoB RNA</td>\n",
       "      <td>Weinberg Z</td>\n",
       "      <td>Weinberg Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3010</th>\n",
       "      <td>RF03110</td>\n",
       "      <td>throat-1</td>\n",
       "      <td>2699</td>\n",
       "      <td>throat-1 RNA</td>\n",
       "      <td>Weinberg Z</td>\n",
       "      <td>Weinberg Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3011</th>\n",
       "      <td>RF03111</td>\n",
       "      <td>Zeta-pan</td>\n",
       "      <td>2700</td>\n",
       "      <td>Zeta-pan RNA</td>\n",
       "      <td>Weinberg Z</td>\n",
       "      <td>Weinberg Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3012</th>\n",
       "      <td>RF03112</td>\n",
       "      <td>Staphylococcus-1</td>\n",
       "      <td>2701</td>\n",
       "      <td>Staphylococcus-1 RNA</td>\n",
       "      <td>Weinberg Z</td>\n",
       "      <td>Weinberg Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3013</th>\n",
       "      <td>RF03113</td>\n",
       "      <td>Poribacteria-1</td>\n",
       "      <td>2702</td>\n",
       "      <td>Poribacteria-1 RNA</td>\n",
       "      <td>Weinberg Z</td>\n",
       "      <td>Weinberg Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3014</th>\n",
       "      <td>RF03114</td>\n",
       "      <td>RT-1</td>\n",
       "      <td>2572</td>\n",
       "      <td>RT-1 RNA</td>\n",
       "      <td>Weinberg Z</td>\n",
       "      <td>Weinberg Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3015</th>\n",
       "      <td>RF03115</td>\n",
       "      <td>KDPG-aldolase</td>\n",
       "      <td>2703</td>\n",
       "      <td>KDPG-aldolase RNA</td>\n",
       "      <td>Weinberg Z</td>\n",
       "      <td>Weinberg Z</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     rfam_acc           rfam_id  auto_wiki           description      author  \\\n",
       "3006  RF03106             RT-11       2572             RT-11 RNA  Weinberg Z   \n",
       "3007  RF03107   saliva-tongue-1       2696   saliva-tongue-1 RNA  Weinberg Z   \n",
       "3008  RF03108    Methylosinus-1       2697    Methylosinus-1 RNA  Weinberg Z   \n",
       "3009  RF03109    Thermales-rpoB       2698    Thermales-rpoB RNA  Weinberg Z   \n",
       "3010  RF03110          throat-1       2699          throat-1 RNA  Weinberg Z   \n",
       "3011  RF03111          Zeta-pan       2700          Zeta-pan RNA  Weinberg Z   \n",
       "3012  RF03112  Staphylococcus-1       2701  Staphylococcus-1 RNA  Weinberg Z   \n",
       "3013  RF03113    Poribacteria-1       2702    Poribacteria-1 RNA  Weinberg Z   \n",
       "3014  RF03114              RT-1       2572              RT-1 RNA  Weinberg Z   \n",
       "3015  RF03115     KDPG-aldolase       2703     KDPG-aldolase RNA  Weinberg Z   \n",
       "\n",
       "     seed_source  \n",
       "3006  Weinberg Z  \n",
       "3007  Weinberg Z  \n",
       "3008  Weinberg Z  \n",
       "3009  Weinberg Z  \n",
       "3010  Weinberg Z  \n",
       "3011  Weinberg Z  \n",
       "3012  Weinberg Z  \n",
       "3013  Weinberg Z  \n",
       "3014  Weinberg Z  \n",
       "3015  Weinberg Z  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%time\n",
    "\n",
    "df.tail(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Write the Data from DF to Iguazio Key/Value Store"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2 µs, sys: 1 µs, total: 3 µs\n",
      "Wall time: 5.01 µs\n"
     ]
    }
   ],
   "source": [
    "%time\n",
    "\n",
    "tablename1 = os.path.join(os.getenv('V3IO_USERNAME')+'/examples/family1')\n",
    "\n",
    "client.write(backend, tablename1, df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Use Pandas Streaming Capabilities to oIngest Large Datasets \n",
    "Many Pandas inputs/outputs including SQL, CSV, as well as Iguazio Frames support chunking. <br>\n",
    "\n",
    "With chunking feature, the driver forms a continious iterator in order to reading/writing data chunk by chunk.  This requires to specify the `chunksize` (number of rows) which enables a DataFrame iterator.  This iterator can be passed as is to a DataFrame writer like Iguazio Frames. <br>\n",
    "\n",
    "The following example will stream data from MySQL to Iguazio NoSQL API."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 3 µs, sys: 1e+03 ns, total: 4 µs\n",
      "Wall time: 7.15 µs\n"
     ]
    }
   ],
   "source": [
    "%time\n",
    "\n",
    "tablename2 = os.path.join(os.getenv('V3IO_USERNAME')+'/examples/family2')\n",
    "CHUNK_SIZE = 1000\n",
    "\n",
    "dfIterator = pd.read_sql(query, conn, chunksize=CHUNK_SIZE)\n",
    "\n",
    "client.write(backend, tablename2, dfIterator)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2 µs, sys: 0 ns, total: 2 µ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>__name</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>RF01792</th>\n",
       "      <td>Boursnell C</td>\n",
       "      <td>2185.0</td>\n",
       "      <td>ribosomal frameshift site</td>\n",
       "      <td>RF01792</td>\n",
       "      <td>eiav_FSE</td>\n",
       "      <td>Published; PMID:3003905</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF00033</th>\n",
       "      <td>Bateman A</td>\n",
       "      <td>1330.0</td>\n",
       "      <td>MicF RNA</td>\n",
       "      <td>RF00033</td>\n",
       "      <td>MicF</td>\n",
       "      <td>Noncoding RNAs Database</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF01294</th>\n",
       "      <td>Wilkinson A</td>\n",
       "      <td>1264.0</td>\n",
       "      <td>Small nucleolar RNA U89</td>\n",
       "      <td>RF01294</td>\n",
       "      <td>snoU89</td>\n",
       "      <td>Predicted; Wilkinson A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF01667</th>\n",
       "      <td>Osuch I</td>\n",
       "      <td>2092.0</td>\n",
       "      <td>Drosophila rox1 ncRNA</td>\n",
       "      <td>RF01667</td>\n",
       "      <td>rox1</td>\n",
       "      <td>INFERNAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF00142</th>\n",
       "      <td>Moxon SJ</td>\n",
       "      <td>2378.0</td>\n",
       "      <td>Small nucleolar RNA Z118/Z121/Z120</td>\n",
       "      <td>RF00142</td>\n",
       "      <td>snoZ118</td>\n",
       "      <td>Griffiths-Jones SR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF00396</th>\n",
       "      <td>Moxon SJ</td>\n",
       "      <td>1254.0</td>\n",
       "      <td>Small nucleolar RNA SNORA13</td>\n",
       "      <td>RF00396</td>\n",
       "      <td>SNORA13</td>\n",
       "      <td>PMID:15199136, INFERNAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF01401</th>\n",
       "      <td>Barquist L</td>\n",
       "      <td>1266.0</td>\n",
       "      <td>rseX Hfq binding RNA</td>\n",
       "      <td>RF01401</td>\n",
       "      <td>rseX</td>\n",
       "      <td>Predicted; mafft; Infernal</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF02251</th>\n",
       "      <td>Eberhardt R</td>\n",
       "      <td>2309.0</td>\n",
       "      <td>Six3os1 conserved region 6</td>\n",
       "      <td>RF02251</td>\n",
       "      <td>Six3os1_6</td>\n",
       "      <td>Predicted; CMfinder</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF02797</th>\n",
       "      <td>Argasinska J</td>\n",
       "      <td>1236.0</td>\n",
       "      <td>Plasmid-encoded Shigella sRNA A</td>\n",
       "      <td>RF02797</td>\n",
       "      <td>PssrA</td>\n",
       "      <td>Argasinska J</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF02641</th>\n",
       "      <td>Argasinska J</td>\n",
       "      <td>1236.0</td>\n",
       "      <td>S. pyogenes small RNA Spy490483c</td>\n",
       "      <td>RF02641</td>\n",
       "      <td>Spy490483c</td>\n",
       "      <td>Argasinska J</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF01163</th>\n",
       "      <td>Wilkinson A</td>\n",
       "      <td>1264.0</td>\n",
       "      <td>Small nucleolar RNA snoR64a</td>\n",
       "      <td>RF01163</td>\n",
       "      <td>snoR64a</td>\n",
       "      <td>Predicted; WAR; Wilkinson A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF02041</th>\n",
       "      <td>Eberhardt R</td>\n",
       "      <td>2262.0</td>\n",
       "      <td>HOXA transcript at the distal tip, conserved r...</td>\n",
       "      <td>RF02041</td>\n",
       "      <td>HOTTIP_2</td>\n",
       "      <td>Predicted; MAFFT; Eberhardt R</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF02130</th>\n",
       "      <td>Eberhardt R</td>\n",
       "      <td>1282.0</td>\n",
       "      <td>GNAS antisense RNA 1 conserved region 4</td>\n",
       "      <td>RF02130</td>\n",
       "      <td>GNAS-AS1_4</td>\n",
       "      <td>Predicted; ClustalW; Eberhardt R</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF03076</th>\n",
       "      <td>Weinberg Z</td>\n",
       "      <td>2677.0</td>\n",
       "      <td>Streptomyces-metH RNA</td>\n",
       "      <td>RF03076</td>\n",
       "      <td>Streptomyces-metH</td>\n",
       "      <td>Weinberg Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF00352</th>\n",
       "      <td>Moxon SJ</td>\n",
       "      <td>1615.0</td>\n",
       "      <td>Small nucleolar RNA R21</td>\n",
       "      <td>RF00352</td>\n",
       "      <td>snoR21</td>\n",
       "      <td>Moxon SJ, INFERNAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF02347</th>\n",
       "      <td>Eberhardt R</td>\n",
       "      <td>2323.0</td>\n",
       "      <td>Alphaproteobacterial sRNA ar45</td>\n",
       "      <td>RF02347</td>\n",
       "      <td>ar45</td>\n",
       "      <td>Published; PMID:22418845</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF00164</th>\n",
       "      <td>Griffiths-Jones SR</td>\n",
       "      <td>1448.0</td>\n",
       "      <td>Coronavirus 3' stem-loop II-like motif (s2m)</td>\n",
       "      <td>RF00164</td>\n",
       "      <td>s2m</td>\n",
       "      <td>PMID:9568965; Griffiths-Jones SR, Moxon SJ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF00861</th>\n",
       "      <td>Wilkinson A</td>\n",
       "      <td>1978.0</td>\n",
       "      <td>microRNA mir-488</td>\n",
       "      <td>RF00861</td>\n",
       "      <td>mir-488</td>\n",
       "      <td>miRBase; Wilkinson A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF01595</th>\n",
       "      <td>Osuch I</td>\n",
       "      <td>1264.0</td>\n",
       "      <td>small nucleolar RNA snoR19</td>\n",
       "      <td>RF01595</td>\n",
       "      <td>snoR19</td>\n",
       "      <td>INFERNAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF02230</th>\n",
       "      <td>Eberhardt R</td>\n",
       "      <td>1285.0</td>\n",
       "      <td>Proteobacterial sRNA sX11</td>\n",
       "      <td>RF02230</td>\n",
       "      <td>sX11</td>\n",
       "      <td>Predicted; CMfinder</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF02977</th>\n",
       "      <td>Weinberg Z</td>\n",
       "      <td>2615.0</td>\n",
       "      <td>folE RNA</td>\n",
       "      <td>RF02977</td>\n",
       "      <td>folE</td>\n",
       "      <td>Weinberg Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF00459</th>\n",
       "      <td>Moxon SJ</td>\n",
       "      <td>1716.0</td>\n",
       "      <td>Mason-Pfizer monkey virus packaging signal</td>\n",
       "      <td>RF00459</td>\n",
       "      <td>MPMV_package</td>\n",
       "      <td>PMID:14687944, INFERNAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF01455</th>\n",
       "      <td>Osuch I</td>\n",
       "      <td>2074.0</td>\n",
       "      <td>Stem loopII regulatory element in POLB</td>\n",
       "      <td>RF01455</td>\n",
       "      <td>DPB</td>\n",
       "      <td>INFERNAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF01935</th>\n",
       "      <td>Eberhardt R</td>\n",
       "      <td>2238.0</td>\n",
       "      <td>Bithoraxoid conserved region 7</td>\n",
       "      <td>RF01935</td>\n",
       "      <td>bxd_7</td>\n",
       "      <td>Predicted; MAFFT; Eberhardt R</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF02722</th>\n",
       "      <td>Argasinska J</td>\n",
       "      <td>2513.0</td>\n",
       "      <td>Small Cajal body specific RNA ncR27</td>\n",
       "      <td>RF02722</td>\n",
       "      <td>sca_ncR27</td>\n",
       "      <td>Argasinska J</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF02853</th>\n",
       "      <td>Argasinska J</td>\n",
       "      <td>2437.0</td>\n",
       "      <td>Yersinia sRNA 201</td>\n",
       "      <td>RF02853</td>\n",
       "      <td>Ysr201</td>\n",
       "      <td>Argasinska J</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF01324</th>\n",
       "      <td>Wilkinson A</td>\n",
       "      <td>1265.0</td>\n",
       "      <td>CRISPR RNA direct repeat element</td>\n",
       "      <td>RF01324</td>\n",
       "      <td>CRISPR-DR11</td>\n",
       "      <td>Predicted; WAR; Wilkinson A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF01715</th>\n",
       "      <td>Weinberg Z</td>\n",
       "      <td>2121.0</td>\n",
       "      <td>Pedo-repair RNA</td>\n",
       "      <td>RF01715</td>\n",
       "      <td>Pedo-repair</td>\n",
       "      <td>Published; PMID:20230605;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF01830</th>\n",
       "      <td>Moore B</td>\n",
       "      <td>1236.0</td>\n",
       "      <td>Salmonella enterica Typhi npcRNA 44</td>\n",
       "      <td>RF01830</td>\n",
       "      <td>StyR-44</td>\n",
       "      <td>Published; PMID:20460466</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF00206</th>\n",
       "      <td>Griffiths-Jones SR</td>\n",
       "      <td>1486.0</td>\n",
       "      <td>Small nucleolar RNA U54</td>\n",
       "      <td>RF00206</td>\n",
       "      <td>U54</td>\n",
       "      <td>Griffiths-Jones SR</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>RF01565</th>\n",
       "      <td>Osuch I</td>\n",
       "      <td>1264.0</td>\n",
       "      <td>Dictyostelium snoRNA DdR15</td>\n",
       "      <td>RF01565</td>\n",
       "      <td>DdR15</td>\n",
       "      <td>INFERNAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF01767</th>\n",
       "      <td>Gardner PP</td>\n",
       "      <td>2167.0</td>\n",
       "      <td>SMK box translational riboswitch</td>\n",
       "      <td>RF01767</td>\n",
       "      <td>SMK_box_riboswitch</td>\n",
       "      <td>Gardner PP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF03053</th>\n",
       "      <td>Weinberg Z</td>\n",
       "      <td>2669.0</td>\n",
       "      <td>OTKONC RNA</td>\n",
       "      <td>RF03053</td>\n",
       "      <td>OTKONC</td>\n",
       "      <td>Weinberg Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF00742</th>\n",
       "      <td>Wilkinson A</td>\n",
       "      <td>1287.0</td>\n",
       "      <td>microRNA MIR162_2</td>\n",
       "      <td>RF00742</td>\n",
       "      <td>MIR162_2</td>\n",
       "      <td>miRBase; Wilkinson A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF00770</th>\n",
       "      <td>Wilkinson A</td>\n",
       "      <td>1950.0</td>\n",
       "      <td>microRNA mir-330</td>\n",
       "      <td>RF00770</td>\n",
       "      <td>mir-330</td>\n",
       "      <td>miRBase; Wilkinson A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF01900</th>\n",
       "      <td>Boursnell C</td>\n",
       "      <td>1287.0</td>\n",
       "      <td>microRNA mir-2024</td>\n",
       "      <td>RF01900</td>\n",
       "      <td>mir-2024</td>\n",
       "      <td>Predicted; ClustalW2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF02777</th>\n",
       "      <td>Argasinska J</td>\n",
       "      <td>2437.0</td>\n",
       "      <td>OppA 5' UTR thermometer</td>\n",
       "      <td>RF02777</td>\n",
       "      <td>OppA_thermometer</td>\n",
       "      <td>Argasinska J</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF00117</th>\n",
       "      <td>Moxon SJ</td>\n",
       "      <td>1409.0</td>\n",
       "      <td>C0719 RNA</td>\n",
       "      <td>RF00117</td>\n",
       "      <td>C0719</td>\n",
       "      <td>Hershberg R</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF02179</th>\n",
       "      <td>Eberhardt R</td>\n",
       "      <td>2490.0</td>\n",
       "      <td>ST7 antisense RNA 1 conserved region 1</td>\n",
       "      <td>RF02179</td>\n",
       "      <td>ST7-AS1_1</td>\n",
       "      <td>Predicted; CMfinder; Eberhardt R</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF00418</th>\n",
       "      <td>Moxon SJ</td>\n",
       "      <td>1677.0</td>\n",
       "      <td>Small nucleolar RNA SNORA58</td>\n",
       "      <td>RF00418</td>\n",
       "      <td>SNORA58</td>\n",
       "      <td>Moxon SJ, INFERNAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF00825</th>\n",
       "      <td>Wilkinson A</td>\n",
       "      <td>1967.0</td>\n",
       "      <td>microRNA mir-344</td>\n",
       "      <td>RF00825</td>\n",
       "      <td>mir-344</td>\n",
       "      <td>miRBase; Wilkinson A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF01124</th>\n",
       "      <td>Wilkinson A</td>\n",
       "      <td>1264.0</td>\n",
       "      <td>Small nucleolar RNA sR36</td>\n",
       "      <td>RF01124</td>\n",
       "      <td>sR36</td>\n",
       "      <td>Predicted; WAR; Wilkinson A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF01804</th>\n",
       "      <td>Bateman A, Moore B</td>\n",
       "      <td>2192.0</td>\n",
       "      <td>Lambda phage CIII thermoregulator element</td>\n",
       "      <td>RF01804</td>\n",
       "      <td>Lambda_thermo</td>\n",
       "      <td>Published; PMID:2532257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF02098</th>\n",
       "      <td>Eberhardt R</td>\n",
       "      <td>2507.0</td>\n",
       "      <td>DiGeorge syndrome critical region gene 5 conse...</td>\n",
       "      <td>RF02098</td>\n",
       "      <td>DGCR5</td>\n",
       "      <td>Predicted; CMfinder; Eberhardt R</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF00194</th>\n",
       "      <td>Moxon SJ</td>\n",
       "      <td>1476.0</td>\n",
       "      <td>Rubella virus 3' cis-acting element</td>\n",
       "      <td>RF00194</td>\n",
       "      <td>Rubella_3</td>\n",
       "      <td>PMID:10074193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF01033</th>\n",
       "      <td>Wilkinson A</td>\n",
       "      <td>2032.0</td>\n",
       "      <td>microRNA mir-767</td>\n",
       "      <td>RF01033</td>\n",
       "      <td>mir-767</td>\n",
       "      <td>miRBase; Wilkinson A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF01209</th>\n",
       "      <td>Wilkinson A</td>\n",
       "      <td>1264.0</td>\n",
       "      <td>Small nucleolar RNA snR76</td>\n",
       "      <td>RF01209</td>\n",
       "      <td>snR76</td>\n",
       "      <td>Predicted; WAR; Wilkinson A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF01764</th>\n",
       "      <td>Weinberg Z</td>\n",
       "      <td>2164.0</td>\n",
       "      <td>yjdF RNA</td>\n",
       "      <td>RF01764</td>\n",
       "      <td>yjdF</td>\n",
       "      <td>Published; PMID:20230605;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF02455</th>\n",
       "      <td>Eberhardt R</td>\n",
       "      <td>2342.0</td>\n",
       "      <td>Dianthovirus RNA2 cap-independent translation ...</td>\n",
       "      <td>RF02455</td>\n",
       "      <td>Virus_CITE_1</td>\n",
       "      <td>Eberhardt R</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF00112</th>\n",
       "      <td>Bateman A, Daub J</td>\n",
       "      <td>1405.0</td>\n",
       "      <td>CyaR/Rye RNA</td>\n",
       "      <td>RF00112</td>\n",
       "      <td>CyaR_RyeE</td>\n",
       "      <td>Gottesman S, Storz G, INFERNAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF00673</th>\n",
       "      <td>Wilkinson A</td>\n",
       "      <td>1287.0</td>\n",
       "      <td>microRNA mir-217</td>\n",
       "      <td>RF00673</td>\n",
       "      <td>mir-217</td>\n",
       "      <td>miRBase; Wilkinson A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF01986</th>\n",
       "      <td>Eberhardt R</td>\n",
       "      <td>2248.0</td>\n",
       "      <td>ZEB2 antisense RNA 1 conserved region 3</td>\n",
       "      <td>RF01986</td>\n",
       "      <td>ZEB2_AS1_3</td>\n",
       "      <td>Predicted; ClustalW; Eberhardt R</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF02180</th>\n",
       "      <td>Eberhardt R</td>\n",
       "      <td>2490.0</td>\n",
       "      <td>ST7 antisense RNA 1 conserved region 2</td>\n",
       "      <td>RF02180</td>\n",
       "      <td>ST7-AS1_2</td>\n",
       "      <td>Predicted; CMfinder; Eberhardt R</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF02469</th>\n",
       "      <td>Eberhardt R</td>\n",
       "      <td>1236.0</td>\n",
       "      <td>Actinobacteria sRNA Ms_IGR-7</td>\n",
       "      <td>RF02469</td>\n",
       "      <td>Ms_IGR-7</td>\n",
       "      <td>Eberhardt R</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF02878</th>\n",
       "      <td>Argasinska J</td>\n",
       "      <td>2551.0</td>\n",
       "      <td>Mesorhizobail RNA 7</td>\n",
       "      <td>RF02878</td>\n",
       "      <td>MH_s7</td>\n",
       "      <td>Argasinska J</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF00544</th>\n",
       "      <td>Moxon SJ</td>\n",
       "      <td>1790.0</td>\n",
       "      <td>Small nucleolar RNA psi28S-3327</td>\n",
       "      <td>RF00544</td>\n",
       "      <td>snopsi28S-3327</td>\n",
       "      <td>Moxon SJ, INFERNAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF01082</th>\n",
       "      <td>Wilkinson A</td>\n",
       "      <td>1651.0</td>\n",
       "      <td>Pseudoknot of upstream pseudoknot domain (UPD)...</td>\n",
       "      <td>RF01082</td>\n",
       "      <td>SBWMV1_UPD-PKh</td>\n",
       "      <td>Pseudobase</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF02012</th>\n",
       "      <td>Weinberg Z, Gardner PP</td>\n",
       "      <td>1326.0</td>\n",
       "      <td>Group II catalytic intron D1-D4-7</td>\n",
       "      <td>RF02012</td>\n",
       "      <td>group-II-D1D4-7</td>\n",
       "      <td>Weinberg Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF02022</th>\n",
       "      <td>Boursnell C</td>\n",
       "      <td>1287.0</td>\n",
       "      <td>microRNA mir-1275</td>\n",
       "      <td>RF02022</td>\n",
       "      <td>mir-1275</td>\n",
       "      <td>Predicted; ClustalW2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF02513</th>\n",
       "      <td>Eberhardt R</td>\n",
       "      <td>2246.0</td>\n",
       "      <td>Pyrrolysine insertion sequence transposase 2</td>\n",
       "      <td>RF02513</td>\n",
       "      <td>PYLIS_6</td>\n",
       "      <td>Predicted; WAR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3016 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                         author  auto_wiki  \\\n",
       "__name                                       \n",
       "RF01792             Boursnell C     2185.0   \n",
       "RF00033               Bateman A     1330.0   \n",
       "RF01294             Wilkinson A     1264.0   \n",
       "RF01667                 Osuch I     2092.0   \n",
       "RF00142                Moxon SJ     2378.0   \n",
       "RF00396                Moxon SJ     1254.0   \n",
       "RF01401              Barquist L     1266.0   \n",
       "RF02251             Eberhardt R     2309.0   \n",
       "RF02797            Argasinska J     1236.0   \n",
       "RF02641            Argasinska J     1236.0   \n",
       "RF01163             Wilkinson A     1264.0   \n",
       "RF02041             Eberhardt R     2262.0   \n",
       "RF02130             Eberhardt R     1282.0   \n",
       "RF03076              Weinberg Z     2677.0   \n",
       "RF00352                Moxon SJ     1615.0   \n",
       "RF02347             Eberhardt R     2323.0   \n",
       "RF00164      Griffiths-Jones SR     1448.0   \n",
       "RF00861             Wilkinson A     1978.0   \n",
       "RF01595                 Osuch I     1264.0   \n",
       "RF02230             Eberhardt R     1285.0   \n",
       "RF02977              Weinberg Z     2615.0   \n",
       "RF00459                Moxon SJ     1716.0   \n",
       "RF01455                 Osuch I     2074.0   \n",
       "RF01935             Eberhardt R     2238.0   \n",
       "RF02722            Argasinska J     2513.0   \n",
       "RF02853            Argasinska J     2437.0   \n",
       "RF01324             Wilkinson A     1265.0   \n",
       "RF01715              Weinberg Z     2121.0   \n",
       "RF01830                 Moore B     1236.0   \n",
       "RF00206      Griffiths-Jones SR     1486.0   \n",
       "...                         ...        ...   \n",
       "RF01565                 Osuch I     1264.0   \n",
       "RF01767              Gardner PP     2167.0   \n",
       "RF03053              Weinberg Z     2669.0   \n",
       "RF00742             Wilkinson A     1287.0   \n",
       "RF00770             Wilkinson A     1950.0   \n",
       "RF01900             Boursnell C     1287.0   \n",
       "RF02777            Argasinska J     2437.0   \n",
       "RF00117                Moxon SJ     1409.0   \n",
       "RF02179             Eberhardt R     2490.0   \n",
       "RF00418                Moxon SJ     1677.0   \n",
       "RF00825             Wilkinson A     1967.0   \n",
       "RF01124             Wilkinson A     1264.0   \n",
       "RF01804      Bateman A, Moore B     2192.0   \n",
       "RF02098             Eberhardt R     2507.0   \n",
       "RF00194                Moxon SJ     1476.0   \n",
       "RF01033             Wilkinson A     2032.0   \n",
       "RF01209             Wilkinson A     1264.0   \n",
       "RF01764              Weinberg Z     2164.0   \n",
       "RF02455             Eberhardt R     2342.0   \n",
       "RF00112       Bateman A, Daub J     1405.0   \n",
       "RF00673             Wilkinson A     1287.0   \n",
       "RF01986             Eberhardt R     2248.0   \n",
       "RF02180             Eberhardt R     2490.0   \n",
       "RF02469             Eberhardt R     1236.0   \n",
       "RF02878            Argasinska J     2551.0   \n",
       "RF00544                Moxon SJ     1790.0   \n",
       "RF01082             Wilkinson A     1651.0   \n",
       "RF02012  Weinberg Z, Gardner PP     1326.0   \n",
       "RF02022             Boursnell C     1287.0   \n",
       "RF02513             Eberhardt R     2246.0   \n",
       "\n",
       "                                               description rfam_acc  \\\n",
       "__name                                                                \n",
       "RF01792                          ribosomal frameshift site  RF01792   \n",
       "RF00033                                           MicF RNA  RF00033   \n",
       "RF01294                            Small nucleolar RNA U89  RF01294   \n",
       "RF01667                              Drosophila rox1 ncRNA  RF01667   \n",
       "RF00142                 Small nucleolar RNA Z118/Z121/Z120  RF00142   \n",
       "RF00396                        Small nucleolar RNA SNORA13  RF00396   \n",
       "RF01401                               rseX Hfq binding RNA  RF01401   \n",
       "RF02251                         Six3os1 conserved region 6  RF02251   \n",
       "RF02797                    Plasmid-encoded Shigella sRNA A  RF02797   \n",
       "RF02641                   S. pyogenes small RNA Spy490483c  RF02641   \n",
       "RF01163                        Small nucleolar RNA snoR64a  RF01163   \n",
       "RF02041  HOXA transcript at the distal tip, conserved r...  RF02041   \n",
       "RF02130            GNAS antisense RNA 1 conserved region 4  RF02130   \n",
       "RF03076                              Streptomyces-metH RNA  RF03076   \n",
       "RF00352                            Small nucleolar RNA R21  RF00352   \n",
       "RF02347                     Alphaproteobacterial sRNA ar45  RF02347   \n",
       "RF00164       Coronavirus 3' stem-loop II-like motif (s2m)  RF00164   \n",
       "RF00861                                   microRNA mir-488  RF00861   \n",
       "RF01595                         small nucleolar RNA snoR19  RF01595   \n",
       "RF02230                          Proteobacterial sRNA sX11  RF02230   \n",
       "RF02977                                           folE RNA  RF02977   \n",
       "RF00459         Mason-Pfizer monkey virus packaging signal  RF00459   \n",
       "RF01455             Stem loopII regulatory element in POLB  RF01455   \n",
       "RF01935                     Bithoraxoid conserved region 7  RF01935   \n",
       "RF02722                Small Cajal body specific RNA ncR27  RF02722   \n",
       "RF02853                                  Yersinia sRNA 201  RF02853   \n",
       "RF01324                   CRISPR RNA direct repeat element  RF01324   \n",
       "RF01715                                    Pedo-repair RNA  RF01715   \n",
       "RF01830                Salmonella enterica Typhi npcRNA 44  RF01830   \n",
       "RF00206                            Small nucleolar RNA U54  RF00206   \n",
       "...                                                    ...      ...   \n",
       "RF01565                         Dictyostelium snoRNA DdR15  RF01565   \n",
       "RF01767                   SMK box translational riboswitch  RF01767   \n",
       "RF03053                                         OTKONC RNA  RF03053   \n",
       "RF00742                                  microRNA MIR162_2  RF00742   \n",
       "RF00770                                   microRNA mir-330  RF00770   \n",
       "RF01900                                  microRNA mir-2024  RF01900   \n",
       "RF02777                            OppA 5' UTR thermometer  RF02777   \n",
       "RF00117                                          C0719 RNA  RF00117   \n",
       "RF02179             ST7 antisense RNA 1 conserved region 1  RF02179   \n",
       "RF00418                        Small nucleolar RNA SNORA58  RF00418   \n",
       "RF00825                                   microRNA mir-344  RF00825   \n",
       "RF01124                           Small nucleolar RNA sR36  RF01124   \n",
       "RF01804          Lambda phage CIII thermoregulator element  RF01804   \n",
       "RF02098  DiGeorge syndrome critical region gene 5 conse...  RF02098   \n",
       "RF00194                Rubella virus 3' cis-acting element  RF00194   \n",
       "RF01033                                   microRNA mir-767  RF01033   \n",
       "RF01209                          Small nucleolar RNA snR76  RF01209   \n",
       "RF01764                                           yjdF RNA  RF01764   \n",
       "RF02455  Dianthovirus RNA2 cap-independent translation ...  RF02455   \n",
       "RF00112                                       CyaR/Rye RNA  RF00112   \n",
       "RF00673                                   microRNA mir-217  RF00673   \n",
       "RF01986            ZEB2 antisense RNA 1 conserved region 3  RF01986   \n",
       "RF02180             ST7 antisense RNA 1 conserved region 2  RF02180   \n",
       "RF02469                       Actinobacteria sRNA Ms_IGR-7  RF02469   \n",
       "RF02878                                Mesorhizobail RNA 7  RF02878   \n",
       "RF00544                    Small nucleolar RNA psi28S-3327  RF00544   \n",
       "RF01082  Pseudoknot of upstream pseudoknot domain (UPD)...  RF01082   \n",
       "RF02012                  Group II catalytic intron D1-D4-7  RF02012   \n",
       "RF02022                                  microRNA mir-1275  RF02022   \n",
       "RF02513       Pyrrolysine insertion sequence transposase 2  RF02513   \n",
       "\n",
       "                    rfam_id                                 seed_source  \n",
       "__name                                                                   \n",
       "RF01792            eiav_FSE                     Published; PMID:3003905  \n",
       "RF00033                MicF                     Noncoding RNAs Database  \n",
       "RF01294              snoU89                      Predicted; Wilkinson A  \n",
       "RF01667                rox1                                    INFERNAL  \n",
       "RF00142             snoZ118                          Griffiths-Jones SR  \n",
       "RF00396             SNORA13                     PMID:15199136, INFERNAL  \n",
       "RF01401                rseX                  Predicted; mafft; Infernal  \n",
       "RF02251           Six3os1_6                         Predicted; CMfinder  \n",
       "RF02797               PssrA                                Argasinska J  \n",
       "RF02641          Spy490483c                                Argasinska J  \n",
       "RF01163             snoR64a                 Predicted; WAR; Wilkinson A  \n",
       "RF02041            HOTTIP_2               Predicted; MAFFT; Eberhardt R  \n",
       "RF02130          GNAS-AS1_4            Predicted; ClustalW; Eberhardt R  \n",
       "RF03076   Streptomyces-metH                                  Weinberg Z  \n",
       "RF00352              snoR21                          Moxon SJ, INFERNAL  \n",
       "RF02347                ar45                    Published; PMID:22418845  \n",
       "RF00164                 s2m  PMID:9568965; Griffiths-Jones SR, Moxon SJ  \n",
       "RF00861             mir-488                        miRBase; Wilkinson A  \n",
       "RF01595              snoR19                                    INFERNAL  \n",
       "RF02230                sX11                         Predicted; CMfinder  \n",
       "RF02977                folE                                  Weinberg Z  \n",
       "RF00459        MPMV_package                     PMID:14687944, INFERNAL  \n",
       "RF01455                 DPB                                    INFERNAL  \n",
       "RF01935               bxd_7               Predicted; MAFFT; Eberhardt R  \n",
       "RF02722           sca_ncR27                                Argasinska J  \n",
       "RF02853              Ysr201                                Argasinska J  \n",
       "RF01324         CRISPR-DR11                 Predicted; WAR; Wilkinson A  \n",
       "RF01715         Pedo-repair                   Published; PMID:20230605;  \n",
       "RF01830             StyR-44                    Published; PMID:20460466  \n",
       "RF00206                 U54                          Griffiths-Jones SR  \n",
       "...                     ...                                         ...  \n",
       "RF01565               DdR15                                    INFERNAL  \n",
       "RF01767  SMK_box_riboswitch                                  Gardner PP  \n",
       "RF03053              OTKONC                                  Weinberg Z  \n",
       "RF00742            MIR162_2                        miRBase; Wilkinson A  \n",
       "RF00770             mir-330                        miRBase; Wilkinson A  \n",
       "RF01900            mir-2024                        Predicted; ClustalW2  \n",
       "RF02777    OppA_thermometer                                Argasinska J  \n",
       "RF00117               C0719                                 Hershberg R  \n",
       "RF02179           ST7-AS1_1            Predicted; CMfinder; Eberhardt R  \n",
       "RF00418             SNORA58                          Moxon SJ, INFERNAL  \n",
       "RF00825             mir-344                        miRBase; Wilkinson A  \n",
       "RF01124                sR36                 Predicted; WAR; Wilkinson A  \n",
       "RF01804       Lambda_thermo                     Published; PMID:2532257  \n",
       "RF02098               DGCR5            Predicted; CMfinder; Eberhardt R  \n",
       "RF00194           Rubella_3                               PMID:10074193  \n",
       "RF01033             mir-767                        miRBase; Wilkinson A  \n",
       "RF01209               snR76                 Predicted; WAR; Wilkinson A  \n",
       "RF01764                yjdF                   Published; PMID:20230605;  \n",
       "RF02455        Virus_CITE_1                                 Eberhardt R  \n",
       "RF00112           CyaR_RyeE              Gottesman S, Storz G, INFERNAL  \n",
       "RF00673             mir-217                        miRBase; Wilkinson A  \n",
       "RF01986          ZEB2_AS1_3            Predicted; ClustalW; Eberhardt R  \n",
       "RF02180           ST7-AS1_2            Predicted; CMfinder; Eberhardt R  \n",
       "RF02469            Ms_IGR-7                                 Eberhardt R  \n",
       "RF02878               MH_s7                                Argasinska J  \n",
       "RF00544      snopsi28S-3327                          Moxon SJ, INFERNAL  \n",
       "RF01082      SBWMV1_UPD-PKh                                  Pseudobase  \n",
       "RF02012     group-II-D1D4-7                                  Weinberg Z  \n",
       "RF02022            mir-1275                        Predicted; ClustalW2  \n",
       "RF02513             PYLIS_6                              Predicted; WAR  \n",
       "\n",
       "[3016 rows x 6 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%time\n",
    "\n",
    "client.read(backend, tablename2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reading From PostgreSQL\n",
    "\n",
    "*# default* <br>\n",
    "The PostgreSQL dialect uses psycopg2 as the default DBAPI <br>\n",
    "engine = create_engine('postgresql://scott:tiger@localhost/mydatabase') <br>\n",
    "*# psycopg2* <br>\n",
    "engine = create_engine('postgresql+psycopg2://scott:tiger@localhost/mydatabase') <br>\n",
    "\n",
    "For more details read [PostgreSQL dialet](https://docs.sqlalchemy.org/en/latest/core/engines.html#postgresql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting psycopg2\n",
      "\u001b[?25l  Downloading https://files.pythonhosted.org/packages/37/25/53e8398975aa3323de46a5cc2745aeb4c9db11352ca905d3a15c53b6a816/psycopg2-2.7.7-cp36-cp36m-manylinux1_x86_64.whl (2.7MB)\n",
      "\u001b[K    100% |################################| 2.7MB 14.1MB/s \n",
      "\u001b[?25hInstalling collected packages: psycopg2\n",
      "Successfully installed psycopg2\n"
     ]
    }
   ],
   "source": [
    "!pip install psycopg2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg2\n",
    "\n",
    "postgresSQL_URL = 'postgresql+psycopg2://postgres:21@172.31.24.47:5432/public'\n",
    "engine = create_engine(postgresSQL_URL)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "query3 = 'select * from employees'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3 = pd.read_sql(query3,engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(df3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tablename3 = os.path.join(os.getenv('V3IO_USERNAME')+'/examples/employees')\n",
    "backend = 'kv'\n",
    "client.write(backend, tablename3, df3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "client.read(backend, tablename3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reading From Oracle\n",
    "\n",
    "*# cx_oracle* <br>\n",
    "The Oracle dialect uses cx_oracle as the default DBAPI <br>\n",
    "engine = create_engine('oracle://scott:tiger@127.0.0.1:1521/sidname') <br>\n",
    "engine = create_engine('oracle+cx_oracle://scott:tiger@tnsname') <br>\n",
    "\n",
    "For more details read [Oracle dialet](https://docs.sqlalchemy.org/en/latest/core/engines.html#oracle)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting cx_oracle\n",
      "\u001b[?25l  Downloading https://files.pythonhosted.org/packages/8e/94/6e9d03db620fa41df7f0e67c02230300abb8ee30c5aaf53ca6190aa00af6/cx_Oracle-7.1.2-cp36-cp36m-manylinux1_x86_64.whl (685kB)\n",
      "\u001b[K    100% |################################| 686kB 25.7MB/s \n",
      "\u001b[?25hInstalling collected packages: cx-oracle\n",
      "Successfully installed cx-oracle-7.1.2\n"
     ]
    }
   ],
   "source": [
    "!pip install cx_oracle"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "import cx_oracle\n",
    "\n",
    "oracle_URL = 'oracle://scott:tiger@hostIP:1521/dbname'\n",
    "engine = create_engine(oracle_URL)\n",
    "\n",
    "query = 'select * from table'\n",
    "\n",
    "df = pd.read_sql(query,engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reading From SQL Server\n",
    "\n",
    "*# pymssql* <br>\n",
    "engine = create_engine('mssql+pymssql://scott:tiger@hostname:port/dbname') <br>\n",
    "\n",
    "For more details read [SQL Server dialet](https://docs.sqlalchemy.org/en/latest/core/engines.html#microsoft-sql-server)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "import pymssql\n",
    "\n",
    "sqlServer_URL = 'mssql+pymssql://scott:tiger@hostname:port/dbname'\n",
    "engine = create_engine(sqlServer_URL)\n",
    "\n",
    "query = 'select * from table'\n",
    "\n",
    "df = pd.read_sql(query,engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Cleanup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Remove Data Created for this Experiment\n",
    "\n",
    "Use Frames' Client `delete` function to remove data in KV stores."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "client.delete(backend, tablename)\n",
    "client.delete(backend, tablename1)\n",
    "client.delete(backend, tablename2)\n",
    "client.delete(backend, tablename3)"
   ]
  }
 ],
 "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": 2
}