kl_python_sqlite_minta_01.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "kl_python_sqlite_minta_01.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "toc_visible": true,
      "include_colab_link": true
    },
    "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.7.4"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/klajosw/python/blob/master/kl_python_sqlite_minta_01.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "NbC6f1eCntYf",
        "colab_type": "text"
      },
      "source": [
        "<p align=\"left\"> \n",
        "    <img src=\"https://raw.githubusercontent.com/klajosw/python/master/kl_mie_python_logo_250.jpg\" \n",
        "         align=\"left\" width=\"251\" height=\"251\">\n",
        "    \n",
        "</p>\n",
        "\n",
        "\n",
        "<p> </p>\n",
        "\n",
        "\n",
        "\n",
        "# Python alapok SQL db használat (Sqlite)\n",
        "\n",
        "<https://klajosw.blogspot.com/>\n",
        "\n",
        "\n",
        "---"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "colab_type": "text",
        "id": "5VWEm6ccHCbi"
      },
      "source": [
        "# SqLite használata\n",
        "\n",
        "## Kapcsolódás az adatbázishoz"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "y_b8GDwsHATh",
        "colab": {}
      },
      "source": [
        "## Modulok impontálása\n",
        "import sqlite3\n",
        "import os\n",
        "import hashlib"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "colab_type": "text",
        "id": "VuUTw3u87coh"
      },
      "source": [
        "## Belső függvények létrehozása"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "Tj2OKv6iHakf",
        "colab": {}
      },
      "source": [
        "## Belső függveny eljárások definiálása\n",
        "def collate_reverse(string1, string2):\n",
        "    if string1 == string2:\n",
        "        return 0\n",
        "    elif string1 < string2:\n",
        "        return 1\n",
        "    else:\n",
        "        return -1\n",
        "\n",
        "## hashlib.md5 / hashlib.sha224 /// Titkosítás GDPR!!!\n",
        "def md5sum(t):\n",
        "    if type(t) == int:\n",
        "        t = str(t)\n",
        "    t = t.encode()    \n",
        "    return hashlib.md5(t).hexdigest()  \n",
        "    \n",
        "def sha224sum(t):\n",
        "    if type(t) == int:\n",
        "        t = str(t)\n",
        "    t = t.encode()    \n",
        "    return hashlib.sha224(t).hexdigest()   "
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "colab_type": "text",
        "id": "BjaHgnYDIHTV"
      },
      "source": [
        "## Memoria adatbázis, tábla használati módszer"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "pdrMq3gVH27-",
        "outputId": "b1b84164-d0b5-42a1-9663-1b269e8d0cd4",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 139
        }
      },
      "source": [
        "## Memoria adatbázis / tábla használata\n",
        "sqlite3.enable_shared_cache(True) ### \n",
        "\n",
        "con = sqlite3.connect(\":memory:\")\n",
        "con.isolation_level = None\n",
        "\n",
        "con.create_collation(\"reverse\", collate_reverse)  ## fuggvény használat\n",
        "\n",
        "con.create_function(\"md5\", 1, md5sum)\n",
        "\n",
        "cur = con.cursor()\n",
        "\n",
        "cur.execute(\"select md5(?)\", (\"foo\",))\n",
        "print(cur.fetchone()[0])\n",
        "\n",
        "cur.execute(\"create table test(x)\")\n",
        "cur.executemany(\"insert into test(x) values (?)\", [(\"a\",), (\"b\",), (\"d\",) , (\"c\",)])\n",
        "## cur.execute(\"select x from test order by x collate reverse\")\n",
        "\n",
        "cur.execute(\"select x from test order by x asc\")\n",
        "\n",
        "print('---- Kiíratás 0.  ----\\n')\n",
        "for row in cur:\n",
        "    print(row)\n",
        "con.close()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "acbd18db4cc2f85cedef654fccc4a4d8\n",
            "---- Kiíratás 0.  ----\n",
            "\n",
            "('a',)\n",
            "('b',)\n",
            "('c',)\n",
            "('d',)\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "colab_type": "text",
        "id": "dQQFg6bPIYjn"
      },
      "source": [
        "## SQLite állomány adatbázis használata"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "Z8TPADekIcc9",
        "outputId": "a4c4c05f-6f66-4f96-e49b-3989172de0a4",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 627
        }
      },
      "source": [
        "## Állomány adatbázis\n",
        "DB_FILE = \"/content/sample_data/mydb.db\"  # adatbázis neve\n",
        "newdb=0    ## Új adatbázis akkor 1 legyen késöbb\n",
        "\n",
        "if os.path.exists(DB_FILE ):  ## adatbázis állomány már létezik akkor törlésse\n",
        "#   os.remove(DB_FILE)        ## törlésd végrehajtása\n",
        "#   newdb = 1\n",
        "   print('Törölve')\n",
        "\n",
        "\n",
        "con = sqlite3.connect(DB_FILE)  ## ha nem létezik létrehozza (de ürsen)\n",
        "\n",
        "\n",
        "def md5sum(t):  \n",
        "    if type(t) == int:\n",
        "        t = str(t)\n",
        "    t = t.encode()    \n",
        "    return hashlib.md5(t).hexdigest()  \n",
        "\n",
        "con.create_function(\"md5\", 1, md5sum)  ## beső fögvény használata/meghívása\n",
        "\n",
        "cur = con.cursor()\n",
        "\n",
        "if newdb == 1 :  ## Ha új adatbázis akkor a táblát létrekell hozni\n",
        "    cur.execute(\"\"\" create table people (\n",
        "      name_last  varchar(20), \n",
        "      age        integer)  \"\"\")\n",
        " \n",
        "## Rekordok beszúrása / INSERT közvetlen megadással\n",
        "    cur.execute(\"insert into people (name_last, age) values ('Lajos',   57)\")    \n",
        "    cur.execute(\"insert into people (name_last, age) values ('Béla',     53)\")   \n",
        "    cur.execute(\"insert into people (name_last, age) values ('Ildikó',     54)\")  \n",
        "\n",
        "\n",
        "\n",
        "\n",
        "    newPeople = (\n",
        "    ('Dani' , 22),\n",
        "    ('Tomi' , 21)\n",
        "    )\n",
        "\n",
        "    for person in newPeople:\n",
        "        cur.execute(\"insert into people (name_last, age) values (?, ?)\", person)\n",
        "\n",
        "con.commit()  ## Beírás véglegesítése\n",
        "\n",
        "##-----------------\n",
        "\n",
        "print('---- Kiíratás 11  ----\\n')\n",
        "cur.execute(\"select * from people \")\n",
        "for row in cur:\n",
        "    for mezo in row:\n",
        "        print(mezo)\n",
        "        \n",
        "print('---- Kiíratás 2.  ----\\n')        \n",
        "cur.execute(\"select * from people \")\n",
        "for row in cur:        \n",
        "        print('%s is %d years old.' % (row[0], row[1]))\n",
        "##\n",
        "print(\"Most törlök \", cur.execute(\"delete from people where name_last = 'Tomi' \").rowcount, \"rekordot/sort.\") \n",
        "\n",
        "print('---- Kiíratás 3.  ----\\n')\n",
        "cur.execute(\" select name_last, age,  md5(name_last) as name_md5 from people \")\n",
        "\n",
        "print(\"with column names:\", cur.fetchone()[0])\n",
        "print(\"with column names:\", cur.fetchone())\n",
        "\n",
        "print(cur.fetchall())  ## Összes további kiíratása\n",
        "\n",
        "\n",
        "print('---- Kiíratás 4.  ----\\n')\n",
        "FIELD_MAX_WIDTH = 20  ## mezők maximális hossz beállítása\n",
        "cur.execute(\" select name_last, age,  md5(name_last) as name_md5 from people \")\n",
        "\n",
        "# Fejléc\n",
        "for fieldDesc in cur.description:\n",
        "    print(fieldDesc[0].ljust(FIELD_MAX_WIDTH), end=' ')\n",
        "print()          # Fejléc vége  \n",
        "print('-' * 78)  # elválasztó sor\n",
        "\n",
        "# For each row, print the value of each field left-justified within\n",
        "# the maximum possible width of that field.\n",
        "fieldIndices = range(len(cur.description))\n",
        "for row in cur:\n",
        "    for fieldIndex in fieldIndices:\n",
        "        fieldValue = str(row[fieldIndex])\n",
        "        print(fieldValue.ljust(FIELD_MAX_WIDTH), end=' ')\n",
        "\n",
        "    print() # kiíratás végén sorvégjel kitétele\n",
        "\n",
        "\n",
        "cur.close()\n",
        "con.close()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Törölve\n",
            "---- Kiíratás 11  ----\n",
            "\n",
            "Lajos\n",
            "57\n",
            "Béla\n",
            "53\n",
            "Ildikó\n",
            "54\n",
            "Dani\n",
            "22\n",
            "Tomi\n",
            "21\n",
            "---- Kiíratás 2.  ----\n",
            "\n",
            "Lajos is 57 years old.\n",
            "Béla is 53 years old.\n",
            "Ildikó is 54 years old.\n",
            "Dani is 22 years old.\n",
            "Tomi is 21 years old.\n",
            "Most törlök  1 rekordot/sort.\n",
            "---- Kiíratás 3.  ----\n",
            "\n",
            "with column names: Lajos\n",
            "with column names: ('Béla', 53, 'f796ac4ec7dd2fc3a28fa2377b2cee9f')\n",
            "[('Ildikó', 54, '77a17c0633ae98c1f082cf2701739473'), ('Dani', 22, '0b2cc82e6a177b18faefd88581b8597d')]\n",
            "---- Kiíratás 4.  ----\n",
            "\n",
            "name_last            age                  name_md5             \n",
            "------------------------------------------------------------------------------\n",
            "Lajos                57                   b48b8adcf3de3785f09f9900af1a3aa6 \n",
            "Béla                 53                   f796ac4ec7dd2fc3a28fa2377b2cee9f \n",
            "Ildikó               54                   77a17c0633ae98c1f082cf2701739473 \n",
            "Dani                 22                   0b2cc82e6a177b18faefd88581b8597d \n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "colab_type": "text",
        "id": "TKIx7dJm-p88"
      },
      "source": [
        "## Táblák lekérdezése"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "oWU3xtqBcABb",
        "outputId": "c413ef47-7e8d-47df-bbba-52f3a909f508",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1000
        }
      },
      "source": [
        "DB_FILE = \"/content/sample_data/flights_information.db\"  # adatbázis neve\n",
        "#DB_FILE = \"/content/sample_data/mydb.db\"  # adatbázis neve\n",
        "con = sqlite3.connect(DB_FILE)  ## ha nem létezik létrehozza\n",
        "cur = con.cursor()\n",
        "# cur.execute(\"select * from airports where id <= 10; \")\n",
        "# print(cur.fetchall())  ## Összes további kiíratása\n",
        "\n",
        "print('---- Kiíratás 11  ----\\n')\n",
        "cur.execute(\"SELECT * FROM sqlite_master WHERE type='table'\")\n",
        "print('\\n---- Tábla infó kiíratás 1.  ----\\n')\n",
        "for row in cur:\n",
        "    for mezo in row:\n",
        "        print(mezo)\n",
        "\n",
        "print('\\n---- Verzió kiíratás 2.  ----\\n')\n",
        "cur.execute('select sqlite_version();')\n",
        "for row in cur:\n",
        "    for mezo in row:\n",
        "        print(mezo)\n",
        "\n",
        "cur.close()\n",
        "con.close()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "---- Kiíratás 11  ----\n",
            "\n",
            "\n",
            "---- Tábla infó kiíratás 1.  ----\n",
            "\n",
            "table\n",
            "airports\n",
            "airports\n",
            "2\n",
            "CREATE TABLE airports (\n",
            "[index] INTEGER,\n",
            "  [id] TEXT,\n",
            "  [name] TEXT,\n",
            "  [city] TEXT,\n",
            "  [country] TEXT,\n",
            "  [code] TEXT,\n",
            "  [icao] TEXT,\n",
            "  [latitude] TEXT,\n",
            "  [longitude] TEXT,\n",
            "  [altitude] TEXT,\n",
            "  [offset] TEXT,\n",
            "  [dst] TEXT,\n",
            "  [timezone] TEXT\n",
            ")\n",
            "table\n",
            "airlines\n",
            "airlines\n",
            "3\n",
            "CREATE TABLE airlines (\n",
            "[index] INTEGER,\n",
            "  [id] TEXT,\n",
            "  [name] TEXT,\n",
            "  [alias] TEXT,\n",
            "  [iata] TEXT,\n",
            "  [icao] TEXT,\n",
            "  [callsign] TEXT,\n",
            "  [country] TEXT,\n",
            "  [active] TEXT\n",
            ")\n",
            "table\n",
            "routes\n",
            "routes\n",
            "4\n",
            "CREATE TABLE routes (\n",
            "[index] INTEGER,\n",
            "  [airline] TEXT,\n",
            "  [airline_id] TEXT,\n",
            "  [source] TEXT,\n",
            "  [source_id] TEXT,\n",
            "  [dest] TEXT,\n",
            "  [dest_id] TEXT,\n",
            "  [codeshare] TEXT,\n",
            "  [stops] TEXT,\n",
            "  [equipment] TEXT\n",
            ")\n",
            "\n",
            "---- Verzió kiíratás 2.  ----\n",
            "\n",
            "3.22.0\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "A_RCO12vWF_S",
        "outputId": "e44d5fbe-80e3-46a9-fc85-387e9171aeb0",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 191
        }
      },
      "source": [
        "import sqlite3\n",
        "DB_FILE = \"/content/sample_data/mydb.db\"  # adatbázis neve\n",
        "con = sqlite3.connect(DB_FILE)  ## ha nem létezik létrehozza\n",
        "cur = con.cursor()\n",
        "newdb=0\n",
        "con.create_function(\"md5\", 1, md5sum)\n",
        "\n",
        "cur = con.cursor()\n",
        "\n",
        "if newdb == 1 :  ## Ha új adatbázis akkor a táblát léátrekell hozni\n",
        "    cur.execute(\"\"\" create table people (\n",
        "      name_last  varchar(20), \n",
        "      age        integer)  \"\"\")\n",
        " \n",
        "    ## Rekordok beszúrása / INSERT\n",
        "    cur.execute(\"insert into people (name_last, age) values ('Lajos',   57)\")    \n",
        "    cur.execute(\"insert into people (name_last, age) values ('Béla',     53)\")   \n",
        "    cur.execute(\"insert into people (name_last, age) values ('Ildikó',     54)\")  \n",
        "    con.commit()  ## Beírás véglegesítése\n",
        "\n",
        "# cur.execute(\" SELECT * FROM sqlite_master WHERE type='table' \")\n",
        "cur.execute(\" SELECT * FROM people WHERE 1=1 \")\n",
        "\n",
        "for row in cur:\n",
        "    for mezo in row:\n",
        "        print(mezo)\n",
        "\n",
        "cur.close()\n",
        "con.close()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Lajos\n",
            "57\n",
            "Béla\n",
            "53\n",
            "Ildikó\n",
            "54\n",
            "Dani\n",
            "22\n",
            "Tomi\n",
            "21\n"
          ],
          "name": "stdout"
        }
      ]
    }
  ]
}