Verwalten von PostgreSQL-Datenbanken in Python mit psycopg2

Erstellen einer Tabelle in PostgreSQL

Es ist an der Zeit, deine erste Tabelle in der Datenbank datacamp_courses zu erstellen. Wir wollen eine Tabelle mit Informationen über einige der Kurse im DataCamp-Kurskatalog erstellen. Die Tabelle hat das folgende Schema: 

Die Spezifikation gibt uns eine ganze Reihe von Informationen über die Spalten der Tabelle. Der Primärschlüssel der Tabelle sollte course_idlauten und der Datentyp eine ganze Zahl sein. Ein Primärschlüssel ist eine Einschränkung, die erzwingt, dass die Spaltenwerte nicht null und eindeutig sind. Damit kannst du eine bestimmte oder eine Gruppe von Instanzen in der Tabelle eindeutig identifizieren. 

Die übrigen Spalten enthalten Informationen über den Kursnamen, den Namen der Lehrkraft und das Thema des Kurses.

Bevor du die Tabelle erstellst, ist es wichtig zu erklären, wie die Verbindungsinstanz funktioniert, die du gerade erstellt hast. Im Wesentlichen kapselt die Verbindung eine Datenbanksitzung und ermöglicht es dir, SQL-Befehle und Abfragen wie SELECT, INSERT, CREATE, UPDATE oder DELETE mit der Methode cursor() auszuführen und Änderungen mit der Methode commit() dauerhaft vorzunehmen .

Sobald du die Cursor-Instanz erstellt hast, kannst du Befehle an die Datenbank senden, indem du die execute() Methode Befehle an die Datenbank senden und Daten aus einer Tabelle abrufen, indem du fetchone(), fetchmany(), oder fetchall().

Schließlich ist es wichtig, den Cursor und die Verbindung zur Datenbank zu schließen, wenn du deine Operationen beendet hast. Andernfalls werden sie weiterhin serverseitige Ressourcen halten. Dazu kannst du dieMethode close() verwenden.

Unten findest du den Code, um die Tabelle datacamp_courses zu erstellen:

# Open a cursor to perform database operations
cur = conn.cursor()
# Execute a command: create datacamp_courses table
cur.execute("""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);
            """)
# Make the changes to the database persistent
conn.commit()
# Close cursor and communication with the database
cur.close()
conn.close()

Dies ist ein sehr einfaches Beispiel dafür, wie man Tabellen in PostgreSQL erstellt, aber die Dinge können noch viel komplexer werden. Wenn du mehr darüber erfahren möchtest, wie du eine PostgreSQL-Datenbank erstellst und die Struktur, die Datentypen und die Normalisierung von Datenbanken kennenlernen möchtest, schau dir unseren PostgreSQL-Datenbank erstellen Kurs.

Ausführen grundlegender PostgreSQL-Abfragen in Python

Die Tabelle datacamp_courses ist fertig; jetzt ist es an der Zeit, mit SQL einige grundlegende Abfragen durchzuführen!

EINFÜGEN 

Du hast vielleicht bemerkt, dass die Tabelle bisher noch keine Werte enthält. Um Datensätze in der Tabelle datacamp_courses zu erstellen, brauchen wir den Befehl INSERT.

cur = conn.cursor()

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Introduction to SQL','Izzy Weber','Julia')");

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Analyzing Survey Data in Python','EbunOluwa Andrew','Python')");

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Introduction to ChatGPT','James Chapman','Theory')");

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Introduction to Statistics in R','Maggie Matsui','R')");

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Hypothesis Testing in Python','James Chapman','Python')");

conn.commit()
cur.close()
conn.close()

Die Tabelle sieht wie folgt aus:

SELECT 

Das Lesen von Daten in SQL-Datenbanken ist wahrscheinlich etwas, das du auf deiner Reise durch die Datenwissenschaft häufig tun wirst. Dies wird im Allgemeinen als SELECT Abfrage bezeichnet. Schauen wir uns erst einmal an, wie sich die Tabelle datacamp_courses verhält.

Wir rufen die klassische Anweisung SELECT * FROM database_name auf, um alle in der Tabelle verfügbaren Daten zu lesen. Dann werden wir die Methode fetchall() verwenden, um alle verfügbaren Zeilen zu holen. Beachte, dass PostgreSQL automatisch einen numerischen Index für die Spalte course_id erstellt.

cur = conn.cursor()
cur.execute('SELECT * FROM datacamp_courses;')
rows = cur.fetchall()
conn.commit()
conn.close()
for row in rows:
    print(row)

(1, 'Introduction to SQL', 'Izzy Weber', 'Julia')
(2, 'Analyzing Survey Data in Python', 'EbunOluwa Andrew', 'Python')
(3, 'Introduction to ChatGPT', 'James Chapman', 'Theory')
(4, 'Introduction to Statistics in R', 'Maggie Matsui', 'R')
(5, 'Hypothesis Testing in Python', 'James Chapman', 'Python')

UPDATE 

Daten sind oft mit Fehlern behaftet. Im vorherigen Abschnitt hast du vielleicht bemerkt, dass das Thema des Kurses "Einführung in SQL" Julia ist. Nachdem wir die Informationen über den Kurs überprüft hatten, entdeckten wir den Fehler. Wir müssen es ändern und stattdessen "SQL" schreiben. Das kannst du mit der Anweisung UPDATE wie folgt tun:

cur = conn.cursor()
cur.execute("UPDATE datacamp_courses SET topic = 'SQL' WHERE course_name = 'Introduction to SQL';")
conn.commit()
conn.close()

LÖSCHEN 

Zum Schluss möchtest du vielleicht einen der Datensätze in deiner Tabelle löschen. Lass uns zum Beispiel den Kurs Einführung in die Statistik in R löschen:

cur = conn.cursor()
cur.execute("""DELETE from datacamp_courses WHERE course_name = 'Introduction to Statistics in R'""");
conn.commit()
cur.close()

Erweiterte PostgreSQL-Abfragen in Python

Im vorherigen Abschnitt haben wir die grundlegendsten SQL-Abfragen untersucht. Aber es gibt viel zu lernen. Sehen wir uns einige fortgeschrittene Abfragen an.

ORDER BY

Angenommen, du möchtest deine Datenbank nach dem Namen der Lehrkraft sortieren. Du kannst die Anweisung ORDER BY verwenden:

cur = conn.cursor()

cur.execute('SELECT * FROM datacamp_courses ORDER BY course_instructor')
rows = cur.fetchall()
for row in rows:
    print(row)

(2, 'Analyzing Survey Data in Python', 'EbunOluwa Andrew', 'Python')
(1, 'Introduction to SQL', 'Izzy Weber', 'SQL')
(3, 'Introduction to ChatGPT', 'James Chapman', 'Theory')
(4, 'Hypothesis Testing in Python', 'James Chapman', 'Python')

GRUPPE BY

Du möchtest vielleicht einige Aggregatfunktionen innerhalb verschiedener Datengruppen durchführen. Du könntest zum Beispiel daran interessiert sein, die Anzahl der Kurse der verschiedenen Kursleiter/innen zu berechnen. Du kannst diese Art von Operation mit der Funktion GROUP BY durchführen.

cur = conn.cursor()
cur.execute('SELECT course_instructor, COUNT(*) FROM datacamp_courses GROUP BY course_instructor')
rows = cur.fetchall()
for row in rows:
    print(row)

('James Chapman', 2)
('Izzy Weber', 1)
('EbunOluwa Andrew', 1)

JOIN

Bis hierhin haben wir nur mit der Tabelle datacamp_course gearbeitet. Das volle Potenzial von relationalen Datenbanken wie PostgreSQL kannst du jedoch erst dann nutzen, wenn du mit mehreren Tabellen gleichzeitig arbeitest. 

Das magische Werkzeug, um mehrere Tabellen zu kombinieren, ist die JOIN Operation. Stell dir vor, wir haben eine zweite Tabelle in unserer Datenbank mit dem Namen programming_languages, die grundlegende Informationen über die wichtigsten Programmiersprachen für Data Science enthält, darunter den Namen, die Position im TIOBE-Index und die Anzahl der Kurse über die Programmiersprache in DataCamp. Die Tabelle sieht so aus:

Stell dir vor, du möchtest die beiden Tabellen zusammenführen, um die Informationen zu den Kursen zusammen mit der Position der Sprache im TIOBE-Index zu erhalten. Wir werden eine INNER JOIN verwenden, um nur die Informationen der Programmiersprachen zu erhalten, die in der Tabelle datacamp_course erscheinen. 

cur = conn.cursor()
cur.execute("""SELECT course_name, course_instructor, topic, tiobe_ranking
FROM datacamp_courses
INNER JOIN programming_languages
ON datacamp_courses.topic = programming_languages.language_name""")
rows = cur.fetchall()
for row in rows:
    print(row)

('Introduction to SQL', 'Izzy Weber', 'SQL', 8)
('Analyzing Survey Data in Python', 'EbunOluwa Andrew', 'Python', 1)
('Hypothesis Testing in Python', 'James Chapman', 'Python', 1)

Das ist nur ein Beispiel, aber die Möglichkeiten von SQL-Joins sind endlos und machen dieses Thema zu einem Muss. Unser Kurs, Tutorium, und Spickzettel auf joins wird dir den Einstieg erleichtern.

Erste Schritte mit SQLAlchemy

Psycopg2 ist ein großartiges Tool zur Verbindung mit PostgreSQL-Datenbanken. Aber was ist, wenn du mit Datenbanken arbeiten musst, die in verschiedenen SQL-Datenbanken gehostet werden, wie z.B. Oracle und MySQL? In diesen Fällen kann es besser sein, ein Tool zu verwenden, das sich mit verschiedenen Arten von SQL-Datenbanken verbinden kann. Hierfür ist SQLAlchemy ein hervorragender Kandidat. 

Mit einfachen Worten: SQLAlchemy ermöglicht es den Nutzern, Datenbanken mit Python zu verbinden, SQL-Abfragen mit objektbasierter Programmierung auszuführen und den Arbeitsablauf zu optimieren. 

Die Syntax unterscheidet sich zwar von der auf psycopg2, aber du kannst so ziemlich dasselbe tun. Im Folgenden findest du ein Beispiel für die Erstellung einer Engine, die eine Verbindung zu unseren datacamp_courses Datensätzen herstellt und alle in der Tabelle datacap_course verfügbaren Daten ausliest.

import sqlalchemy as db


engine = db.create_engine("postgresql://datacamp@localhost:5432/datacamp_courses")
conn = engine.connect() 
output = conn.execute("SELECT * FROM datacamp_courses")
print(output.fetchall())
conn.close()

[(3, 'Introduction to ChatGPT', 'James Chapman', 'Theory'),
 (5, 'Hypothesis Testing in Python', 'James Chapman', 'Python'),
 (2, 'Analyzing Survey Data in Python', 'EbunOluwa Andrew', 'Python'),
 (1, 'Introduction to SQL', 'Izzy Weber', 'SQL')]

SQLAlchemy ist ein weiteres großartiges Tool, das du lernen solltest. Um einen Einblick in die Funktionsweise des Tools zu bekommen, wirf einen Blick auf unser SQLALchemy Tutorial.  

Verwendung von PostgreSQL im DataCamp DataLab

Beim DataCamp arbeiten wir hart daran, Data Science einfacher und für alle zugänglich zu machen. Das war der Grund für die Entwicklung von DataCamp DataLab, einem Cloud-basierten Notizbuch, das speziell für Data Science entwickelt wurde. Du kannst dir das DataCamp DataLab als eine Mischung aus Jupyter Notebook und Google Docs vorstellen, die es den Nutzern ermöglicht, Code zu schreiben, Daten einzeln oder gemeinsam zu analysieren und Dateneinsichten zu teilen.

Die Verbindung von DataLab mit PostgreSQL-Datenbanken und anderen SQL-Datenbanken ist extrem einfach. Mit DataLab kannst du dich sicher mit Datenquellen verbinden. Gleichzeitig kannst du deine Datenanalyse mit Python oder R nahtlos fortsetzen. Dazu musst du lediglich eine Integration erstellen, die ohne Code auskommt, und deine Verbindungsdaten eingeben.

Wenn du die Integration ausprobieren möchtest, aber keine PostgreSQL-Datenbank hast, mit der du dich verbinden kannst, kannst du eine unserer Beispiel-PostgreSQL-Datenbanken verwenden!

Neugierig auf das DataCamp DataLab? In diesem Artikel erfährst du mehr über dieses Notebook der nächsten Generation für Data Science.

Fazit

Gute Arbeit! In diesem Lernprogramm hast du PostgreSQL kennengelernt und erfahren, wie du mit dem Paket psycopg2 Datenbanken verwalten kannst, ohne Python zu verlassen. Aber das ist erst der Anfang der Reise. SQL ist eine der am weitesten verbreiteten Programmiersprachen in der Datenwissenschaft. Außerdem ist es ziemlich einfach zu lernen, du hast also keine Ausrede.  

Zum Glück gibt es das DataCamp für dich. Hier sind einige Ressourcen, die dir helfen können, PostgreSQL und SQL im Allgemeinen fließend zu beherrschen:

• Einführung in PostgreSQL für Anfänger Tutorial
• PostgreSQL-Datenbanken erstellen Kurs
• Verbesserung der Abfrageleistung in PostgreSQL Kurs
• Datenbanken verwalten in SQL Kurs
• Funktionen zur Datenbearbeitung in PostgreSQL Kurs