Week9.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "authorship_tag": "ABX9TyNsx8ZxXtzedxWHlqNbtq46",
      "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/AlainWalbar/database/blob/main/Week_9.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Vdn65GtCimCu",
        "outputId": "ce5a071b-beca-45c8-9f7d-9b9686fcd5dd"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "sqlite\n"
          ]
        }
      ],
      "source": [
        "from sqlalchemy import create_engine\n",
        "my_conn = create_engine(\"sqlite:////content/my_db.db/\")\n",
        "print(my_conn.name)\n"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "r_set=my_conn.execute('''select name from sqlite_master where type = 'table' ''')\n",
        "for row in r_set:\n",
        "  print(row)\n",
        "  "
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "s6haxrl3j5Tz",
        "outputId": "a67eede2-7ef9-49a8-9b6e-b67fc4e3fec2"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "('category',)\n",
            "('subcategory',)\n",
            "('student',)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from sqlalchemy.exc import SQLAlchemyError\n",
        "try:\n",
        "  my_conn.execute('''DROP table student;''')\n",
        "  print(\"student table deleted\")\n",
        "except SQLAlchemyError as e:\n",
        "  error = str(e._dict_['orig'])\n",
        "  print(error)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0UjipCx4mWo5",
        "outputId": "19ea109d-378e-41d3-d5ce-4867b2e3356b"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "student table deleted\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "with open(\"student_tables.sql\", \"r\") as file:\n",
        "  query = file.read()\n",
        "  my_conn.execute(query)\n"
      ],
      "metadata": {
        "id": "favnJ2DGlz-o"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "with open(\"student_insert.sql\",\"r\") as file:\n",
        "  query = file.read()\n",
        "  my_conn.execute(query)"
      ],
      "metadata": {
        "id": "twlVylsznMGC"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "r_set = my_conn.execute('''SELECT * FROM student''');\n",
        "for row in r_set:\n",
        "  print(row)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "DXSrlM9wnI_r",
        "outputId": "9b4a3f3a-1495-4bb9-aaf8-d00fe74ff376"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(1, 'John Deo', 'Four', 75, 'female')\n",
            "(2, 'Max Ruin', 'Three', 85, 'male')\n",
            "(3, 'Arnold', 'Three', 55, 'male')\n",
            "(4, 'Krish Star', 'Four', 60, 'female')\n",
            "(5, 'John Mike', 'Four', 60, 'female')\n",
            "(6, 'Alex John', 'Four', 55, 'male')\n",
            "(7, 'My John Rob', 'Five', 78, 'male')\n",
            "(8, 'Asruid', 'Five', 85, 'male')\n",
            "(9, 'Tes Qry', 'Six', 78, 'male')\n",
            "(10, 'Big John', 'Four', 55, 'female')\n",
            "(11, 'Ronald', 'Six', 89, 'female')\n",
            "(27, 'Big Nose', 'Three', 81, 'female')\n",
            "(28, 'Rojj Base', 'Seven', 86, 'female')\n",
            "(29, 'Tess Played', 'Seven', 55, 'male')\n",
            "(30, 'Reppy Red', 'Six', 79, 'female')\n",
            "(31, 'Marry Toeey', 'Four', 88, 'male')\n",
            "(32, 'Binn Rott', 'Seven', 90, 'female')\n",
            "(33, 'Kenn Rein', 'Six', 96, 'female')\n",
            "(34, 'Gain Toe', 'Seven', 69, 'male')\n",
            "(35, 'Rows Noump', 'Six', 88, 'female')\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from sqlalchemy.exc import SQLAlchemyError\n",
        "q=\"UPDATE student SET mark=mark+1\"\n",
        "try:\n",
        "  r_set=my_conn.execute(q)"
      ],
      "metadata": {
        "id": "7gkrp9wtoNPW"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}