queries.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\n",
  20     "import socket"
  21    ]
  22   },
  23   {
  24    "cell_type": "code",
  25    "execution_count": 2,
  26    "metadata": {
  27     "collapsed": false
  28    },
  29    "outputs": [
  30     {
  31      "data": {
  32       "text/plain": [
  33        "['dmarc.ini']"
  34       ]
  35      },
  36      "execution_count": 2,
  37      "metadata": {},
  38      "output_type": "execute_result"
  39     }
  40    ],
  41    "source": [
  42     "config = configparser.ConfigParser()\n",
  43     "config.read('dmarc.ini')"
  44    ]
  45   },
  46   {
  47    "cell_type": "code",
  48    "execution_count": 3,
  49    "metadata": {
  50     "collapsed": false
  51    },
  52    "outputs": [
  53     {
  54      "data": {
  55       "text/plain": [
  56        "[(('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
  57        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
  58        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
  59        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
  60        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
  61        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
  62        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
  63        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
  64        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
  65        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
  66        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
  67        " (('google.com', 'pass', 'pass', '2607:f8b0:400d:c04::22a'),\n",
  68        "  'mail-qg0-x22a.google.com'),\n",
  69        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
  70        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
  71        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
  72        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
  73        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
  74        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
  75        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
  76        " (('google.com', 'softfail', 'pass', '54.84.192.51'), 'mail1.keybase.io'),\n",
  77        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
  78        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
  79        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
  80        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
  81        " (('google.com', 'softfail', 'pass', '94.136.40.147'), 'mailex.mailcore.me'),\n",
  82        " (('google.com', 'softfail', 'pass', '94.136.40.149'), 'mailex.mailcore.me'),\n",
  83        " (('google.com', 'softfail', 'pass', '94.136.40.147'), 'mailex.mailcore.me'),\n",
  84        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
  85        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
  86        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
  87        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
  88        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
  89        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
  90        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
  91        " (('google.com', 'softfail', 'pass', '2a00:1450:400c:c09::232'),\n",
  92        "  'mail-wm0-x232.google.com'),\n",
  93        " (('google.com', 'softfail', 'pass', '2a00:1450:400c:c09::231'),\n",
  94        "  'mail-wm0-x231.google.com'),\n",
  95        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
  96        " (('google.com', 'softfail', 'pass', '146.185.136.235'), 'new.realms.co.uk'),\n",
  97        " (('google.com', 'softfail', 'pass', '146.185.136.235'), 'new.realms.co.uk'),\n",
  98        " (('google.com', 'pass', 'pass', '82.109.184.9'), 'clublloyds.com'),\n",
  99        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 100        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 101        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk')]"
 102       ]
 103      },
 104      "execution_count": 3,
 105      "metadata": {},
 106      "output_type": "execute_result"
 107     }
 108    ],
 109    "source": [
 110     "conn = psycopg2.connect(host=config['database']['server'],\n",
 111     "                        database=config['database']['database'], \n",
 112     "                        user=config['database']['username'], \n",
 113     "                        password=config['database']['password'])\n",
 114     "with conn:\n",
 115     "    with conn.cursor() as cur:\n",
 116     "        cur.execute(\"\"\"\n",
 117     "        select report_metadata_org_name, auth_results_spf_result, auth_results_dkim_result, source_ip \n",
 118     "        from reports, report_items\n",
 119     "        where report_items.report_id = reports.id and\n",
 120     "          source_ip <> inet '212.69.55.62'\"\"\")\n",
 121     "        results = cur.fetchall()\n",
 122     "[(r, socket.gethostbyaddr(r[3])[0]) for r in results]"
 123    ]
 124   },
 125   {
 126    "cell_type": "code",
 127    "execution_count": 4,
 128    "metadata": {
 129     "collapsed": false
 130    },
 131    "outputs": [
 132     {
 133      "data": {
 134       "text/plain": [
 135        "[(('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 136        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 137        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 138        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 139        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 140        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 141        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 142        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 143        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 144        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 145        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 146        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 147        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 148        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 149        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 150        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 151        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 152        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 153        " (('google.com', 'softfail', 'pass', '54.84.192.51'), 'mail1.keybase.io'),\n",
 154        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 155        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 156        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 157        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 158        " (('google.com', 'softfail', 'pass', '94.136.40.147'), 'mailex.mailcore.me'),\n",
 159        " (('google.com', 'softfail', 'pass', '94.136.40.149'), 'mailex.mailcore.me'),\n",
 160        " (('google.com', 'softfail', 'pass', '94.136.40.147'), 'mailex.mailcore.me'),\n",
 161        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 162        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 163        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 164        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 165        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 166        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 167        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 168        " (('google.com', 'softfail', 'pass', '2a00:1450:400c:c09::232'),\n",
 169        "  'mail-wm0-x232.google.com'),\n",
 170        " (('google.com', 'softfail', 'pass', '2a00:1450:400c:c09::231'),\n",
 171        "  'mail-wm0-x231.google.com'),\n",
 172        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 173        " (('google.com', 'softfail', 'pass', '146.185.136.235'), 'new.realms.co.uk'),\n",
 174        " (('google.com', 'softfail', 'pass', '146.185.136.235'), 'new.realms.co.uk'),\n",
 175        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 176        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n",
 177        " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk')]"
 178       ]
 179      },
 180      "execution_count": 4,
 181      "metadata": {},
 182      "output_type": "execute_result"
 183     }
 184    ],
 185    "source": [
 186     "with conn:\n",
 187     "    with conn.cursor() as cur:\n",
 188     "        cur.execute(\"\"\"\n",
 189     "        select report_metadata_org_name, auth_results_spf_result, auth_results_dkim_result, source_ip \n",
 190     "        from reports, report_items\n",
 191     "        where report_items.report_id = reports.id and\n",
 192     "          auth_results_spf_result <> 'pass'\"\"\")\n",
 193     "        results = cur.fetchall()\n",
 194     "[(r, socket.gethostbyaddr(r[3])[0]) for r in results]"
 195    ]
 196   },
 197   {
 198    "cell_type": "code",
 199    "execution_count": 5,
 200    "metadata": {
 201     "collapsed": false
 202    },
 203    "outputs": [
 204     {
 205      "data": {
 206       "text/plain": [
 207        "'lb.lon5.cpcloud.co.uk'"
 208       ]
 209      },
 210      "execution_count": 5,
 211      "metadata": {},
 212      "output_type": "execute_result"
 213     }
 214    ],
 215    "source": [
 216     "socket.gethostbyaddr('65.20.0.12')[0]"
 217    ]
 218   },
 219   {
 220    "cell_type": "code",
 221    "execution_count": 6,
 222    "metadata": {
 223     "collapsed": false
 224    },
 225    "outputs": [
 226     {
 227      "data": {
 228       "text/plain": [
 229        "[('02aca4c6bb144bb6b54773374c14a43a@hotmail.com',),\n",
 230        " ('10356602625290246518',),\n",
 231        " ('10491829640821461358',),\n",
 232        " ('10706879170593875813',),\n",
 233        " ('10744929815548178249',),\n",
 234        " ('11019573554877343650',),\n",
 235        " ('1102163566627474204',),\n",
 236        " ('11743474449139138027',),\n",
 237        " ('1178c4fe34364ff39cb6e24a51219093@hotmail.com',),\n",
 238        " ('1207168150882246442',),\n",
 239        " ('12137203242660163862',),\n",
 240        " ('121bb39637c14847a40669dd3e7cdf49@hotmail.com',),\n",
 241        " ('1233953228184721030',),\n",
 242        " ('12434676377746978866',),\n",
 243        " ('12700770160536483846',),\n",
 244        " ('129a9dc3ff2e40ae8a3890ecc1c291d9@hotmail.com',),\n",
 245        " ('13032699446207263761',),\n",
 246        " ('13702792868066092542',),\n",
 247        " ('13959504937502303973',),\n",
 248        " ('13f28e952d67451281b90d55eee9c7c3@hotmail.com',),\n",
 249        " ('14089985608998676943',),\n",
 250        " ('14217880258295310962',),\n",
 251        " ('14416654486328871570',),\n",
 252        " ('1448246712.259522',),\n",
 253        " ('1448419746.206772',),\n",
 254        " ('1448592471.754446',),\n",
 255        " ('1448851661.602960',),\n",
 256        " ('1448938177.97045',),\n",
 257        " ('1449024342.601561',),\n",
 258        " ('1449110875.443288',),\n",
 259        " ('1449197294.177690',),\n",
 260        " ('1449283514.868026',),\n",
 261        " ('1449542804.259788',),\n",
 262        " ('1449629119.120001',),\n",
 263        " ('1449888489.668429',),\n",
 264        " ('1449974899.615312',),\n",
 265        " ('1450234735.690828',),\n",
 266        " ('1451184218.12383',),\n",
 267        " ('1451443783.692950',),\n",
 268        " ('1451616408.476341',),\n",
 269        " ('1452134765.308922',),\n",
 270        " ('1452393953.83099',),\n",
 271        " ('1452480188.666644',),\n",
 272        " ('1452566814.805501',),\n",
 273        " ('1452653275.294149',),\n",
 274        " ('1452741832.342075',),\n",
 275        " ('1452826445.971145',),\n",
 276        " ('1452912512.114840',),\n",
 277        " ('1453084984.369330',),\n",
 278        " ('1453171440.157305',),\n",
 279        " ('1453258067.440890',),\n",
 280        " ('1453344550.144698',),\n",
 281        " ('1453517236.399019',),\n",
 282        " ('1453689981.768611',),\n",
 283        " ('1453776417.898739',),\n",
 284        " ('1453862958.482897',),\n",
 285        " ('1454381343.228960',),\n",
 286        " ('1454467952.258012',),\n",
 287        " ('1454640482.903992',),\n",
 288        " ('1454986282.636073',),\n",
 289        " ('1455072659.49250',),\n",
 290        " ('1455418081.446180',),\n",
 291        " ('1455679107.469767',),\n",
 292        " ('1455763965.140751',),\n",
 293        " ('1456109267.60200',),\n",
 294        " ('1456282325.455667',),\n",
 295        " ('1456455410.661669',),\n",
 296        " ('1456541727.873510',),\n",
 297        " ('1456713880.905762',),\n",
 298        " ('1456800606.884839',),\n",
 299        " ('1456886761.643377',),\n",
 300        " ('1457059537.625770',),\n",
 301        " ('1457146943.63077',),\n",
 302        " ('1457232742.472142',),\n",
 303        " ('1457318880.887129',),\n",
 304        " ('1457491692.793016',),\n",
 305        " ('1457750956.253773',),\n",
 306        " ('1457919850.989656',),\n",
 307        " ('1458006833.731671',),\n",
 308        " ('1458093082.193350',),\n",
 309        " ('1458353823.362308',),\n",
 310        " ('1458524834.32161',),\n",
 311        " ('1458611429.595569',),\n",
 312        " ('1458784545.303847',),\n",
 313        " ('1458957186.548175',),\n",
 314        " ('1459129809.695034',),\n",
 315        " ('1459216304.582931',),\n",
 316        " ('1459302353.261157',),\n",
 317        " ('14593873841710243963',),\n",
 318        " ('14661842628106423589',),\n",
 319        " ('14662396456930987863',),\n",
 320        " ('15111277194568576101',),\n",
 321        " ('15185964531645951164',),\n",
 322        " ('15410706527896810898',),\n",
 323        " ('15497495941279624940',),\n",
 324        " ('15974729567081493290',),\n",
 325        " ('16143280651570354241',),\n",
 326        " ('16409503372348966326',),\n",
 327        " ('164180405175632647',),\n",
 328        " ('16520810497197721925',),\n",
 329        " ('16824795799639390032',),\n",
 330        " ('17044038014166863505',),\n",
 331        " ('17071564483265101388',),\n",
 332        " ('17659080448126516391',),\n",
 333        " ('17880573400612806781',),\n",
 334        " ('17984785070730076605',),\n",
 335        " ('18203685646345145151',),\n",
 336        " ('1862812391966551302',),\n",
 337        " ('18c9a966da81401e80ca2a22310ffee6@hotmail.com',),\n",
 338        " ('1922265692629464594',),\n",
 339        " ('1bcc49be66744cc7a70ae1cc53515bbf@hotmail.com',),\n",
 340        " ('1fadaaf3d9d544568a41042a1f42df05@hotmail.com',),\n",
 341        " ('2002148329698541727',),\n",
 342        " ('20246ec4a70041caa276560032fdc595@hotmail.com',),\n",
 343        " ('2150510829392606201',),\n",
 344        " ('2273327349069127175',),\n",
 345        " ('271c80b9234a4c99bb9608abc5470c99@hotmail.com',),\n",
 346        " ('2884439811958006165',),\n",
 347        " ('2cfe3d0ec75b43f89a6a7ae57777e25c@hotmail.com',),\n",
 348        " ('30d5ef78d6d746a4a777c56e7ca6f1c6@hotmail.com',),\n",
 349        " ('3188090359734380692',),\n",
 350        " ('3267388508667988463',),\n",
 351        " ('3508955360705645678',),\n",
 352        " ('3544432548848738700',),\n",
 353        " ('3584764172722215181',),\n",
 354        " ('3689737760348689620',),\n",
 355        " ('36dd4e2b3f424bb6919d2d5d4c77c8dd@hotmail.com',),\n",
 356        " ('3eba8b9e31724809aaf8d517e5656c4b@hotmail.com',),\n",
 357        " ('4158049796460953654',),\n",
 358        " ('4226319448276355598',),\n",
 359        " ('43e7b5e2e0fb400aa914058360c8aaab@hotmail.com',),\n",
 360        " ('456ffff78456494fbaed47b465259d46@hotmail.com',),\n",
 361        " ('4654132158287507198',),\n",
 362        " ('511d6fbb8484454b916ad8d799938390@hotmail.com',),\n",
 363        " ('5164714352250096219',),\n",
 364        " ('543258397004515473',),\n",
 365        " ('580ca0a74948493ba8b7c6e62b8c81d9@hotmail.com',),\n",
 366        " ('594749737641304465',),\n",
 367        " ('6110291457768008366',),\n",
 368        " ('641b4cc500334a7eb3f649b21af003ca@hotmail.com',),\n",
 369        " ('6474240574237085621',),\n",
 370        " ('6505353761044749597',),\n",
 371        " ('6876229717666715946',),\n",
 372        " ('68aad5080a774e2c997d159b546569b9@hotmail.com',),\n",
 373        " ('6996084840178215735',),\n",
 374        " ('7006513620611789399',),\n",
 375        " ('724976339745122723',),\n",
 376        " ('726a3261dfab4b4590b5fc898c561b08@hotmail.com',),\n",
 377        " ('730219275619457',),\n",
 378        " ('75eef2128eb84e9ca8e4837f3d4e31bd@hotmail.com',),\n",
 379        " ('7773a696f4a54f1e8c01f4644fbb94ee@hotmail.com',),\n",
 380        " ('77b6c2aa32bf440aa240195db229cd4a@hotmail.com',),\n",
 381        " ('7815164892280952980',),\n",
 382        " ('7834597727856283739',),\n",
 383        " ('840fc02d5cb847ec9f007fb236c0c190@hotmail.com',),\n",
 384        " ('8426152571808739514',),\n",
 385        " ('85136d21c514431e9f5cf9d36faa4a22@hotmail.com',),\n",
 386        " ('8986675713054039362',),\n",
 387        " ('8c177254c3cb41869dc3afab59f74c76@hotmail.com',),\n",
 388        " ('9037150256193522538',),\n",
 389        " ('9138782308848375092',),\n",
 390        " ('9594298247075547297',),\n",
 391        " ('a6b6aec1af1647eb8c6fd83baaed6bbe@hotmail.com',),\n",
 392        " ('b6d15133598b4e00b21c8e2b7cbec05e@hotmail.com',),\n",
 393        " ('ba1e9c35eec5428b8c3d6a32d25daa46@hotmail.com',),\n",
 394        " ('ba64911adb0743d0b926588647ec5dcf@hotmail.com',),\n",
 395        " ('c8e4fe772ae3434594e8396a8abf77b1@hotmail.com',),\n",
 396        " ('c8f339f8f3364d78820dd03a84d11daa@hotmail.com',),\n",
 397        " ('d1019291012e4f4589f59f1dacef31a3@hotmail.com',),\n",
 398        " ('d71792d8afc246e9a4756bd13e600b59@hotmail.com',),\n",
 399        " ('d77c5b90344149f5bb7b50ff69400aeb@hotmail.com',),\n",
 400        " ('dbe2f49dbea947428b179e0ecec5f813@hotmail.com',),\n",
 401        " ('de651cefbc0749a3aa3d91cdc5084f5a@hotmail.com',),\n",
 402        " ('e245ef81f05b4697bea34bed977d8286@hotmail.com',),\n",
 403        " ('e327753089e04950906848d104a93695@hotmail.com',),\n",
 404        " ('ea3a96df66a845c5a666068ef8ca1368@hotmail.com',),\n",
 405        " ('eda16c25bbdb48b2bae0d18eab13f736@hotmail.com',),\n",
 406        " ('f94f00dee713479fb8bfdbcad539da88@hotmail.com',),\n",
 407        " ('fc0750780e0d4b1395c4c9f41cb9791f@hotmail.com',)]"
 408       ]
 409      },
 410      "execution_count": 6,
 411      "metadata": {},
 412      "output_type": "execute_result"
 413     }
 414    ],
 415    "source": [
 416     "with conn:\n",
 417     "    with conn.cursor() as cur:\n",
 418     "        cur.execute(\"\"\"\n",
 419     "        select report_metadata_report_id\n",
 420     "        from reports\"\"\")\n",
 421     "        results = cur.fetchall()\n",
 422     "sorted(results)"
 423    ]
 424   },
 425   {
 426    "cell_type": "code",
 427    "execution_count": 7,
 428    "metadata": {
 429     "collapsed": true
 430    },
 431    "outputs": [],
 432    "source": [
 433     "conn.close()"
 434    ]
 435   }
 436  ],
 437  "metadata": {
 438   "kernelspec": {
 439    "display_name": "Python 3",
 440    "language": "python",
 441    "name": "python3"
 442   },
 443   "language_info": {
 444    "codemirror_mode": {
 445     "name": "ipython",
 446     "version": 3
 447    },
 448    "file_extension": ".py",
 449    "mimetype": "text/x-python",
 450    "name": "python",
 451    "nbconvert_exporter": "python",
 452    "pygments_lexer": "ipython3",
 453    "version": "3.4.3+"
 454   }
 455  },
 456  "nbformat": 4,
 457  "nbformat_minor": 0
 458 }