public
Created

Class for IPython or IPython Notebook that compares query results across multiple database connections.

  • Download Gist
SQL_Comparitor.ipynb
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63
{
"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": {}
}
]
}

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.