CSV, JSON SQL Data Migration To MongoDB Using Python.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# Created By: Philip Simeon, 12 September 2023\n",
    "# LinkedIn: https://www.linkedin.com/in/philip-simeon-4026a728b/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import the required libraries\n",
    "import pandas as pd\n",
    "import json\n",
    "from pymongo import MongoClient\n",
    "import sqlalchemy as sq"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# loading CSV data into MongoDB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Cell 3: Define a function to load CSV data into MongoDB\n",
    "def mongo_panda(csv_path, username='root', password='123', database='supermarket', port=3303):\n",
    "    # Create a MongoDB connection\n",
    "    mongo_url = f'mongodb://{username}:{password}@localhost:{port}/'\n",
    "    mongo_engine = MongoClient(mongo_url)\n",
    "    myDatabase = mongo_engine[database]\n",
    "    myCollection = myDatabase['finance']\n",
    "     \n",
    "     # Load CSV data into a DataFrame\n",
    "    df = pd.read_csv(csv_path)\n",
    "     \n",
    "     # Convert DataFrame to JSON and insert into MongoDB\n",
    "    load = json.loads(df.to_json(orient='records'))\n",
    "    myCollection.insert_many(load)\n",
    "    row_count = len(load)\n",
    "    print(row_count, \"document(s) loaded successfully\")\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# loading JSON data into MongoDB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Cell 6: Define a function to load JSON data into MongoDB\n",
    "def json_mongo(json_path, username='root', password='123', database='supermarket', port=3303):\n",
    "    # Create a MongoDB connection\n",
    "    mongo_url = f'mongodb://{username}:{password}@localhost:{port}/'\n",
    "    mongo_engine = MongoClient(mongo_url)\n",
    "    myDatabase = mongo_engine[database]\n",
    "    myCollection = myDatabase['finance']\n",
    "    # Load JSON data from a file\n",
    "    with open(json_path, 'r') as readme:\n",
    "        load = json.load(readme)\n",
    "\n",
    "          # Insert JSON data into MongoDB\n",
    "    myCollection.insert_one(load)\n",
    "    row_count = 1\n",
    "    print(row_count, \"document loaded successfully\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# Cell 13: Call the json_mongo function with a JSON file\n",
    "# Replace 'json_path' with your actual JSON file path\n",
    "json_path = 'your_json_file.json'\n",
    "json_mongo(json_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# load data from SQL to MongoDB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def sql_mongo(username='root', password='123', database='supermarket', port=3303):\n",
    "    # Create a MongoDB connection\n",
    "    mongo_url = f'mongodb://{username}:{password}@localhost:{port}/'\n",
    "    mongo_engine = MongoClient(mongo_url)\n",
    "    myDatabase = mongo_engine[database]\n",
    "    myCollection = myDatabase['finance']\n",
    "\n",
    "     # Connect to the SQL database\n",
    "    sql_engine = sq.create_engine(f'mysql+pymysql://{username}:{password}@localhost:{port}/{database}')\n",
    "\n",
    "     # Read data from SQL and insert into MongoDB\n",
    "    df = pd.read_sql_table('company_finace', sql_engine)\n",
    "    load = json.loads(df.to_json(orient='records'))\n",
    "    myCollection.insert_many(load)\n",
    "    row_count = len(load)\n",
    "    print(row_count, \"document(s) loaded successfully\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Cell 16: Call the sql_mongo function to migrate data from SQL to MongoDB\n",
    "sql_mongo()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# Cell 14: Define MongoDB update operations (not implemented in the code)\n",
    "# myCollection.update_one()\n",
    "# myCollection.update_many()"
   ]
  }
 ],
 "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.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}