Datenbankprogrammierung mit Perl

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 bißchen Perl kann und ohne viel Lektüre mit einer Datenbank arbeiten will, der ist hier richtig aufgehoben.

Software: Perl, MySQL, DBI und DBD::mysql

Wer mit der Programmiersprache Perl Datenbanken ansprechen will, der braucht logischerweise Perl und eine MySQL-Datenbank. Ich gehe davon aus, dass beides bereits vorhanden ist. Der Vollständigkeit halber gibt es hier dennoch die Links, wo man die Software bekommt. Beides, Perl und MySQL gibt es kostenlos.

Optional: Für die kleinen Wartungsarbeiten zwischendurch bietet sich das Tool MySQL-Workbench an. Neue Nutzer, Schemata oder Tabellen sind damit im Handumdrehen angelegt.

Strawberry-Perl für Windows

Strawberry-Perl gibt es für Windows und kommt mit einer Reihe vorkompilierter Module (Darunter auch DBD::mysql). Damit kann man schnell starten!

MySQL-Datenbank

Gibt es hier für Windows, Linux oder Mac.

Perl-Schnellstart

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

MySQL-Workbench

Datenbanken und Tabellen anlegen leicht gemacht

Perl-Module DBI und DBD::mysql

Der Zugriff von Perl auf Datenbanken erfolgt über eine einheitliche Schnittstelle: DBI. Das Modul DBI benötigt aber noch einen Treiber für die MySQL-Datenbank: DBD::mysql. 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::mysql schon hat, kann man herausfinden, indem man folgende beiden Befehle in der Kommandozeile ausführt:

perl -e "use DBI 999;"
perl -e "use DBD::mysql 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 at -e line 1.

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

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

Perl-Module installieren

Ganz ausführlich wird die Thematik des Installierens von Perl-Modulen hier behandelt.

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.

Installiert man die MySQL-Datenbank 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

Datei: connect.pl

#!perl

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

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 = '';

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

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

print "rv = $rv\n";

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

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

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::mysql.

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).

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.

Artikeldatenbank in der MySQL-Workbench

Artikel-Tabelle

Datei: select_some_records.pl

#!perl

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

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 = '';

my $dbh = DBI->connect($dsn, $username, $password) 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());
my $rv = $sth->execute($title) or die('Kann Statement nicht ausführen: ' . DBI->errstr());

print "rv = $rv\n";

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 Textdatei 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:

Verbindung zur Datenbank aufbauen: wenn das nicht klappt, können wir nichts importieren.
Datei öffnen: auch hier gilt wieder: wenn das nicht klappt, brauchen wir nicht weiter machen.
Statement vorbereiten
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::mysql;
use FileHandle;

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 = ''; # Passwort hier einfügen

# Verbindung zur DB aufbauen
my $dbh = DBI->connect($dsn, $username, $password) 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($!);

# Befehl für Datenbank vorbereiten
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;
	
	my $rv = $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("rv bei [%s] = %s\n", $product_attributes[0], $rv);
	
}

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

exit(0);

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

Datei: products.tsv

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

Datenbank nach dem Einfügen der Daten...

Wenn die Daten korrekt übermittelt wurden, müsste die Datenbank 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. Außerdem sind alle Umlaute intakt, weil sowohl die Datenbank, als auch das Skript und die zu importierenden Daten utf-8-kodiert waren.

Daten aus einer Tabelle löschen

Dabei sollte man vorsichtig sein. Was weg ist, ist weg. Wenn man weiß, wie viele Datensätze eine Löschaktion betrifft, sollte das unbedingt in der LIMIT-Klausel angegeben werden. So wirken sich eventuelle Fehler nicht ganz so gravierend aus.

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.

Datei: delete_one_record.pl

#!perl

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

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 = ''; # Passwort hier einfügen

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

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

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

print "Ergebnis der Abfrage: $rv\n";

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

exit(0);

Tabelle nach der Löschaktion

Die Ausgabe von $rv durch print() birgt ein Problem. 5 Gummipunkte für den geneigten Leser, der weiß warum oder der es in der Dokumentation zu DBI nachlesen kann.

Daten aktualisieren

Das Aktualisieren von Datensätzen ist so ähnlich wie das Löschen von Datensätzen. Wenn nicht 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 ein UPDATE-Statement in SQL auszudrücken. Die Variante mit SET finde ich persönlich am flexibelsten. Man nennt das Feld und den Wert, der da rein soll.

Datei: update_one_record.pl

#!perl

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

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 = ''; # Passwort hier einfügen

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

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

my $sql = 'UPDATE products SET title = "Plan zum Abnehmen" WHERE product_id = ? LIMIT 1';
my $sth = $dbh->prepare($sql) or die('Kann Statement nicht vorbereiten: ' . DBI->errstr());
my $rv = $sth->execute($zu_aktualisierende_id) or die('Kann Statement nicht ausführen: ' . DBI->errstr());

print "Ergebnis der Abfrage: $rv\n";

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

exit(0);

Weitere Seiten zum Thema Perl und Datenbanken

Hier eine kleine Auflistung weiterer nützlicher Seiten zur Datenbankprogrammierung mit Perl

Perl's Database Interface

Übersicht der verschiedenen Themen auf der Perl-Homepage. Infos zu DBI, Treibern für verschiedene Datenbanken (MySQL, SQLite, Access, CSV, usw.) und ORMs.

DBI-Folien eines Perl-Workshops

Interessanter Foliensatz mit vielen Code-Schnipseln

Kurzeinführung in DBI

Alt aber gut!

Renées Perl-Blog

auch hier gibt es manchmal etwas zu Datenbanken (mit Perl)

Ende

Egal ob man mal schnell Daten aus einer Datei in eine Datenbank importieren muss oder ob man ausgereifte Datenbank-basierte Anwendungen schreiben möchte: Perl kann das. Wer etwas mehr Zucker möchte, kann sich ein ORM zulegen, weg lieber immer und immer wieder das gleiche SQL-Statement schreibt, kann bei DBI bleiben. Und natürlich gibt es auch Wege dazwischen (z.B. SQL::Abstract).

Wichtig zu wissen ist einzg, dass man das alles problemlos mit Perl bewerkstelligen kann - und zwar ganz einfach.