Python_ETL_2.ipynb

{
 "metadata": {
  "name": "",
  "signature": "sha256:216d153948fe3001790451fae4a0f6eb14f4bd2e4588cfd0b6abf47f59b5e4c6"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u6b63\u898f\u8868\u9054\u5f0f"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import re \n",
      "m = re.match(r\"(\\w+)@(\\w+)\", \"david@largtidata.com\") \n",
      "print m.groups()\n",
      " \n",
      "m = re.match(r\"(\\w+)@([a-z.]+)\", \"david@largtidata.com\") \n",
      "print m.groups() \n",
      "\n",
      "m = re.match(r\"(\\d+)\\.(\\d+)\", \"1999.5\") \n",
      "print m.groups() \n",
      "\n",
      "m = re.match(r\"(?P<first_name>\\w+) (?P<last_name>\\w+)\", \"David Chiu\") \n",
      "print m.group('first_name'), m.group('last_name')\n",
      " \n",
      "str1 = 'scp file.txt root@10.0.0.1:./' \n",
      "m=re.search('^scp ([\\w\\.]+) (\\w+)@([\\w\\.]+):(.+)',str1) \n",
      "if m: \n",
      "    print m.group(1), m.group(2), m.group(3), m.group(4)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "('david', 'largtidata')\n",
        "('david', 'largtidata.com')\n",
        "('1999', '5')\n",
        "David Chiu\n",
        "file.txt root 10.0.0.1 ./\n"
       ]
      }
     ],
     "prompt_number": 2
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import re\n",
      "str1 = \"0912-345-678\"\n",
      "str2 = \"0912-345678\"\n",
      "\n",
      "m = re.match(r\"(\\d{4}-?\\d{3}-?\\d{3})\", str1)\n",
      "print m.groups()\n",
      "#print re.match(r\"\\d{4}-?\\d{3}-?\\d{3}\", str2)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "('0912-345-678',)\n"
       ]
      }
     ],
     "prompt_number": 2
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u4f7f\u7528file IO\u8b80\u9032\u8cc7\u6599"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# -*- coding: utf-8 -*-\n",
      "f = open('gov/51239291_0607L1030328.txt', 'r') \n",
      "#print f.read() \n",
      "f.close()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 6
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u628a\u8cc7\u6599\u4e1f\u9032BeautifulSoup"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# -*- coding: utf-8 -*- \n",
      "from bs4 import BeautifulSoup \n",
      "f = open('gov/51239291_0607L1030328.txt', 'r')  \n",
      "response_text = f.read() \n",
      "soup = BeautifulSoup(response_text) \n",
      "f.close()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 10
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u6293\u51fa\u542b\u6709th \u7684\u5217"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# -*- coding: utf-8 -*- \n",
      "from bs4 import BeautifulSoup \n",
      "f = open('gov3/51239291_0607L1030328.txt', 'r') \n",
      "response_text = f.read() \n",
      "f.close() \n",
      "\n",
      "soup = BeautifulSoup(''.join(response_text)) \n",
      "tr = soup.findAll('tr') \n",
      "for row in tr[1:5]: \n",
      "    print row.find('th')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "None\n",
        "None\n",
        "<th align=\"left\" bgcolor=\"#DAEBED\" class=\"T11b\" valign=\"middle\" width=\"200\">\n",
        "     \u6a5f\u95dc\u4ee3\u78bc\n",
        "    </th>\n",
        "<th align=\"left\" bgcolor=\"#DAEBED\" class=\"T11b\" valign=\"middle\" width=\"200\">\n",
        "     \u6a5f\u95dc\u540d\u7a31\n",
        "    </th>\n"
       ]
      }
     ],
     "prompt_number": 6
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u5217\u51fa\u8868\u683c\u4e2d\u6240\u6709th \u540d\u7a31"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "for row in tr[1:10]: \n",
      "    th = row.find('th') \n",
      "    #print th.text.strip()\n",
      "    if th is not None: \n",
      "        print th.text.strip()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\u6a5f\u95dc\u4ee3\u78bc\n",
        "\u6a5f\u95dc\u540d\u7a31\n",
        "\u55ae\u4f4d\u540d\u7a31\n",
        "\u6a5f\u95dc\u5730\u5740\n",
        "\u806f\u7d61\u4eba\n",
        "\u806f\u7d61\u96fb\u8a71\n",
        "\u50b3\u771f\u865f\u78bc\n"
       ]
      }
     ],
     "prompt_number": 12
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Strip \u7bc4\u4f8b"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "s = \" \\t string test \\t \\n\\n\\r\" ' \\t string test \\t \\n\\n\\r' \n",
      "print s"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        " \t string test \t \n",
        "\n",
        "\r",
        " \t string test \t \n",
        "\n",
        "\r\n"
       ]
      }
     ],
     "prompt_number": 45
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "print s.strip()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "string test \t \n",
        "\n",
        "\r",
        " \t string test\n"
       ]
      }
     ],
     "prompt_number": 46
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "print s.rstrip()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        " \t string test \t \n",
        "\n",
        "\r",
        " \t string test\n"
       ]
      }
     ],
     "prompt_number": 47
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "print s.lstrip()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "string test \t \n",
        "\n",
        "\r",
        " \t string test \t \n",
        "\n",
        "\r\n"
       ]
      }
     ],
     "prompt_number": 48
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u627e\u51fa\u201d\u6a5f\u95dc\u4ee3\u78bc\u201d"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "for row in tr: \n",
      "    th = row.find('th') \n",
      "    if th is not None: \n",
      "        if th.text.encode('utf-8').strip() == \"\u6a5f\u95dc\u4ee3\u78bc\": \n",
      "        \tprint row.find('td').text.strip()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "3.13.30\n"
       ]
      }
     ],
     "prompt_number": 14
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Dictionary \u7bc4\u4f8b"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#declare dictionary \n",
      "dic = {'a':100, 'b':\"yes\", 'c':0.98} \n",
      "print dic \n",
      "\n",
      "#get keys in dictionary \n",
      "print dic.keys() \n",
      "\n",
      "#get values in dictionary \n",
      "print dic.values() \n",
      "\n",
      "#get value of given key \n",
      "print dic['a'] \n",
      "\n",
      "#get value of given key \n",
      "print dic.get('a')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "{'a': 100, 'c': 0.98, 'b': 'yes'}\n",
        "['a', 'c', 'b']\n",
        "[100, 0.98, 'yes']\n",
        "100\n",
        "100\n"
       ]
      }
     ],
     "prompt_number": 27
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Dictionary \u7bc4\u4f8b(\u4e8c)"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#add entry into dictionary \n",
      "dic['d'] = 'new' \n",
      "print dic \n",
      "\n",
      "#add entry into dictionary \n",
      "dic.update({'e':123}) \n",
      "print dic \n",
      "\n",
      "# iter the dictionary \n",
      "for rec in dic: \n",
      "    print rec, dic[rec]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "{'a': 100, 'c': 0.98, 'b': 'yes', 'd': 'new'}\n",
        "{'a': 100, 'c': 0.98, 'b': 'yes', 'e': 123, 'd': 'new'}\n",
        "a 100\n",
        "c 0.98\n",
        "b yes\n",
        "e 123\n",
        "d new\n"
       ]
      }
     ],
     "prompt_number": 28
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u53ef\u4ee5\u4f7f\u7528dictionary \u7ba1\u7406"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "dic = {\"\u6a5f\u95dc\u4ee3\u78bc\":\"\", \"\u6a5f\u95dc\u540d\u7a31\":\"\", \"\u6a5f\u95dc\u5730\u5740\":\"\"} \n",
      "for row in tr: \n",
      "    th = row.find('th') \n",
      "    if th is not None: \n",
      "        if th.text.encode('utf-8').strip() in dic: \n",
      "            print row.find('td').text.strip()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "3.13.30\n",
        "\u81fa\u7063\u7cd6\u696d\u80a1\u4efd\u6709\u9650\u516c\u53f8\n",
        "701\u81fa\u5357\u5e02\u6771\u5340\u751f\u7522\u8def68\u865f\n"
       ]
      }
     ],
     "prompt_number": 15
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u628a\u8cc7\u6599\u585e\u5230dictionary value"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "dic = {\"\u6a5f\u95dc\u4ee3\u78bc\":\"\", \"\u6a5f\u95dc\u540d\u7a31\":\"\", \"\u6a5f\u95dc\u5730\u5740\":\"\"} \n",
      "for row in tr: \n",
      "    th = row.find('th') \n",
      "    if th is not None: \n",
      "        if th.text.encode('utf-8').strip() in dic: \n",
      "            dic[th.text.encode('utf-8').strip()] = row.find('td').text.encode('utf-8').strip() \n",
      "print dic"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "{'\\xe6\\xa9\\x9f\\xe9\\x97\\x9c\\xe5\\x90\\x8d\\xe7\\xa8\\xb1': '\\xe8\\x87\\xba\\xe7\\x81\\xa3\\xe7\\xb3\\x96\\xe6\\xa5\\xad\\xe8\\x82\\xa1\\xe4\\xbb\\xbd\\xe6\\x9c\\x89\\xe9\\x99\\x90\\xe5\\x85\\xac\\xe5\\x8f\\xb8', '\\xe6\\xa9\\x9f\\xe9\\x97\\x9c\\xe4\\xbb\\xa3\\xe7\\xa2\\xbc': '3.13.30', '\\xe6\\xa9\\x9f\\xe9\\x97\\x9c\\xe5\\x9c\\xb0\\xe5\\x9d\\x80': '701\\xe8\\x87\\xba\\xe5\\x8d\\x97\\xe5\\xb8\\x82\\xe6\\x9d\\xb1\\xe5\\x8d\\x80\\xe7\\x94\\x9f\\xe7\\x94\\xa2\\xe8\\xb7\\xaf68\\xe8\\x99\\x9f'}\n"
       ]
      }
     ],
     "prompt_number": 30
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u628a\u8cc7\u6599\u5f9e\u5b57\u5178\u4e2d\u53d6\u51fa\u4f86"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "for rec in dic: \n",
      "    print rec, dic[rec]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\u6a5f\u95dc\u540d\u7a31 \u81fa\u7063\u7cd6\u696d\u80a1\u4efd\u6709\u9650\u516c\u53f8\n",
        "\u6a5f\u95dc\u4ee3\u78bc 3.13.30\n",
        "\u6a5f\u95dc\u5730\u5740 701\u81fa\u5357\u5e02\u6771\u5340\u751f\u7522\u8def68\u865f\n"
       ]
      }
     ],
     "prompt_number": 31
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u53d6\u5f97\u6a19\u7684\u5206\u985e"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "dic = {\"\u6a19\u7684\u5206\u985e\":\"\"} \n",
      "for row in tr: \n",
      "    th = row.find('th') \n",
      "    if th is not None: \n",
      "        if th.text.encode('utf-8').strip() in dic: \n",
      "            dic[th.text.encode('utf-8').strip()] = row.find('td').text.encode('utf-8').strip()\n",
      "for rec in dic: \n",
      "    print rec, dic[rec]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\u6a19\u7684\u5206\u985e <\u52de\u52d9\u985e>    \r\n",
        "                            752\r\n",
        "                            \u96fb\u4fe1\u670d\u52d9\n"
       ]
      }
     ],
     "prompt_number": 16
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u4f7f\u7528repr \u627e\u51faformatting"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "a = '\\t\\t string \\n\\t' \n",
      "print a \n",
      "print repr(a)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\t\t string \n",
        "\t\n",
        "'\\t\\t string \\n\\t'\n"
       ]
      }
     ],
     "prompt_number": 18
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u770b\u770bstring \u5167\u542b\u4ec0\u9ebc\u5b57\u5143"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "for rec in dic: \n",
      "    print repr(rec), repr(dic[rec])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "'\\xe6\\xa8\\x99\\xe7\\x9a\\x84\\xe5\\x88\\x86\\xe9\\xa1\\x9e' '<\\xe5\\x8b\\x9e\\xe5\\x8b\\x99\\xe9\\xa1\\x9e>    \\r\\n                            752\\r\\n                            \\xe9\\x9b\\xbb\\xe4\\xbf\\xa1\\xe6\\x9c\\x8d\\xe5\\x8b\\x99'\n"
       ]
      }
     ],
     "prompt_number": 19
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u5c07\u8cc7\u6599\u4f9d\u5206\u9694\u7b26\u865f\u5207\u6210\u9663\u5217"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "a = '123,555,111,99' \n",
      "print a.split(',') \n",
      "print a.split(',',1) "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "['123', '555', '111', '99']\n",
        "['123', '555,111,99']\n"
       ]
      }
     ],
     "prompt_number": 20
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u5c07\u9663\u5217\u55ae\u5143\u4f9d\u5206\u9694\u7b26\u865f\u5408\u6210\u5b57\u4e32"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "a = '123,555,111,99' \n",
      "ary = a.split(',') \n",
      "print '|'.join(ary)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "123|555|111|99\n"
       ]
      }
     ],
     "prompt_number": 21
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u5982\u4f55\u79fb\u9664\u5b57\u4e32\u4e2d\u5167\u542b\u7684\u000b",
      "\n",
      " \u63db\u884c\u7b26\u865f\u53ca\u7a7a\u767d"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "for rec in dic: \n",
      "    print ''.join(dic[rec].split())"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "<\u52de\u52d9\u985e>752\u96fb\u4fe1\u670d\u52d9\n"
       ]
      }
     ],
     "prompt_number": 22
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u6642\u9593\u8ddf\u5b57\u4e32\u8f49\u63db"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "from datetime import date,datetime \n",
      "currenttime = datetime.now() \n",
      "print currenttime.strftime(\"%Y-%m-%d\") \n",
      "\n",
      "\n",
      "a = '2014-05-03 14:00' \n",
      "print datetime.strptime(a, \"%Y-%m-%d %H:%M\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "2014-11-18\n",
        "2014-05-03 14:00:00\n"
       ]
      }
     ],
     "prompt_number": 23
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u53d6\u5f97\u5e74\u5206"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "response_date = '102/12/10 10:30' \n",
      "getyear = response_date.split('/',1) \n",
      "print getyear"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "['102', '12/10 10:30']\n"
       ]
      }
     ],
     "prompt_number": 25
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u8f49\u63db\u6210\u897f\u5143\u5e74"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "bctime= str(int(getyear[0]) + 1911) + \"/\" + ''.join(getyear[1:]) \n",
      "print bctime"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "2013/12/10 10:30\n"
       ]
      }
     ],
     "prompt_number": 40
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u8f49\u63db\u6210\u6642\u9593\u683c\u5f0f"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "response_date = '102/12/10 10:30' \n",
      "getyear = response_date.split('/', 1) \n",
      "bctime= str(int(getyear[0]) + 1911) + \"/\" + ''.join(getyear[1:]) \n",
      "print datetime.strptime(bctime, \"%Y/%m/%d %H:%M\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "2013-12-10 10:30:00\n"
       ]
      }
     ],
     "prompt_number": 41
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u4f7f\u7528\u8cc7\u6599\u5ba3\u544a"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "tenderer_dic = {\"\u5ee0\u5546\u4ee3\u78bc\":\"tenderer_code\", \"\u5ee0\u5546\u540d\u7a31\":\"tenderer_name\", \"\u662f\u5426\u5f97\u6a19\":\"awarded\", \"\u7d44\u7e54\u578b\u614b\":\"orgnization_type\"} \n",
      "result_dic = {} "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 28
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u627e\u5230award_table_tr_3"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "tender_table = soup.find('table', { \"class\" : \"table_block tender_table\" }) \n",
      "award_table_tr_3 = tender_table.findAll( 'tr',{'class':'award_table_tr_3'})"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 29
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u53d6\u5f97\u5ee0\u5546\u7de8\u865f"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import re \n",
      "m = re.match(r'\u6295\u6a19\u5ee0\u5546(\\d+)' ,'\u6295\u6a19\u5ee0\u5546101') \n",
      "print m.group(1)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "101\n"
       ]
      }
     ],
     "prompt_number": 30
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u7d71\u6574\u7a0b\u5f0f\u78bc"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "for tr in award_table_tr_3: \n",
      "    tb =tr.find('table') \n",
      "    grp_num = 0 \n",
      "    if tb is not None: \n",
      "        row = tb.findAll('tr') \n",
      "        for r in row: \n",
      "            th = r.find('th').text \n",
      "            m = re.match(r'\u6295\u6a19\u5ee0\u5546(\\d+)' ,th.encode('utf-8').strip()) \n",
      "            if m is not None: \n",
      "                grp_num = int(m.group(1)) \n",
      "                result_dic[grp_num] = {'tenderer_num': grp_num} \n",
      "            else: \n",
      "                if th.encode('utf-8').strip() in tenderer_dic: \n",
      "                    result_dic[grp_num][tenderer_dic[th.encode('utf-8').strip()]] = r.find('td').text.strip()\n",
      "#print result_dic\n",
      "\n",
      "for rec in result_dic:\n",
      "    for i in  result_dic[rec]:\n",
      "        print i, result_dic[rec][i]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "tenderer_name \u4e2d\u83ef\u96fb\u4fe1\u80a1\u4efd\u6709\u9650\u516c\u53f8\u81fa\u7063\u5357\u5340\u96fb\u4fe1\u5206\u516c\u53f8\n",
        "orgnization_type \u516c\u53f8\u767b\u8a18\n",
        "tenderer_code 96979960\n",
        "tenderer_num 1\n",
        "awarded \u662f\n"
       ]
      }
     ],
     "prompt_number": 39
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u8655\u7406\u91d1\u984d\u554f\u984c"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import re \n",
      "m = re.match( r\"\\$?-?([0-9,]+)\", '$352,111\u5143') \n",
      "print ''.join(m.group(1).split(','))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "352111\n"
       ]
      }
     ],
     "prompt_number": 41
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u6574\u7406\u54c1\u9805\u8ddf\u5f97\u6a19\u5ee0\u5546"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "award_dic = {}\n",
      "tender_award_dic = {\"\u5f97\u6a19\u5ee0\u5546\":\"awarded_tenderer\", \"\u9810\u4f30\u9700\u6c42\u6578\u91cf\":\"request_number\", \"\u6c7a\u6a19\u91d1\u984d\":\"tender_awarding_value\", \"\u5e95\u50f9\u91d1\u984d\":\"floor_price_value\"}\n",
      "award_table_tr_4 = tender_table.findAll( 'tr',{'class':'award_table_tr_4'})\t\t\n",
      "for tr in award_table_tr_4:\n",
      "\ttb =tr.find('table')\n",
      "\tif tb is not None:\n",
      "\t\trow = tb.findAll('tr')\n",
      "\t\titem_num = 0\n",
      "\t\tgrp_num = 0\n",
      "\t\tfor r in row:\n",
      "\t\t\tif r.find('th') is not None:\n",
      "\t\t\t\tth = r.find('th').text\n",
      "\t\t\t\t#print r.find('th').text\n",
      "\t\t\t\tm = re.match(r'\u7b2c(\\d+)\u54c1\u9805' ,th.encode('utf-8').strip())\n",
      "\t\t\t\tm2 = re.match(r'\u5f97\u6a19\u5ee0\u5546(\\d+)' ,th.encode('utf-8').strip())\n",
      "\t\t\t\tif m is not None:\n",
      "\t\t\t\t\titem_num =  int(m.group(1).decode('utf-8'))\n",
      "\t\t\t\t\taward_dic[item_num] = {}\n",
      "\t\t\t\telif m2 is not None:\n",
      "\t\t\t\t\tgrp_num =  int(m2.group(1).decode('utf-8'))\n",
      "\t\t\t\t\taward_dic[item_num][grp_num] = {}\t\t\t\n",
      "\t\t\t\telse:\n",
      "\t\t\t\t\tif th.encode('utf-8').strip() in tender_award_dic:\n",
      "\t\t\t\t\t\t#print th.encode('utf-8').strip().decode('utf-8')\n",
      "\t\t\t\t\t\tif th.encode('utf-8').strip() == \"\u6c7a\u6a19\u91d1\u984d\" or th.encode('utf-8').strip() == \"\u5e95\u50f9\u91d1\u984d\":\n",
      "\t\t\t\t\t\t\tm = re.match( r\"\\$?-?([0-9,]+)\", \"\".join( r.find('td').text.split()))\n",
      "\t\t\t\t\t\t\taward_dic[item_num][grp_num][tender_award_dic[th.encode('utf-8').strip()]] =  int(''.join(m.group(0).split(',')))\t\t\t\t\t\t\t\n",
      "\t\t\t\t\t\telse:\n",
      "\t\t\t\t\t\t\taward_dic[item_num][grp_num][tender_award_dic[th.encode('utf-8').strip()]] = r.find('td').text.strip()\n",
      "#print award_dic\n",
      "\n",
      "for rec in award_dic[1][1]:\n",
      "    print rec, award_dic[1][1][rec]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "request_number 1\n",
        "awarded_tenderer \u4e2d\u83ef\u96fb\u4fe1\u80a1\u4efd\u6709\u9650\u516c\u53f8\u81fa\u7063\u5357\u5340\u96fb\u4fe1\u5206\u516c\u53f8\n",
        "floor_price_value 305000\n",
        "tender_awarding_value 301500\n"
       ]
      }
     ],
     "prompt_number": 44
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u6b65\u9a5f 0 \u5f15\u7528\u5957\u4ef6"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# -*- coding: utf-8 -*- \n",
      "from bs4 import BeautifulSoup \n",
      "from datetime import datetime \n",
      "import os, sys, re"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 45
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u6b65\u9a5f1 \u5efa\u7acb\u8cc7\u6599\u62bd\u51fa\u51fd\u5f0f"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def get_response_element (file_name): \n",
      "    f = open(file_name, 'r') \n",
      "    response_text = f.read() \n",
      "    f.close() \n",
      "    soup = BeautifulSoup(''.join(response_text)) \n",
      "    tender_table = soup.find('table', { \"class\" : \"table_block tender_table\" }) \n",
      "    tr = tender_table.findAll('tr') \n",
      "    return tr"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 46
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u6b65\u9a5f2 \u5efa\u7acb\u6642\u9593\u8f49\u63db\u51fd\u5f0f"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def date_conversion(element):\n",
      "        m = re.match(r\"(\\d+/\\d+/\\d+)\",element)\n",
      "        if m is not None:\n",
      "                t = m.group(1).split('/', 1)\n",
      "                if t[0] != '':\n",
      "                        return datetime.strptime(str(int(t[0]) + 1911) + \"/\" + (''.join(t[1:]).split()[0] ), \"%Y/%m/%d\")\n",
      "        else:\n",
      "                return None"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 47
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u6b65\u9a5f3 \u5efa\u7acb\u91d1\u984d\u8f49\u63db\u51fd\u5f0f"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def money_conversion(element): \n",
      "    m = re.match( r\"\\$?-?([0-9,]+)\", \"\".join(element.split())) \n",
      "    return int(''.join(m.group(0).split(',')))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 48
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u6b65\u9a5f4 \u5efa\u7acb\u79fb\u9664\u7a7a\u767d\u51fd\u5f0f"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def remove_space(element): \n",
      "    return \"\".join(element.split())"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 49
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u6b65\u9a5f5 \u5efa\u7acb\u8f49\u63db\u5b57\u5178"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "name_map = {\"\u6a5f\u95dc\u4ee3\u78bc\":\"entity_code\", \"\u6a5f\u95dc\u540d\u7a31\":\"procuring_entity\",\"\u6a19\u6848\u6848\u865f\":\"job_number\",\"\u62db\u6a19\u65b9\u5f0f\":\"procurement_type\",\\\n",
      "    \"\u6c7a\u6a19\u65b9\u5f0f\":\"tender_awarding_type\",\"\u6a19\u6848\u540d\u7a31\":\"subject_of_procurement\", \"\u6c7a\u6a19\u8cc7\u6599\u985e\u5225\":\"attr_of_tender_awarding\", \\\n",
      "    \"\u6a19\u7684\u5206\u985e\":\"attr_of_procurement\", \"\u9810\u7b97\u91d1\u984d\":\"budget_value\", \"\u958b\u6a19\u6642\u9593\":\"opening_date\",\"\u6c7a\u6a19\u516c\u544a\u65e5\u671f\":\"tender_awarding_announce_date\",\\\n",
      "    \"\u6b78\u5c6c\u8a08\u756b\u985e\u5225\":\"project_type\",\"\u7e3d\u6c7a\u6a19\u91d1\u984d\":\"total_tender_awarding_value\",\"\u5e95\u50f9\u91d1\u984d\":\"floor_price_value\",\"\u6c7a\u6a19\u65e5\u671f\":\"tender_awarding_date\", \"pkAtmMain\":\"pkAtmMain\"}\n",
      "\n",
      "tender_awards_map = {\"\u6a5f\u95dc\u4ee3\u78bc\":remove_space, \"\u6a5f\u95dc\u540d\u7a31\":remove_space, \"\u6a19\u6848\u6848\u865f\":remove_space, \\\n",
      "    \"\u62db\u6a19\u65b9\u5f0f\":remove_space,\"\u6c7a\u6a19\u65b9\u5f0f\":remove_space,\"\u6a19\u6848\u540d\u7a31\":remove_space, \"\u6c7a\u6a19\u8cc7\u6599\u985e\u5225\":remove_space, \\\n",
      "    \"\u6a19\u7684\u5206\u985e\":remove_space, \"\u9810\u7b97\u91d1\u984d\":money_conversion, \"\u958b\u6a19\u6642\u9593\":date_conversion,\"\u6b78\u5c6c\u8a08\u756b\u985e\u5225\":remove_space, \\\n",
      "    \"\u7e3d\u6c7a\u6a19\u91d1\u984d\":money_conversion,\"\u5e95\u50f9\u91d1\u984d\":money_conversion,\"\u6c7a\u6a19\u65e5\u671f\":date_conversion, \"\u6c7a\u6a19\u516c\u544a\u65e5\u671f\":date_conversion}\n",
      "\n",
      "tender_award_item_map = {\"\u5f97\u6a19\u5ee0\u5546\":\"awarded_tenderer\", \"\u9810\u4f30\u9700\u6c42\u6578\u91cf\":\"request_number\", \"\u6c7a\u6a19\u91d1\u984d\":\"tender_awarding_value\", \"\u5e95\u50f9\u91d1\u984d\":\"floor_price_value\"}\n",
      "tenderer_map = {\"\u5ee0\u5546\u4ee3\u78bc\":\"tenderer_code\", \"\u5ee0\u5546\u540d\u7a31\":\"tenderer_name\", \"\u662f\u5426\u5f97\u6a19\":\"awarded\", \"\u7d44\u7e54\u578b\u614b\":\"orgnization_type\"}"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 50
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u6b65\u9a5f6 \u6574\u7406\u6a5f\u95dc\u3001\u5df2\u516c\u544a\u3001\u6c7a\u6a19\u8cc7\u6599"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def get_award_info_dic (element):\n",
      "\treturned_dic = {}\n",
      "\tfor row in element:\n",
      "\t\tth = row.find('th')\n",
      "\t\tif th is not None and row.get('class') is not None:\n",
      "\t\t    th_name = th.text.encode('utf-8').strip()\n",
      "\t\t    if ('award_table_tr_1' or 'award_table_tr_2' or  'award_table_tr_6'  in row.get('class')) and  (th_name in  tender_awards_map):\n",
      "\t\t        returned_dic[name_map[th_name]]   =   tender_awards_map[th_name](row.find('td').text.strip())\n",
      "\treturn returned_dic"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 51
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u6b65\u9a5f7 \u6574\u7406\u6295\u6a19\u5ee0\u5546"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def get_tenderer_info_dic (element):\n",
      "\treturned_dic = {}\n",
      "\tgrp_num = 0\n",
      "\tfor tr in element:\n",
      "\t\tif  tr.get('class') is not None and tr.get('class')[0] == 'award_table_tr_3' and tr.find('table') is not None :\n",
      "\t\t\trow = tr.find('table').findAll('tr')          \n",
      "\t\t\tfor r in row:\n",
      "\t\t\t\tif r.find('th') is not None:\n",
      "\t\t\t\t\tth = r.find('th').text.encode('utf-8').strip()\n",
      "\t\t\t\t\tm = re.match(r'\u6295\u6a19\u5ee0\u5546(\\d+)' ,th)\n",
      "\t\t\t\t\tif m is not None:\n",
      "\t\t\t\t\t\tgrp_num =  int(m.group(1).decode('utf-8'))\n",
      "\t\t\t\t\t\treturned_dic[grp_num] = {'tenderer_num': grp_num}\n",
      "\t\t\t\t\telse:\n",
      "\t\t\t\t\t\tif th in tenderer_map:\n",
      "\t\t\t\t\t\t\treturned_dic[grp_num][tenderer_map[th]] = r.find('td').text.strip()\n",
      "\treturn returned_dic"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 52
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u6b65\u9a5f8 \u6574\u7406\u6c7a\u6a19\u54c1\u9805"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def get_tender_award_item_dic (element):\n",
      "\treturned_dic = {}\n",
      "\titem_num = 0\n",
      "\tgrp_num = 0\n",
      "\tfor tr in element:\n",
      "\t\tif tr.get('class') is not None and tr.get('class')[0] == 'award_table_tr_4' and tr.find('table') is not None:\n",
      "\t\t\trow = tr.find('table').findAll('tr')\n",
      "\t\t\tfor r in row:\n",
      "\t\t\t\tif r.find('th') is not None:\n",
      "\t\t\t\t\tth = r.find('th').text.encode('utf-8').strip()\n",
      "\t\t\t\t\tm = re.match(r'\u7b2c(\\d+)\u54c1\u9805' ,th)\n",
      "\t\t\t\t\tm2 = re.match(r'\u5f97\u6a19\u5ee0\u5546(\\d+)' ,th)\n",
      "\t\t\t\t\tif m is not None:\n",
      "\t\t\t\t\t\titem_num =  int(m.group(1).decode('utf-8'))\n",
      "\t\t\t\t\t\treturned_dic[item_num] = {}\n",
      "\t\t\t\t\telif m2 is not None:\n",
      "\t\t\t\t\t\tgrp_num =  int(m2.group(1).decode('utf-8'))\n",
      "\t\t\t\t\t\treturned_dic[item_num][grp_num] = {}\t\t\t\n",
      "\t\t\t\t\telse:\n",
      "\t\t\t\t\t\tif th in tender_award_item_map:\n",
      "\t\t\t\t\t\t\tif th == \"\u6c7a\u6a19\u91d1\u984d\" or th == \"\u5e95\u50f9\u91d1\u984d\":\n",
      "\t\t\t\t\t\t\t\treturned_dic[item_num][grp_num][tender_award_item_map[th]] = money_conversion(r.find('td').text) \t\t\t\t\t\t\t\n",
      "\t\t\t\t\t\t\telse:\n",
      "\t\t\t\t\t\t\t\treturned_dic[item_num][grp_num][tender_award_item_map[th]] = r.find('td').text.strip()\n",
      "\treturn returned_dic"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 53
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u6b65\u9a5f9 \u700f\u89bd\u6574\u7406\u904e\u7684\u8cc7\u6599"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "ele = get_response_element('gov3/51239291_0607L1030328.txt') \n",
      "award_info_dic = get_award_info_dic(ele) \n",
      "tenderer_info_dic = get_tenderer_info_dic(ele) \n",
      "tender_award_item_dic = get_tender_award_item_dic(ele)\n",
      "#\n",
      "#for i in award_info_dic:\n",
      "#    print i, award_info_dic[i]\n",
      "for i in  tender_award_item_dic[1][1]:\n",
      "    print i , tender_award_item_dic[1][1][i]\n",
      "#print tender_award_item_dic "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        " request_number 1\n",
        "awarded_tenderer \u4e2d\u83ef\u96fb\u4fe1\u80a1\u4efd\u6709\u9650\u516c\u53f8\u81fa\u7063\u5357\u5340\u96fb\u4fe1\u5206\u516c\u53f8\n",
        "floor_price_value 305000\n",
        "tender_awarding_value 301500\n"
       ]
      }
     ],
     "prompt_number": 60
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u4f7f\u7528python \u9023\u7d50SQLite"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import sqlite3 as lite \n",
      "import sys \n",
      "con = None \n",
      "dbname = 'nsb.sqlite'\n",
      "try: \n",
      "    con = lite.connect(dbname) # connect to database \n",
      "    cur = con.cursor() # create cursor \n",
      "    cur.execute('SELECT SQLITE_VERSION()') # selece database version  \n",
      "    data = cur.fetchone() # fetch one data at a time \n",
      "    print \"SQLite version: %s\" % data \n",
      "except lite.Error, e: \n",
      "    print \"Error %s:\" % e.args[0] \n",
      "    sys.exit(1) \n",
      "finally: \n",
      "    if con: \n",
      "        con.close()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "SQLite version: 3.7.9\n"
       ]
      }
     ],
     "prompt_number": 61
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u4f7f\u7528python \u9023\u7d50SQLite (2)"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import sqlite3 as lite \n",
      "import sys \n",
      "con = lite.connect(\"test.db\") \n",
      "with con: \n",
      "    cur = con.cursor() # Drop Table If Exisits \n",
      "    cur.execute(\"DROP TABLE IF EXISTS PhoneAddress\") \n",
      "    cur.execute(\"CREATE TABLE PhoneAddress(phone CHAR(10) PRIMARY KEY, address TEXT, name TEXT unique, age INT NOT NULL)\") \n",
      "    cur.execute(\"INSERT INTO PhoneAddress VALUES('0912173381','United State','Jhon Doe',53)\") \n",
      "    cur.execute(\"INSERT INTO PhoneAddress VALUES('0928375018','Tokyo Japan','MuMu Cat',6)\") \n",
      "    cur.execute(\"INSERT INTO PhoneAddress VALUES('0957209108','Taipei','Richard',29)\") \n",
      "    cur.execute(\"SELECT phone,address FROM PhoneAddress\") \n",
      "    data = cur.fetchall() \n",
      "    for rec in data: \n",
      "        print rec[0], rec[1] \n",
      "con.close()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "0912173381 United State\n",
        "0928375018 Tokyo Japan\n",
        "0957209108 Taipei\n"
       ]
      }
     ],
     "prompt_number": 62
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u4f7f\u7528python \u9023\u7d50SQLite (3)"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import sqlite3 as lite \n",
      "import sys \n",
      "con = lite.connect(\"test.db\") \n",
      "with con:\n",
      "    \n",
      "    cur = con.cursor()\n",
      "    # Read Meta Information\n",
      "    cur.execute(\"PRAGMA table_info(PhoneAddress)\")    \n",
      "    rows = cur.fetchall()\n",
      "    for row in rows:\n",
      "        print row\n",
      "    \n",
      "con.close() "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "(0, u'phone', u'CHAR(10)', 0, None, 1)\n",
        "(1, u'address', u'TEXT', 0, None, 0)\n",
        "(2, u'name', u'TEXT', 0, None, 0)\n",
        "(3, u'age', u'INT', 1, None, 0)\n"
       ]
      }
     ],
     "prompt_number": 63
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "fetchone"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import sqlite3 as lite \n",
      "import sys \n",
      "con = lite.connect(\"test.db\") \n",
      "with con: \n",
      "    cur = con.cursor() # Drop Table If Exisits \n",
      "    cur.execute(\"SELECT phone,address FROM PhoneAddress\") \n",
      "    data = cur.fetchone() \n",
      "    print data[0], data[1] \n",
      "    data1 = cur.fetchone() \n",
      "    print data1[0], data1[1] \n",
      "con.close() "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "0912173381 United State\n",
        "0928375018 Tokyo Japan\n"
       ]
      }
     ],
     "prompt_number": 65
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "fetchall"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import sqlite3 as lite \n",
      "import sys \n",
      "con = lite.connect(\"test.db\") \n",
      "with con: \n",
      "    cur = con.cursor() # Drop Table If Exisits \n",
      "    cur.execute(\"SELECT phone,address FROM PhoneAddress\")\n",
      "    data = cur.fetchone() \n",
      "    print data[0], data[1] \n",
      "    rows = cur.fetchall()\n",
      "    for row in rows:\n",
      "        print row\n",
      "\n",
      "con.close() "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "0912173381 United State\n",
        "(u'0928375018', u'Tokyo Japan')\n",
        "(u'0957209108', u'Taipei')\n"
       ]
      }
     ],
     "prompt_number": 67
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u5efa\u7acb\u8cc7\u6599\u5eab\u9023\u7dda"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# -*- coding: utf-8 -*- \n",
      "import sqlite3\n",
      "\n",
      "db = sqlite3.connect('tender.db') \n",
      "cur = db.cursor()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 68
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u5efa\u7acb Tender_awards"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "cur.execute('''CREATE TABLE IF NOT EXISTS Tender_awards(\n",
      "                id INTEGER PRIMARY KEY,\n",
      "                pkAtmMain TEXT,\n",
      "                procuring_entity TEXT,\n",
      "                entity_code TEXT,\n",
      "                attr_of_procurement TEXT,\n",
      "                opening_date DATETIME,\n",
      "                procurement_type TEXT,\n",
      "                tender_awarding_type TEXT,\n",
      "                project_type TEXT,\n",
      "                subject_of_procurement TEXT,\n",
      "                job_number TEXT,\n",
      "                budget_value BIGINTEGER,\n",
      "                attr_of_tender_awarding TEXT,\n",
      "                floor_price_value BIGINTEGER,\n",
      "                tender_awarding_announce_date DATETIME,\n",
      "                tender_awarding_date DATETIME,\n",
      "                total_tender_awarding_value BIGINTEGER\n",
      "                )''')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 69,
       "text": [
        "<sqlite3.Cursor at 0x980d6a0>"
       ]
      }
     ],
     "prompt_number": 69
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u5efa\u7acb Tenderer"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "cur.execute('''CREATE TABLE IF NOT EXISTS Tenderer(\n",
      "    id INTEGER PRIMARY KEY,\n",
      "    pkAtmMain TEXT,\n",
      "    job_number TEXT,\n",
      "    tenderer_code TEXT,\n",
      "    tenderer_name TEXT,                \n",
      "    awarded TEXT,\n",
      "    orgnization_type TEXT,\n",
      "    tenderer_num INT\n",
      "    )''')\t"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 70,
       "text": [
        "<sqlite3.Cursor at 0x980d6a0>"
       ]
      }
     ],
     "prompt_number": 70
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u5efa\u7acbTender_award_item"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "cur.execute('''CREATE TABLE IF NOT EXISTS Tender_award_item(\n",
      "    id INTEGER PRIMARY KEY,\n",
      "    pkAtmMain TEXT,\n",
      "    job_number TEXT,\n",
      "    awarded_tenderer TEXT,\n",
      "    request_number INT,                \n",
      "    tender_awarding_value BIGINT,\n",
      "    floor_price_value BIGINT,\n",
      "    item_num INT,\n",
      "    awarded_num INT\n",
      "    )''')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 71,
       "text": [
        "<sqlite3.Cursor at 0x980d6a0>"
       ]
      }
     ],
     "prompt_number": 71
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u6aa2\u8996dictionary \u5167\u7684\u8cc7\u6599"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "ele = get_response_element('gov3/51239291_0607L1030328.txt') \n",
      "award_info_dic = get_award_info_dic(ele) \n",
      "tenderer_info_dic = get_tenderer_info_dic(ele) \n",
      "tender_award_item_dic = get_tender_award_item_dic(ele)\n",
      "\n",
      "print award_info_dic\n",
      "print tenderer_info_dic \n",
      "print tender_award_item_dic "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "{'opening_date': datetime.datetime(2014, 3, 28, 0, 0), 'total_tender_awarding_value': 301500, 'tender_awarding_date': datetime.datetime(2014, 3, 28, 0, 0), 'entity_code': u'3.13.30', 'procurement_type': u'\\u9650\\u5236\\u6027\\u62db\\u6a19(\\u672a\\u7d93\\u516c\\u958b\\u8a55\\u9078\\u6216\\u516c\\u958b\\u5fb5\\u6c42)', 'procuring_entity': u'\\u81fa\\u7063\\u7cd6\\u696d\\u80a1\\u4efd\\u6709\\u9650\\u516c\\u53f8', 'attr_of_procurement': u'<\\u52de\\u52d9\\u985e>752\\u96fb\\u4fe1\\u670d\\u52d9', 'tender_awarding_announce_date': datetime.datetime(2014, 4, 30, 0, 0), 'subject_of_procurement': u'\\u96fb\\u8a71\\u4ea4\\u63db\\u6a5f\\u7dad\\u8b77\\u4fdd\\u990a', 'project_type': u'\\u975e\\u5c6c\\u611b\\u53f0\\u5341\\u4e8c\\u9805\\u8a08\\u756b', 'attr_of_tender_awarding': u'\\u6c7a\\u6a19\\u516c\\u544a', 'budget_value': 337500, 'floor_price_value': 305000, 'job_number': u'0607L1030328', 'tender_awarding_type': u'\\u6700\\u4f4e\\u6a19'}\n",
        "{1: {'tenderer_name': u'\\u4e2d\\u83ef\\u96fb\\u4fe1\\u80a1\\u4efd\\u6709\\u9650\\u516c\\u53f8\\u81fa\\u7063\\u5357\\u5340\\u96fb\\u4fe1\\u5206\\u516c\\u53f8', 'orgnization_type': u'\\u516c\\u53f8\\u767b\\u8a18', 'tenderer_code': u'96979960', 'tenderer_num': 1, 'awarded': u'\\u662f'}}\n",
        "{1: {1: {'request_number': u'1', 'awarded_tenderer': u'\\u4e2d\\u83ef\\u96fb\\u4fe1\\u80a1\\u4efd\\u6709\\u9650\\u516c\\u53f8\\u81fa\\u7063\\u5357\\u5340\\u96fb\\u4fe1\\u5206\\u516c\\u53f8', 'floor_price_value': 305000, 'tender_awarding_value': 301500}}}\n"
       ]
      }
     ],
     "prompt_number": 72
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u5c07\u5b57\u5178\u585e\u5165\u8cc7\u6599\u5eab"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#print ', '.join(award_info_dic.keys())\n",
      "columns = ', '.join(award_info_dic.keys()) \n",
      "#', '.join('?' * len(award_info_dic))\n",
      "placeholders = ', '.join('?' * len(award_info_dic)) \n",
      "sql = 'INSERT INTO Tender_awards ({}) VALUES ({})'.format(columns, placeholders) \n",
      "#print sql\n",
      "#cur.execute(sql, award_info_dic.values())\n",
      "db.commit()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 82
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "print tenderer_info_dic"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "{1: {'tenderer_name': u'\\u4e2d\\u83ef\\u96fb\\u4fe1\\u80a1\\u4efd\\u6709\\u9650\\u516c\\u53f8\\u81fa\\u7063\\u5357\\u5340\\u96fb\\u4fe1\\u5206\\u516c\\u53f8', 'orgnization_type': u'\\u516c\\u53f8\\u767b\\u8a18', 'tenderer_code': u'96979960', 'tenderer_num': 1, 'awarded': u'\\u662f'}}\n"
       ]
      }
     ],
     "prompt_number": 83
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u5efa\u7acbinsert \u6307\u4ee4"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "tenderer_sql = 'INSERT INTO Tenderer (pkAtmMain, job_number, tenderer_code, tenderer_name, awarded, orgnization_type, tenderer_num) VALUES (?,?,?,?,?,?,?) '\n",
      "\n",
      "filename = \"gov3/51239291_0607L1030328.txt\"\n",
      "file_param = filename.split(\".txt\")[0].split(\"_\") \n",
      "pkAtmMain = file_param[0] \n",
      "job_number = '_'.join(file_param[1:]) \n",
      "\n",
      "for ele in tenderer_info_dic : \n",
      "        cur.execute(tenderer_sql , (pkAtmMain, job_number,  \n",
      "          tenderer_info_dic[ele][\"tenderer_code\"],   \n",
      "          tenderer_info_dic[ele][\"tenderer_name\"], \n",
      "          tenderer_info_dic[ele][\"awarded\"], \n",
      "          tenderer_info_dic[ele][\"orgnization_type\"], ele))\n",
      "db.commit() \n",
      "db.close()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 84
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u591a\u7b46\u8cc7\u6599\u63d2\u5165"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import sqlite3 as lite\n",
      "\n",
      "salary = (\n",
      "    (1 ,          'Paul'  ,      32 ,         'California',  20000),\n",
      "    (2 ,          'Allen' ,      25 ,         'Texas'     ,  15000),\n",
      "    (3 ,          'Teddy' ,      23 ,         'Norway'    ,  20000),\n",
      "    (4 ,          'Mark'  ,      25 ,         'Rich-Mond' ,  65000),\n",
      "    (5 ,          'David' ,      27 ,         'Texas'     ,  85000),\n",
      "    (6 ,          'Kim'   ,      22 ,         'South-Hall',  45000),\n",
      "    (7 ,          'James' ,      24 ,         'Houston'   ,  10000)\n",
      ")\n",
      "department = (\n",
      "(1,           'IT Billing'  ,1),\n",
      "(2,           'Engineerin'  ,2),\n",
      "(3,           'Finance'     ,7),\n",
      "(4,           'Finance'     ,5),\n",
      "(5,           'Finance'     ,6)\n",
      "\n",
      ")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 85
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "execute v.s. executemany"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "con = lite.connect('test.db')\n",
      "\n",
      "with con:\n",
      "    \n",
      "    cur = con.cursor()    \n",
      "    \n",
      "    cur.execute(\"DROP TABLE IF EXISTS Salary\")\n",
      "    cur.execute(\"DROP TABLE IF EXISTS Department\")\n",
      "    \n",
      "    cur.execute(\"CREATE TABLE Salary(ID INT, NAME TEXT, AGE INT, ADDRESS TEXT, SALARY INT, FOREIGN KEY(ID) REFERENCES QOO(EMP_ID))\")\n",
      "    cur.executemany(\"INSERT INTO Salary VALUES(?, ?, ?, ?, ?)\", salary)\n",
      "    \n",
      "    \n",
      "    cur.execute(\"CREATE TABLE Department(ID INT PRIMARY KEY, DEPT CHAR(50), EMP_ID INT)\")\n",
      "    cur.executemany(\"INSERT INTO Department VALUES(?, ?, ?)\", department)\n",
      "con.close() "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 86
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "execute \u6548\u80fd"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import time\n",
      "dbname = 'test.db'\n",
      "con = lite.connect(dbname)\n",
      "\n",
      "items = tuple(range(1,100000))\n",
      "with con:\n",
      "    \n",
      "    cur = con.cursor()    \n",
      "    \n",
      "    cur.execute(\"DROP TABLE IF EXISTS ptest\")\n",
      "    cur.execute(\"CREATE TABLE ptest(ID INT)\")\n",
      "    start = time.time()\n",
      "    for i in range(0,100000):\n",
      "        cur.execute(\"INSERT INTO ptest VALUES(%s)\"%(i))\n",
      "    end = time.time()\n",
      "    print end -start\n",
      "    cur.execute(\"select count(1) from ptest\")\n",
      "    rows = cur.fetchone()\n",
      "    print rows\n",
      "\n",
      "con.close() "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "1.66068100929\n",
        "(100000,)\n"
       ]
      }
     ],
     "prompt_number": 87
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "executemany \u6548\u80fd"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "dbname = 'test.db'\n",
      "con = lite.connect(dbname)\n",
      "with con:\n",
      "    \n",
      "    cur = con.cursor()    \n",
      "    \n",
      "    cur.execute(\"DROP TABLE IF EXISTS ptest\")\n",
      "    cur.execute(\"CREATE TABLE ptest(ID INT)\")\n",
      "    start = time.time()\n",
      "    cur.executemany(\"INSERT INTO ptest VALUES(?)\",((id_, ) for id_ in xrange(100000)))\n",
      "    end = time.time()\n",
      "    print end - start\n",
      "    cur.execute(\"select count(1) from ptest\")\n",
      "    rows = cur.fetchone()\n",
      "    print rows\n",
      "\n",
      "con.close() "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "0.21133685112\n",
        "(100000,)\n"
       ]
      }
     ],
     "prompt_number": 89
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Commit v.s. Rollback"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "con = lite.connect('test.db')\n",
      "\n",
      "Phone = '0928375018'\n",
      "Age = 9 \n",
      "\n",
      "with con:\n",
      "\n",
      "    cur = con.cursor()    \n",
      "\n",
      "    cur.execute(\"UPDATE PhoneAddress SET age=? WHERE phone=?\", (Age, Phone))        \n",
      "    con.commit()\n",
      "    #con.rollback()\n",
      "    \n",
      "    cur.execute(\"select * from PhoneAddress where phone=?\", (Phone,)) \n",
      "    rows = cur.fetchall()\n",
      "\n",
      "    for row in rows:\n",
      "        print row\n",
      "con.close() "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "(u'0928375018', u'Tokyo Japan', u'MuMu Cat', 9)\n"
       ]
      }
     ],
     "prompt_number": 91
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u5efa\u7acb\u51fd\u5f0f insert_award_info"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def insert_award_info (cur, data_dic, filename):\n",
      "\tfile_param =  filename.split(\".txt\")[0].split(\"_\")\n",
      "\tpkAtmMain = file_param[0]\n",
      "\tdata_dic['pkAtmMain'] = pkAtmMain\n",
      "\tcolumns = ', '.join(data_dic.keys())\n",
      "\tplaceholders = ', '.join('?' * len(data_dic))\n",
      "\tsql = 'INSERT INTO Tender_awards ({}) VALUES ({})'.format(columns, placeholders)\t\n",
      "\tcur.execute(sql, data_dic.values())"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 92
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u5efa\u7acb\u51fd\u5f0f insert_tenderer_info"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "tenderer_sql = 'INSERT INTO Tenderer (pkAtmMain, job_number, tenderer_code, tenderer_name, awarded, orgnization_type, tenderer_num) VALUES (?,?,?,?,?,?,?)'\n",
      "\n",
      "def insert_tenderer_info (cur, data_dic, filename, sql):\n",
      "\tfile_param =  filename.split(\".txt\")[0].split(\"_\")\n",
      "\tpkAtmMain = file_param[0]\n",
      "\tjob_number = '_'.join(file_param[1:])\n",
      "\tfor ele in data_dic:\t\t\t\n",
      "\t\tcur.execute(sql, (pkAtmMain, job_number,  data_dic[ele][\"tenderer_code\"], data_dic[ele][\"tenderer_name\"],\n",
      "\t\t  data_dic[ele][\"awarded\"], data_dic[ele][\"orgnization_type\"], ele))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 93
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u5efa\u7acb\u51fd\u5f0f insert_tenderawarditem_info"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "tenderawarditem_sql = 'INSERT INTO Tender_award_item (pkAtmMain, job_number, awarded_tenderer, request_number, tender_awarding_value, floor_price_value, item_num, awarded_num) VALUES (?,?,?,?,?,?,?,?)' \n",
      "\n",
      "def insert_tenderawarditem_info(cur, data_dic, filename, sql):\n",
      "\tfile_param =  filename.split(\".txt\")[0].split(\"_\")\n",
      "\tpkAtmMain = file_param[0]\n",
      "\tjob_number = '_'.join(file_param[1:])\n",
      "\tfor item in data_dic:\n",
      "\t\tfor grp in data_dic[item]:\n",
      "\t\t\tif 'floor_price_value' not in data_dic[item][grp]:\n",
      "\t\t\t\tdata_dic[item][grp]['floor_price_value'] = None\n",
      "\t\t\tif 'tender_awarding_value' not in data_dic[item][grp]:\n",
      "\t\t\t\tdata_dic[item][grp]['tender_awarding_value'] = None\n",
      "\t\t\tcur.execute(sql, (pkAtmMain, job_number,  data_dic[item][grp][\"awarded_tenderer\"],  data_dic[item][grp][\"request_number\"],\n",
      "\t\t           data_dic[item][grp][\"tender_awarding_value\"],  data_dic[item][grp][\"floor_price_value\"], item, grp)) "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 94
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u8a2d\u5b9a\u8868\u683c"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def setup_table(cur):\n",
      "\tcur.execute('''CREATE TABLE IF NOT EXISTS Tender_awards(\n",
      "                id INTEGER PRIMARY KEY,\n",
      "                pkAtmMain TEXT,\n",
      "                procuring_entity TEXT,\n",
      "                entity_code TEXT,\n",
      "                attr_of_procurement TEXT,\n",
      "                opening_date DATETIME,\n",
      "                procurement_type TEXT,\n",
      "                tender_awarding_type TEXT,\n",
      "                project_type TEXT,\n",
      "                subject_of_procurement TEXT,\n",
      "                job_number TEXT,\n",
      "                budget_value BIGINTEGER,\n",
      "                attr_of_tender_awarding TEXT,\n",
      "                floor_price_value BIGINTEGER,\n",
      "                tender_awarding_announce_date DATETIME,\n",
      "                tender_awarding_date DATETIME,\n",
      "                total_tender_awarding_value BIGINTEGER\n",
      "                )''')\n",
      "\t\t\n",
      "\tcur.execute('''CREATE TABLE IF NOT EXISTS Tenderer(\n",
      "                id INTEGER PRIMARY KEY,\n",
      "                pkAtmMain TEXT,\n",
      "                job_number TEXT,\n",
      "\t\ttenderer_code TEXT,\n",
      "\t\ttenderer_name TEXT,                \n",
      "\t\tawarded TEXT,\n",
      "\t\torgnization_type TEXT,\n",
      "\t\ttenderer_num INT\n",
      "                )''')\t\t\n",
      "\tcur.execute('''CREATE TABLE IF NOT EXISTS Tender_award_item(\n",
      "                id INTEGER PRIMARY KEY,\n",
      "                pkAtmMain TEXT,\n",
      "                job_number TEXT,\t\t\n",
      "\t\tawarded_tenderer TEXT,\n",
      "\t\trequest_number INT,                \n",
      "\t\ttender_awarding_value BIGINT,\n",
      "\t\tfloor_price_value BIGINT,\n",
      "\t\titem_num INT,\n",
      "\t\tawarded_num INT\n",
      "                )''')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 95
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u7a0b\u5f0f\u6d41\u7a0b"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import sqlite3\n",
      "import os\n",
      "db = sqlite3.connect('tender2.db') \n",
      "cur = db.cursor() \n",
      "path = \"gov3/\" \n",
      "dirs = os.listdir( path ) \n",
      "setup_table(cur)\n",
      "for filename in dirs: \n",
      "    ele = get_response_element(path + filename) \n",
      "    award_info_dic = get_award_info_dic(ele) \n",
      "    tenderer_info_dic = get_tenderer_info_dic(ele) \n",
      "    tender_award_item_dic = get_tender_award_item_dic(ele) \n",
      "    insert_award_info(cur, award_info_dic, filename) \n",
      "    insert_tenderer_info(cur, tenderer_info_dic, filename, tenderer_sql) \n",
      "    insert_tenderawarditem_info(cur, tender_award_item_dic, filename, tenderawarditem_sql) \n",
      "    db.commit() \n",
      "db.close() "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 96
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "\u67e5\u8a62\u8cc7\u6599"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import sqlite3\n",
      "import os\n",
      "db = sqlite3.connect('tender.db') \n",
      "cur = db.cursor() \n",
      "cur.execute('select * from Tender_awards')\n",
      "row = cur.fetchall()\n",
      "for r in row[1:5]:\n",
      "    print r[0], r[1], r[2],r[3],r[4],r[5]\n",
      "db.close()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "2 None \u81fa\u7063\u7cd6\u696d\u80a1\u4efd\u6709\u9650\u516c\u53f8 3.13.30 <\u52de\u52d9\u985e>752\u96fb\u4fe1\u670d\u52d9 2014-03-28 00:00:00\n",
        "3 51239883 \u65b0\u5317\u5e02\u7acb\u5927\u89c0\u570b\u6c11\u4e2d\u5b78 3.82.6.2 <\u52de\u52d9\u985e>641\u65c5\u9928\u53ca\u5176\u4ed6\u4f4f\u5bbf\u670d\u52d9 2013-10-23 00:00:00\n",
        "4 51241099 \u5f70\u5316\u7e23\u653f\u5e9c 3.76.47 <\u52de\u52d9\u985e>95\u6703\u54e1\u7d44\u7e54\u670d\u52d9 2014-04-15 00:00:00\n",
        "5 51241135 \u6843\u5712\u7e23\u6843\u5712\u5e02\u516c\u6240 3.76.43.51 <\u52de\u52d9\u985e>96\u5a1b\u6a02,\u6587\u5316,\u9ad4\u80b2\u670d\u52d9 2014-04-25 00:00:00\n"
       ]
      }
     ],
     "prompt_number": 149
    }
   ],
   "metadata": {}
  }
 ]
}