jupyter_notebooks/exporting_to_a_database_using_pybarb.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "3ac80477-f4ab-47ab-8225-1848acdc85bd",
   "metadata": {},
   "source": [
    "# Exporting to a database using pybarb\n",
    "\n",
    "In this demo we will show you how to pull data from the advertising spots endpoint and then export it to a database using pybarb.\n",
    "Note the full API documentation can be found [here](https://barb-api.co.uk/api-docs). \n",
    "\n",
    "It might also be useful to consult the [Getting Started](https://barb-api.co.uk/api-docs#section/Getting-started) section for information about authentication and basic API usage.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e1a2f0cd",
   "metadata": {},
   "source": [
    "## Querying the API with pybarb\n",
    "\n",
    "First we will repeat the steps carried out in the video *Pulling data from the advertising spots endpoint using pybarb* to extract some new data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "1d9c1dfe",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>panel_region</th>\n",
       "      <th>station_name</th>\n",
       "      <th>spot_type</th>\n",
       "      <th>spot_start_datetime</th>\n",
       "      <th>spot_duration</th>\n",
       "      <th>preceding_programme_name</th>\n",
       "      <th>succeeding_programme_name</th>\n",
       "      <th>break_type</th>\n",
       "      <th>position_in_break</th>\n",
       "      <th>broadcaster_spot_number</th>\n",
       "      <th>...</th>\n",
       "      <th>clearcast_buyer_code</th>\n",
       "      <th>clearcast_buyer_name</th>\n",
       "      <th>clearcast_advertiser_code</th>\n",
       "      <th>clearcast_advertiser_name</th>\n",
       "      <th>campaign_approval_id</th>\n",
       "      <th>sales_house_name</th>\n",
       "      <th>audience_size_hundreds</th>\n",
       "      <th>date_of_transmission</th>\n",
       "      <th>audience_name</th>\n",
       "      <th>audience_target_size_hundreds</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BBC Network</td>\n",
       "      <td>Magic TV</td>\n",
       "      <td>spot</td>\n",
       "      <td>2022-03-18 21:36:35</td>\n",
       "      <td>30</td>\n",
       "      <td>TBA</td>\n",
       "      <td>TBA</td>\n",
       "      <td>centre break</td>\n",
       "      <td>other</td>\n",
       "      <td>180948555</td>\n",
       "      <td>...</td>\n",
       "      <td>B000925</td>\n",
       "      <td>MINDSHARE MEDIA UK L</td>\n",
       "      <td>A004642</td>\n",
       "      <td>UNILEVER UK HOME &amp; P</td>\n",
       "      <td>1048980.0</td>\n",
       "      <td>Channel 4 Sales</td>\n",
       "      <td>0</td>\n",
       "      <td>2022-03-18</td>\n",
       "      <td>Men ABC1 16-34</td>\n",
       "      <td>41000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BBC Network</td>\n",
       "      <td>Magic TV</td>\n",
       "      <td>spot</td>\n",
       "      <td>2022-03-18 21:36:35</td>\n",
       "      <td>30</td>\n",
       "      <td>TBA</td>\n",
       "      <td>TBA</td>\n",
       "      <td>centre break</td>\n",
       "      <td>other</td>\n",
       "      <td>180948555</td>\n",
       "      <td>...</td>\n",
       "      <td>B000925</td>\n",
       "      <td>MINDSHARE MEDIA UK L</td>\n",
       "      <td>A004642</td>\n",
       "      <td>UNILEVER UK HOME &amp; P</td>\n",
       "      <td>1048980.0</td>\n",
       "      <td>Channel 4 Sales</td>\n",
       "      <td>0</td>\n",
       "      <td>2022-03-18</td>\n",
       "      <td>Men 35-44</td>\n",
       "      <td>39910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BBC Network</td>\n",
       "      <td>Magic TV</td>\n",
       "      <td>spot</td>\n",
       "      <td>2022-03-18 21:36:35</td>\n",
       "      <td>30</td>\n",
       "      <td>TBA</td>\n",
       "      <td>TBA</td>\n",
       "      <td>centre break</td>\n",
       "      <td>other</td>\n",
       "      <td>180948555</td>\n",
       "      <td>...</td>\n",
       "      <td>B000925</td>\n",
       "      <td>MINDSHARE MEDIA UK L</td>\n",
       "      <td>A004642</td>\n",
       "      <td>UNILEVER UK HOME &amp; P</td>\n",
       "      <td>1048980.0</td>\n",
       "      <td>Channel 4 Sales</td>\n",
       "      <td>0</td>\n",
       "      <td>2022-03-18</td>\n",
       "      <td>All Men</td>\n",
       "      <td>250100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BBC Network</td>\n",
       "      <td>Magic TV</td>\n",
       "      <td>spot</td>\n",
       "      <td>2022-03-18 21:36:35</td>\n",
       "      <td>30</td>\n",
       "      <td>TBA</td>\n",
       "      <td>TBA</td>\n",
       "      <td>centre break</td>\n",
       "      <td>other</td>\n",
       "      <td>180948555</td>\n",
       "      <td>...</td>\n",
       "      <td>B000925</td>\n",
       "      <td>MINDSHARE MEDIA UK L</td>\n",
       "      <td>A004642</td>\n",
       "      <td>UNILEVER UK HOME &amp; P</td>\n",
       "      <td>1048980.0</td>\n",
       "      <td>Channel 4 Sales</td>\n",
       "      <td>0</td>\n",
       "      <td>2022-03-18</td>\n",
       "      <td>Adults 16-34</td>\n",
       "      <td>141860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BBC Network</td>\n",
       "      <td>Magic TV</td>\n",
       "      <td>spot</td>\n",
       "      <td>2022-03-18 21:36:35</td>\n",
       "      <td>30</td>\n",
       "      <td>TBA</td>\n",
       "      <td>TBA</td>\n",
       "      <td>centre break</td>\n",
       "      <td>other</td>\n",
       "      <td>180948555</td>\n",
       "      <td>...</td>\n",
       "      <td>B000925</td>\n",
       "      <td>MINDSHARE MEDIA UK L</td>\n",
       "      <td>A004642</td>\n",
       "      <td>UNILEVER UK HOME &amp; P</td>\n",
       "      <td>1048980.0</td>\n",
       "      <td>Channel 4 Sales</td>\n",
       "      <td>0</td>\n",
       "      <td>2022-03-18</td>\n",
       "      <td>Adults C2</td>\n",
       "      <td>108040</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  panel_region station_name spot_type spot_start_datetime  spot_duration  \\\n",
       "0  BBC Network     Magic TV      spot 2022-03-18 21:36:35             30   \n",
       "1  BBC Network     Magic TV      spot 2022-03-18 21:36:35             30   \n",
       "2  BBC Network     Magic TV      spot 2022-03-18 21:36:35             30   \n",
       "3  BBC Network     Magic TV      spot 2022-03-18 21:36:35             30   \n",
       "4  BBC Network     Magic TV      spot 2022-03-18 21:36:35             30   \n",
       "\n",
       "  preceding_programme_name succeeding_programme_name    break_type  \\\n",
       "0                      TBA                       TBA  centre break   \n",
       "1                      TBA                       TBA  centre break   \n",
       "2                      TBA                       TBA  centre break   \n",
       "3                      TBA                       TBA  centre break   \n",
       "4                      TBA                       TBA  centre break   \n",
       "\n",
       "  position_in_break broadcaster_spot_number  ... clearcast_buyer_code  \\\n",
       "0             other               180948555  ...              B000925   \n",
       "1             other               180948555  ...              B000925   \n",
       "2             other               180948555  ...              B000925   \n",
       "3             other               180948555  ...              B000925   \n",
       "4             other               180948555  ...              B000925   \n",
       "\n",
       "   clearcast_buyer_name  clearcast_advertiser_code clearcast_advertiser_name  \\\n",
       "0  MINDSHARE MEDIA UK L                    A004642      UNILEVER UK HOME & P   \n",
       "1  MINDSHARE MEDIA UK L                    A004642      UNILEVER UK HOME & P   \n",
       "2  MINDSHARE MEDIA UK L                    A004642      UNILEVER UK HOME & P   \n",
       "3  MINDSHARE MEDIA UK L                    A004642      UNILEVER UK HOME & P   \n",
       "4  MINDSHARE MEDIA UK L                    A004642      UNILEVER UK HOME & P   \n",
       "\n",
       "  campaign_approval_id sales_house_name audience_size_hundreds  \\\n",
       "0            1048980.0  Channel 4 Sales                      0   \n",
       "1            1048980.0  Channel 4 Sales                      0   \n",
       "2            1048980.0  Channel 4 Sales                      0   \n",
       "3            1048980.0  Channel 4 Sales                      0   \n",
       "4            1048980.0  Channel 4 Sales                      0   \n",
       "\n",
       "  date_of_transmission   audience_name audience_target_size_hundreds  \n",
       "0           2022-03-18  Men ABC1 16-34                         41000  \n",
       "1           2022-03-18       Men 35-44                         39910  \n",
       "2           2022-03-18         All Men                        250100  \n",
       "3           2022-03-18    Adults 16-34                        141860  \n",
       "4           2022-03-18       Adults C2                        108040  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import json\n",
    "import pybarb as pb\n",
    "import sqlalchemy as db\n",
    "\n",
    "# Set the working directory\n",
    "working_directory = '/path/to/your/dir/'\n",
    "\n",
    "# Get the access token\n",
    "with open(working_directory + \"creds.json\") as file:\n",
    "    creds = json.load(file)\n",
    "\n",
    "# Create a BarbAPI object and connect\n",
    "barb_api = pb.BarbAPI(creds)\n",
    "barb_api.connect()\n",
    "\n",
    "spot_data = barb_api.advertising_spots(min_transmission_date = \"2022-01-01\",\n",
    "                           max_transmission_date = \"2022-04-01\", \n",
    "                           buyer =  \"mindshare_media_uk_l\", \n",
    "                           advertiser = \"UNILEVER_UK_HOME_&_P\",\n",
    "                           panel=\"BBC Network\")\n",
    "\n",
    "spot_df = spot_data.to_dataframe()\n",
    "spot_df.head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "04041225",
   "metadata": {},
   "source": [
    "## Connecting to a database and exporting the data\n",
    "\n",
    "For the purposes of this demonstration we will create a simple SQLite database and export the data to it. SQLite is a lightweight database that is ideal for testing and prototyping. \n",
    "\n",
    "pybarb uses SQLAlchemy to connect to databases. SQLAlchemy is a Python SQL toolkit and Object Relational Mapper (ORM) that gives application developers the full power and flexibility of SQL. It can be used with any database supported by SQLAlchemy. Some of the most popular databases supported by SQLAlchemy are:\n",
    "\n",
    "* PostgreSQL\n",
    "* MySQL\n",
    "* SQLite\n",
    "* Oracle\n",
    "* Microsoft SQL Server\n",
    "\n",
    "For more information about SQLAlchemy see the [documentation](https://docs.sqlalchemy.org/en/13/)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "d5e8facd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define the database connection\n",
    "DATABASE_URL = 'sqlite:///example.db'\n",
    "engine = db.create_engine(DATABASE_URL, echo=False)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2bd4f80a",
   "metadata": {},
   "source": [
    "Now we export the data to a database. We will use the SQLAlchemy SQLite dialect to connect to a SQLite database. We will also use the SQLAlchemy ORM to create a table in the database and insert the data into it.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "6c5d4552",
   "metadata": {},
   "outputs": [],
   "source": [
    "spot_data.to_sql(connection_string=DATABASE_URL, table_name='spot_data')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bd7bfa84",
   "metadata": {},
   "source": [
    "To prove that it worked let's connect to the database and query the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "228cf143",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('BBC Network', 'Magic TV', 'spot', datetime.datetime(2022, 3, 18, 21, 36, 35), 30, 'TBA', 'TBA', 'centre break', 'other', '180948555', 'TAGDGGB071030', 'Persil Gaia 30 NEW', 0, 'England, Scotland and Wales', 'B000925', 'MINDSHARE MEDIA UK L', 'A004642', 'UNILEVER UK HOME & P', 1048980.0, 'Channel 4 Sales', 0, datetime.date(2022, 3, 18), 'Men ABC1 16-34', 41000),\n",
       " ('BBC Network', 'Magic TV', 'spot', datetime.datetime(2022, 3, 18, 21, 36, 35), 30, 'TBA', 'TBA', 'centre break', 'other', '180948555', 'TAGDGGB071030', 'Persil Gaia 30 NEW', 0, 'England, Scotland and Wales', 'B000925', 'MINDSHARE MEDIA UK L', 'A004642', 'UNILEVER UK HOME & P', 1048980.0, 'Channel 4 Sales', 0, datetime.date(2022, 3, 18), 'Men 35-44', 39910),\n",
       " ('BBC Network', 'Magic TV', 'spot', datetime.datetime(2022, 3, 18, 21, 36, 35), 30, 'TBA', 'TBA', 'centre break', 'other', '180948555', 'TAGDGGB071030', 'Persil Gaia 30 NEW', 0, 'England, Scotland and Wales', 'B000925', 'MINDSHARE MEDIA UK L', 'A004642', 'UNILEVER UK HOME & P', 1048980.0, 'Channel 4 Sales', 0, datetime.date(2022, 3, 18), 'All Men', 250100),\n",
       " ('BBC Network', 'Magic TV', 'spot', datetime.datetime(2022, 3, 18, 21, 36, 35), 30, 'TBA', 'TBA', 'centre break', 'other', '180948555', 'TAGDGGB071030', 'Persil Gaia 30 NEW', 0, 'England, Scotland and Wales', 'B000925', 'MINDSHARE MEDIA UK L', 'A004642', 'UNILEVER UK HOME & P', 1048980.0, 'Channel 4 Sales', 0, datetime.date(2022, 3, 18), 'Adults 16-34', 141860),\n",
       " ('BBC Network', 'Magic TV', 'spot', datetime.datetime(2022, 3, 18, 21, 36, 35), 30, 'TBA', 'TBA', 'centre break', 'other', '180948555', 'TAGDGGB071030', 'Persil Gaia 30 NEW', 0, 'England, Scotland and Wales', 'B000925', 'MINDSHARE MEDIA UK L', 'A004642', 'UNILEVER UK HOME & P', 1048980.0, 'Channel 4 Sales', 0, datetime.date(2022, 3, 18), 'Adults C2', 108040),\n",
       " ('BBC Network', 'Magic TV', 'spot', datetime.datetime(2022, 3, 18, 21, 36, 35), 30, 'TBA', 'TBA', 'centre break', 'other', '180948555', 'TAGDGGB071030', 'Persil Gaia 30 NEW', 0, 'England, Scotland and Wales', 'B000925', 'MINDSHARE MEDIA UK L', 'A004642', 'UNILEVER UK HOME & P', 1048980.0, 'Channel 4 Sales', 0, datetime.date(2022, 3, 18), 'Boys 04-15', 49440),\n",
       " ('BBC Network', 'Magic TV', 'spot', datetime.datetime(2022, 3, 18, 21, 36, 35), 30, 'TBA', 'TBA', 'centre break', 'other', '180948555', 'TAGDGGB071030', 'Persil Gaia 30 NEW', 0, 'England, Scotland and Wales', 'B000925', 'MINDSHARE MEDIA UK L', 'A004642', 'UNILEVER UK HOME & P', 1048980.0, 'Channel 4 Sales', 0, datetime.date(2022, 3, 18), 'Adults ABC1 16-24', 33800),\n",
       " ('BBC Network', 'Magic TV', 'spot', datetime.datetime(2022, 3, 18, 21, 36, 35), 30, 'TBA', 'TBA', 'centre break', 'other', '180948555', 'TAGDGGB071030', 'Persil Gaia 30 NEW', 0, 'England, Scotland and Wales', 'B000925', 'MINDSHARE MEDIA UK L', 'A004642', 'UNILEVER UK HOME & P', 1048980.0, 'Channel 4 Sales', 0, datetime.date(2022, 3, 18), 'Men 21-24', 13900),\n",
       " ('BBC Network', 'Magic TV', 'spot', datetime.datetime(2022, 3, 18, 21, 36, 35), 30, 'TBA', 'TBA', 'centre break', 'other', '180948555', 'TAGDGGB071030', 'Persil Gaia 30 NEW', 0, 'England, Scotland and Wales', 'B000925', 'MINDSHARE MEDIA UK L', 'A004642', 'UNILEVER UK HOME & P', 1048980.0, 'Channel 4 Sales', 0, datetime.date(2022, 3, 18), 'Individuals AB', 168250),\n",
       " ('BBC Network', 'Magic TV', 'spot', datetime.datetime(2022, 3, 18, 21, 36, 35), 30, 'TBA', 'TBA', 'centre break', 'other', '180948555', 'TAGDGGB071030', 'Persil Gaia 30 NEW', 0, 'England, Scotland and Wales', 'B000925', 'MINDSHARE MEDIA UK L', 'A004642', 'UNILEVER UK HOME & P', 1048980.0, 'Channel 4 Sales', 0, datetime.date(2022, 3, 18), 'Adults, Lightest Third', 171040)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "connection = engine.connect()\n",
    "metadata = db.MetaData()\n",
    "spot_data_table = db.Table('spot_data', metadata, autoload_with=engine)\n",
    "query = spot_data_table.select()\n",
    "result = connection.execute(query).fetchall()\n",
    "result[:10]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "978a9b66",
   "metadata": {},
   "source": [
    "We can also use SQLAlchemy to append data to an existing table. In this example we will append the data to the table we created in the previous example.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "00cc23c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "spot_data.to_sql(connection_string=DATABASE_URL, table_name='spot_data', if_exists='append')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3adaa4d4",
   "metadata": {},
   "source": [
    "You might for example have a job set up to export the data to your database every day. You could then use the database to create a dashboard to show the client how the campaign is performing.\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}