{ "nbformat": 4, "nbformat_minor": 0, "metadata": { "colab": { "name": "kl_py_sqlite_03.ipynb", "provenance": [], "collapsed_sections": [], "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.7" } }, "cells": [ { "cell_type": "markdown", "metadata": { "id": "view-in-github", "colab_type": "text" }, "source": [ "\"Open" ] }, { "cell_type": "markdown", "metadata": { "id": "nM02BbcJY-PH", "colab_type": "text" }, "source": [ "

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

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

\n", "\n", "\n", "\n", "# Python alapok SQL db használat (Sqlite) 3.\n", "\n", "## hibakezelés. xls-be írás\n", "\n", "\n", "\n", "\n", "---" ] }, { "cell_type": "markdown", "metadata": { "colab_type": "text", "id": "5VWEm6ccHCbi" }, "source": [ "---\n", "\n", "## Kapcsolódás az adatbázishoz\n", "\n", "---" ] }, { "cell_type": "code", "metadata": { "colab_type": "code", "id": "y_b8GDwsHATh", "colab": {} }, "source": [ "## Modulok impontálása\n", "import sqlite3\n", "import os\n" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": { "colab_type": "text", "id": "dQQFg6bPIYjn" }, "source": [ "---\n", "\n", "## SqLite adatbázis lekérdezése és eredmény kiíratása hibakezeléssel\n", "\n", "----" ] }, { "cell_type": "code", "metadata": { "colab_type": "code", "id": "Z8TPADekIcc9", "colab": { "base_uri": "https://localhost:8080/", "height": 627 }, "outputId": "a4c4c05f-6f66-4f96-e49b-3989172de0a4" }, "source": [ "## Minta adatbázis\n", "DB_FILE = r\"c:\\Users\\User\\Downloads\\Chinook_Sqlite.sqlite\" # adatbázis neve\n", "### forrás : https://github.com/lerocha/chinook-database/blob/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite\n", "\n", "try: ## hibakezelés fiygelés kezdet\n", " \n", " con = sqlite3.connect(DB_FILE) ## kapcsolódás\n", " cur = con.cursor() ## kurzor létrehozás\n", " \n", "## paraméteres lekérdezés 1.\n", " cur.execute(\"\"\" SELECT TrackId, Name, Composer, AlbumId, Bytes FROM Track WHERE \n", " GenreId = ? --- sql : paraméter deffinicio\n", " and Composer is not null \"\"\",\n", " ('23',)) ## paraméter érték direct megadása\n", "\n", " print('---- Kiíratás (nyers, csak tartalom) ----\\n')\n", " for row in cur:\n", " for mezo in row:\n", " print(mezo, end=' ') # Mezők egy sorba\n", " print() # kiíratás végén sorvégjel kitétele\n", " \n", " print() \n", "\n", "\n", " ## lezárások, erőforrás felszabadítás\n", " cur.close()\n", " con.close()\n", " \n", "except sqlite3.Error as e: # hiba elkapása\n", " print (\"Hiba történt : \", e.args[0]) # hiba üzenet" ], "execution_count": null, "outputs": [ { "output_type": "stream", "text": [ "---- Kiíratás (nyers, csak tartalom) ----\n", "\n", "3375 No Such Thing Chris Cornell 270 3691272 \n", "3376 Poison Eye Chris Cornell 270 3890037 \n", "3377 Arms Around Your Love Chris Cornell 270 3516224 \n", "3378 Safe and Sound Chris Cornell 270 4207769 \n", "3379 She'll Never Be Your Man Chris Cornell 270 3355715 \n", "3380 Ghosts Chris Cornell 270 3799745 \n", "3381 Killing Birds Chris Cornell 270 3588776 \n", "3382 Billie Jean Michael Jackson 270 4606408 \n", "3383 Scar On the Sky Chris Cornell 270 3616618 \n", "3384 Your Soul Today Chris Cornell 270 3385722 \n", "3385 Finally Forever Chris Cornell 270 3565098 \n", "3386 Silence the Voices Chris Cornell 270 4379597 \n", "3387 Disappearing Act Chris Cornell 270 4476203 \n", "3388 You Know My Name Chris Cornell 270 3940651 \n", "\n" ], "name": "stdout" } ] }, { "cell_type": "code", "metadata": { "id": "ribY0h-2Y-PO", "colab_type": "code", "colab": {}, "outputId": "f5c90fc2-e34f-45f4-9c11-4c435fbc863a" }, "source": [ "## Minta adatbázis\n", "DB_FILE = r\"c:\\Users\\User\\Downloads\\Chinook_Sqlite.sqlite\" # adatbázis neve\n", "### forrás : https://github.com/lerocha/chinook-database/blob/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite\n", "\n", "try: ## hibakezelés fiygelés kezdet\n", " \n", " con = sqlite3.connect(DB_FILE) ## kapcsolódás\n", " cur = con.cursor() ## kurzor létrehozás\n", " \n", "## paraméteres lekérdezés 2.\n", " cur.execute(\"\"\" SELECT TrackId, Name, Composer, AlbumId, Bytes FROM Track WHERE \n", " GenreId = :GenreId --- sql : nevesített paraméter deffinicio\n", " and Composer > :Composer \"\"\",\n", " {\"GenreId\": \"23\", \"Composer\": \"A\"}) ## paraméter szótár érték direct megadása\n", "\n", " print('---- Kiíratás turple (nyers, csak tartalom) ----\\n')\n", " i = cur.fetchone()\n", " while(i):\n", " print(i)\n", " i = cur.fetchone()\n", " \n", " print() \n", " print('------------------ Második ---------------------') \n", " print() \n", " \n", " cur.execute(\"\"\" SELECT TrackId, Name, Composer, AlbumId, Bytes FROM Track WHERE \n", " GenreId = :GenreId --- sql : nevesített paraméter deffinicio\n", " and Composer > :Composer \"\"\",\n", " {\"GenreId\": \"24\", \"Composer\": \"T\"}) ## paraméter szótár érték direct megadása \n", " \n", " print(cur.fetchmany(2)) ## két sor kiírása\n", " \n", " print() \n", " print('------------------ Harmadik ---------------------') \n", " print() \n", "\n", " \n", " rows = cur.fetchall()\n", " for row in rows:\n", " print(\"ID = {} \".format(row[0]))\n", " print(\"NEV = {} \".format(row[1]))\n", " print(\"SZERZO = {}\".format(row[2]))\n", " print(\"BYTE = {}\".format(row[4]))\n", " print('-------------------------------')\n", " \n", "\n", "\n", "\n", " ## lezárások, erőforrás felszabadítás\n", " cur.close()\n", " con.close()\n", " \n", "except sqlite3.Error as e: # hiba elkapása\n", " print (\"Hiba történt : \", e.args[0]) # hiba üzenet" ], "execution_count": null, "outputs": [ { "output_type": "stream", "text": [ "---- Kiíratás turple (nyers, csak tartalom) ----\n", "\n", "(3375, 'No Such Thing', 'Chris Cornell', 270, 3691272)\n", "(3376, 'Poison Eye', 'Chris Cornell', 270, 3890037)\n", "(3377, 'Arms Around Your Love', 'Chris Cornell', 270, 3516224)\n", "(3378, 'Safe and Sound', 'Chris Cornell', 270, 4207769)\n", "(3379, \"She'll Never Be Your Man\", 'Chris Cornell', 270, 3355715)\n", "(3380, 'Ghosts', 'Chris Cornell', 270, 3799745)\n", "(3381, 'Killing Birds', 'Chris Cornell', 270, 3588776)\n", "(3382, 'Billie Jean', 'Michael Jackson', 270, 4606408)\n", "(3383, 'Scar On the Sky', 'Chris Cornell', 270, 3616618)\n", "(3384, 'Your Soul Today', 'Chris Cornell', 270, 3385722)\n", "(3385, 'Finally Forever', 'Chris Cornell', 270, 3565098)\n", "(3386, 'Silence the Voices', 'Chris Cornell', 270, 4379597)\n", "(3387, 'Disappearing Act', 'Chris Cornell', 270, 4476203)\n", "(3388, 'You Know My Name', 'Chris Cornell', 270, 3940651)\n", "\n", "------------------ Második ---------------------\n", "\n", "[(3412, '\"Eine Kleine Nachtmusik\" Serenade In G, K. 525: I. Allegro', 'Wolfgang Amadeus Mozart', 281, 5760129), (3413, 'Concerto for Clarinet in A Major, K. 622: II. Adagio', 'Wolfgang Amadeus Mozart', 282, 6474980)]\n", "\n", "------------------ Harmadik ---------------------\n", "\n", "ID = 3448 \n", "NEV = Lamentations of Jeremiah, First Set \\ Incipit Lamentatio \n", "SZERZO = Thomas Tallis\n", "BYTE = 1208080\n", "-------------------------------\n", "ID = 3454 \n", "NEV = Symphony No. 41 in C Major, K. 551, \"Jupiter\": IV. Molto allegro \n", "SZERZO = Wolfgang Amadeus Mozart\n", "BYTE = 6173269\n", "-------------------------------\n", "ID = 3492 \n", "NEV = Sing Joyfully \n", "SZERZO = William Byrd\n", "BYTE = 2256484\n", "-------------------------------\n", "ID = 3502 \n", "NEV = Quintet for Horn, Violin, 2 Violas, and Cello in E Flat Major, K. 407/386c: III. Allegro \n", "SZERZO = Wolfgang Amadeus Mozart\n", "BYTE = 3665114\n", "-------------------------------\n" ], "name": "stdout" } ] }, { "cell_type": "markdown", "metadata": { "colab_type": "text", "id": "TKIx7dJm-p88" }, "source": [ "---\n", "\n", "## SqLite táblák lekérdezése és eredmény excelbe írása\n", "\n", "---" ] }, { "cell_type": "code", "metadata": { "colab_type": "code", "id": "A_RCO12vWF_S", "colab": { "base_uri": "https://localhost:8080/", "height": 191 }, "outputId": "e44d5fbe-80e3-46a9-fc85-387e9171aeb0" }, "source": [ "import xlsxwriter\n", "import sqlite3\n", "\n", "# Új excel file létrehozása\n", "workbook = xlsxwriter.Workbook(r\"c:\\Users\\User\\Downloads\\Chinook_file.xlsx\")\n", "# Új munkalap létrehozása\n", "worksheet = workbook.add_worksheet()\n", "## Kezdő pozició\n", "row_ = 0\n", "col_ = 0\n", "\n", "DB_FILE = r\"c:\\Users\\User\\Downloads\\Chinook_Sqlite.sqlite\" # adatbázis neve\n", "con = sqlite3.connect(DB_FILE) ## kapcsolódás\n", "cur = con.cursor() ## kurzor létrehozás\n", "\n", "FIELD_MAX_WIDTH = 30 ## mezők maximális hossz beállítása\n", "\n", "\n", "cur.execute(\"\"\" SELECT t.TrackId, t.Name, t.Composer, g.name as Style \n", "FROM Track t INNER JOIN Genre g ON t.GenreId= g.GenreId --- SQL megjegyzés: Join szabály\n", "WHERE t.GenreId=23 and t.Composer is not null --- SQL megjegyzés: Where feltétel \"\"\")\n", "\n", "# Fejléc\n", "for fieldDesc in cur.description:\n", " print(fieldDesc[0].ljust(FIELD_MAX_WIDTH), end=' ')\n", " \n", " ## Excelbe fejléc írása\n", " worksheet.write(row_, col_, fieldDesc[0])\n", " col_ += 1\n", " \n", "print() # Fejléc vége \n", "for fieldDesc in cur.description:\n", " print('-' * 30, end=' ') # Alávonás\n", "print() # elválasztó sor\n", "\n", "\n", "# Adattőrzs\n", "fieldIndices = range(len(cur.description))\n", "for row in cur:\n", " row_ += 1\n", " col_ = 0\n", " for fieldIndex in fieldIndices:\n", " fieldValue = str(row[fieldIndex])\n", " print(fieldValue.ljust(FIELD_MAX_WIDTH), end=' ') # Mezők egy sorba\n", " \n", " ## excelbe adatok írása\n", " worksheet.write(row_, col_, row[fieldIndex])\n", " col_ += 1\n", "\n", " print() # kiíratás végén sorvégjel kitétele\n", "\n", "\n", "\n", "\n", "## lezárások, erőforrás felszabadítás\n", "cur.close()\n", "con.close()\n", "workbook.close()\n", "\n", "\n", "## Hiányzik: kivétel kezelés, ..." ], "execution_count": null, "outputs": [ { "output_type": "stream", "text": [ "TrackId Name Composer Style \n", "------------------------------ ------------------------------ ------------------------------ ------------------------------ \n", "3375 No Such Thing Chris Cornell Alternative \n", "3376 Poison Eye Chris Cornell Alternative \n", "3377 Arms Around Your Love Chris Cornell Alternative \n", "3378 Safe and Sound Chris Cornell Alternative \n", "3379 She'll Never Be Your Man Chris Cornell Alternative \n", "3380 Ghosts Chris Cornell Alternative \n", "3381 Killing Birds Chris Cornell Alternative \n", "3382 Billie Jean Michael Jackson Alternative \n", "3383 Scar On the Sky Chris Cornell Alternative \n", "3384 Your Soul Today Chris Cornell Alternative \n", "3385 Finally Forever Chris Cornell Alternative \n", "3386 Silence the Voices Chris Cornell Alternative \n", "3387 Disappearing Act Chris Cornell Alternative \n", "3388 You Know My Name Chris Cornell Alternative \n" ], "name": "stdout" } ] }, { "cell_type": "markdown", "metadata": { "id": "E32cFjd7Y-PT", "colab_type": "text" }, "source": [ "---\n", "### Adatbázis szerkezete:\n", "\n", "
\n", "
\n", "

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

\n", "\n", "---" ] } ] }