Copy_of_SQLite_remote_db_colab_sqlite3_openpyxl.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "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.6.3"
    },
    "colab": {
      "provenance": [],
      "include_colab_link": true
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/plus2net/Python-basics/blob/main/Copy_of_SQLite_remote_db_colab_sqlite3_openpyxl.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "![alt text](https://www.plus2net.com/images/top2.jpg)        Read more on [SQLite Colab Excel](https://www.plus2net.com/python/sqlite-colab-excel.php)"
      ],
      "metadata": {
        "id": "92fALlH3obiE"
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "source": [
        "# download my_db.db SQLite Database from plus2net.com\n",
        "!wget https://www.plus2net.com/python/download/my_db.db"
      ],
      "outputs": [],
      "metadata": {
        "id": "Ylg7JbjIAZME"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3\n",
        "my_conn = sqlite3.connect('my_db.db') # connect to db"
      ],
      "metadata": {
        "id": "RS82XZ3F-l0b"
      },
      "execution_count": 33,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "query=\"SELECT name FROM sqlite_master WHERE type='table' \"\n",
        "my_data=list(my_conn.execute(query)) # rows of data as list\n",
        "print(my_data) # List of tables"
      ],
      "metadata": {
        "id": "NJC57E6SUVnT"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "query=\"pragma table_info([student])\"\n",
        "my_data=list(my_conn.execute(query)) # rows of data as list\n",
        "l1=[r[1] for r in my_data]\n",
        "print(l1) # List of column header names"
      ],
      "metadata": {
        "id": "-JYWcNzE3RXA"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "query=\"SELECT *  FROM student LIMIT 0,5\" # sql\n",
        "my_data=list(my_conn.execute(query)) # rows of data as list\n",
        "print(*my_data) # display  rows"
      ],
      "metadata": {
        "id": "IUUF1dR6P62Z"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Create Excel file by using SQLite database table"
      ],
      "metadata": {
        "id": "6vC9BnPuMhfr"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "pip install openpyxl # Install the library"
      ],
      "metadata": {
        "id": "CL202Oxo09mD",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "db63c487-3b22-44a2-85b1-ab7b23d2cb5e"
      },
      "execution_count": 1,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Requirement already satisfied: openpyxl in /usr/local/lib/python3.10/dist-packages (3.1.2)\n",
            "Requirement already satisfied: et-xmlfile in /usr/local/lib/python3.10/dist-packages (from openpyxl) (1.1.0)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from openpyxl import Workbook\n",
        "from openpyxl.styles import PatternFill,Font\n",
        "\n",
        "query=\"pragma table_info([student])\" # structure of the table\n",
        "my_data=list(my_conn.execute(query)) # rows of data as list\n",
        "l1=[r[1] for r in my_data] # List of column header\n",
        "\n",
        "query=\"SELECT * FROM student LIMIT 0,10\" # query\n",
        "my_data=my_conn.execute(query)\n",
        "my_data=[r for r in my_data] # List of rows of data\n",
        "\n",
        "wb=Workbook()\n",
        "ws1=wb.active # work with default worksheet\n",
        "ws1.append(l1) # adding column headers at first row\n",
        "\n",
        "my_font=Font(size=14,bold=True) # font styles\n",
        "my_fill=PatternFill(fill_type='solid',start_color='FFFF00') #Background color\n",
        "for cell in ws1[\"1:1\"]: # First row\n",
        "    cell.font = my_font\n",
        "    cell.fill= my_fill\n",
        "\n",
        "r,c=2,0 # row=2 and column=0\n",
        "for row_data in my_data:\n",
        "    d=[r for r in row_data]\n",
        "    ws1.append(d)\n",
        "my_path='my_student.xlsx'#Path\n",
        "wb.save(my_path)"
      ],
      "metadata": {
        "id": "9KI2PqEESX_O"
      },
      "execution_count": 35,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# **Read from Excel file and write to SQLite database table. **"
      ],
      "metadata": {
        "id": "CQS8Km9FC89H"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "query='DROP TABLE student'\n",
        "try:\n",
        "  r_set=my_conn.execute(query)\n",
        "  print(\"No of Records deleted : \",r_set.rowcount)\n",
        "  my_conn.commit()\n",
        "except sqlite3.Error as my_error:\n",
        "  print(\"error: \",my_error)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "xZEazwjbEbN4",
        "outputId": "e6418140-9eb8-43ed-ce71-b45451d5d521"
      },
      "execution_count": 44,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "No of Records deleted :  -1\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# We have to create the table by directly writing the query\n",
        "my_conn.execute('''\n",
        "CREATE TABLE IF NOT EXISTS student(id integer primary key,\n",
        "                      name text,\n",
        "                      class text,\n",
        "                      mark integer,\n",
        "                      gender text\n",
        "                      );''')\n",
        "my_conn.commit()\n",
        "print(\"Student Table created successfully\");"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "dCDdxhe4DMnl",
        "outputId": "e26dc4ea-76e5-4c1e-953b-93aaad5e6859"
      },
      "execution_count": 45,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Student Table created successfully\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Read the Excel page and create a list with rows of data to generate the query for inserting to SQLite table."
      ],
      "metadata": {
        "id": "x7Ol7DWzM8Oz"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from openpyxl import load_workbook\n",
        "wb = load_workbook(filename='my_student.xlsx', read_only=True) # change path\n",
        "ws = wb['Sheet'] # connecting to sheet\n",
        "my_data=[]\n",
        "# remove the column headers and start from 2nd row with data\n",
        "for data in ws.iter_rows(min_row=2,max_col=5,max_row=5,values_only=True):\n",
        "    print(data[0],data[1]) # Show column 1,2 - id, name\n",
        "    my_data.append(data)\n",
        "wb.close()# Close the workbook after reading"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "fyF4eEOQJ5Su",
        "outputId": "a4240147-55ec-43c3-bebf-0063f6d0e868"
      },
      "execution_count": 41,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "1 John Deo\n",
            "2 Max Ruin\n",
            "3 Arnold\n",
            "4 Krish Star\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "query2='INSERT INTO `student` (`id`, `name`, `class`, `mark`, `gender`) VALUES '\n",
        "my_str=\",\".join(map(str,my_data))\n",
        "query2=query2+my_str+';'\n",
        "print(query2) # for checking\n",
        "r_set=my_conn.execute(query2) # insert data to table\n",
        "my_conn.commit()"
      ],
      "metadata": {
        "id": "JKBHpSlsHMNZ"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "X4vgo00BGeZc"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "[More on SQLite database using Colab platform](https://www.plus2net.com/python/sqlite-colab.php)\n",
        "\n",
        "\n",
        "Web site: www.plus2net.com\n",
        "\n",
        "YouTube: https://www.youtube.com/@plus2net1\n",
        "\n",
        "Facebook: https://www.facebook.com/plus2net\n",
        "\n",
        "Instagram: https://www.instagram.com/plus2net.in/\n",
        "\n",
        "LinkedIn: https://www.linkedin.com/in/plus2net/\n",
        "\n",
        "Write to us  https: //www.plus2net.com/contactus.php"
      ],
      "metadata": {
        "id": "x32Q9CyYo69X"
      }
    }
  ]
}