write-to-database.ipynb

   1 {
   2  "cells": [
   3   {
   4    "cell_type": "code",
   5    "execution_count": 92,
   6    "metadata": {
   7     "collapsed": true
   8    },
   9    "outputs": [],
  10    "source": [
  11     "import configparser\n",
  12     "import xml.etree.ElementTree\n",
  13     "import psycopg2\n",
  14     "import re\n",
  15     "import datetime"
  16    ]
  17   },
  18   {
  19    "cell_type": "code",
  20    "execution_count": 2,
  21    "metadata": {
  22     "collapsed": false
  23    },
  24    "outputs": [
  25     {
  26      "data": {
  27       "text/plain": [
  28        "['dmarc.ini']"
  29       ]
  30      },
  31      "execution_count": 2,
  32      "metadata": {},
  33      "output_type": "execute_result"
  34     }
  35    ],
  36    "source": [
  37     "config = configparser.ConfigParser()\n",
  38     "config.read('dmarc.ini')"
  39    ]
  40   },
  41   {
  42    "cell_type": "code",
  43    "execution_count": 4,
  44    "metadata": {
  45     "collapsed": false
  46    },
  47    "outputs": [
  48     {
  49      "data": {
  50       "text/plain": [
  51        "['imap', 'database']"
  52       ]
  53      },
  54      "execution_count": 4,
  55      "metadata": {},
  56      "output_type": "execute_result"
  57     }
  58    ],
  59    "source": [
  60     "config.sections()"
  61    ]
  62   },
  63   {
  64    "cell_type": "code",
  65    "execution_count": 6,
  66    "metadata": {
  67     "collapsed": false
  68    },
  69    "outputs": [
  70     {
  71      "data": {
  72       "text/plain": [
  73        "['server', 'username', 'port', 'password']"
  74       ]
  75      },
  76      "execution_count": 6,
  77      "metadata": {},
  78      "output_type": "execute_result"
  79     }
  80    ],
  81    "source": [
  82     "[k for k in config['imap']]"
  83    ]
  84   },
  85   {
  86    "cell_type": "code",
  87    "execution_count": 7,
  88    "metadata": {
  89     "collapsed": false
  90    },
  91    "outputs": [
  92     {
  93      "data": {
  94       "text/plain": [
  95        "['server', 'database', 'username', 'password']"
  96       ]
  97      },
  98      "execution_count": 7,
  99      "metadata": {},
 100      "output_type": "execute_result"
 101     }
 102    ],
 103    "source": [
 104     "[k for k in config['database']]"
 105    ]
 106   },
 107   {
 108    "cell_type": "code",
 109    "execution_count": 25,
 110    "metadata": {
 111     "collapsed": true
 112    },
 113    "outputs": [],
 114    "source": [
 115     "def walk(node, indent=0):\n",
 116     "    for child in node:\n",
 117     "        if child.text:\n",
 118     "            txt = child.text.strip()\n",
 119     "        else:\n",
 120     "            txt = ''\n",
 121     "        print(' '*indent, child.tag, ':', txt)\n",
 122     "        walk(child, indent+2)"
 123    ]
 124   },
 125   {
 126    "cell_type": "code",
 127    "execution_count": 16,
 128    "metadata": {
 129     "collapsed": false
 130    },
 131    "outputs": [
 132     {
 133      "name": "stdout",
 134      "output_type": "stream",
 135      "text": [
 136       "google.com!njae.me.uk!1458777600!1458863999.xml  hotmail.com!njae.me.uk!1459011600!1459098000.xml  yahoo.com!njae.me.uk!1459036800!1459123199.xml\r\n",
 137       "google.com!njae.me.uk!1458950400!1459036799.xml  yahoo.com!njae.me.uk!1458864000!1458950399.xml\r\n"
 138      ]
 139     }
 140    ],
 141    "source": [
 142     "!ls *xml"
 143    ]
 144   },
 145   {
 146    "cell_type": "code",
 147    "execution_count": 17,
 148    "metadata": {
 149     "collapsed": false
 150    },
 151    "outputs": [
 152     {
 153      "data": {
 154       "text/plain": [
 155        "['google.com!njae.me.uk!1458777600!1458863999.xml',\n",
 156        " 'google.com!njae.me.uk!1458950400!1459036799.xml',\n",
 157        " 'hotmail.com!njae.me.uk!1459011600!1459098000.xml',\n",
 158        " 'yahoo.com!njae.me.uk!1458864000!1458950399.xml',\n",
 159        " 'yahoo.com!njae.me.uk!1459036800!1459123199.xml']"
 160       ]
 161      },
 162      "execution_count": 17,
 163      "metadata": {},
 164      "output_type": "execute_result"
 165     }
 166    ],
 167    "source": [
 168     "xmls = !ls *xml\n",
 169     "xmls"
 170    ]
 171   },
 172   {
 173    "cell_type": "code",
 174    "execution_count": 19,
 175    "metadata": {
 176     "collapsed": false
 177    },
 178    "outputs": [
 179     {
 180      "data": {
 181       "text/plain": [
 182        "{'google.com!njae.me.uk!1458777600!1458863999.xml': <Element 'feedback' at 0x7fa9a8169a98>,\n",
 183        " 'google.com!njae.me.uk!1458950400!1459036799.xml': <Element 'feedback' at 0x7fa9a8170a48>,\n",
 184        " 'hotmail.com!njae.me.uk!1459011600!1459098000.xml': <Element 'feedback' at 0x7fa9a80f4a48>,\n",
 185        " 'yahoo.com!njae.me.uk!1458864000!1458950399.xml': <Element 'feedback' at 0x7fa9a80f74a8>,\n",
 186        " 'yahoo.com!njae.me.uk!1459036800!1459123199.xml': <Element 'feedback' at 0x7fa9a80f7ea8>}"
 187       ]
 188      },
 189      "execution_count": 19,
 190      "metadata": {},
 191      "output_type": "execute_result"
 192     }
 193    ],
 194    "source": [
 195     "dmarc_reports = {f: xml.etree.ElementTree.fromstring(open(f).read()) for f in xmls}\n",
 196     "dmarc_reports"
 197    ]
 198   },
 199   {
 200    "cell_type": "code",
 201    "execution_count": 26,
 202    "metadata": {
 203     "collapsed": false
 204    },
 205    "outputs": [
 206     {
 207      "name": "stdout",
 208      "output_type": "stream",
 209      "text": [
 210       " report_metadata : \n",
 211       "   org_name : google.com\n",
 212       "   email : noreply-dmarc-support@google.com\n",
 213       "   extra_contact_info : https://support.google.com/a/answer/2466580\n",
 214       "   report_id : 2150510829392606201\n",
 215       "   date_range : \n",
 216       "     begin : 1458950400\n",
 217       "     end : 1459036799\n",
 218       " policy_published : \n",
 219       "   domain : njae.me.uk\n",
 220       "   adkim : r\n",
 221       "   aspf : r\n",
 222       "   p : none\n",
 223       "   sp : none\n",
 224       "   pct : 100\n",
 225       " record : \n",
 226       "   row : \n",
 227       "     source_ip : 82.109.184.9\n",
 228       "     count : 1\n",
 229       "     policy_evaluated : \n",
 230       "       disposition : none\n",
 231       "       dkim : fail\n",
 232       "       spf : fail\n",
 233       "   identifiers : \n",
 234       "     header_from : njae.me.uk\n",
 235       "   auth_results : \n",
 236       "     dkim : \n",
 237       "       domain : clublloyds.com\n",
 238       "       result : pass\n",
 239       "     spf : \n",
 240       "       domain : clublloyds.com\n",
 241       "       result : pass\n",
 242       " record : \n",
 243       "   row : \n",
 244       "     source_ip : 212.69.55.62\n",
 245       "     count : 1\n",
 246       "     policy_evaluated : \n",
 247       "       disposition : none\n",
 248       "       dkim : pass\n",
 249       "       spf : pass\n",
 250       "   identifiers : \n",
 251       "     header_from : njae.me.uk\n",
 252       "   auth_results : \n",
 253       "     dkim : \n",
 254       "       domain : njae.me.uk\n",
 255       "       result : pass\n",
 256       "     spf : \n",
 257       "       domain : njae.me.uk\n",
 258       "       result : pass\n",
 259       "\n",
 260       " report_metadata : \n",
 261       "   org_name : Yahoo! Inc.\n",
 262       "   email : postmaster@dmarc.yahoo.com\n",
 263       "   report_id : 1459129809.695034\n",
 264       "   date_range : \n",
 265       "     begin : 1459036800\n",
 266       "     end : 1459123199\n",
 267       " policy_published : \n",
 268       "   domain : njae.me.uk\n",
 269       "   adkim : r\n",
 270       "   aspf : r\n",
 271       "   p : none\n",
 272       "   pct : 100\n",
 273       " record : \n",
 274       "   row : \n",
 275       "     source_ip : 65.20.0.12\n",
 276       "     count : 1\n",
 277       "     policy_evaluated : \n",
 278       "       disposition : none\n",
 279       "       dkim : pass\n",
 280       "       spf : fail\n",
 281       "   identifiers : \n",
 282       "     header_from : njae.me.uk\n",
 283       "   auth_results : \n",
 284       "     dkim : \n",
 285       "       domain : njae.me.uk\n",
 286       "       result : pass\n",
 287       "     spf : \n",
 288       "       domain : njae.me.uk\n",
 289       "       result : softfail\n",
 290       "\n",
 291       " report_metadata : \n",
 292       "   org_name : Yahoo! Inc.\n",
 293       "   email : postmaster@dmarc.yahoo.com\n",
 294       "   report_id : 1458957186.548175\n",
 295       "   date_range : \n",
 296       "     begin : 1458864000\n",
 297       "     end : 1458950399\n",
 298       " policy_published : \n",
 299       "   domain : njae.me.uk\n",
 300       "   adkim : r\n",
 301       "   aspf : r\n",
 302       "   p : none\n",
 303       "   pct : 100\n",
 304       " record : \n",
 305       "   row : \n",
 306       "     source_ip : 212.69.55.62\n",
 307       "     count : 2\n",
 308       "     policy_evaluated : \n",
 309       "       disposition : none\n",
 310       "       dkim : pass\n",
 311       "       spf : pass\n",
 312       "   identifiers : \n",
 313       "     header_from : njae.me.uk\n",
 314       "   auth_results : \n",
 315       "     dkim : \n",
 316       "       domain : njae.me.uk\n",
 317       "       result : pass\n",
 318       "     spf : \n",
 319       "       domain : njae.me.uk\n",
 320       "       result : pass\n",
 321       "\n",
 322       " report_metadata : \n",
 323       "   org_name : Microsoft Corp.\n",
 324       "   email : dmarcrep@microsoft.com\n",
 325       "   report_id : 68aad5080a774e2c997d159b546569b9@hotmail.com\n",
 326       "   date_range : \n",
 327       "     begin : 1459011600\n",
 328       "     end : 1459098000\n",
 329       " policy_published : \n",
 330       "   domain : njae.me.uk\n",
 331       "   adkim : r\n",
 332       "   aspf : r\n",
 333       "   p : none\n",
 334       "   sp : none\n",
 335       "   pct : 100\n",
 336       " record : \n",
 337       "   row : \n",
 338       "     source_ip : 212.69.55.62\n",
 339       "     count : 2\n",
 340       "     policy_evaluated : \n",
 341       "       disposition : none\n",
 342       "       dkim : pass\n",
 343       "       spf : pass\n",
 344       "   identifiers : \n",
 345       "     header_from : njae.me.uk\n",
 346       "   auth_results : \n",
 347       "     spf : \n",
 348       "       domain : njae.me.uk\n",
 349       "       result : pass\n",
 350       "     dkim : \n",
 351       "       domain : njae.me.uk\n",
 352       "       result : pass\n",
 353       "\n",
 354       " report_metadata : \n",
 355       "   org_name : google.com\n",
 356       "   email : noreply-dmarc-support@google.com\n",
 357       "   extra_contact_info : https://support.google.com/a/answer/2466580\n",
 358       "   report_id : 13032699446207263761\n",
 359       "   date_range : \n",
 360       "     begin : 1458777600\n",
 361       "     end : 1458863999\n",
 362       " policy_published : \n",
 363       "   domain : njae.me.uk\n",
 364       "   adkim : r\n",
 365       "   aspf : r\n",
 366       "   p : none\n",
 367       "   sp : none\n",
 368       "   pct : 100\n",
 369       " record : \n",
 370       "   row : \n",
 371       "     source_ip : 146.185.136.235\n",
 372       "     count : 1\n",
 373       "     policy_evaluated : \n",
 374       "       disposition : none\n",
 375       "       dkim : pass\n",
 376       "       spf : fail\n",
 377       "   identifiers : \n",
 378       "     header_from : njae.me.uk\n",
 379       "   auth_results : \n",
 380       "     dkim : \n",
 381       "       domain : njae.me.uk\n",
 382       "       result : pass\n",
 383       "     spf : \n",
 384       "       domain : njae.me.uk\n",
 385       "       result : softfail\n",
 386       "\n"
 387      ]
 388     }
 389    ],
 390    "source": [
 391     "for f in dmarc_reports:\n",
 392     "    walk(dmarc_reports[f])\n",
 393     "    print()"
 394    ]
 395   },
 396   {
 397    "cell_type": "code",
 398    "execution_count": 15,
 399    "metadata": {
 400     "collapsed": false
 401    },
 402    "outputs": [
 403     {
 404      "name": "stdout",
 405      "output_type": "stream",
 406      "text": [
 407       " report_metadata : \n",
 408       "   org_name : Yahoo! Inc.\n",
 409       "   email : postmaster@dmarc.yahoo.com\n",
 410       "   report_id : 1459129809.695034\n",
 411       "   date_range : \n",
 412       "     begin : 1459036800\n",
 413       "     end : 1459123199\n",
 414       " policy_published : \n",
 415       "   domain : njae.me.uk\n",
 416       "   adkim : r\n",
 417       "   aspf : r\n",
 418       "   p : none\n",
 419       "   pct : 100\n",
 420       " record : \n",
 421       "   row : \n",
 422       "     source_ip : 65.20.0.12\n",
 423       "     count : 1\n",
 424       "     policy_evaluated : \n",
 425       "       disposition : none\n",
 426       "       dkim : pass\n",
 427       "       spf : fail\n",
 428       "   identifiers : \n",
 429       "     header_from : njae.me.uk\n",
 430       "   auth_results : \n",
 431       "     dkim : \n",
 432       "       domain : njae.me.uk\n",
 433       "       result : pass\n",
 434       "     spf : \n",
 435       "       domain : njae.me.uk\n",
 436       "       result : softfail\n"
 437      ]
 438     }
 439    ],
 440    "source": [
 441     "root2 = xml.etree.ElementTree.fromstring(open('yahoo.com!njae.me.uk!1459036800!1459123199.xml').read())\n",
 442     "walk(root2)"
 443    ]
 444   },
 445   {
 446    "cell_type": "code",
 447    "execution_count": 28,
 448    "metadata": {
 449     "collapsed": false
 450    },
 451    "outputs": [],
 452    "source": [
 453     "conn = psycopg2.connect(host=config['database']['server'],\n",
 454     "                        database=config['database']['database'], \n",
 455     "                        user=config['database']['username'], \n",
 456     "                        password=config['database']['password'])"
 457    ]
 458   },
 459   {
 460    "cell_type": "code",
 461    "execution_count": 30,
 462    "metadata": {
 463     "collapsed": false
 464    },
 465    "outputs": [
 466     {
 467      "data": {
 468       "text/plain": [
 469        "<cursor object at 0x7fa9a819b7c8; closed: 0>"
 470       ]
 471      },
 472      "execution_count": 30,
 473      "metadata": {},
 474      "output_type": "execute_result"
 475     }
 476    ],
 477    "source": [
 478     "cur = conn.cursor()\n",
 479     "cur"
 480    ]
 481   },
 482   {
 483    "cell_type": "code",
 484    "execution_count": 31,
 485    "metadata": {
 486     "collapsed": false
 487    },
 488    "outputs": [
 489     {
 490      "data": {
 491       "text/plain": [
 492        "{'email': 'varchar', 'org_name': 'varchar', 'report_id': 'varchar'}"
 493       ]
 494      },
 495      "execution_count": 31,
 496      "metadata": {},
 497      "output_type": "execute_result"
 498     }
 499    ],
 500    "source": [
 501     "report_fields = {'date_range_begin': 'timestamp', 'date_range_end': 'timestamp'}\n",
 502     "for child in root2:\n",
 503     "    if child.tag == 'report_metadata':\n",
 504     "        for c in child:\n",
 505     "            if c.tag != 'date_range':\n",
 506     "                report_fields[c.tag] = 'varchar'\n",
 507     "report_fields"
 508    ]
 509   },
 510   {
 511    "cell_type": "code",
 512    "execution_count": 65,
 513    "metadata": {
 514     "collapsed": false
 515    },
 516    "outputs": [],
 517    "source": [
 518     "def xpath_of(node, prefix=''):\n",
 519     "    if len(node) == 0:\n",
 520     "        if node.text:\n",
 521     "            nodes = {prefix + node.tag: node.text.strip()}\n",
 522     "        else:\n",
 523     "            nodes = {prefix + node.tag: ''}\n",
 524     "    else:\n",
 525     "        nodes = {}\n",
 526     "    if node:\n",
 527     "        for child in node:\n",
 528     "            nodes.update(xpath_of(child, \n",
 529     "                                  prefix + node.tag + '/'))\n",
 530     "    return nodes"
 531    ]
 532   },
 533   {
 534    "cell_type": "code",
 535    "execution_count": 66,
 536    "metadata": {
 537     "collapsed": false
 538    },
 539    "outputs": [
 540     {
 541      "data": {
 542       "text/plain": [
 543        "{'feedback/policy_published/adkim': 'r',\n",
 544        " 'feedback/policy_published/aspf': 'r',\n",
 545        " 'feedback/policy_published/domain': 'njae.me.uk',\n",
 546        " 'feedback/policy_published/p': 'none',\n",
 547        " 'feedback/policy_published/pct': '100',\n",
 548        " 'feedback/record/auth_results/dkim/domain': 'njae.me.uk',\n",
 549        " 'feedback/record/auth_results/dkim/result': 'pass',\n",
 550        " 'feedback/record/auth_results/spf/domain': 'njae.me.uk',\n",
 551        " 'feedback/record/auth_results/spf/result': 'softfail',\n",
 552        " 'feedback/record/identifiers/header_from': 'njae.me.uk',\n",
 553        " 'feedback/record/row/count': '1',\n",
 554        " 'feedback/record/row/policy_evaluated/disposition': 'none',\n",
 555        " 'feedback/record/row/policy_evaluated/dkim': 'pass',\n",
 556        " 'feedback/record/row/policy_evaluated/spf': 'fail',\n",
 557        " 'feedback/record/row/source_ip': '65.20.0.12',\n",
 558        " 'feedback/report_metadata/date_range/begin': '1459036800',\n",
 559        " 'feedback/report_metadata/date_range/end': '1459123199',\n",
 560        " 'feedback/report_metadata/email': 'postmaster@dmarc.yahoo.com',\n",
 561        " 'feedback/report_metadata/org_name': 'Yahoo! Inc.',\n",
 562        " 'feedback/report_metadata/report_id': '1459129809.695034'}"
 563       ]
 564      },
 565      "execution_count": 66,
 566      "metadata": {},
 567      "output_type": "execute_result"
 568     }
 569    ],
 570    "source": [
 571     "xpath_of(root2)"
 572    ]
 573   },
 574   {
 575    "cell_type": "code",
 576    "execution_count": 63,
 577    "metadata": {
 578     "collapsed": false
 579    },
 580    "outputs": [
 581     {
 582      "data": {
 583       "text/plain": [
 584        "'1459036800'"
 585       ]
 586      },
 587      "execution_count": 63,
 588      "metadata": {},
 589      "output_type": "execute_result"
 590     }
 591    ],
 592    "source": [
 593     "root2.find('./report_metadata/date_range/begin').text"
 594    ]
 595   },
 596   {
 597    "cell_type": "code",
 598    "execution_count": 77,
 599    "metadata": {
 600     "collapsed": false
 601    },
 602    "outputs": [
 603     {
 604      "data": {
 605       "text/plain": [
 606        "'./report_metadata/date_range/begin'"
 607       ]
 608      },
 609      "execution_count": 77,
 610      "metadata": {},
 611      "output_type": "execute_result"
 612     }
 613    ],
 614    "source": [
 615     "re.sub(r'^\\w*(/.*)$', r'.\\1', 'feedback/report_metadata/date_range/begin')"
 616    ]
 617   },
 618   {
 619    "cell_type": "code",
 620    "execution_count": 82,
 621    "metadata": {
 622     "collapsed": false
 623    },
 624    "outputs": [
 625     {
 626      "data": {
 627       "text/plain": [
 628        "'report_metadata_date_range_begin'"
 629       ]
 630      },
 631      "execution_count": 82,
 632      "metadata": {},
 633      "output_type": "execute_result"
 634     }
 635    ],
 636    "source": [
 637     "re.sub(r'/', r'_',\n",
 638     "       re.sub(r'^[^\\/]*/(.*)$', r'\\1', 'feedback/report_metadata/date_range/begin'))"
 639    ]
 640   },
 641   {
 642    "cell_type": "code",
 643    "execution_count": 156,
 644    "metadata": {
 645     "collapsed": false,
 646     "scrolled": true
 647    },
 648    "outputs": [
 649     {
 650      "data": {
 651       "text/plain": [
 652        "{'./policy_published/adkim': {'pg_field_name': 'policy_published_adkim',\n",
 653        "  'pg_table': 'reports',\n",
 654        "  'pg_type': 'varchar'},\n",
 655        " './policy_published/aspf': {'pg_field_name': 'policy_published_aspf',\n",
 656        "  'pg_table': 'reports',\n",
 657        "  'pg_type': 'varchar'},\n",
 658        " './policy_published/domain': {'pg_field_name': 'policy_published_domain',\n",
 659        "  'pg_table': 'reports',\n",
 660        "  'pg_type': 'varchar'},\n",
 661        " './policy_published/p': {'pg_field_name': 'policy_published_p',\n",
 662        "  'pg_table': 'reports',\n",
 663        "  'pg_type': 'varchar'},\n",
 664        " './policy_published/pct': {'pg_field_name': 'policy_published_pct',\n",
 665        "  'pg_table': 'reports',\n",
 666        "  'pg_type': 'int'},\n",
 667        " './record[{}]/auth_results/dkim/domain': {'pg_field_name': 'auth_results_dkim_domain',\n",
 668        "  'pg_table': 'report_items',\n",
 669        "  'pg_type': 'varchar'},\n",
 670        " './record[{}]/auth_results/dkim/result': {'pg_field_name': 'auth_results_dkim_result',\n",
 671        "  'pg_table': 'report_items',\n",
 672        "  'pg_type': 'varchar'},\n",
 673        " './record[{}]/auth_results/spf/domain': {'pg_field_name': 'auth_results_spf_domain',\n",
 674        "  'pg_table': 'report_items',\n",
 675        "  'pg_type': 'varchar'},\n",
 676        " './record[{}]/auth_results/spf/result': {'pg_field_name': 'auth_results_spf_result',\n",
 677        "  'pg_table': 'report_items',\n",
 678        "  'pg_type': 'varchar'},\n",
 679        " './record[{}]/identifiers/header_from': {'pg_field_name': 'identifiers_header_from',\n",
 680        "  'pg_table': 'report_items',\n",
 681        "  'pg_type': 'varchar'},\n",
 682        " './record[{}]/row/count': {'pg_field_name': 'count',\n",
 683        "  'pg_table': 'report_items',\n",
 684        "  'pg_type': 'int'},\n",
 685        " './record[{}]/row/policy_evaluated/disposition': {'pg_field_name': 'policy_evaluated_disposition',\n",
 686        "  'pg_table': 'report_items',\n",
 687        "  'pg_type': 'varchar'},\n",
 688        " './record[{}]/row/policy_evaluated/dkim': {'pg_field_name': 'policy_evaluated_dkim',\n",
 689        "  'pg_table': 'report_items',\n",
 690        "  'pg_type': 'varchar'},\n",
 691        " './record[{}]/row/policy_evaluated/spf': {'pg_field_name': 'policy_evaluated_spf',\n",
 692        "  'pg_table': 'report_items',\n",
 693        "  'pg_type': 'varchar'},\n",
 694        " './record[{}]/row/source_ip': {'pg_field_name': 'source_ip',\n",
 695        "  'pg_table': 'report_items',\n",
 696        "  'pg_type': 'inet'},\n",
 697        " './report_metadata/date_range/begin': {'pg_field_name': 'report_metadata_date_range_begin',\n",
 698        "  'pg_table': 'reports',\n",
 699        "  'pg_type': 'timestamp'},\n",
 700        " './report_metadata/date_range/end': {'pg_field_name': 'report_metadata_date_range_end',\n",
 701        "  'pg_table': 'reports',\n",
 702        "  'pg_type': 'timestamp'},\n",
 703        " './report_metadata/email': {'pg_field_name': 'report_metadata_email',\n",
 704        "  'pg_table': 'reports',\n",
 705        "  'pg_type': 'varchar'},\n",
 706        " './report_metadata/org_name': {'pg_field_name': 'report_metadata_org_name',\n",
 707        "  'pg_table': 'reports',\n",
 708        "  'pg_type': 'varchar'},\n",
 709        " './report_metadata/report_id': {'pg_field_name': 'report_metadata_report_id',\n",
 710        "  'pg_table': 'reports',\n",
 711        "  'pg_type': 'varchar'}}"
 712       ]
 713      },
 714      "execution_count": 156,
 715      "metadata": {},
 716      "output_type": "execute_result"
 717     }
 718    ],
 719    "source": [
 720     "field_maps = {re.sub(r'^\\w*(/.*)$', r'.\\1', name): \n",
 721     "              {'pg_field_name': re.sub(r'/', r'_', re.sub(r'^[^\\/]*/(.*)$', r'\\1', name)),\n",
 722     "              'pg_table': 'reports',\n",
 723     "              'pg_type': 'varchar'}\n",
 724     "             for name in xpath_of(root2)\n",
 725     "             if 'record' not in name}\n",
 726     "field_maps.update({re.sub(r'^\\w*/record(/.*)$', r'./record[{}]\\1', name): \n",
 727     "              {'pg_field_name': re.sub(r'/', r'_', re.sub(r'^.*/record(/row)?/(.*)$', r'\\2', name)),\n",
 728     "              'pg_table': 'report_items',\n",
 729     "              'pg_type': 'varchar'}\n",
 730     "             for name in xpath_of(root2)\n",
 731     "             if 'record' in name})\n",
 732     "field_maps['./report_metadata/date_range/begin']['pg_type'] = 'timestamp'\n",
 733     "field_maps['./report_metadata/date_range/end']['pg_type'] = 'timestamp'\n",
 734     "field_maps['./policy_published/pct']['pg_type'] = 'int'\n",
 735     "field_maps['./record[{}]/row/count']['pg_type'] = 'int'\n",
 736     "field_maps['./record[{}]/row/source_ip']['pg_type'] = 'inet'\n",
 737     "\n",
 738     "field_maps"
 739    ]
 740   },
 741   {
 742    "cell_type": "code",
 743    "execution_count": 95,
 744    "metadata": {
 745     "collapsed": false
 746    },
 747    "outputs": [
 748     {
 749      "data": {
 750       "text/plain": [
 751        "datetime.datetime(2016, 3, 27, 23, 59, 59)"
 752       ]
 753      },
 754      "execution_count": 95,
 755      "metadata": {},
 756      "output_type": "execute_result"
 757     }
 758    ],
 759    "source": [
 760     "datetime.datetime.utcfromtimestamp(1459123199)"
 761    ]
 762   },
 763   {
 764    "cell_type": "code",
 765    "execution_count": 100,
 766    "metadata": {
 767     "collapsed": false
 768    },
 769    "outputs": [
 770     {
 771      "data": {
 772       "text/plain": [
 773        "1459119599.0"
 774       ]
 775      },
 776      "execution_count": 100,
 777      "metadata": {},
 778      "output_type": "execute_result"
 779     }
 780    ],
 781    "source": [
 782     "datetime.datetime.utcfromtimestamp(1459123199).timestamp()"
 783    ]
 784   },
 785   {
 786    "cell_type": "code",
 787    "execution_count": 157,
 788    "metadata": {
 789     "collapsed": false
 790    },
 791    "outputs": [
 792     {
 793      "data": {
 794       "text/plain": [
 795        "'create table reports (id serial primary key, policy_published_p varchar, report_metadata_date_range_end timestamp, report_metadata_org_name varchar, policy_published_aspf varchar, policy_published_adkim varchar, policy_published_pct int, report_metadata_email varchar, report_metadata_date_range_begin timestamp, report_metadata_report_id varchar, policy_published_domain varchar);'"
 796       ]
 797      },
 798      "execution_count": 157,
 799      "metadata": {},
 800      "output_type": "execute_result"
 801     }
 802    ],
 803    "source": [
 804     "create_report_table_string = 'create table reports (id serial primary key, ' + \\\n",
 805     "', '.join(field_maps[p]['pg_field_name'] + ' ' + field_maps[p]['pg_type'] \n",
 806     " for p in field_maps if field_maps[p]['pg_table'] == 'reports') + \\\n",
 807     "');'\n",
 808     "create_report_table_string"
 809    ]
 810   },
 811   {
 812    "cell_type": "code",
 813    "execution_count": 193,
 814    "metadata": {
 815     "collapsed": false
 816    },
 817    "outputs": [
 818     {
 819      "data": {
 820       "text/plain": [
 821        "'create table report_items (id serial primary key, report_id integer references reports, auth_results_spf_domain varchar, auth_results_dkim_domain varchar, policy_evaluated_dkim varchar, auth_results_spf_result varchar, source_ip inet, policy_evaluated_spf varchar, count int, identifiers_header_from varchar, policy_evaluated_disposition varchar, auth_results_dkim_result varchar);'"
 822       ]
 823      },
 824      "execution_count": 193,
 825      "metadata": {},
 826      "output_type": "execute_result"
 827     }
 828    ],
 829    "source": [
 830     "create_report_item_table_string = 'create table report_items (id serial primary key, ' + \\\n",
 831     "  'report_id integer references reports, ' + \\\n",
 832     "', '.join(field_maps[p]['pg_field_name'] + ' ' + field_maps[p]['pg_type'] \n",
 833     " for p in field_maps if field_maps[p]['pg_table'] == 'report_items') + \\\n",
 834     "');'\n",
 835     "create_report_item_table_string"
 836    ]
 837   },
 838   {
 839    "cell_type": "code",
 840    "execution_count": 117,
 841    "metadata": {
 842     "collapsed": false
 843    },
 844    "outputs": [
 845     {
 846      "data": {
 847       "text/plain": [
 848        "<cursor object at 0x7fa9a819b7c8; closed: 0>"
 849       ]
 850      },
 851      "execution_count": 117,
 852      "metadata": {},
 853      "output_type": "execute_result"
 854     }
 855    ],
 856    "source": [
 857     "cur"
 858    ]
 859   },
 860   {
 861    "cell_type": "code",
 862    "execution_count": 194,
 863    "metadata": {
 864     "collapsed": false
 865    },
 866    "outputs": [],
 867    "source": [
 868     "cur.execute(create_report_table_string)"
 869    ]
 870   },
 871   {
 872    "cell_type": "code",
 873    "execution_count": 195,
 874    "metadata": {
 875     "collapsed": false
 876    },
 877    "outputs": [],
 878    "source": [
 879     "cur.execute(create_report_item_table_string)"
 880    ]
 881   },
 882   {
 883    "cell_type": "code",
 884    "execution_count": 196,
 885    "metadata": {
 886     "collapsed": true
 887    },
 888    "outputs": [],
 889    "source": [
 890     "conn.commit()"
 891    ]
 892   },
 893   {
 894    "cell_type": "code",
 895    "execution_count": 128,
 896    "metadata": {
 897     "collapsed": false
 898    },
 899    "outputs": [],
 900    "source": [
 901     "rep = dmarc_reports['google.com!njae.me.uk!1458777600!1458863999.xml']"
 902    ]
 903   },
 904   {
 905    "cell_type": "code",
 906    "execution_count": 134,
 907    "metadata": {
 908     "collapsed": false
 909    },
 910    "outputs": [
 911     {
 912      "data": {
 913       "text/plain": [
 914        "'1'"
 915       ]
 916      },
 917      "execution_count": 134,
 918      "metadata": {},
 919      "output_type": "execute_result"
 920     }
 921    ],
 922    "source": [
 923     "rep.find('./record/row/count').text"
 924    ]
 925   },
 926   {
 927    "cell_type": "code",
 928    "execution_count": 135,
 929    "metadata": {
 930     "collapsed": false
 931    },
 932    "outputs": [
 933     {
 934      "data": {
 935       "text/plain": [
 936        "[('google.com!njae.me.uk!1458950400!1459036799.xml', '1'),\n",
 937        " ('yahoo.com!njae.me.uk!1459036800!1459123199.xml', '1'),\n",
 938        " ('yahoo.com!njae.me.uk!1458864000!1458950399.xml', '2'),\n",
 939        " ('hotmail.com!njae.me.uk!1459011600!1459098000.xml', '2'),\n",
 940        " ('google.com!njae.me.uk!1458777600!1458863999.xml', '1')]"
 941       ]
 942      },
 943      "execution_count": 135,
 944      "metadata": {},
 945      "output_type": "execute_result"
 946     }
 947    ],
 948    "source": [
 949     "[(r, dmarc_reports[r].find('./record/row/count').text) for r in dmarc_reports]"
 950    ]
 951   },
 952   {
 953    "cell_type": "code",
 954    "execution_count": 136,
 955    "metadata": {
 956     "collapsed": false
 957    },
 958    "outputs": [
 959     {
 960      "name": "stdout",
 961      "output_type": "stream",
 962      "text": [
 963       " report_metadata : \n",
 964       "   org_name : Microsoft Corp.\n",
 965       "   email : dmarcrep@microsoft.com\n",
 966       "   report_id : 68aad5080a774e2c997d159b546569b9@hotmail.com\n",
 967       "   date_range : \n",
 968       "     begin : 1459011600\n",
 969       "     end : 1459098000\n",
 970       " policy_published : \n",
 971       "   domain : njae.me.uk\n",
 972       "   adkim : r\n",
 973       "   aspf : r\n",
 974       "   p : none\n",
 975       "   sp : none\n",
 976       "   pct : 100\n",
 977       " record : \n",
 978       "   row : \n",
 979       "     source_ip : 212.69.55.62\n",
 980       "     count : 2\n",
 981       "     policy_evaluated : \n",
 982       "       disposition : none\n",
 983       "       dkim : pass\n",
 984       "       spf : pass\n",
 985       "   identifiers : \n",
 986       "     header_from : njae.me.uk\n",
 987       "   auth_results : \n",
 988       "     spf : \n",
 989       "       domain : njae.me.uk\n",
 990       "       result : pass\n",
 991       "     dkim : \n",
 992       "       domain : njae.me.uk\n",
 993       "       result : pass\n"
 994      ]
 995     }
 996    ],
 997    "source": [
 998     "walk(dmarc_reports['hotmail.com!njae.me.uk!1459011600!1459098000.xml'])"
 999    ]
1000   },
1001   {
1002    "cell_type": "code",
1003    "execution_count": 137,
1004    "metadata": {
1005     "collapsed": false
1006    },
1007    "outputs": [
1008     {
1009      "name": "stdout",
1010      "output_type": "stream",
1011      "text": [
1012       " report_metadata : \n",
1013       "   org_name : google.com\n",
1014       "   email : noreply-dmarc-support@google.com\n",
1015       "   extra_contact_info : https://support.google.com/a/answer/2466580\n",
1016       "   report_id : 2150510829392606201\n",
1017       "   date_range : \n",
1018       "     begin : 1458950400\n",
1019       "     end : 1459036799\n",
1020       " policy_published : \n",
1021       "   domain : njae.me.uk\n",
1022       "   adkim : r\n",
1023       "   aspf : r\n",
1024       "   p : none\n",
1025       "   sp : none\n",
1026       "   pct : 100\n",
1027       " record : \n",
1028       "   row : \n",
1029       "     source_ip : 82.109.184.9\n",
1030       "     count : 1\n",
1031       "     policy_evaluated : \n",
1032       "       disposition : none\n",
1033       "       dkim : fail\n",
1034       "       spf : fail\n",
1035       "   identifiers : \n",
1036       "     header_from : njae.me.uk\n",
1037       "   auth_results : \n",
1038       "     dkim : \n",
1039       "       domain : clublloyds.com\n",
1040       "       result : pass\n",
1041       "     spf : \n",
1042       "       domain : clublloyds.com\n",
1043       "       result : pass\n",
1044       " record : \n",
1045       "   row : \n",
1046       "     source_ip : 212.69.55.62\n",
1047       "     count : 1\n",
1048       "     policy_evaluated : \n",
1049       "       disposition : none\n",
1050       "       dkim : pass\n",
1051       "       spf : pass\n",
1052       "   identifiers : \n",
1053       "     header_from : njae.me.uk\n",
1054       "   auth_results : \n",
1055       "     dkim : \n",
1056       "       domain : njae.me.uk\n",
1057       "       result : pass\n",
1058       "     spf : \n",
1059       "       domain : njae.me.uk\n",
1060       "       result : pass\n"
1061      ]
1062     }
1063    ],
1064    "source": [
1065     "walk(dmarc_reports['google.com!njae.me.uk!1458950400!1459036799.xml'])"
1066    ]
1067   },
1068   {
1069    "cell_type": "code",
1070    "execution_count": 159,
1071    "metadata": {
1072     "collapsed": false
1073    },
1074    "outputs": [
1075     {
1076      "data": {
1077       "text/plain": [
1078        "['./record[{}]/auth_results/spf/domain',\n",
1079        " './record[{}]/auth_results/dkim/domain',\n",
1080        " './record[{}]/row/policy_evaluated/dkim',\n",
1081        " './record[{}]/auth_results/spf/result',\n",
1082        " './record[{}]/row/source_ip',\n",
1083        " './record[{}]/row/policy_evaluated/spf',\n",
1084        " './record[{}]/row/count',\n",
1085        " './record[{}]/identifiers/header_from',\n",
1086        " './record[{}]/row/policy_evaluated/disposition',\n",
1087        " './record[{}]/auth_results/dkim/result']"
1088       ]
1089      },
1090      "execution_count": 159,
1091      "metadata": {},
1092      "output_type": "execute_result"
1093     }
1094    ],
1095    "source": [
1096     "[p for p in field_maps if field_maps[p]['pg_table'] == 'report_items']"
1097    ]
1098   },
1099   {
1100    "cell_type": "code",
1101    "execution_count": 140,
1102    "metadata": {
1103     "collapsed": false
1104    },
1105    "outputs": [
1106     {
1107      "data": {
1108       "text/plain": [
1109        "2"
1110       ]
1111      },
1112      "execution_count": 140,
1113      "metadata": {},
1114      "output_type": "execute_result"
1115     }
1116    ],
1117    "source": [
1118     "len(dmarc_reports['google.com!njae.me.uk!1458950400!1459036799.xml'].findall('./record'))"
1119    ]
1120   },
1121   {
1122    "cell_type": "code",
1123    "execution_count": 144,
1124    "metadata": {
1125     "collapsed": false
1126    },
1127    "outputs": [
1128     {
1129      "data": {
1130       "text/plain": [
1131        "'212.69.55.62'"
1132       ]
1133      },
1134      "execution_count": 144,
1135      "metadata": {},
1136      "output_type": "execute_result"
1137     }
1138    ],
1139    "source": [
1140     "dmarc_reports['google.com!njae.me.uk!1458950400!1459036799.xml'].find('./record[2]/row/source_ip').text"
1141    ]
1142   },
1143   {
1144    "cell_type": "code",
1145    "execution_count": 153,
1146    "metadata": {
1147     "collapsed": false
1148    },
1149    "outputs": [
1150     {
1151      "data": {
1152       "text/plain": [
1153        "'82.109.184.9'"
1154       ]
1155      },
1156      "execution_count": 153,
1157      "metadata": {},
1158      "output_type": "execute_result"
1159     }
1160    ],
1161    "source": [
1162     "dmarc_reports['google.com!njae.me.uk!1458950400!1459036799.xml'].find('./record[{}]/row/source_ip'.format(1)).text"
1163    ]
1164   },
1165   {
1166    "cell_type": "code",
1167    "execution_count": 163,
1168    "metadata": {
1169     "collapsed": false
1170    },
1171    "outputs": [
1172     {
1173      "name": "stdout",
1174      "output_type": "stream",
1175      "text": [
1176       "policy_published_p :> none\n",
1177       "report_metadata_date_range_end :> 1459036799\n",
1178       "report_metadata_org_name :> google.com\n",
1179       "policy_published_aspf :> r\n",
1180       "policy_published_adkim :> r\n",
1181       "policy_published_pct :> 100\n",
1182       "report_metadata_email :> noreply-dmarc-support@google.com\n",
1183       "report_metadata_date_range_begin :> 1458950400\n",
1184       "report_metadata_report_id :> 2150510829392606201\n",
1185       "policy_published_domain :> njae.me.uk\n"
1186      ]
1187     }
1188    ],
1189    "source": [
1190     "rep = dmarc_reports['google.com!njae.me.uk!1458950400!1459036799.xml']\n",
1191     "for f in [f for f in field_maps if field_maps[f]['pg_table'] == 'reports']:\n",
1192     "    print(field_maps[f]['pg_field_name'], ':>', rep.find(f).text)"
1193    ]
1194   },
1195   {
1196    "cell_type": "code",
1197    "execution_count": 165,
1198    "metadata": {
1199     "collapsed": false
1200    },
1201    "outputs": [
1202     {
1203      "name": "stdout",
1204      "output_type": "stream",
1205      "text": [
1206       "1\n",
1207       "auth_results_spf_domain :> clublloyds.com\n",
1208       "auth_results_dkim_domain :> clublloyds.com\n",
1209       "policy_evaluated_dkim :> fail\n",
1210       "auth_results_spf_result :> pass\n",
1211       "source_ip :> 82.109.184.9\n",
1212       "policy_evaluated_spf :> fail\n",
1213       "count :> 1\n",
1214       "identifiers_header_from :> njae.me.uk\n",
1215       "policy_evaluated_disposition :> none\n",
1216       "auth_results_dkim_result :> pass\n",
1217       "2\n",
1218       "auth_results_spf_domain :> njae.me.uk\n",
1219       "auth_results_dkim_domain :> njae.me.uk\n",
1220       "policy_evaluated_dkim :> pass\n",
1221       "auth_results_spf_result :> pass\n",
1222       "source_ip :> 212.69.55.62\n",
1223       "policy_evaluated_spf :> pass\n",
1224       "count :> 1\n",
1225       "identifiers_header_from :> njae.me.uk\n",
1226       "policy_evaluated_disposition :> none\n",
1227       "auth_results_dkim_result :> pass\n"
1228      ]
1229     }
1230    ],
1231    "source": [
1232     "rep = dmarc_reports['google.com!njae.me.uk!1458950400!1459036799.xml']\n",
1233     "for i in range(1, len(rep.findall('./record'))+1):\n",
1234     "    print(i)\n",
1235     "    for f in [f for f in field_maps if field_maps[f]['pg_table'] == 'report_items']:\n",
1236     "        print(field_maps[f]['pg_field_name'], ':>', rep.find(f.format(i)).text)"
1237    ]
1238   },
1239   {
1240    "cell_type": "code",
1241    "execution_count": 166,
1242    "metadata": {
1243     "collapsed": true
1244    },
1245    "outputs": [],
1246    "source": [
1247     "def maybe_strip(text):\n",
1248     "    if text:\n",
1249     "        return text.strip()\n",
1250     "    else:\n",
1251     "        return ''"
1252    ]
1253   },
1254   {
1255    "cell_type": "code",
1256    "execution_count": 197,
1257    "metadata": {
1258     "collapsed": false
1259    },
1260    "outputs": [
1261     {
1262      "data": {
1263       "text/plain": [
1264        "('insert into reports (policy_published_p, report_metadata_date_range_end, report_metadata_org_name, policy_published_aspf, policy_published_adkim, policy_published_pct, report_metadata_email, report_metadata_date_range_begin, report_metadata_report_id, policy_published_domain) values (%(policy_published_p)s, %(report_metadata_date_range_end)s, %(report_metadata_org_name)s, %(policy_published_aspf)s, %(policy_published_adkim)s, %(policy_published_pct)s, %(report_metadata_email)s, %(report_metadata_date_range_begin)s, %(report_metadata_report_id)s, %(policy_published_domain)s);',\n",
1265        " {'policy_published_adkim': 'r',\n",
1266        "  'policy_published_aspf': 'r',\n",
1267        "  'policy_published_domain': 'njae.me.uk',\n",
1268        "  'policy_published_p': 'none',\n",
1269        "  'policy_published_pct': 100,\n",
1270        "  'report_metadata_date_range_begin': datetime.datetime(2016, 3, 26, 0, 0),\n",
1271        "  'report_metadata_date_range_end': datetime.datetime(2016, 3, 26, 23, 59, 59),\n",
1272        "  'report_metadata_email': 'noreply-dmarc-support@google.com',\n",
1273        "  'report_metadata_org_name': 'google.com',\n",
1274        "  'report_metadata_report_id': '2150510829392606201'})"
1275       ]
1276      },
1277      "execution_count": 197,
1278      "metadata": {},
1279      "output_type": "execute_result"
1280     }
1281    ],
1282    "source": [
1283     "rep = dmarc_reports['google.com!njae.me.uk!1458950400!1459036799.xml']\n",
1284     "field_names = []\n",
1285     "values = {}\n",
1286     "for f in [f for f in field_maps if field_maps[f]['pg_table'] == 'reports']:\n",
1287     "    field_names += [field_maps[f]['pg_field_name']]\n",
1288     "    if field_maps[f]['pg_type'] == 'int':\n",
1289     "        values[field_maps[f]['pg_field_name']] = int(rep.find(f).text)\n",
1290     "    elif field_maps[f]['pg_type'] == 'timestamp':\n",
1291     "        values[field_maps[f]['pg_field_name']] = datetime.datetime.utcfromtimestamp(int(rep.find(f).text))\n",
1292     "    elif field_maps[f]['pg_type'] == 'inet':\n",
1293     "        values[field_maps[f]['pg_field_name']] = maybe_strip(rep.find(f).text)\n",
1294     "    else:\n",
1295     "        values[field_maps[f]['pg_field_name']] = maybe_strip(rep.find(f).text)\n",
1296     "insert_string = 'insert into reports (' + ', '.join(field_names) +') '\n",
1297     "insert_string += 'values (' + ', '.join('%({})s'.format(f) for f in field_names) + ');'\n",
1298     "insert_string, values"
1299    ]
1300   },
1301   {
1302    "cell_type": "code",
1303    "execution_count": 198,
1304    "metadata": {
1305     "collapsed": false
1306    },
1307    "outputs": [],
1308    "source": [
1309     "cur.execute(insert_string, values)\n",
1310     "conn.commit()"
1311    ]
1312   },
1313   {
1314    "cell_type": "code",
1315    "execution_count": 201,
1316    "metadata": {
1317     "collapsed": false
1318    },
1319    "outputs": [
1320     {
1321      "data": {
1322       "text/plain": [
1323        "1"
1324       ]
1325      },
1326      "execution_count": 201,
1327      "metadata": {},
1328      "output_type": "execute_result"
1329     }
1330    ],
1331    "source": [
1332     "cur.execute('select id, report_metadata_report_id from reports where report_metadata_report_id = %s;', \n",
1333     "            [rep.find('./report_metadata/report_id').text])\n",
1334     "cur.fetchall()[0][0]"
1335    ]
1336   },
1337   {
1338    "cell_type": "code",
1339    "execution_count": 203,
1340    "metadata": {
1341     "collapsed": false
1342    },
1343    "outputs": [
1344     {
1345      "name": "stdout",
1346      "output_type": "stream",
1347      "text": [
1348       "1\n",
1349       "insert into report_items (report_id, auth_results_spf_domain, auth_results_dkim_domain, policy_evaluated_dkim, auth_results_spf_result, source_ip, policy_evaluated_spf, count, identifiers_header_from, policy_evaluated_disposition, auth_results_dkim_result) values (%(report_id)s, %(auth_results_spf_domain)s, %(auth_results_dkim_domain)s, %(policy_evaluated_dkim)s, %(auth_results_spf_result)s, %(source_ip)s, %(policy_evaluated_spf)s, %(count)s, %(identifiers_header_from)s, %(policy_evaluated_disposition)s, %(auth_results_dkim_result)s); {'auth_results_spf_result': 'pass', 'count': 1, 'report_id': 1, 'identifiers_header_from': 'njae.me.uk', 'policy_evaluated_disposition': 'none', 'auth_results_dkim_result': 'pass', 'source_ip': '82.109.184.9', 'policy_evaluated_spf': 'fail', 'auth_results_dkim_domain': 'clublloyds.com', 'auth_results_spf_domain': 'clublloyds.com', 'policy_evaluated_dkim': 'fail'}\n",
1350       "2\n",
1351       "insert into report_items (report_id, auth_results_spf_domain, auth_results_dkim_domain, policy_evaluated_dkim, auth_results_spf_result, source_ip, policy_evaluated_spf, count, identifiers_header_from, policy_evaluated_disposition, auth_results_dkim_result) values (%(report_id)s, %(auth_results_spf_domain)s, %(auth_results_dkim_domain)s, %(policy_evaluated_dkim)s, %(auth_results_spf_result)s, %(source_ip)s, %(policy_evaluated_spf)s, %(count)s, %(identifiers_header_from)s, %(policy_evaluated_disposition)s, %(auth_results_dkim_result)s); {'auth_results_spf_result': 'pass', 'count': 1, 'report_id': 1, 'identifiers_header_from': 'njae.me.uk', 'policy_evaluated_disposition': 'none', 'auth_results_dkim_result': 'pass', 'source_ip': '212.69.55.62', 'policy_evaluated_spf': 'pass', 'auth_results_dkim_domain': 'njae.me.uk', 'auth_results_spf_domain': 'njae.me.uk', 'policy_evaluated_dkim': 'pass'}\n"
1352      ]
1353     }
1354    ],
1355    "source": [
1356     "rep = dmarc_reports['google.com!njae.me.uk!1458950400!1459036799.xml']\n",
1357     "for i in range(1, len(rep.findall('./record'))+1):\n",
1358     "    print(i)\n",
1359     "    field_names = []\n",
1360     "    cur.execute('select id, report_metadata_report_id from reports where report_metadata_report_id = %s;', \n",
1361     "            [rep.find('./report_metadata/report_id').text])\n",
1362     "    results = cur.fetchall()\n",
1363     "    if len(results) != 1:\n",
1364     "        raise\n",
1365     "    else:\n",
1366     "        report_id = results[0][0]\n",
1367     "    values = {'report_id': report_id}\n",
1368     "    for f in [f for f in field_maps if field_maps[f]['pg_table'] == 'report_items']:\n",
1369     "        field_names += [field_maps[f]['pg_field_name']]\n",
1370     "        if field_maps[f]['pg_type'] == 'int':\n",
1371     "            values[field_maps[f]['pg_field_name']] = int(rep.find(f.format(i)).text)\n",
1372     "        elif field_maps[f]['pg_type'] == 'timestamp':\n",
1373     "            values[field_maps[f]['pg_field_name']] = datetime.datetime.utcfromtimestamp(int(rep.find(f.format(i)).text))\n",
1374     "        elif field_maps[f]['pg_type'] == 'inet':\n",
1375     "            values[field_maps[f]['pg_field_name']] = maybe_strip(rep.find(f.format(i)).text)\n",
1376     "        else:\n",
1377     "            values[field_maps[f]['pg_field_name']] = maybe_strip(rep.find(f.format(i)).text)\n",
1378     "    insert_string = 'insert into report_items (report_id, ' + ', '.join(field_names) +') '\n",
1379     "    insert_string += 'values (%(report_id)s, ' + ', '.join('%({})s'.format(f) for f in field_names) + ');'\n",
1380     "    print(insert_string, values)\n",
1381     "    cur.execute(insert_string, values)\n",
1382     "conn.commit()"
1383    ]
1384   },
1385   {
1386    "cell_type": "code",
1387    "execution_count": 191,
1388    "metadata": {
1389     "collapsed": true
1390    },
1391    "outputs": [],
1392    "source": [
1393     "conn.rollback()"
1394    ]
1395   },
1396   {
1397    "cell_type": "code",
1398    "execution_count": 209,
1399    "metadata": {
1400     "collapsed": true
1401    },
1402    "outputs": [],
1403    "source": [
1404     "def write_report(connection, cursor, report):\n",
1405     "    \n",
1406     "    field_names = []\n",
1407     "    values = {}\n",
1408     "    for f in [f for f in field_maps if field_maps[f]['pg_table'] == 'reports']:\n",
1409     "        field_names += [field_maps[f]['pg_field_name']]\n",
1410     "        if field_maps[f]['pg_type'] == 'int':\n",
1411     "            values[field_maps[f]['pg_field_name']] = int(report.find(f).text)\n",
1412     "        elif field_maps[f]['pg_type'] == 'timestamp':\n",
1413     "            values[field_maps[f]['pg_field_name']] = datetime.datetime.utcfromtimestamp(int(report.find(f).text))\n",
1414     "        elif field_maps[f]['pg_type'] == 'inet':\n",
1415     "            values[field_maps[f]['pg_field_name']] = maybe_strip(report.find(f).text)\n",
1416     "        else:\n",
1417     "            values[field_maps[f]['pg_field_name']] = maybe_strip(report.find(f).text)\n",
1418     "    insert_string = 'insert into reports (' + ', '.join(field_names) + ') '\n",
1419     "    insert_string += 'values (' + ', '.join('%({})s'.format(f) for f in field_names) + ');'\n",
1420     "    cursor.execute(insert_string, values)\n",
1421     "    \n",
1422     "    for i in range(1, len(report.findall('./record'))+1):\n",
1423     "        field_names = []\n",
1424     "        cursor.execute('select id, report_metadata_report_id from reports where report_metadata_report_id = %s;', \n",
1425     "            [report.find('./report_metadata/report_id').text])\n",
1426     "        results = cursor.fetchall()\n",
1427     "        if len(results) != 1:\n",
1428     "            raise\n",
1429     "        else:\n",
1430     "            report_id = results[0][0]\n",
1431     "        values = {'report_id': report_id}\n",
1432     "        for f in [f for f in field_maps if field_maps[f]['pg_table'] == 'report_items']:\n",
1433     "            field_names += [field_maps[f]['pg_field_name']]\n",
1434     "            if field_maps[f]['pg_type'] == 'int':\n",
1435     "                values[field_maps[f]['pg_field_name']] = int(report.find(f.format(i)).text)\n",
1436     "            elif field_maps[f]['pg_type'] == 'timestamp':\n",
1437     "                values[field_maps[f]['pg_field_name']] = datetime.datetime.utcfromtimestamp(int(report.find(f.format(i)).text))\n",
1438     "            elif field_maps[f]['pg_type'] == 'inet':\n",
1439     "                values[field_maps[f]['pg_field_name']] = maybe_strip(report.find(f.format(i)).text)\n",
1440     "            else:\n",
1441     "                values[field_maps[f]['pg_field_name']] = maybe_strip(report.find(f.format(i)).text)\n",
1442     "        insert_string = 'insert into report_items (report_id, ' + ', '.join(field_names) + ') '\n",
1443     "        insert_string += 'values (%(report_id)s, ' + ', '.join('%({})s'.format(f) for f in field_names) + ');'\n",
1444     "        cursor.execute(insert_string, values)\n",
1445     "    connection.commit()"
1446    ]
1447   },
1448   {
1449    "cell_type": "code",
1450    "execution_count": 210,
1451    "metadata": {
1452     "collapsed": false
1453    },
1454    "outputs": [],
1455    "source": [
1456     "for rep in dmarc_reports:\n",
1457     "    write_report(conn, cur, dmarc_reports[rep])"
1458    ]
1459   },
1460   {
1461    "cell_type": "code",
1462    "execution_count": 211,
1463    "metadata": {
1464     "collapsed": true
1465    },
1466    "outputs": [],
1467    "source": [
1468     "conn.close()"
1469    ]
1470   },
1471   {
1472    "cell_type": "code",
1473    "execution_count": 208,
1474    "metadata": {
1475     "collapsed": true
1476    },
1477    "outputs": [],
1478    "source": [
1479     "conn.rollback()"
1480    ]
1481   },
1482   {
1483    "cell_type": "code",
1484    "execution_count": null,
1485    "metadata": {
1486     "collapsed": true
1487    },
1488    "outputs": [],
1489    "source": []
1490   }
1491  ],
1492  "metadata": {
1493   "kernelspec": {
1494    "display_name": "Python 3",
1495    "language": "python",
1496    "name": "python3"
1497   },
1498   "language_info": {
1499    "codemirror_mode": {
1500     "name": "ipython",
1501     "version": 3
1502    },
1503    "file_extension": ".py",
1504    "mimetype": "text/x-python",
1505    "name": "python",
1506    "nbconvert_exporter": "python",
1507    "pygments_lexer": "ipython3",
1508    "version": "3.4.3+"
1509   }
1510  },
1511  "nbformat": 4,
1512  "nbformat_minor": 0
1513 }