Beginnersgids voor PostgreSQL

Query’s kunnen schrijven op relationele databases is een onmisbare vaardigheid voor een data scientist. SQL, ofwel Structured Query Language, laat je dit op een zeer efficiënte manier doen. Met SQL kun je niet alleen zinvolle vragen aan de data stellen, maar ook op veel verschillende manieren met de data werken. Zonder databases is er praktisch geen enkele real-world toepassing mogelijk. Kennis van databases en ermee kunnen werken zijn dus cruciale onderdelen van de gereedschapskist van een data scientist.

Weetje: SQL wordt ook uitgesproken als SE-QU-EL. Dat heeft een historische reden: de oorspronkelijke naam van SQL was Simple English Query Language.

Over het algemeen zien relationele databases er als volgt uit:

Relaties worden ook wel tabellen genoemd. Er zijn meerdere manieren om databases weer te geven. Dit is er één van, en meteen ook de meest gebruikte.

Deze tutorial introduceert de vier meest voorkomende bewerkingen met SQL: Create, Read, Update en Delete. Samen worden deze vier bewerkingen vaak aangeduid als CRUD. In elke toepassing met gebruikersinteractie kom je deze vier bewerkingen altijd tegen.

Je gebruikt PostgreSQL als relationeel databasemanagementsysteem. PostgreSQL is heel lichtgewicht en bovendien gratis. In deze tutorial ga je:

• Snel aan de slag met PostgreSQL
• Verbinding maken met een PostgreSQL-database
• Tabellen in die database maken, lezen, bijwerken en verwijderen
• SQL uitvoeren in Jupyter Notebook
• SQL uitvoeren in Python

Laten we beginnen.

Snel aan de slag met PostgreSQL

PostgreSQL is een lichtgewicht en open-source RDBMS. Het is in de industrie zeer breed geaccepteerd. Je kunt meer over PostgreSQL leren op de officiële website.

Om query’s in PostgreSQL te kunnen schrijven en uitvoeren, moet je het op je machine installeren. Installeren is erg eenvoudig. De volgende twee korte video’s laten zien hoe je PostgreSQL kunt downloaden en installeren op een 32-bit Windows 7-machine:

• PostgreSQL downloaden
• PostgreSQL installeren

Let op: Noteer tijdens de installatie het wachtwoord en het poortnummer dat je invoert.

Nadat je PostgreSQL succesvol hebt geïnstalleerd, open je pgAdmin. pgAdmin is een handige tool die met de PostgreSQL-installatie wordt meegeleverd en waarmee je via een mooie grafische interface reguliere databasetaken kunt uitvoeren. De interface van pgAdmin ziet er zo uit:

Wanneer je pgAdmin opent, zie je een server met de naam "PostgreSQL 9.4 (localhost:5432)" in de interface staan:

Let op: Jouw versie kan afwijken en dus ook het poortnummer (5432).

Maak verbinding met de server door het wachtwoord in te voeren dat je tijdens de installatie hebt opgegeven. Ter referentie - https://www.loom.com/share/71708f6a99ee483a977991e9e8a25808.

Na een succesvolle verbinding met de lokale databaseserver krijg je een interface vergelijkbaar met het volgende:

Je eerste taak is het aanmaken van een database. Dat doe je door met de rechtermuisknop op het tabblad Databases te klikken en vervolgens New Database te kiezen. Laten we een database maken met de naam DataCamp_Courses. Zodra de database is aangemaakt, kun je verdergaan met de volgende secties van deze tutorial.

CRUD-bewerkingen in PostgreSQL

Een tabel maken volgens een gegeven specificatie -

Om op een database te kunnen werken, heb je een tabel nodig. Laten we daarom een eenvoudige tabel (ook wel relatie genoemd) maken met de naam datacamp_courses met de volgende specificatie (schema):

De specificatie geeft aardig wat informatie over de kolommen van de tabel:

• De primaire sleutel van de tabel moet course_id zijn (merk op dat alleen deze vet is) en het gegevenstype moet een integer zijn. Een primaire sleutel is een constraint die afdwingt dat de kolomwaarden niet null zijn en uniek. Hiermee kun je een specifieke instantie of set instanties in de tabel uniek identificeren.
• De rest van de informatie in de specificatie zou nu eenvoudig te interpreteren moeten zijn.

Om een tabel te maken, klik je met de rechtermuisknop op de zojuist aangemaakte database DataCamp_Courses en selecteer je CREATE Script in de opties. Je zou iets vergelijkbaars moeten zien als het volgende:

Voer nu de volgende query uit:

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

Om de query uit te voeren selecteer je hem en klik je op de knop Uitvoeren in de menubalk:

De uitvoer zou moeten zijn:

De algemene structuur van een query om een tabel te maken in PostgreSQL ziet er zo uit:

CREATE TABLE table_name (
 column_name TYPE column_constraint,
 table_constraint table_constraint
)

We hebben geen table_constraints gespecificeerd bij het maken van de tabel. Dat kan voor nu achterwege blijven. Alles is vrij leesbaar, behalve het sleutelwoord SERIAL. Serial in PostgreSQL laat je een auto-increment-kolom maken. Standaard maakt het waarden van het type integer. Serial verlost ons van de last om de laatst ingevoerde/bijgewerkte primaire sleutel van een tabel te onthouden, en het is een goede gewoonte om auto-increments te gebruiken voor primaire sleutels. Je kunt hier meer over serial leren hier.

Records invoegen in de nieuw aangemaakte tabel

In deze stap voeg je enkele records toe aan de tabel. Je records moeten bevatten:

• Een cursusnaam
• Naam van de docent van de cursus
• Cursustopic

De waarden voor de kolom course_id worden door PostgreSQL zelf afgehandeld. De algemene structuur van een insert-query in PostgreSQL ziet er zo uit:

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

Laten we wat records invoegen:

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

Merk op dat je de primaire sleutels niet expliciet hebt opgegeven. Je ziet zo wat het effect daarvan is.

Wanneer je de bovenstaande twee query’s uitvoert, zou je bij succesvolle inserts het volgende resultaat moeten krijgen:

Query succesvol uitgevoerd: één rij beïnvloed, uitvoeringstijd 11 ms.

Data uit de tabel lezen/bekijken

Dit is waarschijnlijk iets wat je vaak zult doen tijdens je data science-reis. Laten we nu kijken hoe het met de tabel datacamp_courses staat.

Dit wordt doorgaans een select-query genoemd, en de generieke structuur van een select-query ziet er zo uit:

SELECT
 column_1,
 column_2,
 ...
FROM
 table_name;

Laten we alle kolommen selecteren uit de tabel datacamp_courses

SELECT * FROM datacamp_courses;

En je krijgt:

Let nu op de primaire sleutels. Als je alleen de namen van de cursussen wilt zien, kan dat zo:

SELECT course_name from datacamp_courses;

En je krijgt:

Je kunt zoveel kolomnamen opgeven als je in je resultaten wilt zien, mits ze in de tabel bestaan. Als je select course_name, number_particpants from datacamp_courses; uitvoert, krijg je een fout omdat de kolom number_particpants niet in de tabel bestaat. Nu zie je hoe je een specifiek record in de tabel kunt bijwerken.

Een record in de tabel bijwerken

De algemene structuur van een update-query in SQL ziet er als volgt uit:

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

Je gaat het record bijwerken waar course_instructor = "Chester Ismay" en de course_name instellen op "Joining Data in SQL". Daarna verifieer je of het record is bijgewerkt. De query hiervoor is:

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

Laten we controleren of je update-query het beoogde effect had door een select-query uit te voeren:

Je ziet dat je update-query precies heeft gedaan wat je wilde. Nu zie je hoe je een record uit de tabel kunt verwijderen.

Een record in de tabel verwijderen

De algemene structuur van een delete-query in SQL ziet er als volgt uit:

DELETE FROM table
WHERE condition;

Je gaat het record verwijderen waar course_name = "Deep Learning in Python" en vervolgens controleren of het record is verwijderd. Op basis van de structuur kun je zien dat de volgende query dit zou moeten doen:

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

Houd er rekening mee dat de sleutelwoorden in SQL niet hoofdlettergevoelig zijn, maar de data is wél hoofdlettergevoelig. Daarom zie je in de query’s een mix van hoofdletters en kleine letters.

Laten we kijken of het beoogde record uit de tabel is verwijderd of niet:

En ja, het beoogde record is inderdaad verwijderd.

De generieke structuren van de query’s zoals genoemd in de tutorial zijn ontleend aan de PostgreSQL Tutorial.

Je weet nu hoe je basis-CRUD-query’s in SQL uitvoert. Sommigen van jullie gebruiken Jupyter Notebooks intensief en denken misschien dat het handig zou zijn om deze query’s direct vanuit Jupyter Notebook te kunnen uitvoeren. In de volgende sectie zie je hoe je dat kunt doen.

SQL + Jupyter Notebooks

Om SQL-query’s vanuit Jupyter Notebooks uit te voeren, is de eerste stap het installeren van het pakket ipython-sql.

Als het niet is geïnstalleerd, installeer het dan met:

pip install ipython-sql

Laad daarna de sql-extensie in je Jupyter Notebook door het volgende uit te voeren:

%load_ext sql

De volgende stap is verbinding maken met een PostgreSQL-database. Je maakt verbinding met de database die je hebt aangemaakt: DataCamp_Courses.

Om verbinding te kunnen maken met een database die al op je systeem is aangemaakt, moet je Python instrueren om het dialect te herkennen. Simpel gezegd: je moet Python vertellen dat het om een PostgreSQL-database gaat. Daarvoor heb je psycopg2 nodig, dat je kunt installeren met:

pip install psycopg2

Nadat je psycopg hebt geïnstalleerd, maak je verbinding met de database met:

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

'Connected: postgres@DataCamp_Courses'

Let op het gebruik van %sql. Dit is een magic command. Hiermee kun je SQL-instructies uitvoeren vanuit Jupyter Notebook. Wat volgt op %sql wordt een database-connection-URL genoemd, waarin je het volgende opgeeft:

• Dialect (postgres)
• Gebruikersnaam (postgres)
• Wachtwoord (postgres)
• Serveradres (localhost)
• Poortnummer (5432)
• Databasenaam (DaaCamp_Courses)

Vanaf nu kun je alles vanuit je Jupyter Notebook doen wat je in de pgAdmin-interface deed. Laten we beginnen met het maken van de tabel _datacampcourses met exact hetzelfde schema.

Maar voordat je dat doet, moet je de tabel droppen, omdat SQL je niet toestaat twee tabellen met dezelfde naam op te slaan. Je kunt een tabel droppen met:

%sql DROP table datacamp_courses;

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





[] 

De tabel _datacampcourses is nu uit PostgreSQL verwijderd en je kunt dus een nieuwe tabel met deze naam aanmaken.

%%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
);

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





[] 

Let op het gebruik van %sql en %%sql. Voor het uitvoeren van één enkele query kun je %sql gebruiken, maar als je meerdere query’s in één keer wilt uitvoeren, gebruik je %%sql.

Laten we wat records invoegen:

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

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





[] 

Bekijk de tabel om te controleren of de inserts naar verwachting zijn uitgevoerd:

%%sql
select * from datacamp_courses;

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

We gaan door in dezelfde flow. In de volgende stap werk je een record in de tabel bij:

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

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





[] 

Let goed op wanneer je met strings in SQL werkt. In tegenstelling tot traditionele programmeertalen moeten stringwaarden met enkele aanhalingstekens worden omgeven.

Laten we nu controleren of je update-query het beoogde effect had:

%%sql
select * from datacamp_courses;

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

Laten we nu een record verwijderen en controleren:

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

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





[] 

%%sql
select * from datacamp_courses;

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

Als het goed is heb je nu een duidelijk beeld van het uitvoeren van CRUD-bewerkingen in PostgreSQL en hoe je dit via Jupyter Notebook kunt doen. Als je bekend bent met Python en je wilt je database via je Python-code benaderen, dan kan dat ook. De volgende sectie gaat daarover.

Aan de slag met SQLAlchemy en combineren met SQL magic-commands

Voor deze sectie heb je het pakket SQLAlchemy nodig. Dit wordt doorgaans meegeleverd met de Anaconda-distributie. Je kunt het ook via pip installeren. Zodra het is geïnstalleerd, kun je het importeren met:

import sqlalchemy

Om met je databases te kunnen communiceren via SQLAlchemy moet je een engine aanmaken voor het betreffende RDBMS waarin je databases zijn opgeslagen. In jouw geval is dat PostgreSQL. SQLAlchemy laat je met één aanroep van create_engine() een engine voor het RDBMS maken, en die methode neemt een database-connection-URL aan, die je al eerder hebt gezien.

from sqlalchemy import create_engine

engine = create_engine('postgresql://postgres:postgres@localhost:5432/DataCamp_Courses')
print(engine.table_names()) # Laat je de namen zien van de tabellen in de database

['datacamp_courses']

Je ziet de tabel _datacampcourses, wat bevestigt dat het maken van de engine is gelukt. Laten we een eenvoudige select-query uitvoeren om de records van de tabel _datacampcourses te bekijken en op te slaan in een pandas DataFrame-object.

Je gebruikt de methode read_sql() (geleverd door pandas), die een SQL-querystring en een engine verwacht.

import pandas as pd

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

Je kunt het %sql magic-commando ook combineren binnen je Python-code.

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

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

Je bent al een heel eind gekomen!

Gefeliciteerd, geef jezelf een schouderklopje! In deze tutorial ben je niet alleen gestart met PostgreSQL, maar heb je ook gezien hoe je SQL-query’s op verschillende manieren kunt uitvoeren. Als volgende stap kun je dit webinar van David Robinson (Chief Data Scientist bij DataCamp) bekijken, waarin hij laat zien hoe hij SQL in zijn dagelijkse werk gebruikt. Bekijk ook onze tutorial How to Install PostgreSQL on Windows and Mac OS X. Wil je je SQL-vaardigheden verbeteren? Dan zijn de volgende bronnen een heel goed startpunt:

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