Datenbanken mit Perl/Tk

Datenbankprogrammierung mit Perl

Datenbanken mit Perl verwenden - Übersicht

Motivation

Perl & Datenbanken für Ungeduldige

Dieser Artikel bietet eine kleine Übersicht über die Verwendung von Datenbanken mit der Skriptsprache Perl. Es gibt zwar schon einige Einführungen zum Thema, aber oft muss man sich durch die ganzen Grundlagen lesen. Wer schon ein kleines bisschen Perl kann und ohne viel Lektüre mit einer Datenbank arbeiten will, der ist hier richtig aufgehoben.

Software: Perl, SQLite, DBI und DB Browser for SQLite

Wer mit der Programmiersprache Perl Datenbanken ansprechen will, der braucht logischerweise Perl und eine Datenbank. Dieser Artikel beschreibt die Verwendung von SQLite, man kann aber prinzipiell jede andere Datenbank auch nehmen. Ich gehe davon aus, dass Perl und der DB Browser for SQLite bereits installiert sind. Der Vollständigkeit halber gibt es hier dennoch die Links, wo man die Software bekommt. Sowohl Perl, die Perl-Module und den DB Browser for SQLite gibt es kostenlos.

Der DB Browser for SQLite wird nur verwendet, um die Datenbank anzulegen und ggf. Daten einzugeben. Das kann man auch alles mit einem Programm machen. Gerade zu Beginn ist so eine UI aber ganz praktisch, weil man direkt in die Datenbank reinschauen kann.

Strawberry-Perl für Windows

Strawberry-Perl gibt es für Windows und kommt mit einer Reihe vorkompilierter Module. Damit kann man schnell starten!

SQLite

Das Perl-Modul DBD::SQLite enthält sozusagen die Datenbank schon und stellt zugleich den Datenbank-Treiber dar.

DB Browser for SQLite

Grafische Oberfläche zur Anlage und Verwaltung von SQLite-Datenbanken.

Perl-Schnellstart

Wer noch keinen geeigneten Editor hat, sollte hier kurz reinschauen. Dort wird OPI (Open Perl IDE) beschrieben.

SQLite-Datenbank anlegen

Für dieses Tutorial legen wir mit dem DB Browser for SQLite eine SQLite-Datenbank an.
So geht's:

  1. DB Browser for SQLite starten
  2. File > New Database
  3. Ordner und Dateinamen aussuchen
  4. Erste Tabelle anlegen (products, wie nachfolgend aufgeführt)
  5. Write Changes

Als erste und einzige Tabelle legen wir exemplarisch eine Produkte-"Datenbank" an. Die besteht aus fünf Feldern:

Feld Beschreibung
product_id Eindeutige Produktnummer, wird automatisch hochgezählt
title Produktname, Textfeld
description Produktbeschreibung, Textfeld
price Dreirad
timestamp Feld für einen Zeitstempel. Aktuell für das Erstellungsdatum verwendet und automatisch über den Default-Wert befüllt

Perl-Module DBI und DBD::SQLite

Der Zugriff von Perl auf Datenbanken erfolgt über eine einheitliche Schnittstelle: DBI. Das Modul DBI benötigt aber noch einen Treiber für die SQLite-Datenbank: DBD::SQLite. Der Treiber übersetzt die allgemein formulierten Anweisungen von DBI in etwas, das die konkrete Datenbank versteht. Da das von Datenbank zu Datenbank verschieden ist, wird diese Funktionalität in Treibern gekapselt.

Ob man DBI und DBD::SQLite schon hat, kann man herausfinden, indem man folgende beiden Befehle in der Kommandozeile ausführt:

perl -e "use DBI 999;"
perl -e "use DBD::SQLite 999;"

Die Ausgabe müsste in etwa so aussehen:

> perl -e "use DBI 999;"DBI version 999 required--this is only version 1.615 at -e line 1.BEGIN failed--compilation aborted
	
> perl -e "use DBD::SQLite 999;"DBI version 999 required--this is only version 4.071 at -e line 1.BEGIN failed--compilation

Erscheint in der Konsole hingegen ein Hinweis der Art: "Can't locate DBI.pm in @INC ...", dann müssen die Module noch installiert werden.

Verbindung zur Datenbank

Wer eine Verbindung zu einer Datenbank aufbauen will, muss wissen, wo sich die Datenbank befindet und sich dort mit Benutzername und Passwort anmelden.

Bei SQLite-Datenbanken ist die Frage, wo die Datenbank liegt, verhältnismäßig einfach. Es ist der Pfad zur SQLite-Datei. Benutzername und Passwort sind immer leer.

Bei anderen Datenbanken wird i.d.R. ein Datenbank-Server installiert. Installiert man die MariaDB-Datenbank beispielsweise so, wie es der Installer vorschlägt, befindet sich die Datenbank unter 127.0.0.1 und dem Port 3306. Das ist die Adresse, unter der man die Datenbank finden kann. Der Benutzername ist per Voreinstellung normalerweise root und das Passwort leer. Sollte dem nicht so sein, muss man in der Anleitung der jeweiligen Datenbankversion nachschlagen, wie die Standard-Zugangsdaten lauten. Das root-Passwort sollte man selbstverständlich ändern.

Alle Tabellen auslesen

Nun kann es losgehen. Das Perl-Skript connect.pl wird natürlich vorbildlich utf-8-kodiert gespeichert.

Zu Beginn werden ein paar Pragmas und Module von Perl geladen. use strict und use warnigs erleichtern das Auffinden und Behandeln von Fehlern, use utf8 sorgt dafür, dass Umlaute in Stringkonstanten richtig erkannt werden. Außerdem brauchen wir die Module DBI und DBD::SQLite für die Interaktion mit der Datenbank.

Als Bonbon laden wir Data::Dumper. Mit diesem Modul sehen wir uns an, was DBI für unser SQL-Statement zurückliefert.

Der nachfolgende Code ist das Schema-F bei der Arbeit mit Datenbanken. Man stellt die Verbindung her (connect), bereitet die Abfrage or (prepare) und führt diese dann aus (execute). Bei jeder dieser Operationen werden die Rückgabewerte ausgewertet, um Fehler abzufangen.

Schlussendlich werden die Daten ausgelesen und verarbeitet (in diesem Beispiel einfach mit Data::Dumper ausgegeben). Das Programm connect.pl gibt die Namen der Tabellen in der Datenbank aus.

Datei: connect.pl

#!perl

use strict;
use warnings;
use utf8;
use DBI;
use DBD::SQLite;
use Data::Dumper qw/Dumper/;

my $dbfile = 'test-tb.sqlite';
my $dsn = "dbi:SQLite:$dbfile";
my $username = '';
my $password = '';


my $dbh = DBI->connect($dsn, $username, $password, {sqlite_unicode => 1})
    or die('Kann keine Verbindung zur Datenbank aufbauen. Fehler: ' . DBI->errstr());

my $sql = q~SELECT name FROM sqlite_master
  WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'
UNION ALL
SELECT name FROM sqlite_temp_master
  WHERE type IN ('table','view')
ORDER BY 1~;
my $sth = $dbh->prepare($sql) or die('Kann Statement nicht vorbereiten: ' . DBI->errstr());
$sth->execute() or die('Kann Statement nicht ausführen: ' . DBI->errstr());

while( my $row = $sth->fetchrow_hashref() ) {
    print Dumper($row);
}

$sth->finish();
$dbh->disconnect();
exit(0);

Daten aus einer Tabelle auslesen

Im Regelfall hat man Daten in einer Tabelle und möchte diese auslesen. Seltener ist es hingegen der Fall, dass man alle Daten auslesen möchte. Per WHERE-Klausel kann man im SQL-Code einschränken, welche Datensätze ausgelesen werden sollen.

Der Code muss wirklich kaum verändert werden. Der Dreier connect, prepare und execute funktioniert auch hier. Die Tabelle mit den Produkten muss natürlich vorher angelegt werden.

Datei: select_some_records.pl

#!perl
		
use strict;
use warnings;
use utf8;
use DBI;
use DBD::SQLite;
use Data::Dumper qw/Dumper/;

my $dbfile = 'test-tb.sqlite';
my $dsn = "dbi:SQLite:$dbfile";
my $username = '';
my $password = '';

my $dbh = DBI->connect($dsn, $username, $password, {sqlite_unicode => 1})
    or die('Kann keine Verbindung zur Datenbank aufbauen. Fehler: ' . DBI->errstr());

# -- Dieses Feld könnte eine Benutzereingabe sein, z.B. aus einem Suchformular auf einer Webseite.
my $title = '1"); DROP TABLE users; --';

my $sth = $dbh->prepare('SELECT * FROM products WHERE title = ?') 
    or die('Kann Statement nicht vorbereiten: ' . DBI->errstr);
$sth->execute($title) or die('Kann Statement nicht ausführen: ' . DBI->errstr());

while( my $row = $sth->fetchrow_hashref() ) {
    print Dumper($row);
}

$sth->finish();
$dbh->disconnect();
exit(0);

Daten in eine Tabelle einfügen

Nachstehender Quellcode zeigt, wie eine Text-Datei mit Artikeldaten geöffnet und ausgelesen wird. Die Daten werden in eine vorher angelegte Tabelle eingetragen. Als Feldbegrenzung wird der Tabulator verwendet (.tsv-Datei). Im Wesentlichen werden folgende Arbeitsschritte ausgeführt:

  1. Verbindung zur Datenbank aufbauen: wenn das nicht klappt, können wir nichts importieren.
  2. Datei öffnen: auch hier gilt wieder: wenn das nicht klappt, brauchen wir nicht weiter machen.
  3. Statement vorbereiten
  4. Datei zeilenweise durchlaufen
    1. Zeilenumbruch entfernen
    2. Einzelne Felder aufteilen
    3. Statement ausführen

Datei: insert_some_records.pl

#!perl
		
use strict;
use warnings;
use utf8;
use DBI;
use DBD::SQLite;
use Data::Dumper qw/Dumper/;
use FileHandle;

my $dbfile = 'test-tb.sqlite';
my $dsn = "dbi:SQLite:$dbfile";
my $username = '';
my $password = '';

my $dbh = DBI->connect($dsn, $username, $password, {sqlite_unicode => 1})
    or die('Kann keine Verbindung zur Datenbank aufbauen. Fehler: ' . DBI->errstr());

# In dieser Datei stehen die Artikeldaten
my $product_file = 'products.tsv';

# Datei zum Lesen öffnen
my $fh = FileHandle->new($product_file, "<:encoding(UTF-8)") or die($!);

# Statement vorbereiten
my $sql = 'INSERT INTO products VALUES (NULL, ?, ?, ?, CURRENT_TIMESTAMP)';
my $sth = $dbh->prepare($sql) or die('Kann Statement nicht vorbereiten: ' . DBI->errstr());

# Produkt-Datei zeilenweise auslesen und Datensatz in Datenbank schreiben
while( my $product_tsv_line = $fh->getline() ) {
    chomp($product_tsv_line); # Zeilenumbruch am Ende entfernen

    # Bei Tabstops aufspalten
    my @product_attributes = split(m/\t/, $product_tsv_line);

    # Überspringe Datensatz, wenn nicht genau 3 Felder zum Einfügen da sind
    next if $#product_attributes != 2;

    $sth->execute(
        $product_attributes[0], # Produkt-Name
        $product_attributes[2], # Beschreibung
        $product_attributes[1], # Preis
    ) or die('Kann Statement nicht ausführen: ' . DBI->errstr());

    # Ausgabe zur Info
    printf($product_attributes[0] . "\n");
}

# Alles ist eingefügt, jetzt noch schnell aufräumen
$sth->finish();
$dbh->disconnect();
$fh->close();

exit(0);

Die TSV-Datei besteht aus drei Zeilen mit je einem Datensatz.

Datei: products.tsv

Knusperkekse	2.99	Kekse aus kusprigem Keksteig
Fruchtsaft	1.99	Saft aus frischen Früchten
"Diätplan"	0.00	Tipps zum Abnehmen - kostenlos!

Datenbank nach dem Einfügen der Datensätze

Wenn die Daten korrekt übermittelt wurden, müsste die Datenbank ähnlich wie in der nebenstehenden Abbildung aussehen. Zu beachten ist, dass das Skript zum Einfügen der Datensätze nicht prüft, ob bereits ein solcher Datensatz vorhanden ist.

Durch die Verwendung von Platzhaltern (die Fragezeichen) konnten auch Datensätze mit Sonderzeichen problemlos eingefügt werden. Und weil die Datenbank, das Skript und die zu importierenden Daten utf-8-Kodiert waren, sind alle Umlaute intakt.

Daten aus einer Tabelle löschen

Beim Löschen sollte man vorsichtig sein. Was weg ist, ist weg.

Der nachstehende Perl-Code zum Löschen eines Datensatzes aus einer Datenbank setzt voraus, dass die ID des Datensatzes, der gelöscht werden soll, bereits bekannt ist. In einem richtigen Programm wurde diese Information z.B. durch ein Formular auf einer Webseite erfragt oder in einem Ajax-Request mitgegeben.

Datei: delete_one_record.pl

#!perl

use strict;
use warnings;
use utf8;
use DBI;
use DBD::SQLite;

my $dbfile = 'test-tb.sqlite';
my $dsn = "dbi:SQLite:$dbfile";
my $username = '';
my $password = '';

my $zu_loeschende_id = 2; # Der Datensatz mit der ID 2 soll gelöscht werden.

my $dbh = DBI->connect($dsn, $username, $password, {sqlite_unicode => 1})
    or die('Kann keine Verbindung zur Datenbank aufbauen. Fehler: ' . DBI->errstr());

my $sql = 'DELETE FROM products WHERE product_id = ?';
my $sth = $dbh->prepare($sql) or die('Kann Statement nicht vorbereiten: ' . DBI->errstr());
$sth->execute($zu_loeschende_id) or die('Kann Statement nicht ausführen: ' . DBI->errstr());

$sth->finish();
$dbh->disconnect();

exit(0);

Tabelle nach der Löschaktion

Das Ergebnis ist wenig überraschend: der Datensatz mit der ID 2 wurde gelöscht.

Tipp: wer vorab weiß, wie viele Datensätze gelöscht werden, kann das DELETE-Statement auch mit einer LIMIT-Klausel einschränken.

Daten aktualisieren

Das Aktualisieren von Datensätzen ist so ähnlich wie das Löschen von Datensätzen. Wenn nicht durch die WHERE-Klausel eingeschränkt wird, was aktualisiert werden soll, werden alle Datensätze aktualisiert. Der Tipp mit der LIMIT-Angabe funktioniert auch hier.

Es gibt verschiedene Möglichkeiten, um ein UPDATE-Statement in SQL auszudrücken. Die Variante mit SET ist recht angenehm. Man nennt das Feld und den Wert, der da rein soll.

Datei: update_records.pl

#!perl

use strict;
use warnings;
use utf8;
use DBI;
use DBD::SQLite;

my $dbfile = 'test-tb.sqlite';
my $dsn = "dbi:SQLite:$dbfile";
my $username = '';
my $password = '';

my $dbh = DBI->connect($dsn, $username, $password, {sqlite_unicode => 1})
    or die('Kann keine Verbindung zur Datenbank aufbauen. Fehler: ' . DBI->errstr());

my $sql = 'UPDATE products SET price=(price+1) WHERE CAST(price as INTEGER) < 5';
my $sth = $dbh->prepare($sql) or die('Kann Statement nicht vorbereiten: ' . DBI->errstr());
$sth->execute() or die('Kann Statement nicht ausführen: ' . DBI->errstr());

$sth->finish();
$dbh->disconnect();

exit(0);

Andere Datenbanken

Neben SQLite gibt es natürlich noch andere Datenbanken. MariaDB ist eine der populären, die auch in der Webentwicklung häufiger angetroffen wird.

Die Datenbanken unterscheiden sich in dem, was sie können (Überraschung). Sofern man keine speziellen Datenbank-Features einsetzt, beschränken sich die Unterschiede auf den SQL-Dialekt und die Initialisierung des Datenbank-Handles.

Ein Vergleich zwischen SQLite und MariaDB

SQLite

#!perl

use strict;
use warnings;
use utf8;
use DBI;
use DBD::SQLite;
use Data::Dumper qw/Dumper/;

# -- Zugangsdaten
my $dbfile = 'test-tb.sqlite';
my $dsn = "dbi:SQLite:$dbfile";
my $username = '';
my $password = '';

# -- Verbindung aufbauen
my $dbh = DBI->connect(
    $dsn, $username, $password,
    {sqlite_unicode => 1}
) or die('Kann keine Verbindung zur Datenbank '
       . 'aufbauen. Fehler: ' . DBI->errstr());

# -- Tabellen auflisten
my $sql = q~SELECT name FROM sqlite_master
  WHERE type IN ('table','view')
  AND name NOT LIKE 'sqlite_%'
UNION ALL
SELECT name FROM sqlite_temp_master
  WHERE type IN ('table','view')
ORDER BY 1~;

my $sth = $dbh->prepare($sql) or die('Kann Statement nicht vorbereiten: ' . DBI->errstr());
$sth->execute() or die('Kann Statement nicht ausführen: ' . DBI->errstr());

while( my $row = $sth->fetchrow_hashref() ) {
    print Dumper($row);
}

$sth->finish();
$dbh->disconnect();
exit(0);

MariaDB

#!perl

use strict;
use warnings;
use utf8;
use DBI;
use DBD::mysql;
use Data::Dumper qw/Dumper/;

# -- Zugangsdaten
my $server = '127.0.0.1';
my $port = '3306';
my $schema = 'test';
my $dsn = "DBI:mysql:$schema:$server:$port;mysql_enable_utf8=1";
my $username = 'root';
my $password = '';

# -- Verbindung aufbauen
my $dbh = DBI->connect(
    $dsn, $username, $password
) or die('Kann keine Verbindung zur Datenbank '
       . 'aufbauen. Fehler: ' . DBI->errstr());
    or die('Kann keine Verbindung zur Datenbank aufbauen. Fehler: ' . DBI->errstr();

# -- Tabellen auflisten
my $sth = $dbh->prepare('SHOW TABLES') 
    or die('Kann Statement nicht vorbereiten: ' . DBI->errstr());
$sth->execute() or die('Kann Statement nicht ausführen: ' . DBI->errstr());

while( my $row = $sth->fetchrow_hashref() ) {
    print Dumper($row);
}

$sth->finish();
$dbh->disconnect();
exit(0);
Top