--- /dev/null
+{
+ "cells": [
+ {
+ "cell_type": "code",
+ "execution_count": 22,
+ "metadata": {
+ "collapsed": true
+ },
+ "outputs": [],
+ "source": [
+ "import configparser\n",
+ "import psycopg2"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 23,
+ "metadata": {
+ "collapsed": true
+ },
+ "outputs": [],
+ "source": [
+ "field_maps = {'./policy_published/adkim': {'pg_field_name': 'policy_published_adkim',\n",
+ " 'pg_table': 'reports',\n",
+ " 'pg_type': 'varchar'},\n",
+ " './policy_published/aspf': {'pg_field_name': 'policy_published_aspf',\n",
+ " 'pg_table': 'reports',\n",
+ " 'pg_type': 'varchar'},\n",
+ " './policy_published/domain': {'pg_field_name': 'policy_published_domain',\n",
+ " 'pg_table': 'reports',\n",
+ " 'pg_type': 'varchar'},\n",
+ " './policy_published/p': {'pg_field_name': 'policy_published_p',\n",
+ " 'pg_table': 'reports',\n",
+ " 'pg_type': 'varchar'},\n",
+ " './policy_published/pct': {'pg_field_name': 'policy_published_pct',\n",
+ " 'pg_table': 'reports',\n",
+ " 'pg_type': 'int'},\n",
+ " './record[{}]/auth_results/dkim/domain': {'pg_field_name': 'auth_results_dkim_domain',\n",
+ " 'pg_table': 'report_items',\n",
+ " 'pg_type': 'varchar'},\n",
+ " './record[{}]/auth_results/dkim/result': {'pg_field_name': 'auth_results_dkim_result',\n",
+ " 'pg_table': 'report_items',\n",
+ " 'pg_type': 'varchar'},\n",
+ " './record[{}]/auth_results/spf/domain': {'pg_field_name': 'auth_results_spf_domain',\n",
+ " 'pg_table': 'report_items',\n",
+ " 'pg_type': 'varchar'},\n",
+ " './record[{}]/auth_results/spf/result': {'pg_field_name': 'auth_results_spf_result',\n",
+ " 'pg_table': 'report_items',\n",
+ " 'pg_type': 'varchar'},\n",
+ " './record[{}]/identifiers/header_from': {'pg_field_name': 'identifiers_header_from',\n",
+ " 'pg_table': 'report_items',\n",
+ " 'pg_type': 'varchar'},\n",
+ " './record[{}]/row/count': {'pg_field_name': 'count',\n",
+ " 'pg_table': 'report_items',\n",
+ " 'pg_type': 'int'},\n",
+ " './record[{}]/row/policy_evaluated/disposition': {'pg_field_name': 'policy_evaluated_disposition',\n",
+ " 'pg_table': 'report_items',\n",
+ " 'pg_type': 'varchar'},\n",
+ " './record[{}]/row/policy_evaluated/dkim': {'pg_field_name': 'policy_evaluated_dkim',\n",
+ " 'pg_table': 'report_items',\n",
+ " 'pg_type': 'varchar'},\n",
+ " './record[{}]/row/policy_evaluated/spf': {'pg_field_name': 'policy_evaluated_spf',\n",
+ " 'pg_table': 'report_items',\n",
+ " 'pg_type': 'varchar'},\n",
+ " './record[{}]/row/source_ip': {'pg_field_name': 'source_ip',\n",
+ " 'pg_table': 'report_items',\n",
+ " '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",
+ " './report_metadata/date_range/end': {'pg_field_name': 'report_metadata_date_range_end',\n",
+ " 'pg_table': 'reports',\n",
+ " 'pg_type': 'timestamp'},\n",
+ " './report_metadata/email': {'pg_field_name': 'report_metadata_email',\n",
+ " 'pg_table': 'reports',\n",
+ " 'pg_type': 'varchar'},\n",
+ " './report_metadata/org_name': {'pg_field_name': 'report_metadata_org_name',\n",
+ " 'pg_table': 'reports',\n",
+ " 'pg_type': 'varchar'},\n",
+ " './report_metadata/report_id': {'pg_field_name': 'report_metadata_report_id',\n",
+ " 'pg_table': 'reports',\n",
+ " 'pg_type': 'varchar'}}"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 24,
+ "metadata": {
+ "collapsed": false
+ },
+ "outputs": [
+ {
+ "data": {
+ "text/plain": [
+ "['dmarc.ini']"
+ ]
+ },
+ "execution_count": 24,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "config = configparser.ConfigParser()\n",
+ "config.read('dmarc.ini')"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 25,
+ "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'])"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 26,
+ "metadata": {
+ "collapsed": false
+ },
+ "outputs": [],
+ "source": [
+ "with conn.cursor() as cur:\n",
+ " conn.autocommit = True\n",
+ " cur.execute(\"select exists(select * from information_schema.tables where table_name=%s)\", ('report_items',))\n",
+ " if cur.fetchone()[0]:\n",
+ " cur.execute(\"drop table report_items;\")\n",
+ " cur.execute(\"select exists(select * from information_schema.tables where table_name=%s)\", ('reports',))\n",
+ " if cur.fetchone()[0]:\n",
+ " cur.execute(\"drop table reports;\")"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 27,
+ "metadata": {
+ "collapsed": false
+ },
+ "outputs": [
+ {
+ "data": {
+ "text/plain": [
+ "'create table reports (id serial primary key, policy_published_pct int, policy_published_adkim varchar, report_metadata_org_name varchar, policy_published_aspf varchar, policy_published_domain varchar, report_metadata_date_range_end timestamp, policy_published_p varchar, report_metadata_report_id varchar, report_metadata_email varchar, report_metadata_date_range_begin timestamp);'"
+ ]
+ },
+ "execution_count": 27,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "create_report_table_string = 'create table reports (id serial primary key, ' + \\\n",
+ "', '.join(field_maps[p]['pg_field_name'] + ' ' + field_maps[p]['pg_type'] \n",
+ " for p in field_maps if field_maps[p]['pg_table'] == 'reports') + \\\n",
+ "');'\n",
+ "create_report_table_string"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 28,
+ "metadata": {
+ "collapsed": false
+ },
+ "outputs": [
+ {
+ "data": {
+ "text/plain": [
+ "'create table report_items (id serial primary key, report_id integer references reports, auth_results_spf_domain varchar, source_ip inet, policy_evaluated_spf varchar, count int, auth_results_dkim_result varchar, auth_results_dkim_domain varchar, policy_evaluated_disposition varchar, identifiers_header_from varchar, policy_evaluated_dkim varchar, auth_results_spf_result varchar);'"
+ ]
+ },
+ "execution_count": 28,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "create_report_item_table_string = 'create table report_items (id serial primary key, ' + \\\n",
+ " 'report_id integer references reports, ' + \\\n",
+ "', '.join(field_maps[p]['pg_field_name'] + ' ' + field_maps[p]['pg_type'] \n",
+ " for p in field_maps if field_maps[p]['pg_table'] == 'report_items') + \\\n",
+ "');'\n",
+ "create_report_item_table_string"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 29,
+ "metadata": {
+ "collapsed": true
+ },
+ "outputs": [],
+ "source": [
+ "with conn.cursor() as cur:\n",
+ " cur.execute(create_report_table_string)\n",
+ " cur.execute(create_report_item_table_string)\n",
+ "conn.commit()"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "metadata": {
+ "collapsed": true
+ },
+ "outputs": [],
+ "source": []
+ }
+ ],
+ "metadata": {
+ "kernelspec": {
+ "display_name": "Python 3",
+ "language": "python",
+ "name": "python3"
+ },
+ "language_info": {
+ "codemirror_mode": {
+ "name": "ipython",
+ "version": 3
+ },
+ "file_extension": ".py",
+ "mimetype": "text/x-python",
+ "name": "python",
+ "nbconvert_exporter": "python",
+ "pygments_lexer": "ipython3",
+ "version": "3.4.3+"
+ }
+ },
+ "nbformat": 4,
+ "nbformat_minor": 0
+}
projects / dmarc.git / blobdiff