{ "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": [ "\"Open" ] }, { "cell_type": "markdown", "metadata": { "id": "NbC6f1eCntYf", "colab_type": "text" }, "source": [ "

\n", " \n", " \n", "

\n", "\n", "\n", "

\n", "\n", "\n", "\n", "# Python alapok SQL db használat (Sqlite)\n", "\n", "\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" } ] } ] }