PostgreSQL-databases beheren in Python met psycopg2

Een tabel maken in PostgreSQL

Tijd om je eerste tabel te maken in de datacamp_courses-database. We willen een tabel maken met informatie over enkele cursussen in de DataCamp-catalogus. De tabel heeft het volgende schema: 

De specificatie vertelt ons het een en ander over de kolommen van de tabel. De primaire sleutel van de tabel moet course_id zijn, en het gegevenstype moet een integer zijn. Een primaire sleutel is een beperking die afdwingt dat de kolomwaarden niet-null en uniek zijn. Hiermee kun je een specifieke instantie of set instanties in de tabel eenduidig identificeren. 

De overige kolommen geven informatie over de cursusnaam, de naam van de cursusinstructeur en het onderwerp van de cursus.

Voordat we de tabel maken, is het belangrijk om uit te leggen hoe de connectie-instantie die je zojuist hebt gemaakt werkt. In essentie kapselt de connectie een databasesessie in en kun je er SQL-commando’s en -queries mee uitvoeren, zoals SELECT, INSERT, CREATE, UPDATE of DELETE, met de cursor()-methode, en wijzigingen blijvend maken met de commit() -methode. 

Zodra je de cursor-instantie hebt aangemaakt, kun je commando’s naar de database sturen met de execute()-methode en data uit een tabel ophalen met fetchone(), fetchmany() of fetchall().

Tot slot is het belangrijk om de cursor en de verbinding met de database te sluiten zodra je klaar bent. Anders blijven ze serverresources vasthouden. Gebruik hiervoor de close() -methode.

Hieronder vind je de code om de datacamp_courses-tabel te maken:

# 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()

Dit is een heel eenvoudig voorbeeld van hoe je tabellen in PostgreSQL aanmaakt, maar het kan een stuk complexer worden. Wil je meer leren over het aanmaken van een PostgreSQL-database en de structuur, datatypen en normalisatie verkennen? Bekijk dan onze cursus Creating PostgreSQL Database.

Basis-PostgreSQL-queries uitvoeren in Python

De datacamp_courses-tabel is klaar; tijd om SQL te gebruiken voor een paar basisqueries!

INSERT 

Je hebt misschien gemerkt dat de tabel nog geen waarden heeft. Om records in de datacamp_courses-tabel te maken, gebruiken we het INSERT-commando.

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()

De resulterende tabel ziet er zo uit:

SELECT 

Data lezen uit SQL-databases is waarschijnlijk iets wat je vaak zult doen in je data science-reis. Dit noemen we doorgaans een SELECT-query. Laten we nu kijken hoe het staat met de tabel datacamp_courses.

We roepen de klassieke instructie SELECT * FROM database_name aan om alle data in de tabel te lezen. Vervolgens gebruiken we de fetchall()-methode om alle beschikbare rijen op te halen. Merk op dat PostgreSQL automatisch een numerieke index maakt voor de course_id-kolom. 

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 

Data bevat vaak fouten. Je hebt in de vorige sectie misschien gezien dat het onderwerp bij de cursus “Introduction to SQL” Julia is. Na het checken van de cursusinformatie ontdekten we de fout. We moeten dit wijzigen naar “SQL”. Dat kan met de UPDATE-instructie, als volgt:

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

DELETE 

Tot slot wil je misschien een van de records in je tabel verwijderen. Laten we bijvoorbeeld de cursus Introduction to Statistics in R verwijderen:

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

Geavanceerde PostgreSQL-queries in Python

In de vorige sectie bekeken we de meest basale SQL-queries. Maar er valt nog veel meer te leren. Laten we wat geavanceerdere queries bekijken.

ORDER BY

Stel, je wilt je database sorteren op de naam van de instructeur. Dan kun je de ORDER BY-instructie gebruiken:

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')

GROUP BY

Je wilt misschien aggregatiefuncties uitvoeren binnen verschillende datagroepen. Je bent bijvoorbeeld geïnteresseerd in het berekenen van het aantal cursussen per cursusinstructeur. Dit soort bewerkingen kun je doen met de GROUP BY-functie.

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

Tot nu toe hebben we alleen met de tabel datacamp_course gewerkt. Maar je benut pas echt het volledige potentieel van relationele databases, zoals PostgreSQL, wanneer je met meerdere tabellen tegelijk werkt. 

Het tovermiddel om meerdere tabellen te combineren is de JOIN-operatie. Stel dat we een tweede tabel in onze database hebben, programming_languages, met basisinformatie over de top programmeertalen voor data science, inclusief de naam, de positie in de TIOBE-index en het aantal cursussen over de programmeertaal op Datacamp. De tabel ziet er zo uit:

Stel dat je de twee tabellen wilt samenvoegen om de cursusinformatie te combineren met de positie van de taal in de TIOBE-index. We gebruiken een INNER JOIN om alleen de informatie op te halen van de programmeertalen die voorkomen in de tabel datacamp_course. 

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)

Dit is slechts één voorbeeld, maar de mogelijkheden van SQL-joins zijn eindeloos — een absolute must-know. Onze cursus, tutorial en cheatsheet over joins helpen je op weg.

Aan de slag met SQLAlchemy

Psycopg2 is een geweldig hulpmiddel om met PostgreSQL-databases te verbinden. Maar wat als je moet werken met databases die op verschillende SQL-databases zijn gehost, zoals Oracle en MySQL? In zulke scenario’s is het vaak beter om een tool te gebruiken die met verschillende typen SQL-databases kan verbinden. SQLAlchemy is daarvoor een uitstekende kandidaat. 

Eenvoudig gezegd stelt SQLAlchemy gebruikers in staat om databases met Python te verbinden, SQL-queries uit te voeren via objectgeoriënteerd programmeren en de workflow te stroomlijnen. 

Hoewel de syntaxis verschilt van psycopg2, kun je er vrijwel hetzelfde mee doen. Hieronder vind je een voorbeeld om een engine te maken die verbinding maakt met onze datacamp_courses-datasets, en om alle data in de datacap_course-tabel te lezen

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 is nog zo’n tool die de moeite waard is om te leren. Wil je een idee krijgen hoe de tool werkt? Bekijk dan onze SQLALchemy-tutorial.  

PostgreSQL gebruiken in DataCamp DataLab

Bij DataCamp werken we hard om data science makkelijker en voor iedereen toegankelijk te maken. Dat was de reden achter de creatie van DataCamp DataLab, een cloudgebaseerd notebook speciaal ontworpen voor data science. Je kunt DataCamp DataLab zien als een mix van Jupyter Notebook en Google Docs, waarmee gebruikers individueel of samen code kunnen schrijven, data kunnen analyseren en inzichten kunnen delen.

DataLab verbinden met PostgreSQL-databases, maar ook met andere SQL-databases, is extreem eenvoudig. DataLab laat je veilig verbinding maken met databronnen. Tegelijkertijd kun je naadloos verdergaan met je data-analyse in Python of R. Je hoeft alleen een integratie aan te maken via een eenvoudig, no-code proces, waarin je je verbindingsgegevens invoert.

Wil je de integratie proberen maar heb je geen PostgreSQL-database om mee te verbinden? Gebruik dan een van onze voorbeeld-PostgreSQL-databases!

Nieuwsgierig naar DataCamp DataLab? Bekijk dit artikel om meer te weten te komen over dit notebook van de volgende generatie voor data science.

Conclusie

Goed gedaan! In deze tutorial heb je kennisgemaakt met PostgreSQL en geleerd hoe je databases beheert zonder Python te verlaten, met het psycopg2-pakket. Maar dit is nog maar het begin. SQL is een van de meest gebruikte programmeertalen in data science. Het is ook vrij eenvoudig te leren, dus geen excuses.  

Gelukkig zit je bij Datacamp goed. Hier zijn enkele resources die je helpen om vloeiend te worden in PostgreSQL en SQL in het algemeen:

• Beginner Introductions to PostgreSQL Tutorial
• Creating PostgreSQL Databases Course
• Improving Query Performance in PostgreSQL Course
• Managing Databases in SQL Course
• Functions for Manipulating Data in PostgreSQL Course