Create_DataBase.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Importing Libraries 📚"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import mysql.connector"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Reading csv file and change it to DataFrame 📑"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "IMDB_df = pd.read_csv(\"IMDB_Movies.csv\")\n",
    "IMDB_character_df = pd.read_csv(\"IMDB_Names.csv\")\n",
    "\n",
    "\n",
    "IMDB_df[\"gross_us_canada\"] = IMDB_df[\"gross_us_canada\"].fillna(0).astype(int)\n",
    "IMDB_df[\"parental_guide\"] = IMDB_df[\"parental_guide\"].fillna(\"Unrated\")\n",
    "IMDB_df.loc[IMDB_df[\"parental_guide\"].isin([\"None\", \"\", \"Not Rated\"]), \"parental_guide\"] = \"Unrated\"\n",
    "IMDB_df = IMDB_df.astype({\"title\": \"string\", \"parental_guide\": \"string\", \"genre\": \"string\", \"director\": \"string\", \"writer\": \"string\", \"star\": \"string\"})\n",
    "IMDB_df = IMDB_df.astype({\"id\": \"int32\", \"year\": \"int32\", \"runtime\": \"int32\"})"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Connecting to MySQL 🛢"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_database = mysql.connector.connect(\n",
    "  host = \"127.0.0.1\",\n",
    "  port = \"3306\",\n",
    "  user = \"root\",\n",
    "  password = Your_Password,\n",
    "  auth_plugin = \"mysql_native_password\",\n",
    "  database = \"my_db\"\n",
    ")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create DataBase 🍕"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_cursor = my_database.cursor()\n",
    "my_cursor.execute(\"CREATE DATABASE my_db\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create movie table 🌭"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_cursor = my_database.cursor()\n",
    "\n",
    "my_cursor.execute(\"CREATE TABLE movie (id INT PRIMARY KEY, title VARCHAR(255), year INT, runtime INT, parental_guide VARCHAR(255), gross_us_canada INT)\")\n",
    "\n",
    "for i in range(len(IMDB_df)):\n",
    "    data = (int(IMDB_df.loc[i, \"id\"]), IMDB_df.loc[i, \"title\"], int(IMDB_df.loc[i, \"year\"]), int(IMDB_df.loc[i, \"runtime\"]), IMDB_df.loc[i, \"parental_guide\"], int(IMDB_df.loc[i, \"gross_us_canada\"]))\n",
    "    query = \"INSERT INTO movie (id, title, year, runtime, parental_guide, gross_us_canada) VALUES (%s, %s, %s, %s, %s, %s)\"\n",
    "    my_cursor.execute(query, data)\n",
    "\n",
    "my_database.commit()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create person table 🦸‍♂️"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_cursor = my_database.cursor()\n",
    "\n",
    "my_cursor.execute(\"CREATE TABLE person (id INT PRIMARY KEY, name VARCHAR(255))\")\n",
    "\n",
    "for i in range(len(IMDB_character_df)):\n",
    "    data = (int(IMDB_character_df.loc[i, \"name_id\"]), str(IMDB_character_df.loc[i, \"name\"]))\n",
    "    query = \"INSERT INTO person (id, name) VALUES (%s, %s)\"\n",
    "    my_cursor.execute(query, data)\n",
    "\n",
    "my_database.commit()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create cast table 🗿"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "def find_id(name):\n",
    "    name_id = IMDB_character_df.loc[IMDB_character_df[\"name\"] == name, \"name_id\"].values[0]\n",
    "    return name_id\n",
    "\n",
    "my_cursor = my_database.cursor()\n",
    "\n",
    "my_cursor.execute(\"CREATE TABLE cast (id INT AUTO_INCREMENT PRIMARY KEY, movie_id INT, person_id INT, FOREIGN KEY (movie_id) REFERENCES movie(id), FOREIGN KEY (person_id) REFERENCES person(id))\")\n",
    "my_cursor.execute(\"ALTER TABLE cast AUTO_INCREMENT = 1\")\n",
    "\n",
    "for i in range(len(IMDB_df)):\n",
    "    name_list = IMDB_df.loc[i, \"star\"].split(\", \")\n",
    "    for j in name_list:\n",
    "        data = (int(IMDB_df.loc[i, \"id\"]), int(find_id(j)))\n",
    "        query = \"INSERT INTO cast (movie_id, person_id) VALUES (%s, %s)\"\n",
    "        my_cursor.execute(query, data)\n",
    "\n",
    "my_database.commit()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create crew table 🤹‍♂️"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [],
   "source": [
    "def find_id(name):\n",
    "    name_id = IMDB_character_df.loc[IMDB_character_df[\"name\"] == name, \"name_id\"].values[0]\n",
    "    return name_id\n",
    "\n",
    "my_cursor = my_database.cursor()\n",
    "\n",
    "my_cursor.execute(\"CREATE TABLE crew (id INT AUTO_INCREMENT PRIMARY KEY, movie_id INT, person_id INT, role VARCHAR(255), FOREIGN KEY (movie_id) REFERENCES movie(id), FOREIGN KEY (person_id) REFERENCES person(id))\")\n",
    "my_cursor.execute(\"ALTER TABLE crew AUTO_INCREMENT = 1\")\n",
    "\n",
    "for i in range(len(IMDB_df)):\n",
    "\n",
    "    name_list = IMDB_df.loc[i, \"director\"].split(\", \")\n",
    "    for j in name_list:\n",
    "        data = (int(IMDB_df.loc[i, \"id\"]), int(find_id(j)), \"Director\")\n",
    "        query = \"INSERT INTO crew (movie_id, person_id, role) VALUES (%s, %s, %s)\"\n",
    "        my_cursor.execute(query, data)\n",
    "\n",
    "    name_list = IMDB_df.loc[i, \"writer\"].split(\", \")\n",
    "    for k in name_list:\n",
    "        data = (int(IMDB_df.loc[i, \"id\"]), int(find_id(k)), \"Writer\")\n",
    "        query = \"INSERT INTO crew (movie_id, person_id, role) VALUES (%s, %s, %s)\"\n",
    "        my_cursor.execute(query, data)\n",
    "\n",
    "my_database.commit()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create genre table 💯"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_cursor = my_database.cursor()\n",
    "\n",
    "my_cursor.execute(\"CREATE TABLE genre (id INT AUTO_INCREMENT PRIMARY KEY, movie_id INT, genre VARCHAR(255), FOREIGN KEY (movie_id) REFERENCES movie(id))\")\n",
    "my_cursor.execute(\"ALTER TABLE genre AUTO_INCREMENT = 1\")\n",
    "\n",
    "for i in range(len(IMDB_df)):\n",
    "    genre_list = IMDB_df.loc[i, \"genre\"].split(\", \")\n",
    "    for j in genre_list:\n",
    "        data = (int(IMDB_df.loc[i, \"id\"]), str(j))\n",
    "        query = \"INSERT INTO genre (movie_id, genre) VALUES (%s, %s)\"\n",
    "        my_cursor.execute(query, data)\n",
    "\n",
    "my_database.commit()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "qenv",
   "language": "python",
   "name": "qenv"
  },
  "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.6"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}