PostgreSQL für Einsteiger: Der kompakte Guide

Relationale Datenbanksysteme abfragen zu können, ist eine Kernkompetenz für Datenwissenschaftlerinnen und Datenwissenschaftler. SQL (Structured Query Language) ermöglicht dir das auf effiziente Weise. Mit SQL stellst du nicht nur gezielte Fragen an deine Daten, sondern kannst sie auch auf vielfältige Art transformieren. Ohne Datenbanken kommt praktisch keine reale Anwendung aus. Entsprechend gehören Datenbankwissen und der sichere Umgang damit in jeden Data-Science-Werkzeugkasten.

Kurzfakt: SQL wird auch SE-QU-EL ausgesprochen. Historisch kommt das daher, dass SQL ursprünglich Simple English Query Language hieß.

Relationale Datenbanken sehen typischerweise so aus –

Relationen heißen auch Tabellen. Es gibt verschiedene Weisen, Datenbanken darzustellen; dies ist die gängigste.

Dieses Tutorial führt dich durch die vier häufigsten SQL-Operationen: Create, Read, Update und Delete. Zusammen werden sie als CRUD bezeichnet. In jeder Anwendung mit Nutzungsinteraktion spielen sie eine Rolle.

Du verwendest PostgreSQL als relationales Datenbankmanagementsystem. PostgreSQL ist schlank und kostenlos. In diesem Tutorial wirst du:

• PostgreSQL installieren und starten
• Eine Verbindung zu einer PostgreSQL-Datenbank herstellen
• Tabellen in dieser Datenbank erstellen, auslesen, aktualisieren und löschen
• SQL in Jupyter Notebook ausführen
• SQL in Python ausführen

Los geht’s.

PostgreSQL einrichten und starten

PostgreSQL ist ein leichtgewichtiges, quelloffenes RDBMS und in der Industrie weit verbreitet. Mehr Infos findest du auf der offiziellen Website.

Um Abfragen in PostgreSQL schreiben und ausführen zu können, musst du es auf deinem Rechner installieren. Das geht sehr einfach. Die folgenden zwei kurzen Videos zeigen den Download und die Installation auf einem 32‑bit‑Windows‑7‑System:

• PostgreSQL herunterladen
• PostgreSQL installieren

Hinweis: Merke dir während der Installation das eingegebene Passwort und die Portnummer.

Nach erfolgreicher Installation öffne pgAdmin. pgAdmin ist ein praktisches Tool, das mit PostgreSQL mitgeliefert wird und dir gängige Datenbankaufgaben in einer grafischen Oberfläche ermöglicht. Die Oberfläche von pgAdmin sieht so aus –

Nach dem Start siehst du in der Oberfläche einen Server namens „PostgreSQL 9.4 (localhost:5432)“ –

Hinweis: Deine Version und damit auch die Portnummer (5432) können abweichen.

Verbinde dich mit dem Server, indem du das während der Installation vergebene Passwort eingibst. Referenz: https://www.loom.com/share/71708f6a99ee483a977991e9e8a25808.

Nach erfolgreicher Verbindung zum lokalen Datenbankserver siehst du eine Oberfläche wie diese –

Als Erstes legst du eine Datenbank an: Rechtsklicke auf den Tab Databases und wähle New Database aus dem Dropdown. Erstelle eine Datenbank namens DataCamp_Courses. Danach kannst du mit den nächsten Abschnitten fortfahren.

CRUD-Operationen in PostgreSQL

Eine Tabelle nach Vorgabe erstellen –

Um mit einer Datenbank zu arbeiten, brauchst du eine Tabelle. Erstellen wir eine einfache Tabelle (auch Relation genannt) namens datacamp_courses mit folgendem Schema –

Die Spezifikation liefert einige Informationen zu den Spalten der Tabelle –

• Der Primärschlüssel der Tabelle ist course_id (nur dieser Eintrag ist fett) und hat den Datentyp Integer. Ein Primärschlüssel erzwingt, dass die Spaltenwerte nicht NULL und eindeutig sind. So lassen sich einzelne Einträge in der Tabelle eindeutig identifizieren.
• Der Rest der Angaben ist jetzt selbsterklärend.

Zum Erstellen der Tabelle: Rechtsklicke auf die neu angelegte Datenbank DataCamp_Courses und wähle CREATE Script. Du solltest etwas Ähnliches sehen wie –

Führe nun folgende Abfrage aus –

CREATE TABLE datacamp_courses(
 course_id SERIAL PRIMARY KEY,
 course_name VARCHAR (50) UNIQUE NOT NULL,
 course_instructor VARCHAR (100) NOT NULL,
 topic VARCHAR (20) NOT NULL
);
Bereitgestellt von

Markiere die Abfrage und klicke in der Menüleiste auf Ausführen –

Die Ausgabe sollte so aussehen –

Die allgemeine Struktur einer CREATE-TABLE-Abfrage in PostgreSQL sieht so aus –

CREATE TABLE table_name (
 column_name TYPE column_constraint,
 table_constraint table_constraint
)
Bereitgestellt von

Wir haben beim Erstellen keine table_constraints angegeben – das ist fürs Erste nicht nötig. Alles ist gut lesbar, bis auf das Schlüsselwort SERIAL: Serial erzeugt in PostgreSQL eine Auto-Increment-Spalte, standardmäßig als Integer. So musst du dir den zuletzt verwendeten Primärschlüssel nicht merken – Auto-Increments für Primärschlüssel sind gute Praxis. Mehr zu SERIAL findest du hier.

Einige Datensätze in die neue Tabelle einfügen

Im nächsten Schritt fügst du einige Datensätze ein. Jeder Datensatz enthält:

• Einen Kursnamen
• Den Namen der Lehrkraft
• Das Kursthema

Die Werte für die Spalte course_id erzeugt PostgreSQL automatisch. Die allgemeine Struktur einer INSERT-Abfrage in PostgreSQL lautet –

INSERT INTO table(column1, column2, …)
VALUES
 (value1, value2, …);
Bereitgestellt von

Fügen wir einige Datensätze ein –

INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Deep Learning in Python','Dan Becker','Python');

INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Joining Data in PostgreSQL','Chester Ismay','SQL');
Bereitgestellt von

Beachte: Die Primärschlüssel hast du nicht explizit angegeben. Gleich siehst du den Effekt.

Nach Ausführung der beiden Abfragen solltest du folgende Rückmeldung erhalten –

Query returned successfully: one row affected, 11 ms execution time.

Daten aus der Tabelle lesen/anzeigen

Das wirst du auf deiner Data-Science-Reise sehr häufig tun. Schauen wir uns an, was in datacamp_courses steht.

Das ist eine SELECT-Abfrage; ihre generische Struktur sieht so aus –

SELECT
 column_1,
 column_2,
 ...
FROM
 table_name;
Bereitgestellt von

Wählen wir alle Spalten aus der Tabelle datacamp_courses –

SELECT * FROM datacamp_courses;
Bereitgestellt von

Und du erhältst –

Beachte jetzt die Primärschlüssel. Wenn du nur die Kursnamen sehen möchtest, geht das so –

SELECT course_name from datacamp_courses;
Bereitgestellt von

Und du erhältst –

Du kannst beliebig viele existierende Spalten angeben, die im Ergebnis erscheinen sollen. Würdest du select course_name, number_particpants from datacamp_courses; ausführen, gäbe es einen Fehler, weil die Spalte number_particpants in der Tabelle nicht existiert. Als Nächstes siehst du, wie du einen bestimmten Datensatz aktualisierst.

Einen Datensatz in der Tabelle aktualisieren

Die allgemeine Struktur einer UPDATE-Abfrage in SQL sieht so aus:

UPDATE table
SET column1 = value1,
    column2 = value2 ,...
WHERE
 condition;
Bereitgestellt von

Du aktualisierst den Datensatz, bei dem course_instructor = "Chester Ismay" ist, und setzt course_name auf "Joining Data in SQL". Anschließend prüfst du das Ergebnis. Die Abfrage dafür lautet –

UPDATE datacamp_courses SET course_name = 'Joining Data in SQL'
WHERE course_instructor = 'Chester Ismay';
Bereitgestellt von

Prüfen wir mit einer SELECT-Abfrage, ob die Aktualisierung wie gewünscht gegriffen hat –

Wie du siehst, hat die UPDATE-Abfrage genau das getan, was du wolltest. Als Nächstes löschen wir einen Datensatz.

Einen Datensatz in der Tabelle löschen

Die allgemeine Struktur einer DELETE-Abfrage in SQL lautet:

DELETE FROM table
WHERE condition;
Bereitgestellt von

Du löschst den Datensatz, bei dem course_name = "Deep Learning in Python" ist, und prüfst danach das Ergebnis. Nach obigem Muster erledigt das diese Abfrage –

DELETE from datacamp_courses
WHERE course_name = 'Deep Learning in Python';
Bereitgestellt von

Denke daran: Schlüsselwörter sind in SQL nicht case-sensitiv, Daten hingegen schon. Deshalb siehst du in den Abfragen eine Mischung aus Groß- und Kleinschreibung.

Schauen wir nach, ob der gewünschte Datensatz gelöscht wurde –

Ja, der Datensatz wurde wie beabsichtigt entfernt.

Die generischen Abfragestrukturen in diesem Tutorial sind dem PostgreSQL Tutorial entnommen.

Jetzt kennst du die grundlegenden CRUD-Abfragen in SQL. Viele von euch arbeiten intensiv mit Jupyter Notebooks und fragen sich vielleicht, ob sich diese Abfragen direkt dort ausführen lassen. Im nächsten Abschnitt zeige ich dir, wie das geht.

SQL + Jupyter Notebooks

Um SQL-Abfragen aus Jupyter Notebooks heraus auszuführen, installierst du zuerst das Paket ipython-sql.

Falls es noch nicht installiert ist, nutze:

pip install ipython-sql
Bereitgestellt von

Lade anschließend die sql-Erweiterung in deinem Jupyter Notebook mit –

%load_ext sql
Bereitgestellt von

Als Nächstes stellst du die Verbindung zu deiner PostgreSQL-Datenbank her, die du erstellt hast – DataCamp_Courses.

Damit Python die richtige Datenbankdialekt erkennt, sagst du ihm, dass es sich um PostgreSQL handelt. Dafür brauchst du psycopg2, das du so installierst:

pip install psycopg2
Bereitgestellt von

Nachdem psycopg installiert ist, verbindest du dich mit –

%sql postgresql://postgres:postgres@localhost:5432/DataCamp_Courses
Bereitgestellt von

'Connected: postgres@DataCamp_Courses'Bereitgestellt von

Beachte die Verwendung von %sql. Das ist ein Magic Command und ermöglicht SQL-Befehle direkt aus dem Notebook. Darauf folgt die Datenbank-URL, in der du angibst –

• Dialekt (postgres)
• Benutzername (postgres)
• Passwort (postgres)
• Serveradresse (localhost)
• Portnummer (5432)
• Datenbankname (DaaCamp_Courses)

Jetzt kannst du alles aus dem Notebook erledigen, was du in pgAdmin gemacht hast. Erstellen wir die Tabelle _datacampcourses mit demselben Schema.

Davor musst du die Tabelle löschen, da SQL keine zwei Tabellen mit gleichem Namen zulässt. Löschen kannst du so –

%sql DROP table datacamp_courses;
Bereitgestellt von

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
Done.





[] Bereitgestellt von

Die Tabelle _datacampcourses ist nun in PostgreSQL gelöscht und kann neu angelegt werden.

%%sql
CREATE TABLE datacamp_courses(
 course_id SERIAL PRIMARY KEY,
 course_name VARCHAR (50) UNIQUE NOT NULL,
 course_instructor VARCHAR (100) NOT NULL,
 topic VARCHAR (20) NOT NULL
);
Bereitgestellt von

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
Done.





[] Bereitgestellt von

Beachte den Unterschied zwischen %sql und %%sql: Für einzeilige Abfragen nutzt du %sql, für mehrere Anweisungen in einer Zelle %%sql.

Fügen wir Datensätze ein –

%%sql
INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Deep Learning in Python','Dan Becker','Python');
INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Joining Data in PostgreSQL','Chester Ismay','SQL');
Bereitgestellt von

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected.
1 rows affected.





[] Bereitgestellt von

Schaue in die Tabelle, um die Einfügungen zu prüfen –

%%sql
select * from datacamp_courses;
Bereitgestellt von

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
2 rows affected. Bereitgestellt von

Weiter im Flow: Als Nächstes aktualisieren wir einen Datensatz –

%sql update datacamp_courses set course_name = 'Joining Data in SQL' where course_instructor = 'Chester Ismay';
Bereitgestellt von

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected.





[] Bereitgestellt von

Sei bei Zeichenketten in SQL aufmerksam: Anders als in vielen Programmiersprachen werden Strings mit einfachen Anführungszeichen notiert.

Prüfen wir jetzt das Ergebnis der Aktualisierung –

%%sql
select * from datacamp_courses;
Bereitgestellt von

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
2 rows affected. Bereitgestellt von

Jetzt löschen wir einen Datensatz und prüfen das Ergebnis –

%%sql
delete from datacamp_courses where course_name = 'Deep Learning in Python';
Bereitgestellt von

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected.





[] Bereitgestellt von

%%sql
select * from datacamp_courses;
Bereitgestellt von

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected. Bereitgestellt von

Jetzt hast du ein klares Bild davon, wie du CRUD-Operationen in PostgreSQL ausführst – und wie das direkt aus Jupyter Notebook funktioniert. Wenn du mit Python vertraut bist und deine Datenbank aus Python-Code ansprechen möchtest, geht das natürlich auch. Darum geht es im nächsten Abschnitt.

Einstieg in SQLAlchemy und Kombination mit SQL-Magics

Für diesen Abschnitt brauchst du das Paket SQLAlchemy. In der Regel ist es in Anaconda enthalten; alternativ kannst du es per pip installieren. Danach importierst du es so –

import sqlalchemy
Bereitgestellt von

Um mit deinen Datenbanken über SQLAlchemy zu interagieren, erstellst du eine Engine für das jeweilige RDBMS – in deinem Fall PostgreSQL. Mit create_engine() erzeugst du sie in einem einzigen Aufruf und übergibst die schon bekannte Verbindungs-URL.

from sqlalchemy import create_engine
Bereitgestellt von

engine = create_engine('postgresql://postgres:postgres@localhost:5432/DataCamp_Courses')
print(engine.table_names()) # Zeigt dir die Tabellennamen in der Datenbank an
Bereitgestellt von

['datacamp_courses']Bereitgestellt von

Du siehst die Tabelle _datacampcourses – ein weiteres Indiz, dass die Engine erfolgreich erstellt wurde. Führen wir eine einfache SELECT-Abfrage auf _datacampcourses aus und speichern das Ergebnis in einem pandas-DataFrame.

Dazu nutzt du die read_sql()-Methode (bereitgestellt von pandas), der du die SQL-Abfrage und die Engine übergibst.

import pandas as pd

df = pd.read_sql('select * from datacamp_courses', engine)
df.head()
Bereitgestellt von

Du kannst auch das Magic-Command %sql innerhalb deines Python-Codes nutzen.

df_new = %sql select * from datacamp_courses
df_new.DataFrame().head()
Bereitgestellt von

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected. Bereitgestellt von

Stark! Du bist weit gekommen.

Glückwunsch, klopf dir selbst auf die Schulter! In diesem Tutorial hast du nicht nur den Einstieg in PostgreSQL geschafft, sondern auch gesehen, wie du SQL-Abfragen auf verschiedenen Wegen ausführst. Als nächsten Schritt empfehle ich dir dieses Webinar von David Robinson (Chief Data Scientist bei DataCamp), in dem er zeigt, wie er SQL im Alltag nutzt. Schau dir außerdem unser Tutorial How to Install PostgreSQL on Windows and Mac OS X an. Wenn du deine SQL-Kompetenz ausbauen möchtest, sind das hier starke Ressourcen für den Start –

• Intro to SQL for Data Science
• SQL by Kaggle Learn
• Joining Data in SQL