IPython Notebook Demonstrating SQLite to Excel via Pandas

sqlite_to_excel.ipynb

{
 "metadata": {
  "name": "",
  "signature": "sha256:256ea70c121bd798d1361bd2f114e7985561f5005901cac33decc8575444682d"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "First, create a silly test DB with some stock data in it to ensure that we are doing this right."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import sqlite3\n",
      "import os\n",
      "\n",
      "def fake_db(name):\n",
      "    \"\"\" Given a .db file, fill it with fake data\n",
      "        From sqlite3 docs\n",
      "        https://docs.python.org/2/library/sqlite3.html\n",
      "    \"\"\"\n",
      "    if os.path.exists(name):\n",
      "        os.unlink(name)\n",
      "    conn = sqlite3.connect(name)\n",
      "    c = conn.cursor()\n",
      "    c.execute('''CREATE TABLE stocks\n",
      "             (date text, trans text, symbol text, qty real, price real)''')\n",
      "    purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),\n",
      "             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),\n",
      "             ('2006-04-06', 'SELL', 'IBM', 500, 53.00),\n",
      "            ]\n",
      "    c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)\n",
      "    conn.commit()\n",
      "    conn.close()\n",
      "    \n",
      "db_name = 'test.db'\n",
      "fake_db(db_name)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 6
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Now to do the actual conversion:\n",
      "\n",
      "* Connect sqlalchemy to the database\n",
      "* Read the table out of it into a pandas data frame\n",
      "* Display the pandas data frame to ensure it looks sane"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd\n",
      "from sqlalchemy import create_engine\n",
      "engine = create_engine('sqlite:///' + db_name)\n",
      "df = pd.read_sql_table('stocks', engine)\n",
      "df.head()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>date</th>\n",
        "      <th>trans</th>\n",
        "      <th>symbol</th>\n",
        "      <th>qty</th>\n",
        "      <th>price</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> 2006-03-28</td>\n",
        "      <td>  BUY</td>\n",
        "      <td>  IBM</td>\n",
        "      <td> 1000</td>\n",
        "      <td> 45</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> 2006-04-05</td>\n",
        "      <td>  BUY</td>\n",
        "      <td> MSFT</td>\n",
        "      <td> 1000</td>\n",
        "      <td> 72</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td> 2006-04-06</td>\n",
        "      <td> SELL</td>\n",
        "      <td>  IBM</td>\n",
        "      <td>  500</td>\n",
        "      <td> 53</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 4,
       "text": [
        "         date trans symbol   qty  price\n",
        "0  2006-03-28   BUY    IBM  1000     45\n",
        "1  2006-04-05   BUY   MSFT  1000     72\n",
        "2  2006-04-06  SELL    IBM   500     53"
       ]
      }
     ],
     "prompt_number": 4
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Now it's easy to convert this to an excel file."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": true,
     "input": [
      "df.to_excel('example.xlsx')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 7
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "![that worked](http://drop.serialized.net/Screen%20Shot%202015-01-22%20at%207.25.01%20PM-4Er9pXXN4a.png)\n",
      "\n",
      "Or a .csv file if desired..."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.to_csv('example.csv')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 8
    }
   ],
   "metadata": {}
  }
 ]
}