==============
 sqlite-utils
==============

*Python utility functions for manipulating SQLite databases*

This library aims to make creating a SQLite database from a collection of data as easy as possible.

It is not intended to be a full ORM: the focus is utility helpers to make creating the initial database and populating it with data as productive as possible.

Contents
--------

.. toctree::
   :maxdepth: 2

   table

While the full documentation is being constructed, enjoy an example:

.. code-block:: python

    from sqlite_utils import Database
    import sqlite3
    import requests
    import hashlib
    import json

    raw_ads = requests.get(
        "https://raw.githubusercontent.com/edsu/irads/master/ads.json"
    ).json()
    print(raw_ads[0])
    # {'clicks': 32,
    #  'created': '2016-11-14T04:10:27-08:00',
    #  'ended': None,
    #  'file': 'data/2016-11/P(1)0001720.pdf',
    #  'id': 3186,
    #  'impressions': 396,
    #  'spend': {'amount': '1050.77', 'currency': 'RUB'},
    #  'targeting': {'age': ['18 - 65+'],
    #   'excluded_connections': ['Exclude people who like Black guns matter'],
    #   'language': ['English (UK)', 'English (US)'],
    #   'location': ['United States'],
    #   'people_who_match': {'interests': ['Martin Luther King',
    #     'Jr.',
    #     '2nd Amendment',
    #     'National Rifle Association',
    #     'African-American culture',
    #     'African-American Civil Rights Movement (1954—68)',
    #     'Gun Owners of America',
    #     'African—American history',
    #     'Second Amendment to the United States Constitution',
    #     'Concealed carry in the United States',
    #     'Firearm',
    #     'Malcolm X']},
    #   'placements': ['News Feed on desktop computers',
    #    'News Feed on mobile devices',
    #    'Right column on desktop computers']},
    #  'text': 'Black American racial experience is real. We support the 2nd ammendment\nfor our safety.\n\n',
    #  'url': 'https://www.facebook.com/ProtectBIackGunOwners/'}

    def flatten_targeting(targeting, prefix=''):
        # Convert targeting nested dictionary into list of strings
        # e.g. people_who_match:interests:Martin Luther King
        if isinstance(targeting, list) and all(isinstance(s, str) for s in targeting):
            return ["{}:{}".format(prefix, item) for item in targeting]
        elif isinstance(targeting, str):
            return ["{}:{}".format(prefix, targeting)]
        elif isinstance(targeting, dict):
            items = []
            for key, value in targeting.items():
                new_prefix = "{}:{}".format(prefix, key) if prefix else key
                items.extend(flatten_targeting(value, new_prefix))
            return items

    def hash_id(s):
        return hashlib.md5(s.encode("utf8")).hexdigest()[:5]

    database = Database(sqlite3.connect("/tmp/ads3.db"))

    ads = database["ads"]
    targets = database["targets"]
    ad_targets = database["ad_targets"]

    for ad in raw_ads:
        ad_id = int(ad["file"].split(')')[-1].split(".")[0])
        record = {
            "id": ad_id,
            "file": ad["file"],
            "clicks": ad["clicks"],
            "impressions": ad["impressions"],
            "text": ad["text"],
            "url": (ad["url"] or "").replace("httpszll", "https://"),
            "spend_amount": ad["spend"]["amount"],
            "spend_currency": ad["spend"]["currency"] or "USD",
            "created": ad["created"],
            "ended": ad["ended"],
        }
        ads.upsert(record, pk="id")
        for target in flatten_targeting(ad["targeting"]):
            target_id = hash_id(target)
            targets.upsert({
                "id": target_id,
                "name": target,
                "category": target.split(":")[0],
                "prefix": target.rsplit(":", 1)[0]},
                pk="id"
            )
            ad_targets.insert({
                "target_id": target_id,
                "ad_id": ad_id,
            }, foreign_keys=(
                ("ad_id", "INTEGER", "ads", "id"),
                ("target_id", "TEXT", "targets", "id"),
            ))