complete.ipynb

   1 {
   2  "cells": [
   3   {
   4    "cell_type": "code",
   5    "execution_count": 1,
   6    "metadata": {
   7     "collapsed": true
   8    },
   9    "outputs": [],
  10    "source": [
  11     "import configparser\n",
  12     "import imaplib\n",
  13     "import email\n",
  14     "import io\n",
  15     "import zipfile\n",
  16     "import xml.etree.ElementTree\n",
  17     "import psycopg2\n",
  18     "import re\n",
  19     "import datetime"
  20    ]
  21   },
  22   {
  23    "cell_type": "code",
  24    "execution_count": 2,
  25    "metadata": {
  26     "collapsed": true
  27    },
  28    "outputs": [],
  29    "source": [
  30     "def fetch_msg(num):\n",
  31     "    return mailbox.uid('FETCH', num, '(RFC822)')[1][0][1]"
  32    ]
  33   },
  34   {
  35    "cell_type": "code",
  36    "execution_count": 33,
  37    "metadata": {
  38     "collapsed": false
  39    },
  40    "outputs": [],
  41    "source": [
  42     "def xml_of_part(part):\n",
  43     "    try:\n",
  44     "        with zipfile.ZipFile(io.BytesIO(part.get_payload(decode=True))) as zf:\n",
  45     "            fn = zf.infolist()[0].filename\n",
  46     "            contents = zf.read(fn).decode('utf-8')\n",
  47     "            return xml.etree.ElementTree.fromstring(contents)\n",
  48     "    except zipfile.BadZipFile:\n",
  49     "        return None"
  50    ]
  51   },
  52   {
  53    "cell_type": "code",
  54    "execution_count": 28,
  55    "metadata": {
  56     "collapsed": true
  57    },
  58    "outputs": [],
  59    "source": [
  60     "def xml_of(message):\n",
  61     "    reports = []\n",
  62     "    if message.is_multipart():\n",
  63     "        for p in message.get_payload():\n",
  64     "            if 'zip' in p.get_content_type():\n",
  65     "                report = xml_of_part(p)\n",
  66     "                if report:\n",
  67     "                    reports += [report]\n",
  68     "    else:\n",
  69     "        reports = [xml_of_part(message)]\n",
  70     "    return reports"
  71    ]
  72   },
  73   {
  74    "cell_type": "code",
  75    "execution_count": 5,
  76    "metadata": {
  77     "collapsed": true
  78    },
  79    "outputs": [],
  80    "source": [
  81     "def extract_report(msg):\n",
  82     "    pmsg = email.message_from_bytes(msg)\n",
  83     "    return xml_of(pmsg)"
  84    ]
  85   },
  86   {
  87    "cell_type": "code",
  88    "execution_count": 6,
  89    "metadata": {
  90     "collapsed": true
  91    },
  92    "outputs": [],
  93    "source": [
  94     "def maybe_strip(text):\n",
  95     "    if text:\n",
  96     "        return text.strip()\n",
  97     "    else:\n",
  98     "        return ''"
  99    ]
 100   },
 101   {
 102    "cell_type": "code",
 103    "execution_count": 7,
 104    "metadata": {
 105     "collapsed": true
 106    },
 107    "outputs": [],
 108    "source": [
 109     "field_maps = {'./policy_published/adkim': {'pg_field_name': 'policy_published_adkim',\n",
 110     "  'pg_table': 'reports',\n",
 111     "  'pg_type': 'varchar'},\n",
 112     " './policy_published/aspf': {'pg_field_name': 'policy_published_aspf',\n",
 113     "  'pg_table': 'reports',\n",
 114     "  'pg_type': 'varchar'},\n",
 115     " './policy_published/domain': {'pg_field_name': 'policy_published_domain',\n",
 116     "  'pg_table': 'reports',\n",
 117     "  'pg_type': 'varchar'},\n",
 118     " './policy_published/p': {'pg_field_name': 'policy_published_p',\n",
 119     "  'pg_table': 'reports',\n",
 120     "  'pg_type': 'varchar'},\n",
 121     " './policy_published/pct': {'pg_field_name': 'policy_published_pct',\n",
 122     "  'pg_table': 'reports',\n",
 123     "  'pg_type': 'int'},\n",
 124     " './record[{}]/auth_results/dkim/domain': {'pg_field_name': 'auth_results_dkim_domain',\n",
 125     "  'pg_table': 'report_items',\n",
 126     "  'pg_type': 'varchar'},\n",
 127     " './record[{}]/auth_results/dkim/result': {'pg_field_name': 'auth_results_dkim_result',\n",
 128     "  'pg_table': 'report_items',\n",
 129     "  'pg_type': 'varchar'},\n",
 130     " './record[{}]/auth_results/spf/domain': {'pg_field_name': 'auth_results_spf_domain',\n",
 131     "  'pg_table': 'report_items',\n",
 132     "  'pg_type': 'varchar'},\n",
 133     " './record[{}]/auth_results/spf/result': {'pg_field_name': 'auth_results_spf_result',\n",
 134     "  'pg_table': 'report_items',\n",
 135     "  'pg_type': 'varchar'},\n",
 136     " './record[{}]/identifiers/header_from': {'pg_field_name': 'identifiers_header_from',\n",
 137     "  'pg_table': 'report_items',\n",
 138     "  'pg_type': 'varchar'},\n",
 139     " './record[{}]/row/count': {'pg_field_name': 'count',\n",
 140     "  'pg_table': 'report_items',\n",
 141     "  'pg_type': 'int'},\n",
 142     " './record[{}]/row/policy_evaluated/disposition': {'pg_field_name': 'policy_evaluated_disposition',\n",
 143     "  'pg_table': 'report_items',\n",
 144     "  'pg_type': 'varchar'},\n",
 145     " './record[{}]/row/policy_evaluated/dkim': {'pg_field_name': 'policy_evaluated_dkim',\n",
 146     "  'pg_table': 'report_items',\n",
 147     "  'pg_type': 'varchar'},\n",
 148     " './record[{}]/row/policy_evaluated/spf': {'pg_field_name': 'policy_evaluated_spf',\n",
 149     "  'pg_table': 'report_items',\n",
 150     "  'pg_type': 'varchar'},\n",
 151     " './record[{}]/row/source_ip': {'pg_field_name': 'source_ip',\n",
 152     "  'pg_table': 'report_items',\n",
 153     "  'pg_type': 'inet'},\n",
 154     " './report_metadata/date_range/begin': {'pg_field_name': 'report_metadata_date_range_begin',\n",
 155     "  'pg_table': 'reports',\n",
 156     "  'pg_type': 'timestamptz'},\n",
 157     " './report_metadata/date_range/end': {'pg_field_name': 'report_metadata_date_range_end',\n",
 158     "  'pg_table': 'reports',\n",
 159     "  'pg_type': 'timestamptz'},\n",
 160     " './report_metadata/email': {'pg_field_name': 'report_metadata_email',\n",
 161     "  'pg_table': 'reports',\n",
 162     "  'pg_type': 'varchar'},\n",
 163     " './report_metadata/org_name': {'pg_field_name': 'report_metadata_org_name',\n",
 164     "  'pg_table': 'reports',\n",
 165     "  'pg_type': 'varchar'},\n",
 166     " './report_metadata/report_id': {'pg_field_name': 'report_metadata_report_id',\n",
 167     "  'pg_table': 'reports',\n",
 168     "  'pg_type': 'varchar'}}"
 169    ]
 170   },
 171   {
 172    "cell_type": "code",
 173    "execution_count": 8,
 174    "metadata": {
 175     "collapsed": true
 176    },
 177    "outputs": [],
 178    "source": [
 179     "def build_insert_command(table_name, report, preamble_values=None, i=None):\n",
 180     "        field_names = []\n",
 181     "        if preamble_values:\n",
 182     "            values = preamble_values.copy()\n",
 183     "        else:\n",
 184     "            values = {}\n",
 185     "        for f in [f for f in field_maps if field_maps[f]['pg_table'] == table_name]:\n",
 186     "            # print(f)\n",
 187     "            if i:\n",
 188     "                fp = f.format(i)\n",
 189     "            else:\n",
 190     "                fp = f\n",
 191     "            field_names += [field_maps[f]['pg_field_name']]\n",
 192     "            if field_maps[f]['pg_type'] == 'int':\n",
 193     "                values[field_maps[f]['pg_field_name']] = int(report.find(fp).text)\n",
 194     "            elif field_maps[f]['pg_type'] == 'timestamptz':\n",
 195     "                # values[field_maps[f]['pg_field_name']] = datetime.datetime.utcfromtimestamp(int(report.find(fp).text))\n",
 196     "                values[field_maps[f]['pg_field_name']] = \\\n",
 197     "                    datetime.datetime.fromtimestamp(int(report.find(fp).text),  \n",
 198     "                        # tz=psycopg2.tz.FixedOffsetTimezone(offset=0, name='UTC'))\n",
 199     "                                                    tz=datetime.timezone.utc)\n",
 200     "            elif field_maps[f]['pg_type'] == 'inet':\n",
 201     "                values[field_maps[f]['pg_field_name']] = maybe_strip(report.find(fp).text)\n",
 202     "            else:\n",
 203     "                values[field_maps[f]['pg_field_name']] = maybe_strip(report.find(fp).text)\n",
 204     "        insert_string = 'insert into {} ('.format(table_name)\n",
 205     "        if preamble_values:\n",
 206     "            insert_string += ', '.join(sorted(preamble_values.keys())) + ', '\n",
 207     "        insert_string += ', '.join(field_names) + ') '\n",
 208     "        insert_string += 'values ('\n",
 209     "        if preamble_values:\n",
 210     "            insert_string += ', '.join('%({})s'.format(fn) for fn in sorted(preamble_values.keys())) + ', '\n",
 211     "        insert_string += ', '.join('%({})s'.format(f) for f in field_names) + ');'\n",
 212     "        return insert_string, values"
 213    ]
 214   },
 215   {
 216    "cell_type": "code",
 217    "execution_count": 9,
 218    "metadata": {
 219     "collapsed": true
 220    },
 221    "outputs": [],
 222    "source": [
 223     "def write_report(connection, cursor, report):\n",
 224     "    insert_string, values = build_insert_command('reports', report)\n",
 225     "    # print(insert_string, values)\n",
 226     "    cursor.execute(insert_string, values)\n",
 227     "    \n",
 228     "    for i in range(1, len(report.findall('./record'))+1):\n",
 229     "        field_names = []\n",
 230     "        cursor.execute('select id, report_metadata_report_id from reports where report_metadata_report_id = %s;', \n",
 231     "            [report.find('./report_metadata/report_id').text])\n",
 232     "        results = cursor.fetchall()\n",
 233     "        if len(results) != 1:\n",
 234     "            raise RuntimeError('Could not find report record for report item')\n",
 235     "        else:\n",
 236     "            report_id = results[0][0]\n",
 237     "        insert_string, values = build_insert_command('report_items', report, i=i,\n",
 238     "                                                     preamble_values={'report_id': report_id})\n",
 239     "        # print(insert_string, values)\n",
 240     "        cursor.execute(insert_string, values)\n",
 241     "    connection.commit()"
 242    ]
 243   },
 244   {
 245    "cell_type": "code",
 246    "execution_count": 10,
 247    "metadata": {
 248     "collapsed": false
 249    },
 250    "outputs": [
 251     {
 252      "data": {
 253       "text/plain": [
 254        "['dmarc.ini']"
 255       ]
 256      },
 257      "execution_count": 10,
 258      "metadata": {},
 259      "output_type": "execute_result"
 260     }
 261    ],
 262    "source": [
 263     "config = configparser.ConfigParser()\n",
 264     "config.read('dmarc.ini')"
 265    ]
 266   },
 267   {
 268    "cell_type": "code",
 269    "execution_count": 17,
 270    "metadata": {
 271     "collapsed": false
 272    },
 273    "outputs": [
 274     {
 275      "data": {
 276       "text/plain": [
 277        "'ogedei.njae.me.uk'"
 278       ]
 279      },
 280      "execution_count": 17,
 281      "metadata": {},
 282      "output_type": "execute_result"
 283     }
 284    ],
 285    "source": [
 286     "config['database']['server']"
 287    ]
 288   },
 289   {
 290    "cell_type": "code",
 291    "execution_count": 11,
 292    "metadata": {
 293     "collapsed": false
 294    },
 295    "outputs": [],
 296    "source": [
 297     "conn = psycopg2.connect(host=config['database']['server'],\n",
 298     "                        database=config['database']['database'], \n",
 299     "                        user=config['database']['username'], \n",
 300     "                        password=config['database']['password'])\n",
 301     "cur = conn.cursor()"
 302    ]
 303   },
 304   {
 305    "cell_type": "code",
 306    "execution_count": 18,
 307    "metadata": {
 308     "collapsed": false
 309    },
 310    "outputs": [
 311     {
 312      "data": {
 313       "text/plain": [
 314        "datetime.datetime(2017, 1, 13, 23, 59, 59, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))"
 315       ]
 316      },
 317      "execution_count": 18,
 318      "metadata": {},
 319      "output_type": "execute_result"
 320     }
 321    ],
 322    "source": [
 323     "cur.execute('select max(report_metadata_date_range_end) from reports')\n",
 324     "results = cur.fetchall()\n",
 325     "most_recent_date = results[0][0]\n",
 326     "most_recent_date"
 327    ]
 328   },
 329   {
 330    "cell_type": "code",
 331    "execution_count": 19,
 332    "metadata": {
 333     "collapsed": false
 334    },
 335    "outputs": [
 336     {
 337      "data": {
 338       "text/plain": [
 339        "('OK', [b'541'])"
 340       ]
 341      },
 342      "execution_count": 19,
 343      "metadata": {},
 344      "output_type": "execute_result"
 345     }
 346    ],
 347    "source": [
 348     "mailbox = imaplib.IMAP4(host=config['imap']['server'], \n",
 349     "                      port=config['imap']['port'])\n",
 350     "mailbox.starttls()\n",
 351     "mailbox.login(config['imap']['username'], config['imap']['password'])\n",
 352     "mailbox.select('INBOX', readonly=True)"
 353    ]
 354   },
 355   {
 356    "cell_type": "code",
 357    "execution_count": 30,
 358    "metadata": {
 359     "collapsed": false
 360    },
 361    "outputs": [
 362     {
 363      "data": {
 364       "text/plain": [
 365        "('SINCE 11-Jan-2017',\n",
 366        " 'OK',\n",
 367        " [b'519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542'])"
 368       ]
 369      },
 370      "execution_count": 30,
 371      "metadata": {},
 372      "output_type": "execute_result"
 373     }
 374    ],
 375    "source": [
 376     "if most_recent_date:\n",
 377     "    mails_from = \"SINCE \" + (most_recent_date - datetime.timedelta(days=2)).strftime(\"%d-%b-%Y\")\n",
 378     "else:\n",
 379     "    mails_from = \"ALL\"\n",
 380     "resp, nums = mailbox.uid('SEARCH', None, mails_from)\n",
 381     "mails_from, resp, nums"
 382    ]
 383   },
 384   {
 385    "cell_type": "code",
 386    "execution_count": 36,
 387    "metadata": {
 388     "collapsed": false
 389    },
 390    "outputs": [
 391     {
 392      "data": {
 393       "text/plain": [
 394        "[<Element 'feedback' at 0x7f0d85c1af48>,\n",
 395        " <Element 'feedback' at 0x7f0d85b56c78>,\n",
 396        " <Element 'feedback' at 0x7f0d85b63ae8>,\n",
 397        " <Element 'feedback' at 0x7f0d85b63a98>,\n",
 398        " <Element 'feedback' at 0x7f0d85b6f1d8>,\n",
 399        " <Element 'feedback' at 0x7f0d85b6f2c8>,\n",
 400        " <Element 'feedback' at 0x7f0d85b6f098>,\n",
 401        " <Element 'feedback' at 0x7f0d85b74b38>,\n",
 402        " <Element 'feedback' at 0x7f0d85b7ecc8>,\n",
 403        " <Element 'feedback' at 0x7f0d85b7e2c8>,\n",
 404        " <Element 'feedback' at 0x7f0d85b04b38>,\n",
 405        " <Element 'feedback' at 0x7f0d85b04cc8>,\n",
 406        " <Element 'feedback' at 0x7f0d85b04bd8>,\n",
 407        " <Element 'feedback' at 0x7f0d85b0e098>,\n",
 408        " <Element 'feedback' at 0x7f0d85b04b88>,\n",
 409        " <Element 'feedback' at 0x7f0d85b13138>,\n",
 410        " <Element 'feedback' at 0x7f0d85b130e8>,\n",
 411        " <Element 'feedback' at 0x7f0d85b1b818>,\n",
 412        " <Element 'feedback' at 0x7f0d85b1b7c8>,\n",
 413        " <Element 'feedback' at 0x7f0d85b279a8>,\n",
 414        " <Element 'feedback' at 0x7f0d85b27958>,\n",
 415        " <Element 'feedback' at 0x7f0d85b2f458>,\n",
 416        " <Element 'feedback' at 0x7f0d85b2f408>]"
 417       ]
 418      },
 419      "execution_count": 36,
 420      "metadata": {},
 421      "output_type": "execute_result"
 422     }
 423    ],
 424    "source": [
 425     "dmarc_reports = [report for report_set in [extract_report(fetch_msg(n)) for n in nums[0].split()]\n",
 426     "                for report in report_set\n",
 427     "                if report]\n",
 428     "dmarc_reports"
 429    ]
 430   },
 431   {
 432    "cell_type": "code",
 433    "execution_count": 37,
 434    "metadata": {
 435     "collapsed": false
 436    },
 437    "outputs": [
 438     {
 439      "data": {
 440       "text/plain": [
 441        "['3644362597779170059',\n",
 442        " '1484103023.660115',\n",
 443        " '16703417802819394712',\n",
 444        " '1484189549.594225',\n",
 445        " '1484276013.355502',\n",
 446        " '4b1a497aa31d4fdbaaa56c13eb7987fb@hotmail.com',\n",
 447        " '12376654726187971850',\n",
 448        " '1484362840.261197',\n",
 449        " '16723890724451093500',\n",
 450        " '1484448519.463747',\n",
 451        " '3eb8d9f114044eb7bd0509a2e327451d@hotmail.com',\n",
 452        " '17985849130842520792',\n",
 453        " 'b5559b13fcd04062ae46c911334f47e2@hotmail.com',\n",
 454        " '3557556884331174573',\n",
 455        " '80eff7abd7b844c0830df5cdd3fd476c@hotmail.com',\n",
 456        " '13353913580885545629',\n",
 457        " '1484707828.212792',\n",
 458        " '8880815782389375117',\n",
 459        " '1484794248.174834',\n",
 460        " '15941205824990790163',\n",
 461        " '1484880971.613805',\n",
 462        " '12141824316076722331',\n",
 463        " '1484966874.391661']"
 464       ]
 465      },
 466      "execution_count": 37,
 467      "metadata": {},
 468      "output_type": "execute_result"
 469     }
 470    ],
 471    "source": [
 472     "[r.find('./report_metadata/report_id').text for r in dmarc_reports]"
 473    ]
 474   },
 475   {
 476    "cell_type": "code",
 477    "execution_count": 46,
 478    "metadata": {
 479     "collapsed": false
 480    },
 481    "outputs": [
 482     {
 483      "data": {
 484       "text/plain": [
 485        "('BYE', [b'Logging out'])"
 486       ]
 487      },
 488      "execution_count": 46,
 489      "metadata": {},
 490      "output_type": "execute_result"
 491     }
 492    ],
 493    "source": [
 494     "mailbox.close()\n",
 495     "mailbox.logout()"
 496    ]
 497   },
 498   {
 499    "cell_type": "code",
 500    "execution_count": 47,
 501    "metadata": {
 502     "collapsed": false
 503    },
 504    "outputs": [
 505     {
 506      "name": "stdout",
 507      "output_type": "stream",
 508      "text": [
 509       "write 1448246712.259522\n",
 510       "write 75eef2128eb84e9ca8e4837f3d4e31bd@hotmail.com\n",
 511       "write 9138782308848375092\n",
 512       "write 840fc02d5cb847ec9f007fb236c0c190@hotmail.com\n",
 513       "write 15111277194568576101\n",
 514       "write 1448419746.206772\n",
 515       "write 1448592471.754446\n",
 516       "write c8e4fe772ae3434594e8396a8abf77b1@hotmail.com\n",
 517       "write 2273327349069127175\n",
 518       "write 1448851661.602960\n",
 519       "write 1448938177.97045\n",
 520       "write 18203685646345145151\n",
 521       "write 12700770160536483846\n",
 522       "write 1449024342.601561\n",
 523       "write 1fadaaf3d9d544568a41042a1f42df05@hotmail.com\n",
 524       "write 3544432548848738700\n",
 525       "write 1449110875.443288\n",
 526       "write 1449197294.177690\n",
 527       "write 10356602625290246518\n",
 528       "write 1449283514.868026\n",
 529       "write 4654132158287507198\n",
 530       "write 5164714352250096219\n",
 531       "write 1449542804.259788\n",
 532       "write 20246ec4a70041caa276560032fdc595@hotmail.com\n",
 533       "write 14089985608998676943\n",
 534       "write 1449629119.120001\n",
 535       "write c8f339f8f3364d78820dd03a84d11daa@hotmail.com\n",
 536       "write 1233953228184721030\n",
 537       "write 17984785070730076605\n",
 538       "write 1449888489.668429\n",
 539       "write 121bb39637c14847a40669dd3e7cdf49@hotmail.com\n",
 540       "write 14661842628106423589\n",
 541       "write 1449974899.615312\n",
 542       "write 7834597727856283739\n",
 543       "write f94f00dee713479fb8bfdbcad539da88@hotmail.com\n",
 544       "write 17659080448126516391\n",
 545       "write 1450234735.690828\n",
 546       "write 17880573400612806781\n",
 547       "write 1451184218.12383\n",
 548       "write 6505353761044749597\n",
 549       "write 02aca4c6bb144bb6b54773374c14a43a@hotmail.com\n",
 550       "write 1451443783.692950\n",
 551       "write 12137203242660163862\n",
 552       "write d1019291012e4f4589f59f1dacef31a3@hotmail.com\n",
 553       "write 1451616408.476341\n",
 554       "write 11743474449139138027\n",
 555       "write e245ef81f05b4697bea34bed977d8286@hotmail.com\n",
 556       "write 1452134765.308922\n",
 557       "write 271c80b9234a4c99bb9608abc5470c99@hotmail.com\n",
 558       "write 15974729567081493290\n",
 559       "write ba1e9c35eec5428b8c3d6a32d25daa46@hotmail.com\n",
 560       "write 1452393953.83099\n",
 561       "write d71792d8afc246e9a4756bd13e600b59@hotmail.com\n",
 562       "write 1452480188.666644\n",
 563       "write 1452566814.805501\n",
 564       "write fc0750780e0d4b1395c4c9f41cb9791f@hotmail.com\n",
 565       "write 1452653275.294149\n",
 566       "write 1452741832.342075\n",
 567       "write 2884439811958006165\n",
 568       "write 3508955360705645678\n",
 569       "write 1452826445.971145\n",
 570       "write 1452912512.114840\n",
 571       "write eda16c25bbdb48b2bae0d18eab13f736@hotmail.com\n",
 572       "write 724976339745122723\n",
 573       "write 1453084984.369330\n",
 574       "write 1453171440.157305\n",
 575       "write 1862812391966551302\n",
 576       "write 1453258067.440890\n",
 577       "write 1453344550.144698\n",
 578       "write 2002148329698541727\n",
 579       "write 6110291457768008366\n",
 580       "write de651cefbc0749a3aa3d91cdc5084f5a@hotmail.com\n",
 581       "write 1453517236.399019\n",
 582       "write 14593873841710243963\n",
 583       "write ba64911adb0743d0b926588647ec5dcf@hotmail.com\n",
 584       "write 1453689981.768611\n",
 585       "write 13959504937502303973\n",
 586       "write 3eba8b9e31724809aaf8d517e5656c4b@hotmail.com\n",
 587       "write 1453776417.898739\n",
 588       "write 9594298247075547297\n",
 589       "write d77c5b90344149f5bb7b50ff69400aeb@hotmail.com\n",
 590       "write 1453862958.482897\n",
 591       "write 3267388508667988463\n",
 592       "write 580ca0a74948493ba8b7c6e62b8c81d9@hotmail.com\n",
 593       "write 6876229717666715946\n",
 594       "write 6474240574237085621\n",
 595       "write 1454381343.228960\n",
 596       "write 77b6c2aa32bf440aa240195db229cd4a@hotmail.com\n",
 597       "write 1922265692629464594\n",
 598       "write 1454467952.258012\n",
 599       "write 1454640482.903992\n",
 600       "write 16409503372348966326\n",
 601       "write 456ffff78456494fbaed47b465259d46@hotmail.com\n",
 602       "write 543258397004515473\n",
 603       "write 4158049796460953654\n",
 604       "write a6b6aec1af1647eb8c6fd83baaed6bbe@hotmail.com\n",
 605       "write 1454986282.636073\n",
 606       "write 8986675713054039362\n",
 607       "write 30d5ef78d6d746a4a777c56e7ca6f1c6@hotmail.com\n",
 608       "write 1455072659.49250\n",
 609       "write 10744929815548178249\n",
 610       "write 1178c4fe34364ff39cb6e24a51219093@hotmail.com\n",
 611       "write 730219275619457\n",
 612       "write 164180405175632647\n",
 613       "write 16520810497197721925\n",
 614       "write 13f28e952d67451281b90d55eee9c7c3@hotmail.com\n",
 615       "write 1455418081.446180\n",
 616       "write 641b4cc500334a7eb3f649b21af003ca@hotmail.com\n",
 617       "write 14416654486328871570\n",
 618       "write dbe2f49dbea947428b179e0ecec5f813@hotmail.com\n",
 619       "write 1455679107.469767\n",
 620       "write 7815164892280952980\n",
 621       "write 511d6fbb8484454b916ad8d799938390@hotmail.com\n",
 622       "write 1455763965.140751\n",
 623       "write 10491829640821461358\n",
 624       "write 13702792868066092542\n",
 625       "write 1456109267.60200\n",
 626       "write 2cfe3d0ec75b43f89a6a7ae57777e25c@hotmail.com\n",
 627       "write 9037150256193522538\n",
 628       "write e327753089e04950906848d104a93695@hotmail.com\n",
 629       "write 1456282325.455667\n",
 630       "write 3584764172722215181\n",
 631       "write 1456455410.661669\n",
 632       "write 16824795799639390032\n",
 633       "write 14217880258295310962\n",
 634       "write 1456541727.873510\n",
 635       "write 1102163566627474204\n",
 636       "write 1456713880.905762\n",
 637       "write 1456800606.884839\n",
 638       "write 1bcc49be66744cc7a70ae1cc53515bbf@hotmail.com\n",
 639       "write 11019573554877343650\n",
 640       "write 1456886761.643377\n",
 641       "write 36dd4e2b3f424bb6919d2d5d4c77c8dd@hotmail.com\n",
 642       "write b6d15133598b4e00b21c8e2b7cbec05e@hotmail.com\n",
 643       "write 1457059537.625770\n",
 644       "write 85136d21c514431e9f5cf9d36faa4a22@hotmail.com\n",
 645       "write 1457146943.63077\n",
 646       "write 1457232742.472142\n",
 647       "write ea3a96df66a845c5a666068ef8ca1368@hotmail.com\n",
 648       "write 17044038014166863505\n",
 649       "write 1457318880.887129\n",
 650       "write 3689737760348689620\n",
 651       "write 726a3261dfab4b4590b5fc898c561b08@hotmail.com\n",
 652       "write 129a9dc3ff2e40ae8a3890ecc1c291d9@hotmail.com\n",
 653       "write 1457491692.793016\n",
 654       "write 1207168150882246442\n",
 655       "write 10706879170593875813\n",
 656       "write 4226319448276355598\n",
 657       "write 1457750956.253773\n",
 658       "write 12434676377746978866\n",
 659       "write 43e7b5e2e0fb400aa914058360c8aaab@hotmail.com\n",
 660       "write 1457919850.989656\n",
 661       "write 594749737641304465\n",
 662       "write 18c9a966da81401e80ca2a22310ffee6@hotmail.com\n",
 663       "write 1458006833.731671\n",
 664       "write 6996084840178215735\n",
 665       "write 1458093082.193350\n",
 666       "write 3188090359734380692\n",
 667       "write 7006513620611789399\n",
 668       "write 1458353823.362308\n",
 669       "write 17071564483265101388\n",
 670       "write 1458524834.32161\n",
 671       "write 1458611429.595569\n",
 672       "write 14662396456930987863\n",
 673       "write 1458784545.303847\n",
 674       "write 13032699446207263761\n",
 675       "write 8426152571808739514\n",
 676       "write 1458957186.548175\n",
 677       "write 2150510829392606201\n",
 678       "write 68aad5080a774e2c997d159b546569b9@hotmail.com\n",
 679       "write 1459129809.695034\n",
 680       "write 16143280651570354241\n",
 681       "write 8c177254c3cb41869dc3afab59f74c76@hotmail.com\n",
 682       "write 15410706527896810898\n",
 683       "write 1459216304.582931\n",
 684       "write 15497495941279624940\n",
 685       "write 1459302353.261157\n",
 686       "write 7773a696f4a54f1e8c01f4644fbb94ee@hotmail.com\n",
 687       "write 15185964531645951164\n",
 688       "write 1459391203.751219\n",
 689       "write 9452413620101638402\n",
 690       "write 1459475662.735732\n"
 691      ]
 692     }
 693    ],
 694    "source": [
 695     "for report in dmarc_reports:\n",
 696     "    cur.execute('select id, report_metadata_report_id from reports where report_metadata_report_id = %s;', \n",
 697     "            [report.find('./report_metadata/report_id').text])\n",
 698     "    results = cur.fetchall()\n",
 699     "    if not results:\n",
 700     "        print('write', report.find('./report_metadata/report_id').text)\n",
 701     "        write_report(conn, cur, report)"
 702    ]
 703   },
 704   {
 705    "cell_type": "code",
 706    "execution_count": 48,
 707    "metadata": {
 708     "collapsed": true
 709    },
 710    "outputs": [],
 711    "source": [
 712     "# conn.rollback()"
 713    ]
 714   },
 715   {
 716    "cell_type": "code",
 717    "execution_count": 52,
 718    "metadata": {
 719     "collapsed": false
 720    },
 721    "outputs": [
 722     {
 723      "data": {
 724       "text/plain": [
 725        "('insert into reports (policy_published_adkim, report_metadata_org_name, policy_published_pct, policy_published_aspf, policy_published_domain, report_metadata_date_range_begin, report_metadata_report_id, report_metadata_email, policy_published_p, report_metadata_date_range_end) values (%(policy_published_adkim)s, %(report_metadata_org_name)s, %(policy_published_pct)s, %(policy_published_aspf)s, %(policy_published_domain)s, %(report_metadata_date_range_begin)s, %(report_metadata_report_id)s, %(report_metadata_email)s, %(policy_published_p)s, %(report_metadata_date_range_end)s);',\n",
 726        " {'policy_published_adkim': 'r',\n",
 727        "  'policy_published_aspf': 'r',\n",
 728        "  'policy_published_domain': 'njae.me.uk',\n",
 729        "  'policy_published_p': 'none',\n",
 730        "  'policy_published_pct': 100,\n",
 731        "  'report_metadata_date_range_begin': datetime.datetime(2015, 11, 22, 0, 0, tzinfo=datetime.timezone.utc),\n",
 732        "  'report_metadata_date_range_end': datetime.datetime(2015, 11, 22, 23, 59, 59, tzinfo=datetime.timezone.utc),\n",
 733        "  'report_metadata_email': 'postmaster@dmarc.yahoo.com',\n",
 734        "  'report_metadata_org_name': 'Yahoo! Inc.',\n",
 735        "  'report_metadata_report_id': '1448246712.259522'})"
 736       ]
 737      },
 738      "execution_count": 52,
 739      "metadata": {},
 740      "output_type": "execute_result"
 741     }
 742    ],
 743    "source": [
 744     "build_insert_command('reports', dmarc_reports[0])"
 745    ]
 746   },
 747   {
 748    "cell_type": "code",
 749    "execution_count": 53,
 750    "metadata": {
 751     "collapsed": false
 752    },
 753    "outputs": [
 754     {
 755      "data": {
 756       "text/plain": [
 757        "('insert into reports (policy_published_adkim, report_metadata_org_name, policy_published_pct, policy_published_aspf, policy_published_domain, report_metadata_date_range_begin, report_metadata_report_id, report_metadata_email, policy_published_p, report_metadata_date_range_end) values (%(policy_published_adkim)s, %(report_metadata_org_name)s, %(policy_published_pct)s, %(policy_published_aspf)s, %(policy_published_domain)s, %(report_metadata_date_range_begin)s, %(report_metadata_report_id)s, %(report_metadata_email)s, %(policy_published_p)s, %(report_metadata_date_range_end)s);',\n",
 758        " {'policy_published_adkim': 'r',\n",
 759        "  'policy_published_aspf': 'r',\n",
 760        "  'policy_published_domain': 'njae.me.uk',\n",
 761        "  'policy_published_p': 'none',\n",
 762        "  'policy_published_pct': 100,\n",
 763        "  'report_metadata_date_range_begin': datetime.datetime(2016, 3, 31, 0, 0, tzinfo=datetime.timezone.utc),\n",
 764        "  'report_metadata_date_range_end': datetime.datetime(2016, 3, 31, 23, 59, 59, tzinfo=datetime.timezone.utc),\n",
 765        "  'report_metadata_email': 'postmaster@dmarc.yahoo.com',\n",
 766        "  'report_metadata_org_name': 'Yahoo! Inc.',\n",
 767        "  'report_metadata_report_id': '1459475662.735732'})"
 768       ]
 769      },
 770      "execution_count": 53,
 771      "metadata": {},
 772      "output_type": "execute_result"
 773     }
 774    ],
 775    "source": [
 776     "build_insert_command('reports', dmarc_reports[-1])"
 777    ]
 778   },
 779   {
 780    "cell_type": "code",
 781    "execution_count": 50,
 782    "metadata": {
 783     "collapsed": false
 784    },
 785    "outputs": [
 786     {
 787      "data": {
 788       "text/plain": [
 789        "datetime.datetime(2015, 11, 22, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name='UTC'))"
 790       ]
 791      },
 792      "execution_count": 50,
 793      "metadata": {},
 794      "output_type": "execute_result"
 795     }
 796    ],
 797    "source": [
 798     "datetime.datetime.fromtimestamp(int('1448150400'),  \n",
 799     "                        tz=psycopg2.tz.FixedOffsetTimezone(offset=0, name='UTC'))"
 800    ]
 801   },
 802   {
 803    "cell_type": "code",
 804    "execution_count": 51,
 805    "metadata": {
 806     "collapsed": true
 807    },
 808    "outputs": [],
 809    "source": [
 810     "conn.close()"
 811    ]
 812   },
 813   {
 814    "cell_type": "code",
 815    "execution_count": null,
 816    "metadata": {
 817     "collapsed": true
 818    },
 819    "outputs": [],
 820    "source": []
 821   }
 822  ],
 823  "metadata": {
 824   "kernelspec": {
 825    "display_name": "Python 3",
 826    "language": "python",
 827    "name": "python3"
 828   },
 829   "language_info": {
 830    "codemirror_mode": {
 831     "name": "ipython",
 832     "version": 3
 833    },
 834    "file_extension": ".py",
 835    "mimetype": "text/x-python",
 836    "name": "python",
 837    "nbconvert_exporter": "python",
 838    "pygments_lexer": "ipython3",
 839    "version": "3.5.2+"
 840   }
 841  },
 842  "nbformat": 4,
 843  "nbformat_minor": 0
 844 }