Veel data in organisaties zit gestructureerd opgeslagen in SQL databases. Indien je deze data wilt gebruiken voor analyses of het maken van modellen, is de combinatie van Python en SQL ideaal. Deze brug slaat het Python package SQLAlchemy. Beide talen hebben namelijk overlappende functionaliteit (data-analyse), maar er zijn ook grote verschillen. Als je Python en SQL combineert heb je het beste van twee werelden.
Het belangrijkste verschil is dat Python een programmeertaal is die overal voor gebruikt kan worden, terwijl SQL een taal is die alleen gebruikt kan worden om bewerkingen op een SQL database uit te voeren.
Ook interessant: Python vs SQL: wanneer gebruik je wat?
De combinatie van Python en SQL is vooral handig als je data uit een database wilt ontsluiten om er vervolgens in Python logica op toe te passen.
In deze blog zullen we je uitleggen hoe je vanuit Python kunt verbinden met een SQL-database en transacties kunt uitvoeren. Hiervoor gebruiken we het package SQLAlchemy.
Wat is SQLAlchemy?
SQLAlchemy is al jarenlang dé standaard om vanuit Python met SQL te verbinden. Dat heeft een reden: de functionaliteit is zeer uitgebreid en voor elk probleem biedt SQLAlchemy een oplossing. Zo kun je met elk type SQL-database verbinden. Ook de documentatie van SQLAlchemy is erg compleet.
Voordat we aan de slag gaan moet SQLAlchemy geïnstalleerd worden, dat doe je middels "pip install sqlalchemy". Uiteraard is het belangrijk dat je Python hebt geïnstalleerd. We werken in deze tutorial in Jupyter Notebook.
Er zijn ook andere packages om met SQL te verbinden, zoals pyodbc of PyPika, maar wij raden aan om die andere packages alleen maar te gebruiken als je daar een hele specifieke reden voor hebt. Heb je die reden niet, kies dan voor SQLAlchemy.
SQLAlchemy kan in twee varianten gebruikt worden: core en ORM. Core is de meest simpele versie, ORM staat voor Object Relational Mapping. Dit betekent dat tabellen in een SQL database worden vertaald naar objecten in Python, zodat je deze makkelijk kunt hanteren in Python. In deze tutorial gaan we in op de core.
In vijf stappen leggen we uit hoe je SQL kunt gebruiken i.c.m. Python:
- Verbinden met een database
- Een tabel aanmaken (create)
- Een tabel vullen met data (update)
- Data uit één of meerdere tabellen selecteren (select)
- Bestaande data wijzigen en verwijderen (update, delete)
1. Verbinden met een database
De eerste stap is het verbinden met een database. In onderstaand voorbeeld doen we dat met een sqlite-database, dat is een SQL database die in het geheugen van een Python proces wordt gecreëerd. De code die hierna volgt zal echter hetzelfde werken voor andere SQL databases (zoals bijvoorbeeld MySQL, MS SQL en PostgresSQL)
We importeren nu sqlalchemy en creëren een variabele engine.
from sqlalchemy import create_engine
connection_string = 'sqlite:///winkel.db'
engine = create_engine(connection_string, echo=False)
We hebben nu de engine aangemaakt, later zullen we deze gebruiken om daadwerkelijk een query uit te voeren. Hiervoor zijn twee inputs gebruikt, de variabele connection_string geeft aan met welke database verbonden moet worden, tevens gebruiken we echo=True om logs te printen voor elke query die we uitvoeren. Dit is niet noodzakelijk, maar wel handig voor het volgen van deze tutorial.
Indien je met een andere database wilt verbinden, bijvoorbeeld MySQL, kan dit door de connection string te wijzigen. Zie de documentatie van SQLAlchemy voor meer informatie.
2. Een tabel aanmaken
De volgende stap is het definiëren van een tabel. Dit kan of een bestaande tabel zijn of een nieuwe tabel. In onderstaand voorbeeld creëeren we een nieuwe tabel genaamd producten.
from sqlalchemy import Table, Column, Integer, Float, String, MetaData
meta = MetaData()
producten = Table(
'producten', meta,
Column('id', Integer, primary_key = True),
Column('product_naam', String),
Column('kostprijs', Float),
Column('verkoopprijs', Float),
)
meta.drop_all(engine)
meta.create_all(engine)
We doen hier drie dingen:
Allereerst definiëren we de variabele 'meta'. Deze variabele gebruik je om de variabele engine en de aan te maken tabel aan elkaar te koppelen.
Daarna gaan we de tabel definiëren door de tabelnaam en kolommen op te geven. Per kolom moet het datatype worden aangeven. Zoals je ziet zijn alle gebruikte variabelen (Table, Column, Integer, etc.) allemaal afkomstig uit het SQLAlchemy package. Indien de tabel al bestaat in de gebruikte database moeten de datatypes 100% overeenkomen met hoe je deze definieert.
De volgende stap is het daadwerkelijk aanmaken van de tabel, dit is alleen nodig als de tabel nog niet in de database bestaat. We gebruiken hiervoor meta.create_all(engine), indien je meerdere tabellen zou aanmaken worden deze allemaal aangemaakt middels dit commando.
3. Een tabel vullen met data (update)
Nu de tabel is aangemaakt kan deze worden voorzien van data.
insert = producten.insert().values(product_naam = 'Wasmand', kostprijs = '1.5', verkoopprijs = '4.5')
with engine.connect() as connection:
connection.execute(insert)
Allereerst maken we een 'insert' variabele waarin we de query definiëren. Als argumenten voor values() kan je de kolommen en de bijbehorende waardes opgeven.
Vervolgens maken we een verbinding met de database via engine.connect() (in combinatie met een with blok) en gebruiken we deze verbinding om de query ('insert') uit te voeren. De gebruikte notatie 'with engine.connect() as connection' zorgt ervoor dat de verbinding ook daadwerkelijk wordt gesloten als de query is uitgevoerd.
Als argument voor values() kan ook een dictionary of list gebruikt worden. Middels een list kan je meerdere rijen tegelijk aanmaken.
Enkele rij invoegen middels een dictionary:
insert = producten.insert().values({ 'product_naam': 'Wasmand', 'kostprijs': 2, 'verkoopprijs': 5.5 })
with engine.connect() as connection:
connection.execute(insert)
Meerdere rijen invoegen middels een list:
insert = producten.insert().values(
[
{ 'product_naam': 'Wasmand', 'kostprijs': 2, 'verkoopprijs': 5.5 },
{ 'product_naam': 'Droogrek', 'kostprijs': 10, 'verkoopprijs': 15 }
])
with engine.connect() as connection:
connection.execute(insert)
4. Data uit één of meerdere tabellen selecteren (select)
We hebben nu gezien hoe je data kan invoeren in een tabel. Deze data kan je middels een select query ook weer ophalen, bijvoorbeeld op de volgende manier:
s = producten.select()
with engine.connect() as connection:
result = connection.execute(s)
for row in result:
print(row)
Het is ook mogelijk filters toe te passen om slechts een deel van de data op te halen:
from sqlalchemy import select
s = select([producten]).where(producten.c.product_naam == 'Wasmand')
with engine.connect() as connection:
result = connection.execute(s)
for row in result:
print(row)
Daarnaast kan je ook middels andere notaties queries uitvoeren. Onderstaande code geeft bijvoorbeeld hetzelfde resultaat:
s = producten.select().where(producten.c.product_naam == 'Wasmand')
with engine.connect() as connection:
result = connection.execute(s)
for row in result:
print(row)
De data tonen we nu door middels een for loop te itereren over de resultaten. Op deze manier is het echter nog niet makkelijk om de data te bewerken. Gelukkig kan je middels het package Pandas ook een SQLAlchemy query uitvoeren en deze direct in een dataframe opslaan.
import pandas as pd
with engine.connect() as connection:
df = pd.read_sql('producten', connection)
df
Hier komt de overlap van Python (Pandas) en SQL naar voren, want zowel middels het package Pandas als SQL kan je groeperen, aggregeren en filteren. We kunnen bijvoorbeeld de gemiddelde verkoopprijs per product_naam op meerdere manieren berekenen:
Via pandas:
import pandas as pd
with engine.connect() as connection:
df = pd.read_sql('producten', connection)
df = df.groupby('product_naam').mean()
df
Via een SQL query:
import pandas as pd
query = """
select product_naam, avg(kostprijs) as Kostprijs, avg(verkoopprijs) as Verkoopprijs
from producten
group by product_naam
"""
with engine.connect() as connection:
df = pd.read_sql(query, connection)
df
Via SQLAlchemy:
from sqlalchemy import func, select
s = select([producten.c.product_naam, func.avg(producten.c.verkoopprijs)]).group_by(producten.c.product_naam)
with engine.connect() as connection:
result = connection.execute(s)
for row in result:
print(row)
5. Bestaande data wijzigen en verwijderen
Bestaande data wijzigen doen we door gebruik te maken van .update() in plaats van .select() of .insert().
In onderstaand voorbeeld zie je hoe je op basis van een conditie waardes kunt updaten. Voor het gemak weergeven we ook meteen de tabel in een dataframe om te zien wat het resultaat is.
update = producten.update().values({ 'kostprijs': 3, 'verkoopprijs': 6 }).where(producten.c.product_naam == 'Wasmand')
with engine.connect() as connection:
connection.execute(update)
df = pd.read_sql('producten', connection)
df
Op dezelfde manier kan je eveneens data verwijderen:
delete = producten.delete().where(producten.c.verkoopprijs < 10)
with engine.connect() as connection:
connection.execute(delete)
df = pd.read_sql('producten', connection)
df
Samenvatting
Je hebt gezien hoe je Python kunt gebruiken om in een SQL database tabellen aan te maken, data te voegen, data te wijzigen en data te verwijderen. Deze combinatie van Python en SQL is voor elke data-analist of data scientist erg waardevol.
Indien je meer wilt weten over hoe Python en SQL te combineren, kijk dan eens naar onze 1-daagse training Python en SQL. Wil je meer weten over hoe je deze data in Python verder kunt verwerken? Dan is mogelijk onze Python cursus voor data science interessant.
Download één van onze opleidingsbrochures voor meer informatie
Terry is afgestudeerd aan de TU Delft als ingenieur en heeft zich in zijn carrière beziggehouden met het optimaal benutten van data om bedrijfsprestaties te verbeteren. Dit heeft hij gedaan in verschillende rollen, als software ontwikkelaar en als data scientist.
