Mit Python in MySQL Datenbanken arbeiten

Wer mit Python Daten erfassen, generieren oder speichern möchte, kann dies in Textdateien, Variablen oder eben einer Datenbank tun.

Letzeres ist die eleganteste und sicherste Methode. Zudem kann hier eine fast unbegrenzte Menge an Datensätzen gespeichert und abgerufen werden.

Ich möchte das am Beispiel der MySQL Datenbank erklären. Ein Vorteil ist hierbei, dass ich mit Python die Daten erfassen kann und im Browser per php die Datenbank darstellen kann.

Als Beispiel nutze ich den altbewährten RFID Türöffner, den ich bereits hier vorgestellt habe.

In der Datenbank sollen nun die Besitzer einer Zugangskarte hinterlegt werden, die zugehörige Kartennummer, die Anzahl der Zutritte sowie der jüngste Zugang der Person.

Um das Thema etwas weiterzuspinnen, speichere ich zusätzlich noch einen Geldbetrag in jedem Datensatz. Hier kann man sich z.B. Kaffeautomaten vorstellen, die den Betrag vom Kartenguthaben abziehen.

Meine Tabelle sieht nun wie folgt aus:

Name Kartenummer Letzter Zugang Zutritte Guthaben
Christoph 53877037723976 05.06.2013 11:20:54 12 5.2
Hans 97377575723873 07.06.2013 21:26:54 35 6.6

Das System besteht aus 2 Teilen:

– Der administrative Teil. (Nutzer hinzufügen, Nutzer löschen, Guthaben ändern) den ich über phpmyadmin realisiere

– Das Hauptprogramm, welches im Normalbetrieb dauerhaft läuft, (Zugangskontrolle, Tür öffnen, Zugänge mitloggen) in Python.

Voraussetzungen für dieses Projekt:

– LAMP Server mit pyphmyadmin

– RFID Reader

– LCD Display

– Den Aufbau der Geräte nach dem ersten Tutorial

– Die Datei funktionen.py aus dem ersten Tutorial im selben Ordner.

Ich starte mit dem Python Programm.

Im alten Programm, werden Benutzer und Kartennummer im Code in einem Dictionary gespeichert. Das ersetze ich nun durch die MySQL DB.

Voraussetzung ist eine vorhandene Datenbank. Ich nenne sie “rfid”und baue sie nach dem oben gennanten Schema auf.

Hierzu wird phpmyadmin aufgerufen und mit folgenden SQL Befehl die Struktur in einem Rutsch erstellt:

CREATE TABLE IF NOT EXISTS rfid.user (
  nameid int(11) NOT NULL AUTO_INCREMENT,
  name varchar(45) DEFAULT NULL,
  cid text,
  lastacc varchar(255) DEFAULT NULL,
  count int(255) DEFAULT 0,
  money decimal(65,2) DEFAULT 0.00,
  PRIMARY KEY (nameid)
)

 

INSERT INTO user (nameid, name, cid, lastacc, count, money) VALUES
(2, 'Christoph', '68173919907565', '20138301748', 3, 7.60),
(3, 'Hans', '53877037723976', '20138301739', 35, 1.40);

Hiermit wurde nun eine Tabelle “user” in der Datenbank “rfid” erstellt. Anschließend werden bereits 2 Datensätze eingepflegt. Die Datensätze enthalten bereits eine Kartennummer, einen Datumstempel, einige Zugänge sowie Guthaben.
Wer eigene Zutrittsberechtigte anlegen möchte, lässt den zweiten Teil des SQL Dumps weg und erstellt den Inhalt der Tabelle selbst. (Oder passt den SQL Befehl an)

Falls noch nicht geschehen, muss noch die Python Library mysqldb installiert werden. Das geht fix mit:

apt-get install python-mysqldb

Jetzt kann die Python Datei erstellt werden. Als Grundlage nutze ich auch das Python Script des alten Tutorials.

Das zuvor installierte Paket wird nun zuerst einmal geladen:

import mysqldb

Dann können wir bereits eine Verbindung zur Datenbank herstellen.

 db = MySQLdb.connect(host="localhost", user="root", passwd="123456", db="rfid")

Als nächstes definieren wir den Cursor und benennen die Funktion der Einfachheit halber mit einer Variable.

cur = db.cursor()

Nun wird der Abfrageteil des alten Scripts durch den neuen ersetzt und die Berechnungen für die Zeitstempel, Zähler und das Guthaben eingefügt. Das Guthaben ist in diesem Beispiel eher zur Übung gedacht, da ich noch keinen kostenpflichtigen Kaffeeautomaten besitze 😉
In meinem Script “kostet” jeder Zutritt einen gewissen Betrag, damit man beispielhaft die Verwendung einer solchen Funktion erkennen kann.

Update 20.08.2015:

Aufgrund einiger Nachfragen habe ich das RFID-Kapitel aus dem Buch “Raspberry Pi – Das umfassende Handbuch” also Leseprobe erstellen dürfen. Darin wurde das Thema ebenfalls behandelt und etwas überarbeitet. Bis ich zur Überarbeitung dieses Beitrags komme, verweise ich bei Problemen vorerst auf das Buchkapitel:

Leseprobe_RFID-Tueroeffner als PDF herunterladen.

 

Weitere Erläuterungen zu den einzelnen Schritten habe ich direkt ins fertige Script eingebaut:

#!/usr/bin/python
from sys import exit
import RPi.GPIO as GPIO
from time import *
from funktionen import *
import MySQLdb
import datetime

opentime = 5 # Zeit in der die Tuer geoeffnet bleibt
Preis = 0.8 # Zugangspreis

while True:
    # Verbindung zur DB herstellen. user und passwd muessen angepasst werden.    
    db = MySQLdb.connect(host="localhost", user="root", passwd="30985", db="rfid") 

    # Cursorfunktion in Variable schreiben    
    cur = db.cursor()

    lcd ("Karte","einscannen") # Syntax: lcd("Zeile 1 max 16 Zeichen", "Zeile 2 max 16 Zeichen")
    for i in range(1):

       card  = rfid() 

    #SQL Abfrage. Hier werden die Werte aus "name, count und money" fuer den Datensatz mit der eingescannten Kartennnummer (cid) ausgelesen
    a= cur.execute("SELECT name,count,money FROM user WHERE cid = %s" ,card )

    #Falls Karte nicht in DB eingetragen, wird der Zutritt verweigert und die Schleife staret neu
    if a == 0:
        lcd ("Karte nicht","erlaubt")
        continue

    # Wenn Kartennnummer in DB gefunden wurde, werden die 3 Werte zur weiteren Verarbeitung in Variablen geschrieben    
    else:
        for row in cur.fetchall():
            name = str(row[0])
            counter = int(row[1])
            guthaben = float(row[2])

    #  Funktion um die aktuelle Zeit und Datum in Variablen zu schreiben      
            lt= localtime()
            jahr, monat, tag,h,m,s = lt[0:6]   

    # Die Anzahl der Zutritte wird mit 1 addiert     
            counter= counter +1 

    # Der "Zugangspreis" wird vom vorhandenen Guthaben abgezogen        
            guthaben = guthaben - Preis

    #Falls Guthaben zu gering ist, wird der Zutritt ebenfalls verweigert und die while Schleife startet neu        

            if guthaben < Preis:

                lcd ("Zu wenig","Guthaben")
                continue
            else:  
    # Ist genung Guthaben vorhanden, wir der Datensatz angepasst. Hier werden die Werte lastacc (Letzer Zugang), count und money (Guthaben) mit den berechneten Werten überschrieben
                a= cur.execute("UPDATE user SET lastacc = %s, count =%s, money = %s WHERE cid = '%s'" % (str(jahr)+str(monat)+str(tag)+str(h)+str(m),counter,guthaben,card))

    # Daten in DB sichern, Cursorfunktion beeden, Datenbankverbindung trennen
                cur.close()
                db.commit()
                db.close ()   

    # Tür (Relais an GPIO 21) wird geöffnet und nach X Sekunden wieder geschlossen
                lcd ("Guten Tag",name)
                sleep(1)
                lcd ("Restguthaben",guthaben)
                sleep(1)
                lcd ("Tuer offen", "fuer "+str(opentime)+" Sek")
                GPIO.setwarnings(False)
                GPIO.setmode(GPIO.BCM)
                GPIO.setup(21,GPIO.OUT)
                GPIO.output(21, True)
                sleep (opentime)
                GPIO.output(21, False)
                lcd ("Tuer wieder", "verriegelt")
                sleep(1)
    # While Schleife beginnt von vorn und wartet auf einscannen einer Karte

GPIO.cleanup()

Somit haben wir bereits viele Befehle und Funktionen von Python und MySQL abgedeckt und etwas praktisch ausprobiert.

Diese Grundlagen sollten bereits reichen um das System um viele weitere Funktionen zu erweitern.

Zu guter Letzt werfen wir einen Blick in phpmyadmin, wo der “Hausmeister” die Benutzer verwalten kann.

  phpmyadmin_struktur phpmyadmin_inhalt phpmyadmin_edit

Auf den Bildern kann man erkennen, dass man in phpmyadmin die komplette Datenbank verwalten kann. Es kann die Struktur sowie auch der Inhalt der Datensätze angepasst werden. So können ganz einfach neue Nutzer mit der entsprechenden Kartennummer eingefügt werden, bzw vorhandenen Nutzer die Berechtigung entzogen werden.

Bisher sind mir keine Bugs im Script aufgefallen. Falls jemand die Sache nachbaut und Fehler findet, meldet sie mir bitte.
Mir ist aufgefallen, dass teilweise beim Einfügen des Codes in den Blog einige Semikolons verloren gehen. Auch hier bin ich dankbar über jeden Hinweis.

15 Gedanken zu “Mit Python in MySQL Datenbanken arbeiten

  1. Hallo ich habe dienen RFID Türöffner nachgebaut und will jetzt noch das selbe mit der MySQL Datenbank "nachrüsten". Meine grösste frage ist läuft das ganze auch ohne LAMP server sprich offline, auslesen wollte ich die Datenbank dann mit einen Bildschirm denn ich brauche das ganze in einem Getränkeautomat.

    • Hey,
      also die Datenbank läuft ja auf dem Pi. Hälst Du dich an mein Beispiel, brauchst du keine Internetverbindung. Kann also auch Offline laufen. Nur wenn du die Datenbank bei einem Webhoster nutzen willst, brauchst du natürlich eine Internetverbindung.

      MySQL ist für diesen Anwendungsfall zwar gut nutzbar, aber auch etwas "overpowered".
      Generell kannst du die werte auch in Textdateien speichern, etc.

      Gruß
      Christoph

      • Okay ja das dachte ich mir auch schon ,blos ich finde kein anhaltspunkt wie ich das anstellen soll mit dem in .txt schreiben. Ich bin eher ein Anfänger auf diesen gebiet.

  2. Hallo,

    ich würde gerne Deinen Code probieren, aber leider kann ich Dein Archiv rfiddoor.rar mit meinem Raspberry Pi nicht auspacken. Ich schaffe es leider nicht, die unpack utility zu installieren. Darf ich die Funktionen.py bitte als Sourcecode bekommen?

    Vielen Dank und beste Grüße,

    Dirk aus Stuttgart

  3. Hey,

    Also der aufbau und das "zum Laufen bringen" hat alles super geklappt.

    Nun habe ich folgende 2 Probleme:

    1. Wenn ich die Karte länger drüber halte erkennt er sie und geht die entsprechenden Schritte durch. Nur sobald er wieder auf das einscannen der Karte wartet erkennt er sie (ohne dass sie davor gehalten wird) erneut manchmal 4-5 mal…

    Anscheinend puffert der pi den serial Eingang hast du da eine Idee wie man das unterbinden kann?

    2. Wenn ich bei der Datenbankverbindung einen externen Server angebe kann der Pi sich nicht verbinden (statt host="localhost" z.B. host="192.168.2.100") der SQL Server läuft sicher.

    • Hey

      Kannst du ein pythonscript schreiben was aus einem Excelsheet ausließt und in eine MySql Datenbank einließt?

      Danke schonmal im vorraus

  4. Hi

    Kann jemand ein python Script schreiben das aus einem Excelsheet ausließt und in eine MySql Datenbank einließt?

    Danke schonmal im vorraus

  5. Hi,

    habe das Projekt erfolgreich nachgebaut, vielen Dank.

    Es dauert aber irgendwie ziemlich lang, bis der Transponder ausgelesen wird. Da passiert es, dass dieser dann gleich 3 mal eingelesen wird und damit auch 3 mal abgebucht wird. Wie kann man verhindern, dass dies passiert?

    lg Frank

  6. Hallo,

    super Anleitung, hab es nachgebaut und es hat alles geklappt.

    Nun möchte ich das ganze einwenig umbauen, ich möchte das RFID-Türschloss nicht als bezahlsystem für denKaffeautomat nutzen sondern als Türschloss.

    Wie programmiere ich die Datenbank so um das ich auch eine übersichtsseite habe mit den letzen 50 aktivitäten den Türschlosses? Den von MySQL habe ich noch nicht so viel Ahnung. Wäre nett wenn mir dabei jemand helfen könnte.

  7. Hallo Christoph,

    Ich bin auf dein Projekt gestoßen und bin sehr begeistert davon. Jedoch habe ich auch die Probleme wie Sebastian am 28. Juni 2015 um 14:16, und Frank am 5. August 2015 um 10:27 beschreiben. Desweiteren zeigt mein LCD zwischendurch das an was es soll und ansonsten nur irgendein wirwa.Hast du eine Idee woran das mit dem LCD liegen kann?

    Gibt es schon Lösungen zu den Problemen wie sie auch Sebastian und Frank haben?

    Gruß

    Tobi

    • Hi Tobi,

      es gibt eine Übergangslösung 😉

      Schau mal in den Beitrag, über dem Code. Habe dort nun eine PDF-Datei verlinkt. Vielleicht hilft dir das für`s Erste.

      (P.S.: Das dort verlinkte Kapitel ist bereits an Python 3 angepasst.)

      Gruß

      Christoph

      • Hallo Christoph,

        vielen dank für deine Antwort.

        Habe mich den ganzen abend mit dem Neuen Code rumgeschlagen, jedoch ohne erfolg.

        Hast du den Neuen Code bei dir am laufen?

        wenn ja, würdest du ihn mir per Mail schicken?

        Ich muss bei mir Irgendwo einen oder mehrere fehler drin haben.

        wenn ich eine Neue RFID einlesen will kommt bei mir immer "Fehler beim Schreiben" und ohne Datensatz kann ich die Main.py auch nicht Testen.

        Gruß

        Tobi

  8. Pingback: Python | Pearltrees

  9. Hallo,
    Ich habe das Problem, wenn ich dein python script starten will, dann habe ich die Meldung: “ImportError: No modul named mysqldb”
    obwohl bei der Installation:
    apt-get install python-mysqldb
    alles gut funktioniert hat.
    wenn ich es jetzt installieren will, dann zeigt er mir:
    root@raspberrypi:/home/pi# apt-get install python-mysqldb
    Paketlisten werden gelesen… Fertig
    Abhängigkeitsbaum wird aufgebaut.
    Statusinformationen werden eingelesen…. Fertig
    python-mysqldb ist schon die neueste Version.
    python-mysqldb wurde als manuell installiert festgelegt.
    0 aktualisiert, 0 neu installiert, 0 zu entfernen und 0 nicht aktualisiert.
    root@raspberrypi:/home/pi#
    Hat jemand eine idee, wie ich dieses Problem lösen kann?
    Vielen Dank im voraus
    Gruß Detlef

Kommentar verfassen