#!/usr/bin/env python
# coding: utf-8

# # Datenbankzugriff
# 
# Für die folgenden Beispiele wollen wir eine Tabelle Personen(nr, name) mit den Attributen `nr` und `Name` erstellen und jeweils wieder abfragen.

# In[13]:


TESTDATEN = [[101, "Peter"], 
             [102, "Petra"], 
             [103, "Hans"], 
             [104, "Claudia"]]


# ## Sqlite
# 
# Mit dem Python-Paket [sqlite3](https://docs.python.org/library/sqlite3.html)
# kann auf  sqlite-Datenbanken direkt zugegriffen werden. Es muss daher nicht
# installiert werden und ist bei jeder Python-Installation vorhanden.
# 
# In der folgenden Beispielanwendung erstellen wir eine Beispieltabelle,
# fügen ein paar Daten hinzu und geben diese wieder aus.

# In[22]:


import sqlite3


# Nach dem Import des `sqlite3`-Moduls können nun Daten hinzugefügt und anschließend wieder ausgelesen werden.
# Zunächst wird eine Verbindung erstellt und aus dieser ein `Cursor` generiert, mit dem auf die Datenbank zugegriffen werden kann.
# 
# Das SQL-Statement enthält ``?``. Diese werden durch die Werte in der Liste, die als zweites Argument übergeben
# werden, ersetzt. Hierdurch werden Sicherheitslücken wie SQL-Injection vermieden.

# In[32]:


conn = sqlite3.connect("datenbank.db")
c = conn.cursor()
c.execute("""CREATE TABLE IF NOT EXISTS 
    personen(nr int, name text)""")

for nr, name in TESTDATEN:
    # use paramters ? to avoid sql injection
    print("Füge Daten hinzu:", nr, name)
    c.execute(
        "INSERT INTO personen (nr, name) VALUES(?,?)", 
        (nr, name))

conn.commit()
conn.close()


# Manchmal ist es übersichtlicher, wenn man den Parametern in der SQL-Anweisung einen Namen gibt.
# Dann werden statt ``?`` die Parameter mit einem Namen wie ``:mein_parameter`` referenziert.

# In[33]:


conn = sqlite3.connect("datenbank.db")
c = conn.cursor()

c.execute(
  "INSERT INTO personen (nr, name) VALUES(:nr,:name)", 
  {'nr':105, 'name':'Hannes'})

conn.commit()
conn.close()


# Wir finden nun eine Datei `datenbank.db` im aktuellen Verzeichnis.

# In[4]:


get_ipython().system(' ls datenbank.db')


# Die Daten können wieder aus der Datei gelesen werden.

# In[34]:


conn = sqlite3.connect("datenbank.db")
cur = conn.cursor()
cur.execute("SELECT nr, name FROM personen")

print("Nr.\t Name")
for i, name in cur:
    print(i, "\t", name)

conn.close()


# Ändert man die `row_factory` einer sqlite-Verbindung, so kann man auch über den
# Namen auf Einträge zugreifen.

# In[36]:


conn = sqlite3.connect("datenbank.db")
conn.row_factory = sqlite3.Row
cur = conn.cursor()
cur.execute("SELECT nr, name FROM personen")
for row in cur:
    print(row['nr'], row['name'])


# Auch über das `sqlite3`-Kommandozeilentool lassen sich die Daten anzeigen.

# In[14]:


get_ipython().system(' sqlite3 datenbank.db "SELECT * FROM personen"')


# Die Standardausgabe kann auch hübscher dargestellt werden.

# In[15]:


get_ipython().system(' sqlite3 --box datenbank.db "SELECT * FROM personen"')


# ### Fremdschlüssel (Foreign Keys) in SQLite
# 
# [Foreign Keys](https://sqlite.org/foreignkeys.html) werden in SQLite standardmäßig nicht
# unterstützt und müssen pro Connection einmal über `PRAGMA foreign_keys = ON` aktiviert 
# werden.

# In[37]:


conn = sqlite3.connect("datenbank.db")
cur = conn.cursor()
cur.execute("""
    CREATE TABLE freundschaft (
        person1 int, 
        person2 int,
        FOREIGN KEY (person1) REFERENCES personen(nr),
        FOREIGN KEY (person2) REFERENCES personen(nr),
        PRIMARY KEY (person1, person2)
    )
    """)


# Wir befreunden die Personen Peter (101) und Petra (102) miteinander.

# In[7]:


cur.execute("INSERT INTO freundschaft VALUES (101,102)")
conn.commit()


# Leider wird nicht geprüft, ob die Personen, die befreundet werden sollen, überhaupt
# existieren. 
# 
# So würden wir bei der nächsten Anweisung eine Fehlermeldung erwarten, da die Personen
# mit den IDs -1 und -2 nicht existieren.

# In[8]:


cur.execute("INSERT INTO freundschaft VALUES (-1,-2)")
conn.commit()


# Wird die Unterstützung für Fremdschlüssel aktiviert, erfolgt die gewünschte Fehlermeldung.

# In[9]:


cur.execute("PRAGMA foreign_keys = ON")
cur.execute("INSERT INTO freundschaft VALUES (-3,-4)")
conn.commit()


# Wir löschen zum Schluss die Datenbank-Datei.

# In[30]:


get_ipython().system(' rm datenbank.db')


# ## MariaDB/MySQL
# 
# Für den Zugriff auf eine MariaDB/MySQL-Datenbank muss zusätzlich ein Connector für
# [MySQL](https://dev.mysql.com/doc/connector-python/en/connector-python-installation-binary.html)
# oder [MariaDB](https://mariadb.com/de/resources/blog/how-to-connect-python-programs-to-mariadb/)
# installiert werden. Im Folgenden konzentrieren wir uns auf die Verwendung des MariaDB-Connectors.
# 
# Für Windows gibt es ein eigenes [Installationsprogramm](https://mariadb.com/docs/connect/programming-languages/python/install/). Für Linux oder den RaspberryPi muss ggf. vorher 
# [MariaDB Connector/C](https://mariadb.com/docs/connect/programming-languages/c/install/#install-on-debian-ubuntu) 
# installiert werden.
# 
# Zunächst wird der Connector installiert:
# 
#     $ pip install mariadb  
# 
# Bei Rechteproblemen:
# 
#     $ pip install --user mariadb
# 
# Wenn alles geklappt hat, können wir im Anschluss das neue Modul importieren.

# In[4]:


import mariadb


# Wie stellen eine Verbindung mit der Datenbank her. Diesmal müssen wir Zugangsdaten und die Datenbank angeben.

# In[6]:


conn = mariadb.connect(user='root', password='',
                       host='127.0.0.1',
                       database='test')
conn.close()


# Nun können wir eine Tabelle `personen` in der Datenbank `test` erstellen.

# In[17]:


conn = mariadb.connect(user='root', password='',
                       host='127.0.0.1',
                       database='test')
cur = conn.cursor()
cur.execute("DROP TABLE IF EXISTS personen")
cur.execute("""CREATE TABLE IF NOT EXISTS 
               personen(nr int, name text)""")

conn.close()


# Wir benutzen das Kommandozeilentool `mysql` und lassen uns die Tabellen in der Datenbank `test` anzeigen.

# In[11]:


get_ipython().system(' mysql -uroot --execute="SHOW TABLES;" test')


# Nun können wir neue Daten in die Tabelle einfügen.

# In[18]:


conn = mariadb.connect(user="root", password="",
                       host="127.0.0.1", database="test")
cur = conn.cursor()
for nr, name in TESTDATEN:
    print("Füge Daten hinzu:", nr, name)
    cur.execute(
        """INSERT INTO personen (nr, name) VALUES(?,?)""", 
        (nr, name))
conn.commit()
conn.close()


# Schließlich werden die Daten wieder mit einer SELECT-Anweisung aus der Datenbank abgefragt. Wir nutzen zunächst den Kommandozeilen-Client.

# In[15]:


get_ipython().system(' mysql -uroot --execute="SELECT * FROM personen" test')


# Die Daten können natürlich auch mit Python abgerufen werden.

# In[20]:


conn = mariadb.connect(user="root", password="",
                       host="127.0.0.1", database="test")
cur = conn.cursor()
cur.execute("SELECT nr,name FROM personen")
for nr, name in cur:
    print(nr, name)
    
conn.close()


# ## NoSQL mit TinyDB
# 
# Jenseits von SQL gibt es noch verschiedene andere Ansätze, Daten in
# Datenbanken zu verwalten - hierbei werden keine Tabellen verwendet. Einer
# der Ansätze ist z.B. eine dokument-basierte Datenbank. Hier werden Daten
# als Dokumente wie z.B. JSON abgelegt. 
# [TinyDB](https://tinydb.readthedocs.io/) ist eine solche Datenbank für
# Python.

# ### Installation
# 
# Die Installation erfolgt auch hier einfach mit Hilfe von pip:
# 
#     $ pip install tinydb

# In[15]:


import tinydb


# Eine Datenbank kann mit der Klasse `TinyDB` leicht erzeugt werden. Wie bei sqlite werden die Daten direkt in einer Datei gespeichert.

# In[16]:


db = tinydb.TinyDB("db.json")


# Die Datei `db.json` wurd als Textdatei angelegt. Wir können den Inhalt daher leicht betrachten und ggf. verändern.

# In[17]:


get_ipython().system(' file db.json')


# In[18]:


get_ipython().system(' cat db.json')


# ### Daten hinzufügen
# 
# Noch ist die Datenbank leer. Füllen wir sie also mir ein paar Daten. Die Daten können einfache Dictionaries sein.

# In[19]:


db.insert({"type": "apple", "count": 7})


# In[20]:


db.insert({"type": "peach", "count": 3})


# Als Rückgabewert erhalten wir eine ID für den neuen Eintrag. Ein anschließender Blick in die Datei offenbart die neuen Inhalte.

# In[21]:


get_ipython().system(' cat db.json')


# Wir sehen einen Eintrag `_default`, der auf den Namen der Standardtabelle hinweist, mit der wir arbeiten. Dazu noch die hinzugefügten Einträge.

# ### Daten abfragen
# 
# Lassen wir uns alle Einträge einmal anzeigen.

# In[22]:


db.all()


# Über diese Liste aus Einzeleinträgen kann auch mit einer Schleife iteriert werden.

# In[23]:


for item in db:
    print(item, "Typ:", item["type"])


# Bis jetzt haben wir noch nicht viel gewonnen gegenüber einer direkten Speicherung eines Dictionaries. Dokumentenbasierte Datenbanken wie TinyDB bieten jedoch die Möglichkeit, komplexere Anfrage zu stellen.
# 
# Hierfür wird ein Query-Objekt erzeugt.

# In[24]:


query = tinydb.Query()


# In der `search`-Methode kann das Query-Objekt für Abfragen genutzt werden.

# In[25]:


db.search(query.type == "apple")


# In[26]:


db.search(query.count < 5)


# Mit `get` greifen wir auf das erste Ergebnis einer Suche zu. Jeder Eintrag in der Datenbank hat eine eindeutige ID, die über das Attribut `doc_id` abgefragt werden kann.

# In[27]:


peach = db.get(query.type == "peach")
peach.doc_id


# Mit der ID können Einträge auch direkt abgerufen werden.

# In[28]:


db.get(doc_id = 2)


# ### Daten aktualisieren
# 
# Auch Aktualisierungen werden über das Query-Objekt gemacht, wenn nicht alle Datensätze geändert werden sollen.

# In[29]:


db.update({"count": 10}, query.type == "apple")


# Wird keine Bedingung mit einem Query-Objekt verwendet, so werden alle Datensätze geändert.

# In[30]:


db.update({"new_key": "new_value"})


# In[31]:


db.all()


# Schließlich löschen wir wieder alle Einträge.

# In[32]:


db.purge()
db.all()


# Und löschen auch die Datenbankdatei.

# In[33]:


get_ipython().system(' rm db.json')


# Fortgeschrittene Anwendungen mit TinyDB werden im [Handbuch](https://tinydb.readthedocs.io/en/latest/usage.html) beschrieben. Hier werden Fragen zur Performance beantwortet und komplexere Anfragen beschrieben.