SQL_Comparitor.ipynb

{
 "metadata": {
  "name": "SQL_Comparitor"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Side-by-side comparisons of query results across multiple database connections.  Depends on [ipython-sql](https://pypi.python.org/pypi/ipython-sql)."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "%load_ext sql\n",
      "import pandas as pd\n",
      "from IPython.core.display import HTML\n",
      "class SQL_Comparitor(object):\n",
      "    def __init__(self, *connection_strings):\n",
      "        self.connection_strings = connection_strings\n",
      "    def run(self, qry):\n",
      "        dframes = []\n",
      "        for connection_string in self.connection_strings:\n",
      "            result = %sql $connection_string $qry\n",
      "            short_name = connection_string.split('@')[1]            \n",
      "            keys = [result.keys[0]]\n",
      "            for key in result.keys[1:]:\n",
      "                keys.append('%s_%s' % (short_name, key))\n",
      "            dframes.append(pd.DataFrame(result, columns=keys))\n",
      "        result = dframes[0]\n",
      "        for dframe in dframes[1:]:\n",
      "            result = pd.merge(result, dframe, on=keys[0])\n",
      "        return result"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "comparitor = SQL_Comparitor('mysql+pymysql://username:password@server1/db1', \n",
      "                            'mysql+pymysql://username:password@server2/db2',\n",
      "                           )\n",
      "results = comparitor.run(\"SHOW VARIABLES LIKE '%cache%' \")\n",
      "HTML(results.to_html())"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    }
   ],
   "metadata": {}
  }
 ]
}