Data_Engineer_Project.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "7cc2b0e7-cfa5-4066-b583-2e2974cfd634",
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "3d3076de-1de3-4667-9b24-18644aeb7af1",
   "metadata": {},
   "outputs": [],
   "source": [
    "#pip install pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "1ed4dab2-3e7f-4884-924d-6da5dc8840c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "48ecb9c7-b3b9-45b6-ac23-e9c519c589ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "def create_database():\n",
    "    # create connection to the database\n",
    "    conn = psycopg2.connect(\"host=localhost dbname=details user=postgres password=emilia\")\n",
    "    conn.set_session(autocommit=True)\n",
    "    cur = conn.cursor()\n",
    "\n",
    "    # create sparkify database with UTF encoding\n",
    "    #cur.execute(\"DROP DATABASE IF EXISTS new_db\")\n",
    "    #cur.execute(\"CREATE DATABASE new_db\")\n",
    "\n",
    "    # close the connection to the default database\n",
    "    conn.close()\n",
    "\n",
    "    # create a new connection to the new people database\n",
    "    conn = psycopg2.connect(\"host=localhost dbname=new_db user=postgres password=emilia\")\n",
    "    cur = conn.cursor()\n",
    "\n",
    "    return cur, conn\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "66202db7-ea2b-4648-a02d-9018aded4b90",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = psycopg2.connect(\"host=localhost dbname=new_db user=postgres password=emilia\")\n",
    "conn.set_session(autocommit=True)\n",
    "cur = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3b2dccfe-7440-408d-a698-1bcb7dc15935",
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_table():\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "0071e94a-e903-4df7-9856-91a79db5f936",
   "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>id</th>\n",
       "      <th>country</th>\n",
       "      <th>postal_code</th>\n",
       "      <th>credit_card_type</th>\n",
       "      <th>credit_card_number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Indonesia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>jcb</td>\n",
       "      <td>3.580000e+15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Argentina</td>\n",
       "      <td>8503</td>\n",
       "      <td>visa</td>\n",
       "      <td>4.620000e+15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Democratic Republic of the Congo</td>\n",
       "      <td>NaN</td>\n",
       "      <td>jcb</td>\n",
       "      <td>3.560000e+15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>China</td>\n",
       "      <td>NaN</td>\n",
       "      <td>diners-club-us-ca</td>\n",
       "      <td>5.400000e+15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Libya</td>\n",
       "      <td>NaN</td>\n",
       "      <td>jcb</td>\n",
       "      <td>3.530000e+15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id                           country postal_code   credit_card_type  \\\n",
       "0   1                         Indonesia         NaN                jcb   \n",
       "1   2                         Argentina        8503               visa   \n",
       "2   3  Democratic Republic of the Congo         NaN                jcb   \n",
       "3   4                             China         NaN  diners-club-us-ca   \n",
       "4   5                             Libya         NaN                jcb   \n",
       "\n",
       "   credit_card_number  \n",
       "0        3.580000e+15  \n",
       "1        4.620000e+15  \n",
       "2        3.560000e+15  \n",
       "3        5.400000e+15  \n",
       "4        3.530000e+15  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "account_details=pd.read_csv(\"Data/Account_Details_Data.csv\")\n",
    "account_details.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "171794f8-bc4d-4358-b800-5654fd46e397",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(account_details)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "c2f3874e-133d-409c-a52e-fa6f11734b52",
   "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>id</th>\n",
       "      <th>Country</th>\n",
       "      <th>Postal Code</th>\n",
       "      <th>Credit Card Type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mazda</td>\n",
       "      <td>MPV</td>\n",
       "      <td>1998</td>\n",
       "      <td>JHMGE8G24AS283531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Kia</td>\n",
       "      <td>Sedona</td>\n",
       "      <td>2002</td>\n",
       "      <td>JTDKDTB36F1481381</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Porsche</td>\n",
       "      <td>Panamera</td>\n",
       "      <td>2012</td>\n",
       "      <td>5J8TB18279A927590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mazda</td>\n",
       "      <td>MPV</td>\n",
       "      <td>1992</td>\n",
       "      <td>1G6YV34A055158621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Subaru</td>\n",
       "      <td>Loyale</td>\n",
       "      <td>1992</td>\n",
       "      <td>5LMJJ3J54DE815655</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        id   Country  Postal Code   Credit Card Type\n",
       "0    Mazda       MPV         1998  JHMGE8G24AS283531\n",
       "1      Kia    Sedona         2002  JTDKDTB36F1481381\n",
       "2  Porsche  Panamera         2012  5J8TB18279A927590\n",
       "3    Mazda       MPV         1992  1G6YV34A055158621\n",
       "4   Subaru    Loyale         1992  5LMJJ3J54DE815655"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "location_details=pd.read_csv(\"Data/Location_Details_Data.csv\")\n",
    "location_details.head()\n",
    "#type(location_details)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "bdbe98a3-1034-4a50-a905-f54b68c68696",
   "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>id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>email</th>\n",
       "      <th>gender</th>\n",
       "      <th>ip_address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Donielle</td>\n",
       "      <td>Dunley</td>\n",
       "      <td>ddunley0@ovh.net</td>\n",
       "      <td>Female</td>\n",
       "      <td>19.229.197.180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Auberon</td>\n",
       "      <td>Rubinfajn</td>\n",
       "      <td>arubinfajn1@mayoclinic.com</td>\n",
       "      <td>Male</td>\n",
       "      <td>85.188.248.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Sutherland</td>\n",
       "      <td>Ilyenko</td>\n",
       "      <td>silyenko2@nyu.edu</td>\n",
       "      <td>Male</td>\n",
       "      <td>117.191.198.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Dollie</td>\n",
       "      <td>Tomasek</td>\n",
       "      <td>dtomasek3@nytimes.com</td>\n",
       "      <td>Female</td>\n",
       "      <td>107.31.142.234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Rosana</td>\n",
       "      <td>Andrea</td>\n",
       "      <td>randrea4@paypal.com</td>\n",
       "      <td>Female</td>\n",
       "      <td>38.187.232.11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  first_name  last_name                       email  gender  \\\n",
       "0   1    Donielle     Dunley            ddunley0@ovh.net  Female   \n",
       "1   2     Auberon  Rubinfajn  arubinfajn1@mayoclinic.com    Male   \n",
       "2   3  Sutherland    Ilyenko           silyenko2@nyu.edu    Male   \n",
       "3   4      Dollie    Tomasek       dtomasek3@nytimes.com  Female   \n",
       "4   5      Rosana     Andrea         randrea4@paypal.com  Female   \n",
       "\n",
       "       ip_address  \n",
       "0  19.229.197.180  \n",
       "1   85.188.248.20  \n",
       "2  117.191.198.79  \n",
       "3  107.31.142.234  \n",
       "4   38.187.232.11  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Personal_Data=pd.read_csv(\"Data/Personal_Data_Table.csv\")\n",
    "Personal_Data.head()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "1d74a3c7-3ffd-4a49-8f24-0d80f770199a",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "cur, conn = create_database()\n",
    "#conn.rollback()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "6677206f-5ca4-4a55-9a2c-28d0bab2985e",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "account_details = \"\"\"CREATE TABLE IF NOT EXISTS account_detail (\n",
    "    id VARCHAR ,\n",
    "    Country VARCHAR,\n",
    "    postal_code VARCHAR,\n",
    "    credit_card_type VARCHAR,\n",
    "    credit_card_number VARCHAR\n",
    ")\"\"\"\n",
    "\n",
    "#conn.rollback()\n",
    "#cur.execute(account_details)\n",
    "#conn.commit()\n",
    "\n",
    "\n",
    "#Personal_Data_table= (\"\"\"CREATE TABLE Personal_Data(id numeric,first_name VARCHAR, Last_name varcha, email varchar, gender VARCHAR, IP_address numeric)\"\"\") "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "c99f37a6-8b29-4e9f-8428-f21b40f20308",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "try:\n",
    "    # Execute the CREATE TABLE statement\n",
    "    cur.execute(account_details)\n",
    "\n",
    "    # Commit the transaction\n",
    "    conn.commit()\n",
    "\n",
    "except psycopg2.Error as e:\n",
    "    # Handle the error\n",
    "    print(\"Error:\", e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "66a54927-a920-43a6-a0d2-bdebfe5802e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "#conn.rollback()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "8721d146-793d-4636-baae-f191d2216006",
   "metadata": {},
   "outputs": [],
   "source": [
    "location_details = \"\"\"CREATE TABLE IF NOT EXISTS location_detail (\n",
    "    id VARCHAR PRIMARY KEY,\n",
    "    Country VARCHAR,\n",
    "    postal_code VARCHAR,\n",
    "    credit_card_type VARCHAR\n",
    ")\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "a767f29e-c499-4f72-b1f7-cee46890a1d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    # Execute the CREATE TABLE statement\n",
    "    cur.execute(location_details)\n",
    "\n",
    "    # Commit the transaction\n",
    "    conn.commit()\n",
    "\n",
    "except psycopg2.Error as e:\n",
    "    # Handle the error\n",
    "    print(\"Error:\", e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "e433321b-ee10-4df5-bfde-d60714d9044d",
   "metadata": {},
   "outputs": [],
   "source": [
    "personal_details = \"\"\"CREATE TABLE IF NOT EXISTS personal_details (\n",
    "    id VARCHAR PRIMARY KEY,\n",
    "    first_name VARCHAR,\n",
    "    last_name VARCHAR,\n",
    "    email VARCHAR,\n",
    "    gender VARCHAR,\n",
    "    ip_address VARCHAR\n",
    ")\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "dc55ca3c-c0c2-492d-8bb2-4fd09c5481e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    # Execute the CREATE TABLE statement\n",
    "    cur.execute(personal_details)\n",
    "\n",
    "    # Commit the transaction\n",
    "    conn.commit()\n",
    "\n",
    "except psycopg2.Error as e:\n",
    "    # Handle the error\n",
    "    print(\"Error:\", e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "f1d8d3e5-7efa-4bb8-9648-0c5d63c3b02d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                    1000\n",
       "country               1000\n",
       "postal_code            496\n",
       "credit_card_type      1000\n",
       "credit_card_number    1000\n",
       "dtype: int64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(account_details)\n",
    "account_details.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "59b50ede-02ba-4838-8352-f3ce2c2e99dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "d78517d8-aa3a-4aaf-b0d4-4d7820385783",
   "metadata": {},
   "outputs": [],
   "source": [
    "account_details_insert = (\"\"\"INSERT INTO account_detail (\n",
    "    id,\n",
    "    Country,\n",
    "    postal_code,\n",
    "    credit_card_type,\n",
    "    credit_card_number)\n",
    "    VALUES(%s,%s,%s,%s,%s)\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "eb4a8b30-d3ed-487a-b947-d443ea559a6b",
   "metadata": {},
   "outputs": [],
   "source": [
    "for i, row in account_details.iterrows():\n",
    "    cur.execute(account_details_insert, list(row))\n",
    "conn.commit()\n",
    "    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6cf5bf12-b465-44d6-9261-8d398ea0ce29",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "778ce5f8-0f82-4a0a-b38d-c9031789b5ed",
   "metadata": {},
   "outputs": [],
   "source": [
    "location_details_insert = (\"\"\"\n",
    "    INSERT INTO  location_detail (\n",
    "    id,\n",
    "        Country,\n",
    "        postal_code,\n",
    "        credit_card_type)\n",
    "        VALUES (%s, %s, %s, %s)\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "d1ef250b-8f0c-473d-b2d6-28ae1ae2ca57",
   "metadata": {},
   "outputs": [],
   "source": [
    "for i, row in location_details.iterrows():\n",
    "    cur.execute(location_details_insert, list(row))\n",
    "connection.commit()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "ca431f85-12d7-4a59-841f-ebd684bf1760",
   "metadata": {},
   "outputs": [],
   "source": [
    "personal_details_insert = (\"\"\"\n",
    "    INSERT INTO personal_details   (\n",
    "     id,\n",
    "    first_name,\n",
    "    last_name,\n",
    "    email,\n",
    "    gender,\n",
    "    ip_address    \n",
    "    )\n",
    "        VALUES (%s, %s, %s, %s,%s,%s)\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "ab3cdc26-f58f-42a4-ae8d-18ed6321e980",
   "metadata": {},
   "outputs": [],
   "source": [
    "for i, row in Personal_Data.iterrows():\n",
    "    cur.execute(personal_details_insert, list(row))\n",
    "conn.commit()"
   ]
  }
 ],
 "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.12.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}