# Webshop met SQL en Flask
Iemand die ik ken is op haar werk aan het leren hoe SQL werkt.
Ze heeft de [tutorial](https://www.w3schools.com/sql/) gevolgd
van W3Schools. Nu wilde ze deze kennis toepassen op haar werk
door een overzicht te maken voor haar collega's van belangrijke
product data. Dat lukt voor een deel, maar de puntjes op de i
blijken moeilijk. Ik vertelde haar dat in alle IT banen die ik tot
nu toe heb gehad SQL voorkwam. Zelfs als de opdracht niet
helemaal lukt, dan heeft ze alsnog belangrijke kennis opgedaan.

Ik zei dat de meeste websites een interface zijn voor een
(SQL) database. Ik herinnerde me dat ik zelf ook een tutorial
heb gevolgd waarbij je een boekenwebsite maakte. Je kon boeken
toevoegen en reviews achterlaten. Voor mijn werk gebruiken we
vooral Python met een [ORM](https://en.wikipedia.org/wiki/Object%E2%80%93relational_mapping)
om met de database te communiceren. Dan hoef je eigenlijk nooit
SQL te schrijven. Toch denk ik dat het goed is om te weten hoe
de ORM achter de schermen werkt. Een webshopje lijkt mij een
ideaal project om SQL in actie te zien.

Ik begon met een schema schrijven voor een simpele SQL database,
gebaseerd op het shema van de W3Schools tutorial. Zo kan ze
voortbouwen op de kennis die ze al heeft opgedaan en hoef ik
het wiel niet opnieuw uit te vinden. De SQL implementatie die
ik kies is [SQLite](https://www.sqlite.org/). Deze komt standaard
op MacOS en de API voor Python komt meegeleverd. Zij gebruikt op werk
[SQL Server](https://www.microsoft.com/en-gb/sql-server) van
Microsoft. De eigenaardigheden die ik tegenkwam bij het maken van
het schema zetten me wel aan het twijfelen of het een goed idee
is om SQLite te gebruiken. Gelukkig is de syntax erg hetzelfde,
dus later overstappen is geen grote taak.

Laten we kijken naar wat SQL code. De eerste tabel die ik wil
aanmaken is voor de producten. Deze ziet er als volgt uit:

```sql
CREATE TABLE Producten (
    ProductID INTEGER PRIMARY KEY,
    ProductNaam TEXT,
    Prijs REAL
);
```

Het is goed om even stil te staan bij de drie types die in deze
code voorkomen. `INTEGER` is voor hele getallen en in dit geval
wordt deze gebruikt voor de `PRIMARY KEY`. SQLite gebruikt voor elke
rij in een tabel een uniek geheel getal. Deze kolom is altijd
gedefineerd en kan worden aangeroepen met de naam `rowid`. In het
geval dat een van de rijen in de `CREATE TABLE` een `INTEGER` is
en een `PRIMARY KEY` is dit een alias voor deze rij. Dit zorgt er
ook voor dat als we een rij in de tabel stoppen de waarde automatisch
wordt gegenereerd. `TEXT` is voor tekst, de maximale lengte van de
tekst is gedefineerd in de variabele `SQLITE_MAX_LENGTH` met een
standaardwaarde van een miljard. `REAL` is voor floating points. Het
gebruiken van een floating point voor een prijs is nit optimaal,
omdat het tot afrondingsfouten kan leiden, maar dat is een ander
probleem.

We voegen waardes toe aan de tabel met de volgende SQL:

```sql
INSERT INTO Producten(ProductNaam, Prijs) VALUES ('Ham', 2.99);
```

Als je de SQLite gebruikershandleiding leest kom je erachter dat
types niet worden gedwongen. Als ik een nummer wil gebruiken als
`ProductNaam` kan dat. Hier begint het gebruik van SQLite al te
jeuken. Het volgende zal bijvoorbeeld zonder problemen worden
geinterpreteerd door SQLite:

```sql
-- Voorbeeld van 'verkeerde' types toevoegen aan een tabel
INSERT INTO Producten(ProductNaam, Prijs) VALUES (1.23, 'Kaas');
```

Om na te gaan dat iets correcte SQL(ite) is gebruiken we de `sqlite3`
opdracht:

```sh
sqlite3 < db.sql
```

Als je na het evalueren van het script de SQLite prompt wil gebruiken
om te kijken naar de database kan je de `--init` vlag gebruiken:

```sh
sqlite3 --init db.sql
```

De `Producten` tabel wordt gevuld door de administrator van de webshop.
Een gebruiker van de webshop zal `Producten` alleen uitlezen en ze kunnen
bestellen. Daar is een andere tabel voor. Om alle `Producten` uit te lezen
gebruiken we een `SELECT`:

```sql
SELECT ProductID, ProductNaam, Prijs FROM Producten;
```

De andere twee tabellen die we voor onze webshop nodig hebben zijn voor de
klanten en de bestellingen. Deze zien er als volgt uit:

```sql
CREATE TABLE Klanten (
    KlantID INTEGER PRIMARY KEY,
    KlantNaam TEXT
);

CREATE TABLE Bestellingen (
    BestellingID INTEGER PRIMARY KEY,
    ProductID INTEGER,
    KlantID INTEGER,
    Aantal INTEGER,
    FOREIGN KEY(KlantID) REFERENCES Klanten(KlantID),
    FOREIGN KEY(ProductID) REFERENCES Producten(ProductID)
);
```

Een klant is alleen een id met een naam. Een bestellingen is een aantal
producten op een tijdstip voor een klant. Het enige wat nieuw is zijn de
`FOREIGN KEY` stukjes. Hier geven we aan dat de kolommen van de `Bestellingen`
tabel overeenkomende waardes heeft in de `Klanten` en `Producten` tabellen. 

We vullen de database met een aantal producten en klanten. Dat zou je met de
volgende code kunnen doen:

```sql
INSERT INTO Producten(ProductNaam, Prijs) VALUES ('Ham', 1.99);
INSERT INTO Producten(ProductNaam, Prijs) VALUES ('Kaas', 2.99);
INSERT INTO Producten(ProductNaam, Prijs) VALUES ('Jam', 0.99);

INSERT INTO Klanten(KlantNaam) VALUES ('IKEA');
INSERT INTO Klanten(KlantNaam) VALUES ("Jan's Super");
INSERT INTO Klanten(KlantNaam) VALUES ('Vermeer Foundation');
```

Nu gaan we onze webshop bouwen. Deze bestaat uit een formulier waar je
bestellingen kunt plaatsen en een bevestigingspagina. Een echte webshop heeft
natuurlijk meer functionaliteit. We gebruiken voor onze webshop
[Flask](https://flask.palletsprojects.com/en/3.0.x/), een Python web framework.

Flask kan worden geinstalleerd met `pip install flask`. Een minimale Flask web
server ziet er als volgt uit:

```python
from flask import Flask

app = Flask(__name__)

@app.route("/")
def index():
    return "Hallo wereld!"
```

Nu kan je naar `http://localhost:5000` en zie je de tekst `Hallo wereld!`.

De volgende stap is de verbinding met de database. Python gebruikt voor
communicatie met SQLite de [sqlite3](https://docs.python.org/3/library/sqlite3.html)
module. Deze wordt standaard met Python meegeleverd. Een query uitvoeren
ziet er als volgt uit:

```python
import sqlite3

con = sqlite3.connect("database.db")
cur = con.cursor()
res = cur.execute("SELECT ProductID, ProductNaam, Prijs FROM Producten;").fetchall()
con.close()

print(res)
```

Laten we onze webshop bouwen met een pagina. We kunnen een HTML string
terug geven met de `index` methode van onze Flask app en deze wordt dan
door de browser geinterpreteerd en mooi weergegeven. Als startpunt maken
we een formulier met een veld waarin we een aantal kunnen aangeven:

```html
<form action="/order" method="post">
    Aantal: <input name="aantal" type="number" min="1" value="1">
    <br>
    <input type="submit" value="Verzenden">
</form>
```

Het `action` veld geeft aan naar welk pad we het ingevulde formulier moeten
sturen en de `method` met welke HTTP methode we dat doen. We zien in de `form`
een `input` tag voor het aantal en een tweede `input` voor het verzenden van
het formulier. Daar de `name` te zetten kunnen we straks de waarde van het
formulier uitlezen door te indexeren met `aantal`.

We willen ook kunnen aangeven welk product de bestelling heeft en voor welke
klant deze is. We gebruiken daarvoor een `select` HTML element en vullen deze
met data uit de database. Voor de producten ziet dat er in Python als volgt uit.
Voor klanten doen we hetzelde. Neem aan dat in `res` het resultaat zit van de
SQL opdracht die we voor de `Producten` in het vorige stukje hebben uitgevoerd.
`res` bevat een lijst van rijen van de tabel.

```python
html = "<select name='product'>"
for (product_id, product_naam, prijs) in res:
    html += f"<option value={product_id}>{product_naam}: €{prijs}</option>"
html += "</select>"
```

Nu moeten we de data die we van het formulier krijgen verwerken. De data van
het formulier kunnen we in Flask krijgen met `request.form`. Dat ziet er als
volgt uit:

```python
from Flask import request

@app.route("/order", methods=("POST",))
def order():
    print(request.form["product"])
    print(request.form["klant"])
    print(request.form["aantal"])
```

De informatie van het formulier stoppen we in de database. Dat is onze eerste
webshop. De gehele code staat hieronder voor compleetheid:

```python
import os
import sqlite3
from flask import Flask, request

DB_NAME = "database.db"

app = Flask(__name__)

CREATE_DB = """
CREATE TABLE Producten (
    ProductID INTEGER PRIMARY KEY,
    ProductNaam TEXT,
    Prijs REAL
);

CREATE TABLE Klanten (
    KlantID INTEGER PRIMARY KEY,
    KlantNaam TEXT
);

CREATE TABLE Bestellingen (
    BestellingID INTEGER PRIMARY KEY,
    KlantID INTEGER,
    ProductID INTEGER,
    Aantal INTEGER,
    FOREIGN KEY(KlantID) REFERENCES Klanten(KlantID),
    FOREIGN KEY(ProductID) REFERENCES Producten(ProductID)
);

INSERT INTO Producten(ProductNaam, Prijs) VALUES ('Ham', 1.99);
INSERT INTO Producten(ProductNaam, Prijs) VALUES ('Kaas', 2.99);
INSERT INTO Producten(ProductNaam, Prijs) VALUES ('Jam', 0.99);

INSERT INTO Klanten(KlantNaam) VALUES ('IKEA');
INSERT INTO Klanten(KlantNaam) VALUES ("Jan's Super");
INSERT INTO Klanten(KlantNaam) VALUES ('Vermeer Foundation');
"""

# Ga na of de database bestaat, maak hem anders aan
if not os.path.isfile(DB_NAME):
    con = sqlite3.connect(DB_NAME)
    con.cursor().executescript(CREATE_DB)
    con.commit()
    con.close()

@app.route("/")
def index():
    con = sqlite3.connect(DB_NAME)
    cur = con.cursor()
    producten = cur.execute("SELECT ProductID, ProductNaam, Prijs FROM Producten;").fetchall()
    klanten = cur.execute("SELECT KlantID, KlantNaam FROM Klanten;").fetchall()
    con.close()

    html = ""
    html += "<h1>Bestellen</h1>"
    html += '<form action="/order", method="post">'

    html += 'Klant: '
    html += '<select name="klant">'
    for (klant_id, klant_naam) in klanten:
        html += f'<option value={klant_id}>{klant_naam}</option>'
    html += '</select>'
    html += "<br>"

    html += 'Product: '
    html += '<select name="product">'
    for (product_id, product_naam, prijs) in producten:
        html += f'<option value={product_id}>{product_naam}: €{prijs}</option>'
    html += '</select>'
    html += "<br>"

    html += "Aantal: "
    html += '<input name="aantal" type="number" min="1" value="1">'
    html += "<br>"

    html += '<input type="submit" value="Verzenden">'

    html += '</form>'

    return html


@app.route("/order", methods=("POST",))
def order():
    con = sqlite3.connect(DB_NAME)
    cur = con.cursor()
    cur.execute(
        "INSERT INTO Bestellingen(KlantID, ProductID, Aantal) VALUES (?, ?, ?);",
        (
            request.form["klant"],
            request.form["product"],
            request.form["aantal"],
        )
    )
    con.commit()
    con.close()

    return f"Bestelling met id {cur.lastrowid} succesvol verwerkt."
```