X-Git-Url: https://git.njae.me.uk/?a=blobdiff_plain;f=complete.ipynb;h=4f607a123ec46dd67c014c57f64eefa1298a4175;hb=HEAD;hp=bd272eb7ad705ecee8787fc5aa3eaaa633b1f3fb;hpb=4501f68fa04a34934ff3c3ec8e9ddf43e060c5e5;p=dmarc.git diff --git a/complete.ipynb b/complete.ipynb index bd272eb..4f607a1 100644 --- a/complete.ipynb +++ b/complete.ipynb @@ -33,22 +33,25 @@ }, { "cell_type": "code", - "execution_count": 3, + "execution_count": 33, "metadata": { - "collapsed": true + "collapsed": false }, "outputs": [], "source": [ "def xml_of_part(part):\n", - " with zipfile.ZipFile(io.BytesIO(part.get_payload(decode=True))) as zf:\n", + " try:\n", + " with zipfile.ZipFile(io.BytesIO(part.get_payload(decode=True))) as zf:\n", " fn = zf.infolist()[0].filename\n", " contents = zf.read(fn).decode('utf-8')\n", - " return xml.etree.ElementTree.fromstring(contents)" + " return xml.etree.ElementTree.fromstring(contents)\n", + " except zipfile.BadZipFile:\n", + " return None" ] }, { "cell_type": "code", - "execution_count": 4, + "execution_count": 28, "metadata": { "collapsed": true }, @@ -59,7 +62,9 @@ " if message.is_multipart():\n", " for p in message.get_payload():\n", " if 'zip' in p.get_content_type():\n", - " reports += [xml_of_part(p)]\n", + " report = xml_of_part(p)\n", + " if report:\n", + " reports += [report]\n", " else:\n", " reports = [xml_of_part(message)]\n", " return reports" @@ -148,10 +153,10 @@ " 'pg_type': 'inet'},\n", " './report_metadata/date_range/begin': {'pg_field_name': 'report_metadata_date_range_begin',\n", " 'pg_table': 'reports',\n", - " 'pg_type': 'timestamp'},\n", + " 'pg_type': 'timestamptz'},\n", " './report_metadata/date_range/end': {'pg_field_name': 'report_metadata_date_range_end',\n", " 'pg_table': 'reports',\n", - " 'pg_type': 'timestamp'},\n", + " 'pg_type': 'timestamptz'},\n", " './report_metadata/email': {'pg_field_name': 'report_metadata_email',\n", " 'pg_table': 'reports',\n", " 'pg_type': 'varchar'},\n", @@ -186,8 +191,12 @@ " field_names += [field_maps[f]['pg_field_name']]\n", " if field_maps[f]['pg_type'] == 'int':\n", " values[field_maps[f]['pg_field_name']] = int(report.find(fp).text)\n", - " elif field_maps[f]['pg_type'] == 'timestamp':\n", - " values[field_maps[f]['pg_field_name']] = datetime.datetime.utcfromtimestamp(int(report.find(fp).text))\n", + " elif field_maps[f]['pg_type'] == 'timestamptz':\n", + " # values[field_maps[f]['pg_field_name']] = datetime.datetime.utcfromtimestamp(int(report.find(fp).text))\n", + " values[field_maps[f]['pg_field_name']] = \\\n", + " datetime.datetime.fromtimestamp(int(report.find(fp).text), \n", + " # tz=psycopg2.tz.FixedOffsetTimezone(offset=0, name='UTC'))\n", + " tz=datetime.timezone.utc)\n", " elif field_maps[f]['pg_type'] == 'inet':\n", " values[field_maps[f]['pg_field_name']] = maybe_strip(report.find(fp).text)\n", " else:\n", @@ -236,59 +245,28 @@ "cell_type": "code", "execution_count": 10, "metadata": { - "collapsed": true + "collapsed": false }, - "outputs": [], + "outputs": [ + { + "data": { + "text/plain": [ + "['dmarc.ini']" + ] + }, + "execution_count": 10, + "metadata": {}, + "output_type": "execute_result" + } + ], "source": [ - "def write_report_old(connection, cursor, report):\n", - " \n", - " field_names = []\n", - " values = {}\n", - " for f in [f for f in field_maps if field_maps[f]['pg_table'] == 'reports']:\n", - " field_names += [field_maps[f]['pg_field_name']]\n", - " if field_maps[f]['pg_type'] == 'int':\n", - " values[field_maps[f]['pg_field_name']] = int(report.find(f).text)\n", - " elif field_maps[f]['pg_type'] == 'timestamp':\n", - " values[field_maps[f]['pg_field_name']] = datetime.datetime.utcfromtimestamp(int(report.find(f).text))\n", - " elif field_maps[f]['pg_type'] == 'inet':\n", - " values[field_maps[f]['pg_field_name']] = maybe_strip(report.find(f).text)\n", - " else:\n", - " values[field_maps[f]['pg_field_name']] = maybe_strip(report.find(f).text)\n", - " insert_string = 'insert into reports (' + ', '.join(field_names) + ') '\n", - " insert_string += 'values (' + ', '.join('%({})s'.format(f) for f in field_names) + ');'\n", - " # print(insert_string, values)\n", - " cursor.execute(insert_string, values)\n", - " \n", - " for i in range(1, len(report.findall('./record'))+1):\n", - " field_names = []\n", - " cursor.execute('select id, report_metadata_report_id from reports where report_metadata_report_id = %s;', \n", - " [report.find('./report_metadata/report_id').text])\n", - " results = cursor.fetchall()\n", - " if len(results) != 1:\n", - " raise\n", - " else:\n", - " report_id = results[0][0]\n", - " values = {'report_id': report_id}\n", - " for f in [f for f in field_maps if field_maps[f]['pg_table'] == 'report_items']:\n", - " field_names += [field_maps[f]['pg_field_name']]\n", - " if field_maps[f]['pg_type'] == 'int':\n", - " values[field_maps[f]['pg_field_name']] = int(report.find(f.format(i)).text)\n", - " elif field_maps[f]['pg_type'] == 'timestamp':\n", - " values[field_maps[f]['pg_field_name']] = datetime.datetime.utcfromtimestamp(int(report.find(f.format(i)).text))\n", - " elif field_maps[f]['pg_type'] == 'inet':\n", - " values[field_maps[f]['pg_field_name']] = maybe_strip(report.find(f.format(i)).text)\n", - " else:\n", - " values[field_maps[f]['pg_field_name']] = maybe_strip(report.find(f.format(i)).text)\n", - " insert_string = 'insert into report_items (report_id, ' + ', '.join(field_names) + ') '\n", - " insert_string += 'values (%(report_id)s, ' + ', '.join('%({})s'.format(f) for f in field_names) + ');'\n", - " # print(insert_string, values)\n", - " cursor.execute(insert_string, values)\n", - " connection.commit()" + "config = configparser.ConfigParser()\n", + "config.read('dmarc.ini')" ] }, { "cell_type": "code", - "execution_count": 11, + "execution_count": 17, "metadata": { "collapsed": false }, @@ -296,22 +274,36 @@ { "data": { "text/plain": [ - "['dmarc.ini']" + "'ogedei.njae.me.uk'" ] }, - "execution_count": 11, + "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ - "config = configparser.ConfigParser()\n", - "config.read('dmarc.ini')" + "config['database']['server']" ] }, { "cell_type": "code", - "execution_count": 12, + "execution_count": 11, + "metadata": { + "collapsed": false + }, + "outputs": [], + "source": [ + "conn = psycopg2.connect(host=config['database']['server'],\n", + " database=config['database']['database'], \n", + " user=config['database']['username'], \n", + " password=config['database']['password'])\n", + "cur = conn.cursor()" + ] + }, + { + "cell_type": "code", + "execution_count": 18, "metadata": { "collapsed": false }, @@ -319,29 +311,24 @@ { "data": { "text/plain": [ - "datetime.datetime(2016, 3, 29, 23, 59, 59)" + "datetime.datetime(2017, 1, 13, 23, 59, 59, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))" ] }, - "execution_count": 12, + "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ - "with psycopg2.connect(host=config['database']['server'],\n", - " database=config['database']['database'], \n", - " user=config['database']['username'], \n", - " password=config['database']['password']) as conn:\n", - " with conn.cursor() as cur:\n", - " cur.execute('select max(report_metadata_date_range_end) from reports')\n", - " results = cur.fetchall()\n", + "cur.execute('select max(report_metadata_date_range_end) from reports')\n", + "results = cur.fetchall()\n", "most_recent_date = results[0][0]\n", "most_recent_date" ] }, { "cell_type": "code", - "execution_count": 13, + "execution_count": 19, "metadata": { "collapsed": false }, @@ -349,10 +336,10 @@ { "data": { "text/plain": [ - "('OK', [b'178'])" + "('OK', [b'541'])" ] }, - "execution_count": 13, + "execution_count": 19, "metadata": {}, "output_type": "execute_result" } @@ -367,7 +354,7 @@ }, { "cell_type": "code", - "execution_count": 14, + "execution_count": 30, "metadata": { "collapsed": false }, @@ -375,10 +362,12 @@ { "data": { "text/plain": [ - "('SINCE 27-Mar-2016', 'OK', [b'169 170 171 172 173 174 175 176 177 178 179'])" + "('SINCE 11-Jan-2017',\n", + " 'OK',\n", + " [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'])" ] }, - "execution_count": 14, + "execution_count": 30, "metadata": {}, "output_type": "execute_result" } @@ -394,7 +383,7 @@ }, { "cell_type": "code", - "execution_count": 15, + "execution_count": 36, "metadata": { "collapsed": false }, @@ -402,33 +391,90 @@ { "data": { "text/plain": [ - "['1458957186.548175',\n", - " '2150510829392606201',\n", - " '68aad5080a774e2c997d159b546569b9@hotmail.com',\n", - " '1459129809.695034',\n", - " '16143280651570354241',\n", - " '8c177254c3cb41869dc3afab59f74c76@hotmail.com',\n", - " '15410706527896810898',\n", - " '1459216304.582931',\n", - " '15497495941279624940',\n", - " '1459302353.261157',\n", - " '7773a696f4a54f1e8c01f4644fbb94ee@hotmail.com']" + "[,\n", + " ,\n", + " ,\n", + " ,\n", + " ,\n", + " ,\n", + " ,\n", + " ,\n", + " ,\n", + " ,\n", + " ,\n", + " ,\n", + " ,\n", + " ,\n", + " ,\n", + " ,\n", + " ,\n", + " ,\n", + " ,\n", + " ,\n", + " ,\n", + " ,\n", + " ]" ] }, - "execution_count": 15, + "execution_count": 36, "metadata": {}, "output_type": "execute_result" } ], "source": [ "dmarc_reports = [report for report_set in [extract_report(fetch_msg(n)) for n in nums[0].split()]\n", - " for report in report_set]\n", + " for report in report_set\n", + " if report]\n", + "dmarc_reports" + ] + }, + { + "cell_type": "code", + "execution_count": 37, + "metadata": { + "collapsed": false + }, + "outputs": [ + { + "data": { + "text/plain": [ + "['3644362597779170059',\n", + " '1484103023.660115',\n", + " '16703417802819394712',\n", + " '1484189549.594225',\n", + " '1484276013.355502',\n", + " '4b1a497aa31d4fdbaaa56c13eb7987fb@hotmail.com',\n", + " '12376654726187971850',\n", + " '1484362840.261197',\n", + " '16723890724451093500',\n", + " '1484448519.463747',\n", + " '3eb8d9f114044eb7bd0509a2e327451d@hotmail.com',\n", + " '17985849130842520792',\n", + " 'b5559b13fcd04062ae46c911334f47e2@hotmail.com',\n", + " '3557556884331174573',\n", + " '80eff7abd7b844c0830df5cdd3fd476c@hotmail.com',\n", + " '13353913580885545629',\n", + " '1484707828.212792',\n", + " '8880815782389375117',\n", + " '1484794248.174834',\n", + " '15941205824990790163',\n", + " '1484880971.613805',\n", + " '12141824316076722331',\n", + " '1484966874.391661']" + ] + }, + "execution_count": 37, + "metadata": {}, + "output_type": "execute_result" + } + ], + "source": [ "[r.find('./report_metadata/report_id').text for r in dmarc_reports]" ] }, { "cell_type": "code", - "execution_count": 16, + "execution_count": 46, "metadata": { "collapsed": false }, @@ -439,7 +485,7 @@ "('BYE', [b'Logging out'])" ] }, - "execution_count": 16, + "execution_count": 46, "metadata": {}, "output_type": "execute_result" } @@ -451,7 +497,7 @@ }, { "cell_type": "code", - "execution_count": 18, + "execution_count": 47, "metadata": { "collapsed": false }, @@ -460,24 +506,308 @@ "name": "stdout", "output_type": "stream", "text": [ + "write 1448246712.259522\n", + "write 75eef2128eb84e9ca8e4837f3d4e31bd@hotmail.com\n", + "write 9138782308848375092\n", + "write 840fc02d5cb847ec9f007fb236c0c190@hotmail.com\n", + "write 15111277194568576101\n", + "write 1448419746.206772\n", + "write 1448592471.754446\n", + "write c8e4fe772ae3434594e8396a8abf77b1@hotmail.com\n", + "write 2273327349069127175\n", + "write 1448851661.602960\n", + "write 1448938177.97045\n", + "write 18203685646345145151\n", + "write 12700770160536483846\n", + "write 1449024342.601561\n", + "write 1fadaaf3d9d544568a41042a1f42df05@hotmail.com\n", + "write 3544432548848738700\n", + "write 1449110875.443288\n", + "write 1449197294.177690\n", + "write 10356602625290246518\n", + "write 1449283514.868026\n", + "write 4654132158287507198\n", + "write 5164714352250096219\n", + "write 1449542804.259788\n", + "write 20246ec4a70041caa276560032fdc595@hotmail.com\n", + "write 14089985608998676943\n", + "write 1449629119.120001\n", + "write c8f339f8f3364d78820dd03a84d11daa@hotmail.com\n", + "write 1233953228184721030\n", + "write 17984785070730076605\n", + "write 1449888489.668429\n", + "write 121bb39637c14847a40669dd3e7cdf49@hotmail.com\n", + "write 14661842628106423589\n", + "write 1449974899.615312\n", + "write 7834597727856283739\n", + "write f94f00dee713479fb8bfdbcad539da88@hotmail.com\n", + "write 17659080448126516391\n", + "write 1450234735.690828\n", + "write 17880573400612806781\n", + "write 1451184218.12383\n", + "write 6505353761044749597\n", + "write 02aca4c6bb144bb6b54773374c14a43a@hotmail.com\n", + "write 1451443783.692950\n", + "write 12137203242660163862\n", + "write d1019291012e4f4589f59f1dacef31a3@hotmail.com\n", + "write 1451616408.476341\n", + "write 11743474449139138027\n", + "write e245ef81f05b4697bea34bed977d8286@hotmail.com\n", + "write 1452134765.308922\n", + "write 271c80b9234a4c99bb9608abc5470c99@hotmail.com\n", + "write 15974729567081493290\n", + "write ba1e9c35eec5428b8c3d6a32d25daa46@hotmail.com\n", + "write 1452393953.83099\n", + "write d71792d8afc246e9a4756bd13e600b59@hotmail.com\n", + "write 1452480188.666644\n", + "write 1452566814.805501\n", + "write fc0750780e0d4b1395c4c9f41cb9791f@hotmail.com\n", + "write 1452653275.294149\n", + "write 1452741832.342075\n", + "write 2884439811958006165\n", + "write 3508955360705645678\n", + "write 1452826445.971145\n", + "write 1452912512.114840\n", + "write eda16c25bbdb48b2bae0d18eab13f736@hotmail.com\n", + "write 724976339745122723\n", + "write 1453084984.369330\n", + "write 1453171440.157305\n", + "write 1862812391966551302\n", + "write 1453258067.440890\n", + "write 1453344550.144698\n", + "write 2002148329698541727\n", + "write 6110291457768008366\n", + "write de651cefbc0749a3aa3d91cdc5084f5a@hotmail.com\n", + "write 1453517236.399019\n", + "write 14593873841710243963\n", + "write ba64911adb0743d0b926588647ec5dcf@hotmail.com\n", + "write 1453689981.768611\n", + "write 13959504937502303973\n", + "write 3eba8b9e31724809aaf8d517e5656c4b@hotmail.com\n", + "write 1453776417.898739\n", + "write 9594298247075547297\n", + "write d77c5b90344149f5bb7b50ff69400aeb@hotmail.com\n", + "write 1453862958.482897\n", + "write 3267388508667988463\n", + "write 580ca0a74948493ba8b7c6e62b8c81d9@hotmail.com\n", + "write 6876229717666715946\n", + "write 6474240574237085621\n", + "write 1454381343.228960\n", + "write 77b6c2aa32bf440aa240195db229cd4a@hotmail.com\n", + "write 1922265692629464594\n", + "write 1454467952.258012\n", + "write 1454640482.903992\n", + "write 16409503372348966326\n", + "write 456ffff78456494fbaed47b465259d46@hotmail.com\n", + "write 543258397004515473\n", + "write 4158049796460953654\n", + "write a6b6aec1af1647eb8c6fd83baaed6bbe@hotmail.com\n", + "write 1454986282.636073\n", + "write 8986675713054039362\n", + "write 30d5ef78d6d746a4a777c56e7ca6f1c6@hotmail.com\n", + "write 1455072659.49250\n", + "write 10744929815548178249\n", + "write 1178c4fe34364ff39cb6e24a51219093@hotmail.com\n", + "write 730219275619457\n", + "write 164180405175632647\n", + "write 16520810497197721925\n", + "write 13f28e952d67451281b90d55eee9c7c3@hotmail.com\n", + "write 1455418081.446180\n", + "write 641b4cc500334a7eb3f649b21af003ca@hotmail.com\n", + "write 14416654486328871570\n", + "write dbe2f49dbea947428b179e0ecec5f813@hotmail.com\n", + "write 1455679107.469767\n", + "write 7815164892280952980\n", + "write 511d6fbb8484454b916ad8d799938390@hotmail.com\n", + "write 1455763965.140751\n", + "write 10491829640821461358\n", + "write 13702792868066092542\n", + "write 1456109267.60200\n", + "write 2cfe3d0ec75b43f89a6a7ae57777e25c@hotmail.com\n", + "write 9037150256193522538\n", + "write e327753089e04950906848d104a93695@hotmail.com\n", + "write 1456282325.455667\n", + "write 3584764172722215181\n", + "write 1456455410.661669\n", + "write 16824795799639390032\n", + "write 14217880258295310962\n", + "write 1456541727.873510\n", + "write 1102163566627474204\n", + "write 1456713880.905762\n", + "write 1456800606.884839\n", + "write 1bcc49be66744cc7a70ae1cc53515bbf@hotmail.com\n", + "write 11019573554877343650\n", + "write 1456886761.643377\n", + "write 36dd4e2b3f424bb6919d2d5d4c77c8dd@hotmail.com\n", + "write b6d15133598b4e00b21c8e2b7cbec05e@hotmail.com\n", + "write 1457059537.625770\n", + "write 85136d21c514431e9f5cf9d36faa4a22@hotmail.com\n", + "write 1457146943.63077\n", + "write 1457232742.472142\n", + "write ea3a96df66a845c5a666068ef8ca1368@hotmail.com\n", + "write 17044038014166863505\n", + "write 1457318880.887129\n", + "write 3689737760348689620\n", + "write 726a3261dfab4b4590b5fc898c561b08@hotmail.com\n", + "write 129a9dc3ff2e40ae8a3890ecc1c291d9@hotmail.com\n", + "write 1457491692.793016\n", + "write 1207168150882246442\n", + "write 10706879170593875813\n", + "write 4226319448276355598\n", + "write 1457750956.253773\n", + "write 12434676377746978866\n", + "write 43e7b5e2e0fb400aa914058360c8aaab@hotmail.com\n", + "write 1457919850.989656\n", + "write 594749737641304465\n", + "write 18c9a966da81401e80ca2a22310ffee6@hotmail.com\n", + "write 1458006833.731671\n", + "write 6996084840178215735\n", + "write 1458093082.193350\n", + "write 3188090359734380692\n", + "write 7006513620611789399\n", + "write 1458353823.362308\n", + "write 17071564483265101388\n", + "write 1458524834.32161\n", + "write 1458611429.595569\n", + "write 14662396456930987863\n", + "write 1458784545.303847\n", + "write 13032699446207263761\n", + "write 8426152571808739514\n", + "write 1458957186.548175\n", + "write 2150510829392606201\n", + "write 68aad5080a774e2c997d159b546569b9@hotmail.com\n", + "write 1459129809.695034\n", + "write 16143280651570354241\n", + "write 8c177254c3cb41869dc3afab59f74c76@hotmail.com\n", + "write 15410706527896810898\n", + "write 1459216304.582931\n", + "write 15497495941279624940\n", "write 1459302353.261157\n", - "write 7773a696f4a54f1e8c01f4644fbb94ee@hotmail.com\n" + "write 7773a696f4a54f1e8c01f4644fbb94ee@hotmail.com\n", + "write 15185964531645951164\n", + "write 1459391203.751219\n", + "write 9452413620101638402\n", + "write 1459475662.735732\n" ] } ], "source": [ - "with psycopg2.connect(host=config['database']['server'],\n", - " database=config['database']['database'], \n", - " user=config['database']['username'], \n", - " password=config['database']['password']) as conn:\n", - " with conn.cursor() as cur:\n", - " for report in dmarc_reports:\n", - " cur.execute('select id, report_metadata_report_id from reports where report_metadata_report_id = %s;', \n", - " [report.find('./report_metadata/report_id').text])\n", - " results = cur.fetchall()\n", - " if not results:\n", - " print('write', report.find('./report_metadata/report_id').text)\n", - " write_report(conn, cur, report)" + "for report in dmarc_reports:\n", + " cur.execute('select id, report_metadata_report_id from reports where report_metadata_report_id = %s;', \n", + " [report.find('./report_metadata/report_id').text])\n", + " results = cur.fetchall()\n", + " if not results:\n", + " print('write', report.find('./report_metadata/report_id').text)\n", + " write_report(conn, cur, report)" + ] + }, + { + "cell_type": "code", + "execution_count": 48, + "metadata": { + "collapsed": true + }, + "outputs": [], + "source": [ + "# conn.rollback()" + ] + }, + { + "cell_type": "code", + "execution_count": 52, + "metadata": { + "collapsed": false + }, + "outputs": [ + { + "data": { + "text/plain": [ + "('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", + " {'policy_published_adkim': 'r',\n", + " 'policy_published_aspf': 'r',\n", + " 'policy_published_domain': 'njae.me.uk',\n", + " 'policy_published_p': 'none',\n", + " 'policy_published_pct': 100,\n", + " 'report_metadata_date_range_begin': datetime.datetime(2015, 11, 22, 0, 0, tzinfo=datetime.timezone.utc),\n", + " 'report_metadata_date_range_end': datetime.datetime(2015, 11, 22, 23, 59, 59, tzinfo=datetime.timezone.utc),\n", + " 'report_metadata_email': 'postmaster@dmarc.yahoo.com',\n", + " 'report_metadata_org_name': 'Yahoo! Inc.',\n", + " 'report_metadata_report_id': '1448246712.259522'})" + ] + }, + "execution_count": 52, + "metadata": {}, + "output_type": "execute_result" + } + ], + "source": [ + "build_insert_command('reports', dmarc_reports[0])" + ] + }, + { + "cell_type": "code", + "execution_count": 53, + "metadata": { + "collapsed": false + }, + "outputs": [ + { + "data": { + "text/plain": [ + "('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", + " {'policy_published_adkim': 'r',\n", + " 'policy_published_aspf': 'r',\n", + " 'policy_published_domain': 'njae.me.uk',\n", + " 'policy_published_p': 'none',\n", + " 'policy_published_pct': 100,\n", + " 'report_metadata_date_range_begin': datetime.datetime(2016, 3, 31, 0, 0, tzinfo=datetime.timezone.utc),\n", + " 'report_metadata_date_range_end': datetime.datetime(2016, 3, 31, 23, 59, 59, tzinfo=datetime.timezone.utc),\n", + " 'report_metadata_email': 'postmaster@dmarc.yahoo.com',\n", + " 'report_metadata_org_name': 'Yahoo! Inc.',\n", + " 'report_metadata_report_id': '1459475662.735732'})" + ] + }, + "execution_count": 53, + "metadata": {}, + "output_type": "execute_result" + } + ], + "source": [ + "build_insert_command('reports', dmarc_reports[-1])" + ] + }, + { + "cell_type": "code", + "execution_count": 50, + "metadata": { + "collapsed": false + }, + "outputs": [ + { + "data": { + "text/plain": [ + "datetime.datetime(2015, 11, 22, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name='UTC'))" + ] + }, + "execution_count": 50, + "metadata": {}, + "output_type": "execute_result" + } + ], + "source": [ + "datetime.datetime.fromtimestamp(int('1448150400'), \n", + " tz=psycopg2.tz.FixedOffsetTimezone(offset=0, name='UTC'))" + ] + }, + { + "cell_type": "code", + "execution_count": 51, + "metadata": { + "collapsed": true + }, + "outputs": [], + "source": [ + "conn.close()" ] }, { @@ -506,7 +836,7 @@ "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", - "version": "3.4.3+" + "version": "3.5.2+" } }, "nbformat": 4,