Archive for the ‘SQL’ Category.

Fehlerhafte SQLite Updates abfangen

Bei einem kleinen Projekt, an dem ich arbeite, fiel mir auf, dass fehlerhafte — d.h. syntaktisch richtige, aber inhaltlich falsche — SQLite Updates im Nirvana verschwinden. Danke Google und Stackexchange war die Lösung wie folgt: fehlerhafte SQL-Updates erzeugen keine Exception, hier muss man nach dem Update den rowcount prüfen, um die Anzahl der aktualisierten Zeilen zu checken. Das folgende Programm illustriert das ganze:

import sqlite3
 
conn = sqlite3.connect(':memory:')
conn.execute('create table if not exists mytable(id INTEGER PRIMARY KEY \
             AUTOINCREMENT, status TEXT, prio TEXT)')
 
today = '2020-08-06'
cursor = conn.cursor()
task = "Foobar"
 
cursor.execute('insert into mytable(status, prio) values (?, ?)',
               [task, today])
conn.commit()
 
my_id = 1 # For my_id = 1 it works, for any other number rowcount is 0
prio = 'A'
cursor = conn.cursor()
cursor.execute('UPDATE mytable SET prio = ? WHERE id = ?', [prio, my_id])
conn.commit()
print(cursor.rowcount)
 
try:
    cursor = conn.cursor()
    cursor.execute('select * from mytable')
    results = cursor.fetchall()
    for entry in results:
        print(entry)
except Exception as err:
    print('Listing failed: %s\nError: %s' % str(err))
 
finally:
    cursor.close()

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website

Normalisierung von Datenbanktabellen – Die 3. Normalform

This entry is part 3 of 3 in the series Data Warehouse, 3NF und Dimensional Modelling

In diesem Artikel geht es um die 3. Normalform. 3. Normalform bedeutet, dass die Daten

  • in der 2. Normalform sind und
  • kein Nichtschlüsselattribut transitiv von einem Kandidatenschlüssel abhängt.

Diese transitive Abhängigkeit erklärt man am besten an einem Beispiel:

CD

CD-ID Interpret Album Erscheinungsjahr Geburtsjahr
1234 Gabi Mustermann Gabi singt 2001 1963
2345 Max Mustermann Debütalbum 2001 1960
  • Aus der CD-ID folgt der Interpret oder die Interpretin
  • Aus dem Interpreten können wir nicht auf die CD schließen, denn jeder Interpret oder jede Interpretin kann mehr als eine CD veröffentlichen.
  • Aus dem Interpreten folgt aber das Geburtsjahr

Damit hängt das Geburtsjahr (ein Nichtschlüsselattribut) transitiv von der CD-ID (dem Schlüssel/Schlüsselkandidaten) ab. Transitiv bedeutet formell ausgedrückt: „Eine zweistellige Relation R heißt transitiv, wenn aus a R b und b R c stets a R c folgt.“

Wie löst man die transitive Abhängigkeit auf? Indem man weitere Tabellen erstellt. In unserem Beispiel erstellen wir eine Interpreten-Tabelle und verweisen in der CD-Tabelle nur noch auf den entsprechenden Schlüssel.

Interpret

Interpret-ID Name Geburtsjahr
1 Gabi Mustermann 1963
2 Max Muster 1960

CD

CD-ID Interpret-ID Album Erscheinungsjahr
1234 1 Gabi singt 2001
2345 3 Debütalbum 2001

Titel

CD-ID Tracknummer Titel
1234 1 Gabi singt laut
1234 2 Gabi singt leise
1234 3 Gabi singt weiter
2345 1 Von der Liebe
2345 2 Vom Leben
2345 3 Vom Ableben
2345 4 Duett mit Gabi

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website

Normalisierung von Datenbanktabellen – Die 2. Normalform

This entry is part 2 of 3 in the series Data Warehouse, 3NF und Dimensional Modelling

Im ersten Artikel dieser Reihe hatten wir uns die 1. Normalform angeschaut, in diesem Artikel soll es um die 2. Normalform gehen.

Die Wikipedia sagt zur 2. Normalform: „Eine Relation ist genau dann in der zweiten Normalform, wenn die erste Normalform vorliegt und kein Nichtprimärattribut (Attribut, das nicht Teil eines Schlüsselkandidaten ist) funktional von einer echten Teilmenge eines Schlüsselkandidaten abhängt.“

Das hört sich etwas sperrig an, am besten betrachten wir unser Beispiel, das wir in die 1. Normalform gebracht hatten, die Schlüsselspalten seien CD ID und Tracknummer.

CD-ID Interpret Album Erscheinungsjahr Geburtsjahr Tracknummer Titel
1234 Gabi Mustermann Gabi singt 2000 1963 1 Gabi singt laut
1234 Gabi Mustermann Gabi singt 2000 1963 2 Gabi singt leise
1234 Gabi Mustermann Gabi singt 2000 1963 3 Gabi singt weiter
2345 Max Mustermann Debütalbum 2001 1960 1 Von der Liebe
2345 Max Mustermann Debütalbum 2001 1960 2 Vom Leben
2345 Max Mustermann Debütalbum 2001 1960 3 Vom Ableben
2345 Max Mustermann Debütalbum 2001 1960 4 Duett mit Gabi

Wir erkennen, dass viele Informationen redundant sind, was leicht zu Dateninkonsistenzen führen kann. Ändern wir beispielsweise in einer Zeile den Albumtitel und nur den, so haben wir zwei unterschiedliche Albentitel für ein und das selbe Album. Außerdem hängen Albumtitel, Interpret und Erscheinungsjahr nur vom Schlüssel CD-ID ab, nicht von der Track-ID.

Man löst diese Probleme und Redundanzen auf, indem man die Daten auf zwei Tabellen aufteilt, CD und Titel.

CD

CD-ID Interpret Album Erscheinungsjahr Geburtsjahr
1234 Gabi Mustermann Gabi singt 2001 1963
2345 Max Mustermann Debütalbum 2001 1960

Titel

CD-ID Tracknummer Titel
1234 1 Gabi singt laut
1234 2 Gabi singt leise
1234 3 Gabi singt weiter
2345 1 Von der Liebe
2345 2 Vom Leben
2345 3 Vom Ableben
2345 4 Duett mit Gabi

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website

Normalisierung von Datenbanktabellen – Die 1. Normalform

This entry is part 1 of 3 in the series Data Warehouse, 3NF und Dimensional Modelling

Am besten lernt man, wenn man lehrt! Da ich mich momentan intensiver mit Themen wie Data Warehouse, Data Vault, 3NF und Dimensional Modelling beschäftige, versuche ich in den Artikeln dieser Reihe für mich ein wenig Licht in das Begriffsdunkel zu bringen. Quellen dieser Artikel sind die entsprechenden Wikipedia-Artikel, Google Ergebnisse und die 7. Auflage des Buchs „Fundamentals of Database Systems“ von Elmasri und Navathe, das es bei Amazon für knapp 45 Euro gibt.

In diesem Artikel soll es um die Normalformen gehen, siehe dazu den Wikipedia-Artikel für die Grundlagen.

Betrachten wir als Beispiel einen Satz Musikdaten, bestehend jeweils aus Interpret, Albumtitel, Erscheinungsjahr, Geburtsjahr des Interpreten, Titelliste:


Gabi Mustermann - Gabi singt, 2000, 1963, {1. Gabi singt laut, 2. Gabi singt leise, 3. Gabi singt weiter}
Max Mustermann - Debütalbum, 2001, 1960, {1. Von der Liebe, 2. Vom Leben, 3. Vom Ableben, 4. Duett mit Gabi} 

Diese Daten werden wir im Folgenden in die entsprechenden Normalformen bringen.

1NF

Für die 1. Normalform gilt (laut Wikipedia): Jedes Attribut der Relation muss einen atomaren/atomischen Wertebereich haben, und die Relation muss frei von Wiederholungsgruppen sein.

  • Atomar heißt, dass zusammengesetzte, mengenwertige oder geschachtelte Wertebereiche (also relationenwertige Attributwertebereiche) nicht erlaubt sind. Der Wertebereich keines Attributs einer Relation in 1NF kann in weitere (sinnvolle) Teilbereiche aufgespaltet werden.
  • Frei von Wiederholungsgruppen bedeutet, dass Attribute, die gleiche oder gleichartige Information enthalten, in eine andere Relation ausgelagert werden müssen.

Angewandt auf unser Beispiel bedeutet

  • atomar, dass wir sinnvoll aufteilbar Attribute wie Interpret-Albumtitel in entsprechende einzelne Attribute aufteilen müssen.
  • frei von Wiederholungsgruppen, dass die Liste der Lieddaten auf einzelne Zeilen verteilt werden muss.

Für unsere Musikdaten ergibt sich daher die folgende 1NF:

Interpret Album Erscheinungsjahr Geburtsjahr Tracknummer Titel
Gabi Mustermann Gabi singt 2000 1963 1 Gabi singt laut
Gabi Mustermann Gabi singt 2000 1963 2 Gabi singt leise
Gabi Mustermann Gabi singt 2000 1963 3 Gabi singt weiter
Max Mustermann Debütalbum 2001 1960 1 Von der Liebe
Max Mustermann Debütalbum 2001 1960 2 Vom Leben
Max Mustermann Debütalbum 2001 1960 3 Vom Ableben
Max Mustermann Debütalbum 2001 1960 4 Duett mit Gabi

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website

Overviews of SQL Joins (with LaTeX code)

Here’s a short overview of SQL joins, find the source code of the document attached to the PDF (use Adobe Reader to access it).

SQL Joins

\documentclass[12pt]{article}
\usepackage{xcolor}
\usepackage{arev}
\usepackage{attachfile}
 
 
\usepackage{tikz}
\usetikzlibrary{shapes,snakes}
\def\firstcircle{(0,0) circle (2cm)}
\def\secondcircle{(0:3cm) circle (2cm)}
 
\colorlet{circle edge}{blue!50}
\colorlet{circle area}{blue!20}
\colorlet{white area}{white}
 
\tikzset{
filled/.style={fill=circle area, draw=circle edge, thick},
outline/.style={draw=circle edge, thick},
white/.style={fill=white area, draw=circle edge, very thick}
}
 
\begin{document}
 
\textattachfile{\jobname.tex}{\LaTeX~Code}
 
\section{Inner Join}
 
Inner joins return those records, that are present in table 1 and table 2.
 
\begin{center}
\begin{tikzpicture}[scale=1, every node/.style={scale=1}] % Inner Join
    \begin{scope}
        \clip \firstcircle;
        \fill[filled] \secondcircle;
    \end{scope}
    \draw[outline] \firstcircle node {A};
    \draw[outline] \secondcircle node {B};
\end{tikzpicture}
\end{center}
 
\section{Left Join}
 
Every record from table 1 is returned, regardless if it has a matching record in table 2. If the record has a matching record in table 2 this record from table 2 is returned as well.
 
\begin{center}
\begin{tikzpicture}[scale=1, every node/.style={scale=1}] % Left Join
    \begin{scope}
        \clip \firstcircle;
        \draw[filled] \firstcircle node {A}
                                     \secondcircle;
    \end{scope}
    \draw[outline] \firstcircle
                   \secondcircle node {B};
\end{tikzpicture}
\end{center}
 
\clearpage
 
\section{Left Outer Join}
 
Those records from table 1 are returned, that do not have a match in table 2.
 
\begin{center}
\begin{tikzpicture}[scale=1, every node/.style={scale=1}] % Left Outer Join
    \draw[filled] \firstcircle node {A};
    \draw[white] \secondcircle node {B};
    \draw[outline] \firstcircle node {A};
\end{tikzpicture}
\end{center}
 
\section{Right Join}
 
Every record from table 2 is returned, regardless if it has a matching record in table 1. If the record has a matching record in table 1 this record from table 1 is returned as well.
 
\begin{center}
\begin{tikzpicture}[scale=1, every node/.style={scale=1}] % Right Join
    \begin{scope}
        \clip \secondcircle;
        \draw[filled] \firstcircle \secondcircle node {B};
    \end{scope}
    \draw[outline] \firstcircle node {A} \secondcircle;
\end{tikzpicture}
 
\end{center}
 
\clearpage
 
\section{Right Outer Join}
 
Those records from table 2 are returned, that do not have a match in table 1.
 
\begin{center}
\begin{tikzpicture}[scale=1, every node/.style={scale=1}] % Right Join 2
    \draw[filled]   \secondcircle node {B};
    \draw[white]  \firstcircle node {A};
    \draw[outline] \secondcircle node {B};
\end{tikzpicture}
\end{center}
 
 
 
\section{Theta Join}
 
Every row from dataset 1 is multiplied by every row of dataset 2. Usually a mistake unless you really want this Cartesian product.
 
\begin{center}
\begin{tikzpicture}[scale=1, every node/.style={scale=1}] % Theta
    \draw[filled] \firstcircle node {A} \secondcircle node {B};
\end{tikzpicture}
\end{center}
 
\clearpage
 
\section{Full Outer}
 
Returns all records from table 1 and table 2 regardless if they have matches in the other table. Records, that do match, are linked together via key. 
 
\begin{center}
\begin{tikzpicture}[scale=1, every node/.style={scale=1}] % Full Outer
    \draw[filled] \firstcircle node {A};
    \draw[filled] \secondcircle node {B};
    \begin{scope}
        \clip \firstcircle;
         \fill[white] \secondcircle;
        \draw[outline] \secondcircle;  
        \draw[outline] \firstcircle;  
    \end{scope}
\end{tikzpicture}
\end{center}
 
\end{document}

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website

SQL: Nur den aktuellsten Eintrag aus einer gejointen Tabelle laden

Ich habe seit ein paar Monaten wieder verstärkt Gelegenheit, meine rostigen SQL-Kenntnisse aufzufrischen. Kürzlich bin ich dabei an einer kleinen Herausforderung vorbeigekommen, die mich doch ein paar Minuten mehr beschäftigt hat. Gegeben sind zwei Tabellen, Namen und Adressen.

Namen

ID Name
1 Anna Amsel
2 Bernd Borstel
3 Cesar Causel
4 Dagmal Dack

Adressen

ID NameID Adresse
1 1 Amselweg 2
2 2 Badgasse 34
3 3 Chausseeweg 123
4 3 Dorfstraße 34
5 1 Alabasterpfad 32

ID ist jeweils der Primary Key, NameID der Fremdschlüssel von Adressen auf Namen. Ziel der Übung ist es, alle Namen mit der (sofern vorhanden) aktuellsten Adresse abzufragen.

Ein inner join bringt ganz klar das falsche Ergebnis, denn Dagmar Dack fehlt in der Ergebnismenge, Anna und Cesar sind zweimal vertreten.

SELECT n.Name, a.Adresse FROM Namen n JOIN Adressen a ON n.ID = a.NameID ;

Ein left join muss her, was das Ergebnis aber nur ein wenig besser macht: Dagmar ist jetzt drin, Anna und Cesar aber immer noch doppelt:

SELECT n.Name, a.Adresse FROM Namen n JOIN Adressen a ON n.ID = a.NameID ;

Beholfen habe ich mir dann mit einem Subselect:

SELECT n.Name, a.Adresse FROM Namen n
LEFT JOIN (SELECT MAX(ID) ID, MIN(NameID) NameID, MIN(Adresse) Adresse FROM Adressen GROUP BY NameID) a
ON n.ID = a.NameID

Ergebnis

Name Adresse
Anna Amsel Alabasterpfad 32
Bernd Borstel Badgasse 34
Cesar Causel Chausseeweg 123
Dagmal Dack NULL

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website

Nordwind-DB mit Python abfragen (Python, pandas, MySQL)

Hier ein einfaches Beispiel, wie man die Daten aus der Nordwind-Datenbank in einen pandas Dataframe bekommt. Der originale Code stammt von https://pythontic.com/pandas/serialization/mysql und wurde auf meine Datenbank angepasst.

# -*- coding: utf-8 -*-
 
from sqlalchemy import create_engine
import pymysql
import pandas as pd
 
sqlEngine       = create_engine('mysql+pymysql://nwread:northwind@192.168.0.60/northwind', pool_recycle=3600)
dbConnection    = sqlEngine.connect()
frame           = pd.read_sql("select * from products", dbConnection);
 
pd.set_option('display.expand_frame_repr', False)
print(frame)
 
frame.to_excel('r:/abc.xlsx')
 
dbConnection.close()

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website

Zugriff aus dem lokalen Netz auf die Nordwind-DB

Um aus dem lokalen Netz Zugriff auf die Nordwind-DB zu erhalten, muss man folgendes tun:

  • die Datei 50-server.cnf in /etc/mysql/mariadb.conf.d bearbeiten. Hier statt 127.0.0.1 die IP-Adresse des Servers eingeben. An dieses Interface wird MariaDB gebunden.
  • einen neuen Nutzer anlegen:

    CREATE USER 'nwread' IDENTIFIED BY 'northwind';

  • Zugriff aus dem Netz für diesen User erteilen:

    GRANT USAGE ON *.* TO 'nwread'@'%' IDENTIFIED BY 'northwind';

    Kann sein, dass dieses Statement schon ausreichend ist, das werde ich noch überprüfen. Ich habe aber zusätzlich noch ausgeführt:

  • Zugriff auf die Datenbank geben:

    GRANT ALL privileges ON `northwind`.* TO 'nwread'@'%';

Nach dem Neustarts des MariaDB-Servers klappt der Zugriff, beispielsweise über HeidiSQL, das ich sehr empfehlen kann.

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website

Die Nordwind-Datenbank für MySQL nutzen

Mit der Nordwind (Northwind) Datenbank gibt es eine ursprünglich für MS Access entwickelte Datenbank, an der sich viele SQL-Konstrukte üben lassen. Die Datenbank gibt es auch für MySQL, unter anderem unter https://github.com/dalers/mywind.

Hier nun die notwendigen Schritte für das Einspielen in eine MySQL/MariaDB-Datenbank:

  • git clone https://github.com/dalers/mywind.git
  • cd mywind
  • sudo mariadb < northwind.sql
  • sudo mariadb < northwind-data.sql

Mittels SQL kann man jetzt prüfen, ob die Daten vollständig geladen wurden:

  • sudo mariadb
  • use northwind
  • select count(*) from products;

bringt eine Zeilenzahl von 45 Zeilen.

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website

MariaDB installieren unter Linux

Für Apache Guacamole brauchte ich einen MySQL/MariaDB Server auf meinem Linux-Server, hier die Schritte zur Installation:

  1. sudo apt update
  2. sudo apt upgrade
  3. sudo apt install mariadb-server
  4. sudo mariadb

Standardmäßig kommt man als root sofort in die Datenbank, ohne ein Passwort einzugeben. Ich bin zwar den Hinweisen von https://kofler.info/root-login-problem-mit-mariadb/ gefolgt, um für den root-User ebenfalls ein Passwort festzulegen.

Aktuell wird aber jedes Passwort akzeptiert, wenn ich root bin, jedoch das festgelegte Passwort nicht akzeptiert, wenn ich als normaler User mysql -u root -p ausführe.

Sinnvolle mysql/mariadb-Befehle

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website