Python MySQL (WoS).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# How to use Python MySQL bindings for WoS\n",
    "\n",
    "In this we demonstrate:\n",
    "\n",
    "    1) How to open a connection to the Web of Science (WoS) database\n",
    "    2) How to instantiate a cursor to the database and issue SELECT queries\n",
    "    3) Different strategies for fetching results from the queries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Library imports\n",
    "\n",
    "There are different Python MySQL binding libraries that you can use. Two examples are [MySQLdb](http://mysql-python.sourceforge.net/MySQLdb.html) and [PyMySQL](https://github.com/PyMySQL/PyMySQL)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql as mysql\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    woshost = os.environ['woshost']\n",
    "    wosuser = os.environ['wosuser']\n",
    "    wospasswd = os.environ['wospasswd']\n",
    "except KeyError:\n",
    "    with open('./.wos_creds', 'r') as fp:\n",
    "        woshost = fp.readline().strip()\n",
    "        wosuser = fp.readline().strip()\n",
    "        wospasswd = fp.readline().strip()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Opening the connection\n",
    "\n",
    "The connection object requires the following information:\n",
    "    \n",
    "   * __host__ : the hostname (either user-readable or IP address of the server hosting the database)\n",
    "   * __port__ : MySQL servers usually listen on port 3306\n",
    "   * __user__ : username for the server (if necessary)\n",
    "   * __passwd__ : password for the server (if necessary)\n",
    "   * __db__ : database to use (optional)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = mysql.connect(host=woshost, \n",
    "               port=3306, \n",
    "               user=wosuser,\n",
    "               passwd=wospasswd,\n",
    "               db='wos2')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Opening a Cursor and issuing Queries\n",
    "\n",
    "The cursor object lets us directly interact with the data, through issuing queries and fetching data. There are two different cursors in pymysql that implement different strategies for how to store/deliver query results. \n",
    "\n",
    "** For more information check out the following documentation: [docs](http://pymysql.readthedocs.io/en/latest/modules/cursors.html)**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "num_results = 100\n",
    "\n",
    "cur = conn.cursor()\n",
    "\n",
    "cur.execute(\"SELECT wos_id FROM publications LIMIT {};\".format(num_results))\n",
    "\n",
    "print(cur.description)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Retrieving Results"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fetching all the results at once"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = cur.fetchall()\n",
    "for row in data:\n",
    "    print(row[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Iterating through the results\n",
    "\n",
    "\n",
    "   **Important**: Once you fetch and iterate through the results, they are gone. It is expected that when you deal with the results, you save any of the information you need. \n",
    "\n",
    "To run the next cell and get results, you need to **rerun the query**. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = cur.fetchone()\n",
    "while data is not None:\n",
    "    print(data[0])\n",
    "    data = cur.fetchone() "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Different SQL Strategies for Query Optimization"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One of the great things about SQL (and in our case, MySQL) is the diversity of clauses for extracting information. By no means will this section be exhaustive, but we hope to provide some examples of different strategies that you can do to optimize your query.\n",
    "\n",
    "Let's assume the usecase is that we have a set of document ids that we want to use to pull the rest of those documents' publication information."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ids = [1, 2, 14000000, 15000000]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Search by comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur = conn.cursor()\n",
    "\n",
    "query = \"SELECT pubyear FROM publications WHERE id={};\"\n",
    "\n",
    "for id_ in ids:\n",
    "    cur.execute(query.format(id_))\n",
    "    data = cur.fetchall()\n",
    "    print(\"id_: {}\\t publication year: {}\".format(id_, data[0]))\n",
    "\n",
    "cur.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Search by membership"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**N.B.** A caveat to using an approach like this is that you may be limited by the actual length of your query in bytes. If you get an error that suggests your query is too long, look for global variables in your server like `max_allowed_packet`. If you can configure that variable to lengthen the query length, then great. Otherwise, consider either splitting the query into different subqueries, or another approach entirely. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur = conn.cursor()\n",
    "\n",
    "query = \"SELECT id, pubyear FROM publications WHERE id IN (\"\n",
    "\n",
    "for id_ in ids[:-1]:\n",
    "    query += \"{},\".format(id_)\n",
    "    \n",
    "query += \"{});\".format(ids[-1])\n",
    "\n",
    "cur.execute(query)\n",
    "data = cur.fetchall()\n",
    "for row in data:\n",
    "    print(\"id: {}\\t publication year: {}\".format(row[0], row[1]))\n",
    "\n",
    "cur.close()\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Since the cursor works inside the connection, and you might want to \n",
    "# have another cursor sometime, you close the cursor first, and then \n",
    "# the connection\n",
    "\n",
    "cur.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}