ETL-PhotoGallery.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c3f7cff3",
   "metadata": {},
   "source": [
    "# Implementing ETL Pipeline Using SQLAlchemy"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "269de74c",
   "metadata": {},
   "source": [
    "SQLAlchemy is a Python module that allows data engineers and developers to define schemas, write queries, and manipulate SQL databases entirely through Python. SQLAlchemy’s Object Relational Mapper (ORM) and Expression Language functionalities allowing us to associate Python classes and constructs with data tables and expressions.\n",
    "\n",
    "For this project, I will be extracting data from two different mock APIs which are:\n",
    "- [Random User Generator](https://randomuser.me/) - to get user profiles data\n",
    "- [JSONPlaceholder](https://jsonplaceholder.typicode.com/) - to get albums and photos data\n",
    "\n",
    "The extracted data will be in JSON format, which will be transformed into a flat format that can be safely written to the database without error.\n",
    "\n",
    "It will then be loaded into its database which is `gallery.db`. This database should contain three tables: **users, albums, photos** <br><br>\n",
    "\n",
    "![Entity Relation Diagram](entity-relation-diagram.png)\n",
    "<p style=\"text-align: center;\"><i>Entity Relation Diagram for Photo Gallery database</i></p><br>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4a8cd6a3",
   "metadata": {},
   "source": [
    "## Defining a database schema\n",
    "A database schema defines the structure of a database system, in terms of tables, columns, fields, and the relationships between them. Schemas can be defined in raw SQL, or through the use of SQLAlchemy’s ORM feature."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "bf0b8cd1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: sqlalchemy in c:\\users\\user\\anaconda3\\lib\\site-packages (1.4.7)\n",
      "Requirement already satisfied: greenlet!=0.4.17 in c:\\users\\user\\anaconda3\\lib\\site-packages (from sqlalchemy) (1.0.0)\n"
     ]
    }
   ],
   "source": [
    "# Install sqlalchemy\n",
    "! pip install sqlalchemy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "025f15ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import *\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "from sqlalchemy.orm import sessionmaker\n",
    "from sqlalchemy.sql import *\n",
    "\n",
    "engine = create_engine('sqlite:///gallery.db')\n",
    "\n",
    "Base = declarative_base()\n",
    "\n",
    "class Users(Base):\n",
    "    __tablename__ = \"users\"\n",
    "    UserId = Column(Integer, primary_key=True)\n",
    "    Title = Column(String)\n",
    "    FirstName = Column(String)\n",
    "    LastName = Column(String)\n",
    "    Email = Column(String)\n",
    "    Username = Column(String)\n",
    "    DOB = Column(DateTime)\n",
    "    \n",
    "class Albums(Base):\n",
    "    __tablename__=\"albums\"\n",
    "    AlbumId = Column(Integer, primary_key=True)\n",
    "    UserId = Column(Integer)\n",
    "    Title = Column(String)\n",
    "    \n",
    "class Photos(Base):\n",
    "    __tablename__=\"photos\"\n",
    "    PhotoId = Column(Integer, primary_key=True)\n",
    "    AlbumId = Column(Integer)\n",
    "    Title = Column(String)\n",
    "    Url = Column(String)\n",
    "    \n",
    "Users.__table__.create(bind=engine, checkfirst=True)\n",
    "Albums.__table__.create(bind=engine, checkfirst=True)\n",
    "Photos.__table__.create(bind=engine, checkfirst=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7b3100dc",
   "metadata": {},
   "source": [
    "## Extract"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "26c11198",
   "metadata": {},
   "source": [
    "Once the schema has been defined, the next task is to extract the raw data from its source.\n",
    "The **Users** table will be populated with profiles randomly generated at `randomuser.me`, while the **Albums** and **Photos** table will contain data courtesy of JSONPlaceholder.\n",
    "\n",
    "Python’s `Requests` module is used to call these APIs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "7a4515ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "\n",
    "url = 'https://randomuser.me/api/?results=10'\n",
    "users_json = requests.get(url).json()\n",
    "\n",
    "url2 = 'https://jsonplaceholder.typicode.com/albums/'\n",
    "albums_json = requests.get(url2).json()\n",
    "\n",
    "url3 = 'https://jsonplaceholder.typicode.com/photos/'\n",
    "photos_json = requests.get(url3).json()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "180c9194",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'info': {'page': 1,\n",
      "          'results': 10,\n",
      "          'seed': 'da82295f31b344a3',\n",
      "          'version': '1.3'},\n",
      " 'results': [{'cell': '(69) 1141-3075',\n",
      "              'dob': {'age': 70, 'date': '1951-05-05T04:03:23.515Z'},\n",
      "              'email': 'benta.barros@example.com',\n",
      "              'gender': 'female',\n",
      "              'id': {'name': '', 'value': None},\n",
      "              'location': {'city': 'Várzea Paulista',\n",
      "                           'coordinates': {'latitude': '-72.4530',\n",
      "                                           'longitude': '107.8648'},\n",
      "                           'country': 'Brazil',\n",
      "                           'postcode': 77002,\n",
      "                           'state': 'Rondônia',\n",
      "                           'street': {'name': 'Rua Doze ', 'number': 2256},\n",
      "                           'timezone': {'description': 'Brazil, Buenos Aires, '\n",
      "                                                       'Georgetown',\n",
      "                                        'offset': '-3:00'}},\n",
      "              'login': {'md5': '05e16726af91f81c6ffd8be5d3e65660',\n",
      "                        'password': 'beater',\n",
      "                        'salt': 'w08ggsqr',\n",
      "                        'sha1': 'ad9701705f198d9f5d37eadb73bf7fad9f4c24ac',\n",
      "                        'sha256': '0db632fec552375d8989bc00c3b928933c115d513d13d060cc47c3054eecf2a1',\n",
      "                        'username': 'smallmouse460',\n",
      "                        'uuid': '119535fc-b5ff-4114-841d-961af7c39008'},\n",
      "              'name': {'first': 'Benta', 'last': 'Barros', 'title': 'Mrs'},\n",
      "              'nat': 'BR',\n",
      "              'phone': '(48) 2500-5608',\n",
      "              'picture': {'large': 'https://randomuser.me/api/portraits/women/93.jpg',\n",
      "                          'medium': 'https://randomuser.me/api/portraits/med/women/93.jpg',\n",
      "                          'thumbnail': 'https://randomuser.me/api/portraits/thumb/women/93.jpg'},\n",
      "              'registered': {'age': 7, 'date': '2014-02-19T06:59:41.499Z'}},\n",
      "             {'cell': '0175-3970651',\n",
      "              'dob': {'age': 25, 'date': '1996-05-20T10:38:44.917Z'},\n",
      "              'email': 'miroslaw.weidmann@example.com',\n",
      "              'gender': 'male',\n",
      "              'id': {'name': '', 'value': None},\n",
      "              'location': {'city': 'Dietzenbach',\n",
      "                           'coordinates': {'latitude': '21.0443',\n",
      "                                           'longitude': '-149.2836'},\n",
      "                           'country': 'Germany',\n",
      "                           'postcode': 65177,\n",
      "                           'state': 'Baden-Württemberg',\n",
      "                           'street': {'name': 'Buchenweg', 'number': 2683},\n",
      "                           'timezone': {'description': 'Eniwetok, Kwajalein',\n",
      "                                        'offset': '-12:00'}},\n",
      "              'login': {'md5': '73400fca4a2a45849573e7deb98541b3',\n",
      "                        'password': 'nextel',\n",
      "                        'salt': 'xW40zjiJ',\n",
      "                        'sha1': 'abd1a12a6913c87194a05e84d08c4128e25371bb',\n",
      "                        'sha256': 'af68c63152e602fb3c943b23057888f634dd3e6f16ad2692bd5b111917d8f480',\n",
      "                        'username': 'blueduck690',\n",
      "                        'uuid': 'd9e12715-0b84-46be-8423-096cef6229a3'},\n",
      "              'name': {'first': 'Miroslaw', 'last': 'Weidmann', 'title': 'Mr'},\n",
      "              'nat': 'DE',\n",
      "              'phone': '0005-5241621',\n",
      "              'picture': {'large': 'https://randomuser.me/api/portraits/men/75.jpg',\n",
      "                          'medium': 'https://randomuser.me/api/portraits/med/men/75.jpg',\n",
      "                          'thumbnail': 'https://randomuser.me/api/portraits/thumb/men/75.jpg'},\n",
      "              'registered': {'age': 10, 'date': '2011-05-03T19:57:37.972Z'}},\n",
      "             {'cell': '93551070',\n",
      "              'dob': {'age': 34, 'date': '1987-01-03T00:19:37.330Z'},\n",
      "              'email': 'nicoline.mortensen@example.com',\n",
      "              'gender': 'female',\n",
      "              'id': {'name': 'CPR', 'value': '030187-5236'},\n",
      "              'location': {'city': 'Ryslinge',\n",
      "                           'coordinates': {'latitude': '-54.9593',\n",
      "                                           'longitude': '56.3200'},\n",
      "                           'country': 'Denmark',\n",
      "                           'postcode': 39993,\n",
      "                           'state': 'Nordjylland',\n",
      "                           'street': {'name': 'Søgade', 'number': 3847},\n",
      "                           'timezone': {'description': 'Abu Dhabi, Muscat, '\n",
      "                                                       'Baku, Tbilisi',\n",
      "                                        'offset': '+4:00'}},\n",
      "              'login': {'md5': 'fd676b5b5037d648ff40a208bf666331',\n",
      "                        'password': 'isabella',\n",
      "                        'salt': '4laOHg41',\n",
      "                        'sha1': 'f559e31da67d7657fe70a87f14625d8dc7eb7673',\n",
      "                        'sha256': 'd66442179b14fcd52e3b8b9bebb1823b72a1ed4bdeccff04680fc826a272fd9e',\n",
      "                        'username': 'bigbird669',\n",
      "                        'uuid': 'f8c11893-71a4-4285-bc1a-8248b86a6da9'},\n",
      "              'name': {'first': 'Nicoline',\n",
      "                       'last': 'Mortensen',\n",
      "                       'title': 'Mrs'},\n",
      "              'nat': 'DK',\n",
      "              'phone': '45138957',\n",
      "              'picture': {'large': 'https://randomuser.me/api/portraits/women/8.jpg',\n",
      "                          'medium': 'https://randomuser.me/api/portraits/med/women/8.jpg',\n",
      "                          'thumbnail': 'https://randomuser.me/api/portraits/thumb/women/8.jpg'},\n",
      "              'registered': {'age': 4, 'date': '2017-02-23T07:39:13.581Z'}},\n",
      "             {'cell': '37561293',\n",
      "              'dob': {'age': 51, 'date': '1970-02-20T08:39:04.221Z'},\n",
      "              'email': 'filippa.poulsen@example.com',\n",
      "              'gender': 'female',\n",
      "              'id': {'name': 'CPR', 'value': '200270-5914'},\n",
      "              'location': {'city': 'Stokkemarke',\n",
      "                           'coordinates': {'latitude': '-30.0132',\n",
      "                                           'longitude': '139.8373'},\n",
      "                           'country': 'Denmark',\n",
      "                           'postcode': 50744,\n",
      "                           'state': 'Danmark',\n",
      "                           'street': {'name': 'Brorsonsvej', 'number': 71},\n",
      "                           'timezone': {'description': 'Western Europe Time, '\n",
      "                                                       'London, Lisbon, '\n",
      "                                                       'Casablanca',\n",
      "                                        'offset': '0:00'}},\n",
      "              'login': {'md5': 'f1104ba4e8e2fa9a4004ed7227b3afca',\n",
      "                        'password': 'sixsix',\n",
      "                        'salt': 'rNRvhXeN',\n",
      "                        'sha1': '3504eef5eec971204c469887c5b76fe8ded37254',\n",
      "                        'sha256': '0f7f4411bd85b560adc86f3348447ce7b9a3d8492492225858ee3efd6e254a7c',\n",
      "                        'username': 'heavywolf486',\n",
      "                        'uuid': '20a81ed7-11ac-4d16-825e-f8cf58921c08'},\n",
      "              'name': {'first': 'Filippa', 'last': 'Poulsen', 'title': 'Mrs'},\n",
      "              'nat': 'DK',\n",
      "              'phone': '87549498',\n",
      "              'picture': {'large': 'https://randomuser.me/api/portraits/women/91.jpg',\n",
      "                          'medium': 'https://randomuser.me/api/portraits/med/women/91.jpg',\n",
      "                          'thumbnail': 'https://randomuser.me/api/portraits/thumb/women/91.jpg'},\n",
      "              'registered': {'age': 11, 'date': '2010-06-24T15:53:11.105Z'}},\n",
      "             {'cell': '702-803-9703',\n",
      "              'dob': {'age': 67, 'date': '1954-08-28T07:40:56.157Z'},\n",
      "              'email': 'camille.chow@example.com',\n",
      "              'gender': 'female',\n",
      "              'id': {'name': '', 'value': None},\n",
      "              'location': {'city': 'Westport',\n",
      "                           'coordinates': {'latitude': '-52.4362',\n",
      "                                           'longitude': '-47.4635'},\n",
      "                           'country': 'Canada',\n",
      "                           'postcode': 'J6E 6V0',\n",
      "                           'state': 'Nunavut',\n",
      "                           'street': {'name': 'Main St', 'number': 991},\n",
      "                           'timezone': {'description': 'Adelaide, Darwin',\n",
      "                                        'offset': '+9:30'}},\n",
      "              'login': {'md5': 'bbe07060a38024199da8353585e181b8',\n",
      "                        'password': '1103',\n",
      "                        'salt': 'hJRMJ4ig',\n",
      "                        'sha1': 'c71c826cd442af6ab7d458da5f5e8a09350ef9aa',\n",
      "                        'sha256': 'a71615c3a615fe436c7831ed507c0ec0f207e1da6b12257fb08a7ee061287e4e',\n",
      "                        'username': 'beautifulleopard910',\n",
      "                        'uuid': 'a4b4c970-1d27-44ab-91c1-ad7f677fdb87'},\n",
      "              'name': {'first': 'Camille', 'last': 'Chow', 'title': 'Mrs'},\n",
      "              'nat': 'CA',\n",
      "              'phone': '938-134-2259',\n",
      "              'picture': {'large': 'https://randomuser.me/api/portraits/women/78.jpg',\n",
      "                          'medium': 'https://randomuser.me/api/portraits/med/women/78.jpg',\n",
      "                          'thumbnail': 'https://randomuser.me/api/portraits/thumb/women/78.jpg'},\n",
      "              'registered': {'age': 12, 'date': '2009-01-12T15:44:44.463Z'}},\n",
      "             {'cell': '075 948 46 09',\n",
      "              'dob': {'age': 75, 'date': '1946-04-20T23:46:23.550Z'},\n",
      "              'email': 'aaron.simon@example.com',\n",
      "              'gender': 'male',\n",
      "              'id': {'name': 'AVS', 'value': '756.2876.9568.09'},\n",
      "              'location': {'city': 'Épalinges',\n",
      "                           'coordinates': {'latitude': '72.8972',\n",
      "                                           'longitude': '-34.2948'},\n",
      "                           'country': 'Switzerland',\n",
      "                           'postcode': 3440,\n",
      "                           'state': 'Nidwalden',\n",
      "                           'street': {'name': 'Rue de la Mairie',\n",
      "                                      'number': 510},\n",
      "                           'timezone': {'description': 'Atlantic Time '\n",
      "                                                       '(Canada), Caracas, La '\n",
      "                                                       'Paz',\n",
      "                                        'offset': '-4:00'}},\n",
      "              'login': {'md5': '2ec74d182b6d96ada3d63647467f8b02',\n",
      "                        'password': 'watcher',\n",
      "                        'salt': 'SjPVV4q5',\n",
      "                        'sha1': '857dd8e3873dc5fcd0dc91014b5df54dff627dc4',\n",
      "                        'sha256': 'dff89c6bb571b86f474899dc65bb9ed9fbbaecdbdde2a8f6f83316f5ed5d8a38',\n",
      "                        'username': 'ticklishrabbit384',\n",
      "                        'uuid': 'fd5c9a23-f59f-4272-b428-e19f9519ba9e'},\n",
      "              'name': {'first': 'Aaron', 'last': 'Simon', 'title': 'Monsieur'},\n",
      "              'nat': 'CH',\n",
      "              'phone': '077 307 26 69',\n",
      "              'picture': {'large': 'https://randomuser.me/api/portraits/men/98.jpg',\n",
      "                          'medium': 'https://randomuser.me/api/portraits/med/men/98.jpg',\n",
      "                          'thumbnail': 'https://randomuser.me/api/portraits/thumb/men/98.jpg'},\n",
      "              'registered': {'age': 12, 'date': '2009-07-17T18:09:31.566Z'}},\n",
      "             {'cell': '489-800-2487',\n",
      "              'dob': {'age': 70, 'date': '1951-12-24T15:19:22.268Z'},\n",
      "              'email': 'james.abraham@example.com',\n",
      "              'gender': 'male',\n",
      "              'id': {'name': '', 'value': None},\n",
      "              'location': {'city': 'Glenwood',\n",
      "                           'coordinates': {'latitude': '-65.0835',\n",
      "                                           'longitude': '-143.8507'},\n",
      "                           'country': 'Canada',\n",
      "                           'postcode': 'D2R 8B6',\n",
      "                           'state': 'Ontario',\n",
      "                           'street': {'name': 'Brock Rd', 'number': 6445},\n",
      "                           'timezone': {'description': 'Eastern Time (US & '\n",
      "                                                       'Canada), Bogota, Lima',\n",
      "                                        'offset': '-5:00'}},\n",
      "              'login': {'md5': '7d2d587ee811d679e4e7df8eeb83e71a',\n",
      "                        'password': 'yoda',\n",
      "                        'salt': 'klUHXKee',\n",
      "                        'sha1': '0e55013013c452aa58eaa126fdc9846f59d52047',\n",
      "                        'sha256': '0c3d080692efc5baf015f3f1d446055acdf414f64b6e44454af12ad472b1036a',\n",
      "                        'username': 'whiteostrich169',\n",
      "                        'uuid': '008e26ce-5d26-4733-8ec0-3d05fb055a39'},\n",
      "              'name': {'first': 'James', 'last': 'Abraham', 'title': 'Mr'},\n",
      "              'nat': 'CA',\n",
      "              'phone': '259-300-1159',\n",
      "              'picture': {'large': 'https://randomuser.me/api/portraits/men/1.jpg',\n",
      "                          'medium': 'https://randomuser.me/api/portraits/med/men/1.jpg',\n",
      "                          'thumbnail': 'https://randomuser.me/api/portraits/thumb/men/1.jpg'},\n",
      "              'registered': {'age': 17, 'date': '2004-12-19T02:14:04.486Z'}},\n",
      "             {'cell': '0755-582-789',\n",
      "              'dob': {'age': 42, 'date': '1979-11-20T16:12:58.237Z'},\n",
      "              'email': 'fernando.macrae@example.com',\n",
      "              'gender': 'male',\n",
      "              'id': {'name': 'NINO', 'value': 'MB 73 04 68 D'},\n",
      "              'location': {'city': 'Stirling',\n",
      "                           'coordinates': {'latitude': '55.4986',\n",
      "                                           'longitude': '-144.2236'},\n",
      "                           'country': 'United Kingdom',\n",
      "                           'postcode': 'I56 2TG',\n",
      "                           'state': 'Suffolk',\n",
      "                           'street': {'name': 'Grange Road', 'number': 2207},\n",
      "                           'timezone': {'description': 'Brussels, Copenhagen, '\n",
      "                                                       'Madrid, Paris',\n",
      "                                        'offset': '+1:00'}},\n",
      "              'login': {'md5': '7768a66a886400652713dacb4bbdf8bb',\n",
      "                        'password': 'coldbeer',\n",
      "                        'salt': 'Ln9Tn0i6',\n",
      "                        'sha1': '2ee0305b9bf64f5db3b5f3053a116ae3279a46b3',\n",
      "                        'sha256': '83f8897abdb53ef429b41cd809b4915c8044ca939fc2e4a4eff2313730dbee2a',\n",
      "                        'username': 'crazysnake348',\n",
      "                        'uuid': '360f40a4-89b2-4acc-a4ef-dc506f4850b3'},\n",
      "              'name': {'first': 'Fernando', 'last': 'Macrae', 'title': 'Mr'},\n",
      "              'nat': 'GB',\n",
      "              'phone': '016977 83623',\n",
      "              'picture': {'large': 'https://randomuser.me/api/portraits/men/23.jpg',\n",
      "                          'medium': 'https://randomuser.me/api/portraits/med/men/23.jpg',\n",
      "                          'thumbnail': 'https://randomuser.me/api/portraits/thumb/men/23.jpg'},\n",
      "              'registered': {'age': 18, 'date': '2003-08-06T11:46:30.131Z'}},\n",
      "             {'cell': '90757997',\n",
      "              'dob': {'age': 66, 'date': '1955-12-26T00:23:31.679Z'},\n",
      "              'email': 'maximillian.heggen@example.com',\n",
      "              'gender': 'male',\n",
      "              'id': {'name': 'FN', 'value': '26125525538'},\n",
      "              'location': {'city': 'Florø',\n",
      "                           'coordinates': {'latitude': '34.3906',\n",
      "                                           'longitude': '57.6965'},\n",
      "                           'country': 'Norway',\n",
      "                           'postcode': '4168',\n",
      "                           'state': 'Hedmark',\n",
      "                           'street': {'name': 'Stensberggata', 'number': 177},\n",
      "                           'timezone': {'description': 'Kaliningrad, South '\n",
      "                                                       'Africa',\n",
      "                                        'offset': '+2:00'}},\n",
      "              'login': {'md5': '6317cf6740ffc1288f97a27ab175212b',\n",
      "                        'password': 'schmidt',\n",
      "                        'salt': 'hjQiv8Yh',\n",
      "                        'sha1': 'c4c38a96b5ae6380a2ea31f5fae1e5e17c213812',\n",
      "                        'sha256': '7420d25df9ca7118e012c7f75cec99cba1070413b2676b374e6df1d2616b3b0f',\n",
      "                        'username': 'sadkoala521',\n",
      "                        'uuid': 'f0f7c6a1-f4dc-4af8-9d1f-22704a35a423'},\n",
      "              'name': {'first': 'Maximillian', 'last': 'Heggen', 'title': 'Mr'},\n",
      "              'nat': 'NO',\n",
      "              'phone': '35538881',\n",
      "              'picture': {'large': 'https://randomuser.me/api/portraits/men/52.jpg',\n",
      "                          'medium': 'https://randomuser.me/api/portraits/med/men/52.jpg',\n",
      "                          'thumbnail': 'https://randomuser.me/api/portraits/thumb/men/52.jpg'},\n",
      "              'registered': {'age': 14, 'date': '2007-06-26T19:20:16.977Z'}},\n",
      "             {'cell': '637-578-673',\n",
      "              'dob': {'age': 53, 'date': '1968-03-23T13:18:17.164Z'},\n",
      "              'email': 'rocio.leon@example.com',\n",
      "              'gender': 'female',\n",
      "              'id': {'name': 'DNI', 'value': '55810496-M'},\n",
      "              'location': {'city': 'Cuenca',\n",
      "                           'coordinates': {'latitude': '44.1008',\n",
      "                                           'longitude': '12.9893'},\n",
      "                           'country': 'Spain',\n",
      "                           'postcode': 27031,\n",
      "                           'state': 'Castilla la Mancha',\n",
      "                           'street': {'name': 'Avenida del Planetario',\n",
      "                                      'number': 7776},\n",
      "                           'timezone': {'description': 'Azores, Cape Verde '\n",
      "                                                       'Islands',\n",
      "                                        'offset': '-1:00'}},\n",
      "              'login': {'md5': '44d0103d5c18419660b59d243214c854',\n",
      "                        'password': 'sanford',\n",
      "                        'salt': '70BtuKhl',\n",
      "                        'sha1': 'df695102933a9d71e076a8aec1cc85ffdf1ac8fa',\n",
      "                        'sha256': '2bddb85c46da68edcc7caf7127d78cc63c73e97a8c5355f98fd338dc5878e1dc',\n",
      "                        'username': 'organiczebra872',\n",
      "                        'uuid': 'c911ffa1-2204-49f5-9121-8f7ded0047db'},\n",
      "              'name': {'first': 'Rocio', 'last': 'Leon', 'title': 'Ms'},\n",
      "              'nat': 'ES',\n",
      "              'phone': '983-554-223',\n",
      "              'picture': {'large': 'https://randomuser.me/api/portraits/women/87.jpg',\n",
      "                          'medium': 'https://randomuser.me/api/portraits/med/women/87.jpg',\n",
      "                          'thumbnail': 'https://randomuser.me/api/portraits/thumb/women/87.jpg'},\n",
      "              'registered': {'age': 19, 'date': '2002-06-24T00:35:17.815Z'}}]}\n"
     ]
    }
   ],
   "source": [
    "import pprint\n",
    "pprint.pprint(users_json)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7fb82c3f",
   "metadata": {},
   "outputs": [],
   "source": [
    "#pprint.pprint(albums_json)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b2c081e9",
   "metadata": {},
   "outputs": [],
   "source": [
    "#pprint.pprint(photos_json)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "af5d6485",
   "metadata": {},
   "source": [
    "The data is currently held in three objects (`users_json`,`albums_json`, and `photos_json`) in JSON format. The next step will be to transform and load this data into the tables defined earlier."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f8079fb9",
   "metadata": {},
   "source": [
    "## Transform"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e3d2696a",
   "metadata": {},
   "source": [
    "Transform the data from its current nested JSON format to a flat format that can be safely written to the database without error.\n",
    "The code below creates three lists, `users`,`albums` and `photos`, which will be used in the final step:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "ff82e976",
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime, timedelta\n",
    "import dateutil.parser\n",
    "\n",
    "users, albums, photos = [],[],[]\n",
    "\n",
    "for i, result in enumerate(users_json['results']):\n",
    "    row = {}\n",
    "    row['UserId'] = i\n",
    "    row['Title'] = result['name']['title']\n",
    "    row['FirstName'] = result['name']['first']\n",
    "    row['LastName'] = result['name']['last']\n",
    "    row['Email'] = result['email']\n",
    "    row['Username'] = result['login']['username']\n",
    "    d = dateutil.parser.parse(result['dob']['date'])\n",
    "    dob = d.strftime('%Y-%m-%d %H:%M:&S')\n",
    "    dob = datetime.strptime(dob, '%Y-%m-%d %H:%M:&S' )\n",
    "    row['DOB']=dob\n",
    "    users.append(row)\n",
    "\n",
    "for result in albums_json:\n",
    "    row = {}\n",
    "    row['AlbumId'] = result['id']\n",
    "    row['UserId'] = result['userId']\n",
    "    row['Title'] = result['title']\n",
    "    albums.append(row)\n",
    "    \n",
    "for result in photos_json:\n",
    "    row = {}\n",
    "    row['PhotoId'] = result['id']\n",
    "    row['AlbumId'] = result['albumId']\n",
    "    row['Title'] = result['title']\n",
    "    row['Url'] = result['url']\n",
    "    photos.append(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "43f22350",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{'UserId': 0, 'Title': 'Mrs', 'FirstName': 'Benta', 'LastName': 'Barros', 'Email': 'benta.barros@example.com', 'Username': 'smallmouse460', 'DOB': datetime.datetime(1951, 5, 5, 4, 3)}, {'UserId': 1, 'Title': 'Mr', 'FirstName': 'Miroslaw', 'LastName': 'Weidmann', 'Email': 'miroslaw.weidmann@example.com', 'Username': 'blueduck690', 'DOB': datetime.datetime(1996, 5, 20, 10, 38)}, {'UserId': 2, 'Title': 'Mrs', 'FirstName': 'Nicoline', 'LastName': 'Mortensen', 'Email': 'nicoline.mortensen@example.com', 'Username': 'bigbird669', 'DOB': datetime.datetime(1987, 1, 3, 0, 19)}, {'UserId': 3, 'Title': 'Mrs', 'FirstName': 'Filippa', 'LastName': 'Poulsen', 'Email': 'filippa.poulsen@example.com', 'Username': 'heavywolf486', 'DOB': datetime.datetime(1970, 2, 20, 8, 39)}, {'UserId': 4, 'Title': 'Mrs', 'FirstName': 'Camille', 'LastName': 'Chow', 'Email': 'camille.chow@example.com', 'Username': 'beautifulleopard910', 'DOB': datetime.datetime(1954, 8, 28, 7, 40)}, {'UserId': 5, 'Title': 'Monsieur', 'FirstName': 'Aaron', 'LastName': 'Simon', 'Email': 'aaron.simon@example.com', 'Username': 'ticklishrabbit384', 'DOB': datetime.datetime(1946, 4, 20, 23, 46)}, {'UserId': 6, 'Title': 'Mr', 'FirstName': 'James', 'LastName': 'Abraham', 'Email': 'james.abraham@example.com', 'Username': 'whiteostrich169', 'DOB': datetime.datetime(1951, 12, 24, 15, 19)}, {'UserId': 7, 'Title': 'Mr', 'FirstName': 'Fernando', 'LastName': 'Macrae', 'Email': 'fernando.macrae@example.com', 'Username': 'crazysnake348', 'DOB': datetime.datetime(1979, 11, 20, 16, 12)}, {'UserId': 8, 'Title': 'Mr', 'FirstName': 'Maximillian', 'LastName': 'Heggen', 'Email': 'maximillian.heggen@example.com', 'Username': 'sadkoala521', 'DOB': datetime.datetime(1955, 12, 26, 0, 23)}, {'UserId': 9, 'Title': 'Ms', 'FirstName': 'Rocio', 'LastName': 'Leon', 'Email': 'rocio.leon@example.com', 'Username': 'organiczebra872', 'DOB': datetime.datetime(1968, 3, 23, 13, 18)}]\n"
     ]
    }
   ],
   "source": [
    "print(users)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ed9c4375",
   "metadata": {},
   "outputs": [],
   "source": [
    "#print(albums)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "40c39ce5",
   "metadata": {},
   "outputs": [],
   "source": [
    "#print(photos)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "22fdcd65",
   "metadata": {},
   "source": [
    "## Load"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f097d5ab",
   "metadata": {},
   "source": [
    "Finally, the data is in a form that can be loaded into the database. SQLAlchemy makes this step straightforward through its Session API.\n",
    "The code below creates a new session object, adds rows to it, then merges and commits them to the database:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "305d4b46",
   "metadata": {},
   "outputs": [],
   "source": [
    "Session = sessionmaker(bind=engine)\n",
    "\n",
    "session = Session()  \n",
    "\n",
    "for user in users:\n",
    "    row = Users(**user)\n",
    "    session.add(row)\n",
    "    \n",
    "for album in albums:\n",
    "    row = Albums(**album)\n",
    "    session.add(row)\n",
    "    \n",
    "for photo in photos:\n",
    "    row = Photos(**photo)\n",
    "    session.add(row)\n",
    "    \n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83b0dfcd",
   "metadata": {},
   "source": [
    "## Check existing tables in database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "f5dc63c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql\n",
    "%sql sqlite:///gallery.db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "11f9b0fc",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///gallery.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count(*)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(10,)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT count(*) FROM users;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "0a560485",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///gallery.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>UserId</th>\n",
       "        <th>Title</th>\n",
       "        <th>FirstName</th>\n",
       "        <th>LastName</th>\n",
       "        <th>Email</th>\n",
       "        <th>Username</th>\n",
       "        <th>DOB</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "        <td>Mrs</td>\n",
       "        <td>Benta</td>\n",
       "        <td>Barros</td>\n",
       "        <td>benta.barros@example.com</td>\n",
       "        <td>smallmouse460</td>\n",
       "        <td>1951-05-05 04:03:00.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Mr</td>\n",
       "        <td>Miroslaw</td>\n",
       "        <td>Weidmann</td>\n",
       "        <td>miroslaw.weidmann@example.com</td>\n",
       "        <td>blueduck690</td>\n",
       "        <td>1996-05-20 10:38:00.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Mrs</td>\n",
       "        <td>Nicoline</td>\n",
       "        <td>Mortensen</td>\n",
       "        <td>nicoline.mortensen@example.com</td>\n",
       "        <td>bigbird669</td>\n",
       "        <td>1987-01-03 00:19:00.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Mrs</td>\n",
       "        <td>Filippa</td>\n",
       "        <td>Poulsen</td>\n",
       "        <td>filippa.poulsen@example.com</td>\n",
       "        <td>heavywolf486</td>\n",
       "        <td>1970-02-20 08:39:00.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Mrs</td>\n",
       "        <td>Camille</td>\n",
       "        <td>Chow</td>\n",
       "        <td>camille.chow@example.com</td>\n",
       "        <td>beautifulleopard910</td>\n",
       "        <td>1954-08-28 07:40:00.000000</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0, 'Mrs', 'Benta', 'Barros', 'benta.barros@example.com', 'smallmouse460', '1951-05-05 04:03:00.000000'),\n",
       " (1, 'Mr', 'Miroslaw', 'Weidmann', 'miroslaw.weidmann@example.com', 'blueduck690', '1996-05-20 10:38:00.000000'),\n",
       " (2, 'Mrs', 'Nicoline', 'Mortensen', 'nicoline.mortensen@example.com', 'bigbird669', '1987-01-03 00:19:00.000000'),\n",
       " (3, 'Mrs', 'Filippa', 'Poulsen', 'filippa.poulsen@example.com', 'heavywolf486', '1970-02-20 08:39:00.000000'),\n",
       " (4, 'Mrs', 'Camille', 'Chow', 'camille.chow@example.com', 'beautifulleopard910', '1954-08-28 07:40:00.000000')]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM users LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "45f1d0c8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///gallery.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count(*)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>100</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(100,)]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT count(*) FROM albums;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "e5a6c5f1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///gallery.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>AlbumId</th>\n",
       "        <th>UserId</th>\n",
       "        <th>Title</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>quidem molestiae enim</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>1</td>\n",
       "        <td>sunt qui excepturi placeat culpa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>1</td>\n",
       "        <td>omnis laborum odio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>1</td>\n",
       "        <td>non esse culpa molestiae omnis sed optio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>1</td>\n",
       "        <td>eaque aut omnis a</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 1, 'quidem molestiae enim'),\n",
       " (2, 1, 'sunt qui excepturi placeat culpa'),\n",
       " (3, 1, 'omnis laborum odio'),\n",
       " (4, 1, 'non esse culpa molestiae omnis sed optio'),\n",
       " (5, 1, 'eaque aut omnis a')]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM albums LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "d3407674",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///gallery.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count(*)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5000</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(5000,)]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT count(*) FROM photos;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "e99b2b6d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///gallery.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>PhotoId</th>\n",
       "        <th>AlbumId</th>\n",
       "        <th>Title</th>\n",
       "        <th>Url</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>accusamus beatae ad facilis cum similique qui sunt</td>\n",
       "        <td>https://via.placeholder.com/600/92c952</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>1</td>\n",
       "        <td>reprehenderit est deserunt velit ipsam</td>\n",
       "        <td>https://via.placeholder.com/600/771796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>1</td>\n",
       "        <td>officia porro iure quia iusto qui ipsa ut modi</td>\n",
       "        <td>https://via.placeholder.com/600/24f355</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>1</td>\n",
       "        <td>culpa odio esse rerum omnis laboriosam voluptate repudiandae</td>\n",
       "        <td>https://via.placeholder.com/600/d32776</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>1</td>\n",
       "        <td>natus nisi omnis corporis facere molestiae rerum in</td>\n",
       "        <td>https://via.placeholder.com/600/f66b97</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 1, 'accusamus beatae ad facilis cum similique qui sunt', 'https://via.placeholder.com/600/92c952'),\n",
       " (2, 1, 'reprehenderit est deserunt velit ipsam', 'https://via.placeholder.com/600/771796'),\n",
       " (3, 1, 'officia porro iure quia iusto qui ipsa ut modi', 'https://via.placeholder.com/600/24f355'),\n",
       " (4, 1, 'culpa odio esse rerum omnis laboriosam voluptate repudiandae', 'https://via.placeholder.com/600/d32776'),\n",
       " (5, 1, 'natus nisi omnis corporis facere molestiae rerum in', 'https://via.placeholder.com/600/f66b97')]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM photos LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "46e9d8ca",
   "metadata": {},
   "source": [
    "The End."
   ]
  }
 ],
 "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.8.8"
  },
  "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": 5
}