{ "cells": [ { "cell_type": "markdown", "id": "b53d472a-14d4-42bb-a0ee-38eb8c9c24a8", "metadata": {}, "source": [ "Zunächst muss ein Paket zur Anbindung installiert werden. Wir verwenden psycopg 2 (psycopg.org).\n", "Dies kann über den Reiter \"Environment\" im Anaconda Navicator installiert werden.\n", "Ist dies geschehen können wir das Paket importieren." ] }, { "cell_type": "code", "execution_count": 1, "id": "0d3fea87-d2a6-4da8-9327-4c08bb70ef9e", "metadata": {}, "outputs": [], "source": [ "import psycopg2" ] }, { "cell_type": "markdown", "id": "bedf4588-60dc-4169-9295-21f6b3317e9f", "metadata": {}, "source": [ "Nun sind wir bereit, um eine Verbindung zur Datenbank aufzubauen. Nehmen wir zum Beispiel die \"bank\" Datenbank. Da wir die Installation local haben, muss keine URL angegeben werden. Dafür legen wir eine Variable mit dem connect Befehl an." ] }, { "cell_type": "code", "execution_count": 42, "id": "62b5a0b5-bafd-4125-8b62-4a2aa493a061", "metadata": {}, "outputs": [], "source": [ "conn = psycopg2.connect (\"dbname=7Wochen user=su password=postgres\")\n", "cursor = conn.cursor()" ] }, { "cell_type": "markdown", "id": "8861806c-5786-42fb-81d3-1ba5283b1d2b", "metadata": {}, "source": [ "Als nächstes führen wir eine einfache SELECT Abfrage aus.\n", "Zunächst werden wir verbunden, dann stellen wir die Abfrage, dann rufen wir das Ergebnis der Abfrage ab und lassen es uns anzeigen.\n", "Dazu benötigen wir ein cursor Objekt, das uns die Abfrage aber auch die Rücklieferung der Daten liefert. Dieses kommt in die Variable cursor.\n", "Die Variable result speichert uns die Ergebnisse. fetchall wartet bis alle Ergebniszeilen geliefert sind und ermöglicht dann die Anzeige." ] }, { "cell_type": "code", "execution_count": 10, "id": "09dbc41c-0ed7-449a-853d-3a18fbb1d97e", "metadata": {}, "outputs": [], "source": [ "cursor.execute(\"CREATE EXTENSION IF NOT EXISTS fuzzystrmatch; CREATE EXTENSION IF NOT EXISTS cube;\")" ] }, { "cell_type": "markdown", "id": "81c22552-4d3c-4824-82c7-5b8a0c74402c", "metadata": {}, "source": [ "Pro Klammer bekommen wir nun ein Tupel angezeigt. Das ist noch nicht so schön. Mit einer kleinen Schleife können wir uns die Tupel zeilenweise anzeigen lassen." ] }, { "cell_type": "code", "execution_count": 11, "id": "dba24620-fe85-4feb-8109-59a3d7c5e3b3", "metadata": {}, "outputs": [], "source": [ "cursor.execute(\"CREATE EXTENSION IF NOT EXISTS fuzzystrmatch; CREATE EXTENSION IF NOT EXISTS cube;\")" ] }, { "cell_type": "markdown", "id": "b975ba44-de7e-4337-a2f9-a4fa06fc59b1", "metadata": {}, "source": [ "Wollen wir nur eine bestimmte Spalte geht dies über die Angabe der Spaltennummer." ] }, { "cell_type": "code", "execution_count": 43, "id": "6159012d-ea9c-44da-bec0-5d073d210bd3", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "[('(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 7, 0, 0, 0)',)]\n" ] } ], "source": [ "suchbegriff = \"Broos Wilis\"\n", "\n", "cursor.execute(f\"SELECT genre FROM movies NATURAL JOIN movies_actors NATURAL JOIN actors WHERE metaphone(name, 6) = metaphone('{suchbegriff}', 6) UNION SELECT genre FROM movies WHERE metaphone(title, 6) = metaphone('{suchbegriff}', 6) LIMIT 1;\")\n", "result = cursor.fetchall()\n", "print(result)" ] }, { "cell_type": "markdown", "id": "b3180855-e952-4d4a-86b5-a27c9d326f69", "metadata": {}, "source": [ "Spaltennummern zählen ist nun etwas aufwendig und unschön. Mit einer Erweituerung des Pakets können wir den cursor anpassen und dann auch Spaltennamen angeben. Wir wählen psycopg2.extras und dann einen DictCursor." ] }, { "cell_type": "code", "execution_count": 44, "id": "75b7a5c8-4677-4546-a461-e7b36b97ff5e", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[('Silent Running', 0.0),\n", " ('Star Trek VI - The Undiscovered Country', 0.0),\n", " ('The Fifth Element', 0.0),\n", " ('Star Trek - Generations', 0.0),\n", " ('The Time Machine', 2.0),\n", " ('Heat Wave', 2.0),\n", " ('Star Trek - Insurrection', 2.0),\n", " ('The Swarm', 2.0),\n", " ('Things to Come', 2.0),\n", " ('One Million Years B.C.', 2.0),\n", " ('The Day of the Triffids', 2.0),\n", " ('Dark City', 2.0),\n", " ('Soylent Green', 2.0),\n", " ('THX 1138', 2.0),\n", " ('Fortress', 2.0),\n", " ('Journey to the Center of the Earth', 2.0),\n", " ('Stranded', 2.0),\n", " ('Forbidden Planet', 2.0),\n", " ('This Island Earth', 2.0),\n", " ('My Science Project', 2.0),\n", " ('Rollerball', 2.0),\n", " ('Star Trek - The Motion Picture', 2.0),\n", " ('Plan 9 from Outer Space', 2.0),\n", " ('The Black Hole', 2.0),\n", " ('Screamers', 2.0),\n", " ('Robinson Crusoe on Mars', 2.0),\n", " ('Star Trek II - The Wrath of Khan', 2.0),\n", " ('Star Trek III - The Search for Spock', 2.0),\n", " ('The Day the Earth Caught Fire', 2.0),\n", " ('Star Trek V - The Final Frontier', 2.0),\n", " ('The Angry Red Planet', 2.0),\n", " ('Zardoz', 2.0),\n", " ('A Boy and His Dog', 2.0),\n", " ('Battle for the Planet of the Apes', 2.0),\n", " ('An Eye for an Eye', 2.0),\n", " ('Star Trek - First Contact', 3.0),\n", " ('Enemy Mine', 5.385164807134504),\n", " ('The Day the Earth Stood Still', 5.385164807134504),\n", " ('Close Encounters of the Third Kind', 5.385164807134504),\n", " ('The X Files', 5.385164807134504),\n", " ('The Man Who Fell To Earth', 5.385164807134504),\n", " ('The Thirteenth Floor', 5.385164807134504),\n", " ('The Thing', 5.385164807134504),\n", " ('Escape from New York', 5.385164807134504),\n", " ('The Rapture', 5.385164807134504),\n", " ('Escape from the Planet of the Apes', 5.385164807134504),\n", " ('Fahrenheit 451', 5.385164807134504),\n", " ('Voyage to the Bottom of the Sea', 5.385164807134504),\n", " ('Fantastic Voyage', 5.385164807134504),\n", " ('Bicentennial Man', 5.385164807134504),\n", " ('Short Circuit', 5.385164807134504),\n", " (\"The Astronaut's Wife\", 5.385164807134504),\n", " ('Frankenstein', 5.385164807134504),\n", " ('20.000 Leagues under the Sea', 5.385164807134504),\n", " ('Ghostbusters II', 5.385164807134504),\n", " ('The Two Faces of Dr. Jekyll', 5.385164807134504),\n", " ('Young Frankenstein', 5.385164807134504),\n", " ('Frankenstein meets the Wolf Man', 5.385164807134504),\n", " ('Son of Frankenstein', 5.385164807134504),\n", " ('The Revenge of Frankenstein', 5.385164807134504),\n", " ('The Desert Hawk', 5.385164807134504),\n", " ('Royal Wedding', 5.385164807134504),\n", " ('Critters', 5.385164807134504),\n", " ('The World, the Flesh and the Devil', 5.385164807134504),\n", " ('Trancers', 5.385164807134504),\n", " ('Alive', 5.385164807134504),\n", " ('UFOria', 5.385164807134504),\n", " ('Alien Resurrection', 5.385164807134504),\n", " ('Event Horizon', 5.385164807134504),\n", " ('The Blob', 5.385164807134504),\n", " ('Species', 5.385164807134504),\n", " ('Universal Soldier', 5.385164807134504),\n", " ('Honey, I Shrunk the Kids', 5.385164807134504),\n", " ('Mad Max Beyond Thunderdome', 5.385164807134504),\n", " ('Virus', 5.385164807134504),\n", " ('Soldier', 5.385164807134504),\n", " ('Runaway', 5.385164807134504),\n", " ('Flubber', 5.385164807134504),\n", " ('Coneheads', 5.385164807134504),\n", " ('Mad Max', 5.385164807134504),\n", " ('Starman', 5.385164807134504),\n", " ('My Favorite Martian', 5.385164807134504),\n", " ('Johnny Mnemonic', 5.385164807134504),\n", " ('The Stepford Wives', 5.385164807134504),\n", " ('Bad Taste', 5.385164807134504),\n", " ('Escape from L.A.', 5.385164807134504),\n", " ('Flight of the Navigator', 5.385164807134504),\n", " ('Alien Nation', 5.385164807134504),\n", " ('Cyborg', 5.385164807134504),\n", " ('Sphere', 5.385164807134504),\n", " ('The Lawnmower Man', 5.385164807134504),\n", " ('The Quiet Earth', 5.385164807134504),\n", " ('The Rocketeer', 5.385164807134504),\n", " ('The Arrival', 5.385164807134504),\n", " ('The Philadelphia Experiment', 5.385164807134504),\n", " ('Space Truckers', 5.385164807134504),\n", " ('The Thing from Another World', 5.385164807134504),\n", " ('The Invisible Man', 5.385164807134504),\n", " ('Them!', 5.385164807134504),\n", " ('Hardware', 5.385164807134504),\n", " ('Sleeper', 5.385164807134504),\n", " ('Village of the Damned', 5.385164807134504),\n", " ('Weird Science', 5.385164807134504),\n", " ('The Last Starfighter', 5.385164807134504),\n", " ('My Stepmother is an Alien', 5.385164807134504),\n", " ('The incredible shrinking woman', 5.385164807134504),\n", " ('Death Race 2000', 5.385164807134504),\n", " ('Quatermass 2', 5.385164807134504),\n", " ('The Food of the Gods', 5.385164807134504),\n", " ('The Mole People', 5.385164807134504),\n", " ('The Invisible Woman', 5.385164807134504),\n", " ('Not of This Earth', 5.385164807134504),\n", " ('Empire of the Ants', 5.385164807134504),\n", " ('20 Million Miles to Earth', 5.385164807134504),\n", " ('The Valley of Gwangi', 5.385164807134504),\n", " ('The Monster That Challenged the World', 5.385164807134504),\n", " ('Donovan`s Brain', 5.385164807134504),\n", " ('Repo Man', 5.385164807134504),\n", " ('First Men in the Moon', 5.385164807134504),\n", " ('Demon Seed', 5.385164807134504),\n", " ('Never Let Me Go', 5.385164807134504),\n", " ('Late for Dinner', 5.385164807134504),\n", " ('Second Chance', 5.385164807134504),\n", " ('Biggles-Adventures in Time', 5.385164807134504),\n", " ('Zone Troopers', 5.385164807134504),\n", " ('Tomorrow', 5.385164807134504),\n", " ('Return from Witch Mountain', 5.385164807134504),\n", " ('The Dark Tower', 5.385164807134504),\n", " ('The People That Time Forgot', 5.385164807134504),\n", " ('Attack of the Crab Monsters', 5.385164807134504),\n", " ('It Conquered the World', 5.385164807134504),\n", " ('The Giant Claw', 5.385164807134504),\n", " ('Blade Runner', 5.830951894845301),\n", " ('The Hidden', 7.3484692283495345),\n", " ('The Postman', 7.3484692283495345),\n", " ('Predator 2', 7.3484692283495345),\n", " (\"Logan's Run\", 7.3484692283495345),\n", " ('Mimic', 7.3484692283495345),\n", " ('Priest', 7.3484692283495345),\n", " ('Cocoon: The Return', 7.3484692283495345),\n", " ('Dune', 7.3484692283495345),\n", " ('They Live', 7.3484692283495345),\n", " ('Wild Wild West', 7.3484692283495345),\n", " ('Superman II', 7.3484692283495345),\n", " ('Deep Impact', 7.3484692283495345),\n", " ('Timecop', 7.3484692283495345),\n", " ('*batteries not included', 7.3484692283495345),\n", " ('Slither', 7.3484692283495345),\n", " ('Planet of the Apes', 7.3484692283495345),\n", " ('Virtuosity', 7.3484692283495345),\n", " ('Freejack', 7.3484692283495345),\n", " ('The Island of Dr. Moreau', 7.3484692283495345),\n", " ('Invasion of the Body Snatchers', 7.3484692283495345),\n", " ('Strange Days', 7.3484692283495345),\n", " ('The Land That Time Forgot', 7.3484692283495345),\n", " ('The Truman Show', 7.3484692283495345),\n", " ('Judge Dredd', 7.3484692283495345),\n", " ('Explorers', 7.3484692283495345),\n", " ('Superman III', 7.3484692283495345),\n", " ('Scanners', 7.3484692283495345),\n", " ('Capricorn One', 7.3484692283495345),\n", " ('Lifeforce', 7.3484692283495345),\n", " ('Phantasm', 7.3484692283495345),\n", " ('Independence Day', 7.3484692283495345),\n", " ('Demolition Man', 7.3484692283495345),\n", " ('Robot Monster', 7.3484692283495345),\n", " ('Electric Dreams', 7.3484692283495345),\n", " ('The Quatermass Xperiment', 7.3484692283495345),\n", " ('The Satan Bug', 7.3484692283495345),\n", " ('Monkey Shines', 7.3484692283495345),\n", " ('Tron', 7.3484692283495345),\n", " ('InnerSpace', 7.3484692283495345),\n", " ('Airplane II: The Sequel', 7.3484692283495345),\n", " ('Kafka', 7.3484692283495345),\n", " ('The Abyss', 7.3484692283495345),\n", " ('Cocoon', 7.3484692283495345),\n", " ('Abbott and Costello meet Dr. Jekyll and Mr. Hyde', 7.3484692283495345),\n", " ('The Andromeda Strain', 7.3484692283495345),\n", " ('Island of Lost Souls', 7.3484692283495345),\n", " ('Outland', 7.3484692283495345),\n", " ('Frankenstein Unbound', 7.3484692283495345),\n", " ('The Ghost of Frankenstein', 7.3484692283495345),\n", " ('Project X', 7.3484692283495345),\n", " ('Six-String Samurai', 7.3484692283495345),\n", " ('The Curse of Frankenstein', 7.3484692283495345),\n", " ('House of Frankenstein', 7.3484692283495345),\n", " ('Frankenstein Created Woman', 7.3484692283495345),\n", " ('Futureworld', 7.3484692283495345),\n", " ('Dr. Cyclops', 7.3484692283495345),\n", " ('Stargate', 7.3484692283495345),\n", " ('Lost in Space', 7.3484692283495345),\n", " ('Killer Klowns from Outer Space', 7.3484692283495345),\n", " ('Earth vs. the flying saucers', 7.3484692283495345),\n", " ('The last Man on Earth', 7.3484692283495345),\n", " ('Flatliners', 7.3484692283495345),\n", " ('Dark Star', 7.3484692283495345),\n", " ('The Lost World', 7.681145747868608),\n", " ('Sunshine', 8.660254037844387),\n", " ('Krull', 8.660254037844387),\n", " ('Tarantula', 8.888194417315589),\n", " ('E.T. The Extra-Terrestrial', 8.888194417315589),\n", " ('Earth, Girls are Easy', 8.888194417315589),\n", " ('Westworld', 8.888194417315589),\n", " ('Godzilla', 8.888194417315589),\n", " ('Flash Gordon', 8.888194417315589),\n", " ('Waterworld', 8.888194417315589),\n", " ('Memoirs of an Invisible Man', 8.888194417315589),\n", " ('Altered States', 8.888194417315589),\n", " ('The Ice Pirates', 8.888194417315589),\n", " ('Wild in the Streets', 8.888194417315589),\n", " ('Barbarella', 8.888194417315589),\n", " ('RoboCop 2', 8.888194417315589),\n", " ('Dreamscape', 8.888194417315589),\n", " ('Moonraker', 8.888194417315589),\n", " ('The Running Man', 8.888194417315589),\n", " ('The Rocky Horror Picture Show', 8.888194417315589),\n", " ('Frankenstein must be Destroyed', 8.888194417315589),\n", " ('The Fury', 8.888194417315589),\n", " ('The Faculty', 8.888194417315589),\n", " ('Night of the Comet', 8.888194417315589),\n", " ('The Fly', 8.888194417315589),\n", " ('The Final Programme', 10.198039027185569),\n", " ('RoboCop', 10.198039027185569),\n", " ('What About Bob ?', 11.357816691600547),\n", " ('Bad Blood', 11.357816691600547)]" ] }, "execution_count": 44, "metadata": {}, "output_type": "execute_result" } ], "source": [ "cursor = conn.cursor()\n", "cursor.execute(f\"SELECT title, cube_distance(genre, '{result[0][0]}') dist FROM movies WHERE cube_enlarge('{result[0][0]}'::cube, 6,18) @> genre ORDER BY dist;\")\n", "result = cursor.fetchall()\n", "result" ] }, { "cell_type": "markdown", "id": "3b90228d-bbc6-40ca-bf18-abb698318603", "metadata": {}, "source": [ "Problembehandlung Transaktion: Da wir Abfragen durchführen sollten wir diese auch korrekt starten und abschliessen. Vor allem wenn es zu einem Fehler (z.B. einem Tippfehler) kommt. Anosnten laufen wir auf eine Fehlermeldung und eine offen Transaktion.\n", "Die Lösung ist unseren SQL Befehl in einen try, except bzw finally Block zu setzen.\n", "MIt conn.commit() wird die Durchführung der Transaktion bestätigt." ] }, { "cell_type": "code", "execution_count": null, "id": "2e24b6bb-4773-4652-b027-7ee4faf6db92", "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": ".venv (3.12.11)", "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.12.11" } }, "nbformat": 4, "nbformat_minor": 5 }