mock-data-set-generation-to-database.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# mock-data-set-generation-to-database\n",
    "\n",
    "\n",
    "- a process to follow when generating test data\n",
    "- a template to generate mock data sets to CSV file(s), including the use of weighting\n",
    "- a template to allow the generated data to be inserted in to a PostgreSQL database\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Example local database setup\n",
    "\n",
    "Commands can be run in your terminal\n",
    "\n",
    "`docker pull postgres`\n",
    "\n",
    "`docker run --name pg-docker -e POSTGRES_PASSWORD={INSERT_CHOSEN_PASSWORD} -p 5433:5433 -d postgres`\n",
    "\n",
    "`docker inspect pg-docker`\n",
    "\n",
    " - retrieve IP address from output of inspect command\n",
    "\n",
    "`docker ps -a`\n",
    "\n",
    "`docker exec -it {container ID from above command} bash`\n",
    "\n",
    "`psql -h {retrieved IP address} -U postgres`\n",
    "\n",
    " - enter chosen password\n",
    "\n",
    "`CREATE DATABASE test_db;`\n",
    "\n",
    "`\\c test_db`\n",
    "\n",
    "`CREATE SCHEMA test_schema ;`\n",
    "\n",
    "`CREATE TABLE test_table (\n",
    "  id SERIAL PRIMARY KEY,\n",
    "  test_username varchar NOT NULL,\n",
    "  test_password varchar NOT NULL,\n",
    "  test_email varchar,\n",
    "  test_address varchar,\n",
    "  test_gender varchar,\n",
    "  test_phone_number numeric,\n",
    "  test_created_date date\n",
    ");`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-09-05T00:53:15.099403Z",
     "start_time": "2020-09-05T00:53:15.089650Z"
    }
   },
   "outputs": [],
   "source": [
    "import psycopg2\n",
    "import random\n",
    "import pandas as pd\n",
    "import csv\n",
    "import getpass\n",
    "\n",
    "from datetime import datetime, timedelta\n",
    "from faker import Faker"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Helper functions\n",
    "\n",
    "Useful section to define any useful function(s) used throughout the Notebook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-09-05T00:53:16.631322Z",
     "start_time": "2020-09-05T00:53:16.620281Z"
    }
   },
   "outputs": [],
   "source": [
    "# example database connection for a postgres docker container\n",
    "\n",
    "def connection(db):\n",
    "    return psycopg2.connect(database=db,\n",
    "                            user=getpass.getpass(prompt=\"Enter database user: \"),\n",
    "                            password=getpass.getpass(prompt=\"Enter database password: \"),\n",
    "                            host=getpass.getpass(prompt=\"Enter database host: \"),\n",
    "                            port=getpass.getpass(prompt=\"Enter database port: \"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-09-05T00:30:55.435508Z",
     "start_time": "2020-09-05T00:30:55.425010Z"
    }
   },
   "outputs": [],
   "source": [
    "def get_next_value_in_sequence(list_of_sequence_numbers: list):\n",
    "    for num in list_of_sequence_numbers:\n",
    "        sequence_value = list_of_sequence_numbers.pop(0)\n",
    "        return sequence_value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-09-05T00:30:55.687385Z",
     "start_time": "2020-09-05T00:30:55.664278Z"
    }
   },
   "outputs": [],
   "source": [
    "def query_database_return_dataframe(db_connection: str, query: str):\n",
    "    with connection(db_connection) as conn, conn.cursor() as cursor:\n",
    "        print(\"\\n Running provided query... \\n\")\n",
    "        cursor.execute(f\"\"\"{query}\"\"\")  \n",
    "        print(\"Query complete!\\n\")\n",
    "        col_names = []\n",
    "        for column_name in cursor.description:\n",
    "            col_names.append(column_name[0])\n",
    "        \n",
    "        result = cursor.fetchall()\n",
    "        result_df = pd.DataFrame(result, columns=col_names)\n",
    "        \n",
    "        return result_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-09-05T00:30:55.941149Z",
     "start_time": "2020-09-05T00:30:55.922916Z"
    }
   },
   "outputs": [],
   "source": [
    "def truncate_data_in_database(db_connection: str, db_table: str, cascade_required=False):\n",
    "    with connection(db_connection) as conn, conn.cursor() as cursor:\n",
    "        print(f\"truncating - {db_table} - started\\n\")\n",
    "        if cascade_required:\n",
    "            print(\"CASCADE requested. . . \\n\")\n",
    "            cursor.execute(f\"\"\"truncate {db_table} CASCADE ;\"\"\")\n",
    "            conn.commit()\n",
    "            print(f\"truncating - {db_table} - with CASCADE completed\\n\")\n",
    "        else:\n",
    "            cursor.execute(f\"\"\"truncate {db_table} ;\"\"\")\n",
    "            conn.commit()\n",
    "            print(f\"truncating - {db_table} - completed\\n\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-09-05T00:30:56.203775Z",
     "start_time": "2020-09-05T00:30:56.186437Z"
    }
   },
   "outputs": [],
   "source": [
    "def insert_data_to_database_from_csv(db_connection: str, table_name: str, file_name: str):\n",
    "    with connection(db_connection) as conn, conn.cursor() as cursor:\n",
    "        print(f\"Connecting to {db_connection}\\n\")\n",
    "        with open(file_name, 'r') as f:\n",
    "            print(f\"Copying data from {file_name} in to {db_connection}.{table_name}\\n\")\n",
    "            next(f) \n",
    "            cursor.copy_from(f, table_name, sep=',')\n",
    "\n",
    "            conn.commit()\n",
    "            print(\"Data inserted successfully!\\n\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-09-05T00:30:56.740649Z",
     "start_time": "2020-09-05T00:30:56.726453Z"
    }
   },
   "outputs": [],
   "source": [
    "def set_sequence_in_database(db_connection: str, sequence_name: str, sequence_value: int):\n",
    "    with connection(db_connection) as conn, conn.cursor() as cursor:\n",
    "        print(f\"Connecting to {db_connection} to set the sequence value to {sequence_value}\\n\")\n",
    "        cursor.execute(f\"\"\"SELECT pg_catalog.setval('{sequence_name}', {sequence_value}, true);\"\"\")\n",
    "        print(f\"Set sequence value to {sequence_value} - now complete!\\n\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-09-05T00:30:57.189525Z",
     "start_time": "2020-09-05T00:30:57.182645Z"
    }
   },
   "outputs": [],
   "source": [
    "def view_csv_file_as_dataframe(file_name: str):\n",
    "    return pd.read_csv(file_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SQL commands\n",
    "\n",
    "Useful section to define all of your SQL commands you plan to execute\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-09-05T00:30:57.948124Z",
     "start_time": "2020-09-05T00:30:57.942142Z"
    }
   },
   "outputs": [],
   "source": [
    "EXAMPLE_QUERY = \"\"\"\n",
    "SELECT *\n",
    "FROM test_table;\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Generate CSV files of mock data\n",
    "\n",
    "Useful section to define the model(s) of data you wish to be created in a CSV file, which can then be used to populate your database or explore within the Notebook \n",
    "\n",
    "#### Performance:\n",
    "\n",
    "- 1,000 records == 8ms\n",
    "- 10,000 records == 4 seconds\n",
    "- 100,000 records == 46 seconds\n",
    "- 1,000,000 records == 12 minutes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-09-05T00:34:30.371304Z",
     "start_time": "2020-09-05T00:34:30.363154Z"
    }
   },
   "outputs": [],
   "source": [
    "NUMBER_OF_TEST_RECORDS_TO_GENERATE = 1000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-09-05T00:34:30.944593Z",
     "start_time": "2020-09-05T00:34:30.878069Z"
    }
   },
   "outputs": [],
   "source": [
    "fake = Faker()\n",
    "\n",
    "\n",
    "def create_csv_file_of_mock_data(number_of_records: int, file_name: str):\n",
    "\n",
    "    test_ids = [number for number in range(1, number_of_records)]\n",
    "\n",
    "    with open(file_name, 'w', newline='') as csvfile:\n",
    "        fieldnames = ['id',\n",
    "                      'test_username',\n",
    "                      'test_password',\n",
    "                      'test_email',\n",
    "                      'test_gender',\n",
    "                      'test_address',\n",
    "                      'test_phone_number',\n",
    "                      'test_created_date']\n",
    "        \n",
    "        writer = csv.DictWriter(csvfile, fieldnames=fieldnames)\n",
    "\n",
    "        writer.writeheader()\n",
    "        for i in range(number_of_records - 1):\n",
    "            \n",
    "            test_genders = ['Male', 'Female']\n",
    "\n",
    "            writer.writerow(\n",
    "                {\n",
    "                   \n",
    "                  'id': get_next_value_in_sequence(test_ids),\n",
    "                  'test_username': fake.first_name() + fake.last_name(),\n",
    "                  'test_password':fake.uuid4(),\n",
    "                  'test_email': fake.safe_email(),\n",
    "                  'test_gender': random.choices(test_genders, weights=[50,50], k=1)[0],\n",
    "                  'test_address': fake.street_address(),\n",
    "                  'test_phone_number': fake.random_int(44777777777, 44777777999),\n",
    "                  'test_created_date': datetime.today() - timedelta(days = 1)\n",
    "                }\n",
    "            )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-09-05T00:45:54.828129Z",
     "start_time": "2020-09-05T00:34:31.520836Z"
    }
   },
   "outputs": [],
   "source": [
    "create_csv_file_of_mock_data(NUMBER_OF_TEST_RECORDS_TO_GENERATE, 'test_mock_data_file.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-09-05T00:46:30.967239Z",
     "start_time": "2020-09-05T00:46:28.113372Z"
    }
   },
   "outputs": [],
   "source": [
    "view_csv_file_as_dataframe('test_mock_data_file.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Insert generated data from CSV file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-09-05T00:49:17.233289Z",
     "start_time": "2020-09-05T00:49:09.269344Z"
    }
   },
   "outputs": [],
   "source": [
    "insert_data_to_database_from_csv('test_db',\n",
    "                                 'test_table',\n",
    "                                 'test_mock_data_file.csv')\n",
    "\n",
    "set_sequence_in_database('test_db', 'test_table_id_seq', NUMBER_OF_TEST_RECORDS_TO_GENERATE + 1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Inspect the data inserted in to the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-09-05T00:53:50.348519Z",
     "start_time": "2020-09-05T00:53:24.497795Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "query_database_return_dataframe('test_db', EXAMPLE_QUERY)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Truncate data in database table\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-09-05T00:48:00.704534Z",
     "start_time": "2020-09-05T00:48:00.616311Z"
    }
   },
   "outputs": [],
   "source": [
    "truncate_data_in_database('test_db',\n",
    "                          'test_table')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.7.6 64-bit ('aws': conda)",
   "language": "python",
   "name": "python37664bitawscondadc7dd8adede046d1a312bac4eba0d2b5"
  },
  "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.7.6"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}