climax-database-test.ipynb

{
 "metadata": {
  "name": "",
  "signature": "sha256:e7ac68720b1e38ff1c45a20af1d49439ec187e149e12546be810a5f6b6cd6e20"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "from climax import login"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 1
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "from climax.climate_data import PREC_QUERY, IRRI_QUERY, DAYLIGHT_QUERY"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 2
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# database = login.get_db()\n",
      "local_database = login.get_db(host='localhost', user='root', passwd='mariadb', db='trost_prod')\n",
      "# cursor = database.cursor()\n",
      "cursor = local_database.cursor()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 3
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "cultureID = 44443\n",
      "# cultureID = 56878"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 4
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "cursor.execute(DAYLIGHT_QUERY % {'CULTURE_ID': cultureID})\n",
      "light_data = cursor.fetchall()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 6
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "light_data[0]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 10,
       "text": [
        "(datetime.datetime(2011, 4, 15, 0, 0), 0.0)"
       ]
      }
     ],
     "prompt_number": 10
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "cursor.execute(PREC_QUERY % {'CULTURE_ID': cultureID})\n",
      "prec_rows = cursor.fetchall()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 9
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# {date: precip for (date, precip) in prec_rows}"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 10
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "cursor.execute(PREC_QUERY % {'CULTURE_ID': cultureID})\n",
      "\n",
      "\n",
      "# precipitation = dict(map(lambda x: (x[0], x[1:]), [row for row in cursor.fetchall()]))\n",
      "precipitation = {date: precip for (date, precip) in cursor.fetchall()}"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 11
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# precipitation"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 12
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "cursor.execute(IRRI_QUERY % {'CULTURE_ID': cultureID})\n",
      "irrigation_rows = cursor.fetchall()\n",
      "\n",
      "#maps datetime.date to (float, long)\n",
      "# irrigation = dict(map(lambda x: (x[0], x[1:]),\n",
      "#                       [row for row in cursor.fetchall()]))\n",
      "\n"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 13
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "irrigation_rows"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 14,
       "text": [
        "((datetime.date(2011, 5, 5), 6.16, 169L),\n",
        " (datetime.date(2011, 5, 5), 6.16, 170L),\n",
        " (datetime.date(2011, 5, 16), 3.08, 169L),\n",
        " (datetime.date(2011, 5, 26), 9.24, 169L),\n",
        " (datetime.date(2011, 5, 31), 3.08, 169L),\n",
        " (datetime.date(2011, 5, 31), 3.08, 170L),\n",
        " (datetime.date(2011, 6, 1), 6.16, 169L),\n",
        " (datetime.date(2011, 6, 4), 9.24, 169L),\n",
        " (datetime.date(2011, 6, 6), 3.08, 169L),\n",
        " (datetime.date(2011, 6, 6), 3.08, 170L),\n",
        " (datetime.date(2011, 6, 15), 9.24, 169L),\n",
        " (datetime.date(2011, 6, 18), 9.24, 169L),\n",
        " (datetime.date(2011, 6, 29), 9.24, 169L))"
       ]
      }
     ],
     "prompt_number": 14
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "from collections import defaultdict\n",
      "\n",
      "irrigation_dict = defaultdict(list)\n",
      "for date, irri_amount, treatment_id in irrigation_rows:\n",
      "    irrigation_dict[date].append( (irri_amount, treatment_id) )"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 15
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "for date in irrigation_dict:\n",
      "    print irrigation_dict[date]\n",
      "    "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "[(9.24, 169L)]\n",
        "[(3.08, 169L), (3.08, 170L)]\n",
        "[(6.16, 169L)]\n",
        "[(9.24, 169L)]\n",
        "[(9.24, 169L)]\n",
        "[(3.08, 169L), (3.08, 170L)]\n",
        "[(6.16, 169L), (6.16, 170L)]\n",
        "[(9.24, 169L)]\n",
        "[(9.24, 169L)]\n",
        "[(3.08, 169L)]\n"
       ]
      }
     ],
     "prompt_number": 16
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "PREC_QUERY"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 17,
       "text": [
        "'SELECT DATE(P.datum), P.amount FROM precipitation P JOIN cultures C ON P.location_id = C.location_id WHERE C.id = %(CULTURE_ID)i ORDER BY P.datum;'"
       ]
      }
     ],
     "prompt_number": 17
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "cursor.execute('SHOW TABLES')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 18,
       "text": [
        "72L"
       ]
      }
     ],
     "prompt_number": 18
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pony"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 21
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "from pony import Database"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "ename": "ImportError",
       "evalue": "cannot import name Database",
       "output_type": "pyerr",
       "traceback": [
        "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m\n\u001b[1;31mImportError\u001b[0m                               Traceback (most recent call last)",
        "\u001b[1;32m<ipython-input-22-08c07f577eac>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[1;32mfrom\u001b[0m \u001b[0mpony\u001b[0m \u001b[1;32mimport\u001b[0m \u001b[0mDatabase\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
        "\u001b[1;31mImportError\u001b[0m: cannot import name Database"
       ]
      }
     ],
     "prompt_number": 22
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "from pony.orm import Database"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 23
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "db = Database()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 24
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "db.bind('mysql', host='localhost', user='root', passwd='mariadb', db='trost_prod')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 27
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "db.generate_mapping(check_tables=True, create_tables=False)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 28
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    }
   ],
   "metadata": {}
  }
 ]
}