},
{
"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
},
" 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"
" '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",
" 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",
"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
},
{
"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
},
{
"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
},
{
"data": {
"text/plain": [
- "('OK', [b'178'])"
+ "('OK', [b'541'])"
]
},
- "execution_count": 13,
+ "execution_count": 19,
"metadata": {},
"output_type": "execute_result"
}
},
{
"cell_type": "code",
- "execution_count": 14,
+ "execution_count": 30,
"metadata": {
"collapsed": false
},
{
"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"
}
},
{
"cell_type": "code",
- "execution_count": 15,
+ "execution_count": 36,
"metadata": {
"collapsed": false
},
{
"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']"
+ "[<Element 'feedback' at 0x7f0d85c1af48>,\n",
+ " <Element 'feedback' at 0x7f0d85b56c78>,\n",
+ " <Element 'feedback' at 0x7f0d85b63ae8>,\n",
+ " <Element 'feedback' at 0x7f0d85b63a98>,\n",
+ " <Element 'feedback' at 0x7f0d85b6f1d8>,\n",
+ " <Element 'feedback' at 0x7f0d85b6f2c8>,\n",
+ " <Element 'feedback' at 0x7f0d85b6f098>,\n",
+ " <Element 'feedback' at 0x7f0d85b74b38>,\n",
+ " <Element 'feedback' at 0x7f0d85b7ecc8>,\n",
+ " <Element 'feedback' at 0x7f0d85b7e2c8>,\n",
+ " <Element 'feedback' at 0x7f0d85b04b38>,\n",
+ " <Element 'feedback' at 0x7f0d85b04cc8>,\n",
+ " <Element 'feedback' at 0x7f0d85b04bd8>,\n",
+ " <Element 'feedback' at 0x7f0d85b0e098>,\n",
+ " <Element 'feedback' at 0x7f0d85b04b88>,\n",
+ " <Element 'feedback' at 0x7f0d85b13138>,\n",
+ " <Element 'feedback' at 0x7f0d85b130e8>,\n",
+ " <Element 'feedback' at 0x7f0d85b1b818>,\n",
+ " <Element 'feedback' at 0x7f0d85b1b7c8>,\n",
+ " <Element 'feedback' at 0x7f0d85b279a8>,\n",
+ " <Element 'feedback' at 0x7f0d85b27958>,\n",
+ " <Element 'feedback' at 0x7f0d85b2f458>,\n",
+ " <Element 'feedback' at 0x7f0d85b2f408>]"
]
},
- "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
},
"('BYE', [b'Logging out'])"
]
},
- "execution_count": 16,
+ "execution_count": 46,
"metadata": {},
"output_type": "execute_result"
}
},
{
"cell_type": "code",
- "execution_count": 18,
+ "execution_count": 47,
"metadata": {
"collapsed": false
},
"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()"
]
},
{
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
- "version": "3.4.3+"
+ "version": "3.5.2+"
}
},
"nbformat": 4,
projects / dmarc.git / blobdiff