Python_MySQL_P4.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "authorship_tag": "ABX9TyPnDxcB6YsEhL/xN+ATAsX4",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/Animeshcoder/MySQL-Python/blob/main/Python_MySQL_P4.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### **Introduction:**\n",
        "This project is a Python script that demonstrates how to extract data from a MySQL database, manipulate it using JSON parsing, and update the database with the manipulated data. The script uses PyMySQL to connect to the database, execute SQL queries, and commit changes. In this project inside a column named as Value which contain json data so in place of that data i am updating that with the media key value of json data. For example if json data is like {\"name\":\"John\",\"media\":\"abc.com\"} so this code will update whole json data to media key value which is here \"abc.com\"."
      ],
      "metadata": {
        "id": "L_PmSgzKlTzk"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### **Involved Steps:**\n",
        "\n",
        "**Step 1: Connect to the database**\n",
        "\n",
        "The first part of the script sets up the connection details for the MySQL database and creates a connection object using PyMySQL’s connect function. The host, user, password, and db arguments are passed to this function to specify the connection details.\n",
        "\n",
        "**Step 2: Query data from the table**\n",
        "\n",
        "Next, a cursor object is created using the cursor method of the connection object. This cursor is used to execute an SQL query that selects the id and Value columns from the oldtable table in the database. The execute method of the cursor object is called with this query as its argument, and the results are fetched using the fetchall method.\n",
        "\n",
        "**Step 3: Extract and manipulate data**\n",
        "\n",
        "The script then loops through each row in the results and extracts the values of the id and Value columns. If the value in the Value column is not NULL, it is parsed as JSON data using Python’s built-in json module. The script then loops through each dictionary in this JSON data and checks if it contains a key named 'media'. If this key is found, its value is extracted and used to update the value in the Value column of the current row in the database.\n",
        "\n",
        "**Step 4: Update data in the database**\n",
        "\n",
        "To update the value in the Value column, another SQL query is defined that uses an UPDATE statement to set the value of this column to the extracted media value for rows where the value of the id column matches the current row’s id. This query is executed using the cursor’s execute method, which takes two arguments: the query string and a tuple of values to substitute into placeholders in the query.\n",
        "\n",
        "**Step 5: Commit changes and close connection**\n",
        "\n",
        "After all rows have been processed, any changes made to the database are committed by calling the connection object’s commit method. Finally, both cursor and connection objects are closed by calling their respective close methods."
      ],
      "metadata": {
        "id": "mOavJAQKobTL"
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "itH7uulflS8_"
      },
      "outputs": [],
      "source": [
        "import pymysql\n",
        "import json\n",
        "\n",
        "# Connect to the database\n",
        "connection = pymysql.connect(host='yourhost',\n",
        "                             user='youruser',\n",
        "                             password='yourpassword@123',\n",
        "                             db='yourdatabasename')\n",
        "\n",
        "try:\n",
        "    with connection.cursor() as cursor:\n",
        "        # Query the data from the table\n",
        "        sql = \"SELECT id, Value FROM oldtable\"\n",
        "        cursor.execute(sql)\n",
        "        results = cursor.fetchall()\n",
        "\n",
        "        # Loop through the results and extract the value of the media key\n",
        "        for row in results:\n",
        "            id = row[0]\n",
        "            col = row[1]\n",
        "\n",
        "            # Check if the value in the col column is NULL\n",
        "            if col is not None:\n",
        "                try:\n",
        "                    data = json.loads(col)\n",
        "\n",
        "                    # Loop through the list of dictionaries\n",
        "                    for item in data:\n",
        "                        # Check if the media key exists in the dictionary\n",
        "                        if 'media' in item:\n",
        "                            media = item['media']\n",
        "\n",
        "                            # Update the value in the TRF column\n",
        "                            sql = \"UPDATE oldtable SET Value = %s WHERE id = %s\"\n",
        "                            cursor.execute(sql, (media, id))\n",
        "                except json.decoder.JSONDecodeError:\n",
        "                    print(f\"Error: Unable to parse JSON data for row with id {id}\")\n",
        "\n",
        "    # Commit the changes to the database\n",
        "    connection.commit()\n",
        "finally:\n",
        "    connection.close()\n"
      ]
    }
  ]
}