Skip to content

10. Oefening [IMPOTS] met MySQL

10.1. Overdracht van een tekstbestand naar een tabel MySQL

Het volgende script zal de gegevens uit het volgende tekstbestand overzetten:

12620:13190:15640:24740:31810:39970:48360:55790:92970:127860:151250:172040:195000:0
0:0.05:0.1:0.15:0.2:0.25:0.3:0.35:0.4:0.45:0.5:0.55:0.6:0.65
0:631:1290.5:272.5:3309.5:4900:6898.5:9316.5:12106:16754.5:23147.5:30710:39312:49062

naar de tabel [impots] van de volgende database MySQL [dbimpots]:

 

De verbinding met de database [dbimpots] wordt tot stand gebracht met de gebruikersnaam (root,"").

Programma (impotstxt2mysql)

We gaan de volgende architectuur gebruiken:

Het script [console] dat we gaan schrijven, zal de klasse [ImpotsFile] gebruiken om toegang te krijgen tot de gegevens in het tekstbestand. Schrijftoegang tot de database zal plaatsvinden met behulp van de eerder besproken methoden.

De code van het script is als volgt:


# -*- coding=utf-8 -*-

# de module met de belastingklassen importeren
from impots import *
import re

# --------------------------------------------------------------------------
def copyToMysql(limites,coeffR,coeffN,HOTE,USER,PWD,BASE,TABLE):
    # kopieert de 3 tabellen met numerieke limieten, coeffR, coeffN
    # naar de tabel TABLE in de MySQL-database BASE
    # de MySQL-database bevindt zich op de machine HOTE
    # de gebruiker wordt geïdentificeerd door USER en PWD

    # de query's SQL worden in een lijst geplaatst
    # verwijdering van de tabel
    requetes=["drop table %s" % (TABLE)]
    # de tabel wordt aangemaakt
    requete="create table %s (limites decimal(10,2), coeffR decimal(6,2), coeffN decimal(10,2))" % (TABLE)
    requetes.append(requete)
    # vullen
    for i in range(len(limites)):
        # invoerverzoek
        requetes.append("insert into %s (limites,coeffR,coeffN) values (%s,%s,%s)" % (TABLE,limites[i],coeffR[i],coeffN[i]))
    # de opdrachten worden uitgevoerd SQL
    return executerCommandes(HOTE,USER,PWD,BASE,requetes,False,False)
    

def executerCommandes(HOTE,ID,PWD,BASE,requetes,suivi=False,arret=True):
    # de verbinding gebruiken (HOTE,ID,PWD,BASE)
    # voert via deze verbinding de opdrachten SQL uit die in de lijst met verzoeken staan
    # als 'volgen'=True, wordt bij elke uitvoering van een opdracht SQL een melding weergegeven waarin wordt aangegeven of deze is geslaagd of mislukt
    # als 'stop'=False, stopt de functie bij de eerste fout die wordt aangetroffen; anders voert ze alle SQL-opdrachten uit
    # de functie retourneert een lijst (aantal fouten, fout1, fout2, ...)

    # verbinding
    try:
        connexion=MySQLdb.connect(host=HOTE,user=ID,passwd=PWD,db=BASE)
    except MySQLdb.OperationalError,erreur:
        return [1,"Erreur lors de la connexion a MySQL sous l'identite (%s,%s,%s,%s) : %s" % (HOTE, ID, PWD, BASE, erreur)]
      
    # er wordt een cursor aangevraagd
    curseur=connexion.cursor()
    # uitvoering van de query's SQL die in de lijst met query's staan
    # ze worden uitgevoerd – aanvankelijk geen fouten
    erreurs=[0]
    for i in range(len(requetes)):
        # de query wordt in een lokale variabele opgeslagen
        requete=requetes[i]
        # is de query leeg?
        if re.match(r"^\s*$",requete):
            continue
        # de query i wordt uitgevoerd
        erreur=""
        try:
            curseur.execute(requete)
        except Exception, erreur:
            pass
        # is er een fout opgetreden?
        if erreur:
            # nog een fout
            erreurs[0]+=1
            # foutmelding
            msg="%s : Erreur (%s)" % (requete[0:len(requete)-1],erreur)
            erreurs.append(msg)
            # scherm volgen of niet?
            if suivi:
                print msg
            # stoppen we?
            if arret:
                return erreurs
        else:
            if suivi: 
                # de query weergeven zonder de regeleinde-markering
                print "%s : Execution reussie" % (cutNewLineChar(requete))
                # informatie over het resultaat van de uitgevoerde query
                afficherInfos(curseur)
    # verbinding verbreken
    try:
        connexion.commit()
        connexion.close()
    except MySQLdb.OperationalError,erreur:
        # nog een fout
        erreurs[0]+=1
        # foutmelding
        msg="%s : Erreur (%s)" % (requete,erreur)
        erreurs.append(msg)

    # terug
    return erreurs


# ------------------------------------------------ hoofdprogramma
# de identiteit van de gebruiker
USER="root"
PWD=""
# de hostmachine van het DBMS
HOTE="localhost"
# identiteit van de database
BASE="dbimpots"
# identiteit van de gegevenstabel
TABLE="impots"
# het gegevensbestand
IMPOTS="impots.txt"

# instantie van de laag [dao]
try:
    dao=ImpotsFile(IMPOTS)
except (IOError, ImpotsError) as infos:
    print ("Une erreur s'est produite : {0}".format(infos))
    sys.exit()

# de opgehaalde gegevens worden overgebracht naar een MySQL-tabel
erreurs=copyToMysql(dao.limites,dao.coeffR,dao.coeffN,HOTE,USER,PWD,BASE,TABLE)
if erreurs[0]:
    for i in range(1,len(erreurs)):
        print erreurs[i]
else:
    print "Transfert opere"
# einde
sys.exit()

Opmerkingen:

  • regels 106-110: we maken een instantie aan van de klasse [ImpotsFile] die in paragraaf 8.1 is beschreven;
  • regel 113: de tabellen limites, coeffR en coeffN worden overgebracht naar een database met de naam MySQL;
  • regel 8: de functie copyToMysql voert deze overdracht uit. De functie copyToMysql maakt een lijst aan met uit te voeren query's en laat deze uitvoeren door de functie executerCommandes, regel 25;
  • regels 28-89: de functie executerCommandes is dezelfde als eerder beschreven in paragraaf 9.7, met één verschil: in plaats van in een tekstbestand staan de query's in een lijst;
Resultaten

Het tekstbestand impots.txt:

12620:13190:15640:24740:31810:39970:48360:55790:92970:127860:151250:172040:195000:0
0:0.05:0.1:0.15:0.2:0.25:0.3:0.35:0.4:0.45:0.5:0.55:0.6:0.65
0:631:1290.5:272.5:3309.5:4900:6898.5:9316.5:12106:16754.5:23147.5:30710:39312:49062

De schermresultaten:

Transfert opéré

Controle met phpMyAdmin:

 

10.2. Het programma voor de belastingberekening

Nu de gegevens die nodig zijn voor de belastingberekening in een database staan, kunnen we het script voor de belastingberekening schrijven. We gebruiken opnieuw een drielaagse architectuur:

De nieuwe laag [dao] wordt gekoppeld aan SGBD en MySQL en wordt geïmplementeerd door de klasse [ImpotsMySQL]. Deze laag biedt de laag [metier] dezelfde interface als voorheen, bestaande uit de enige methode getData die het tuple (limieten, coeffR, coeffN) retourneert. De laag [metier] blijft dus ongewijzigd ten opzichte van de vorige versie.

10.3. De klasse [ImpotsMySQL]

De laag [dao] wordt nu geïmplementeerd door de volgende klasse [ImpotsMySQL] (bestand impots.py):


class ImpotsMySQL:

    # constructor
    def __init__(self,HOTE,USER,PWD,BASE,TABLE):
        # initialiseert de grensattributen, coeffR, coeffN
        # de gegevens die nodig zijn voor de belastingberekening zijn in de tabel mysqL geplaatst TABLE
        # die deel uitmaakt van de database BASE. De tabel heeft de volgende structuur
        # limieten decimal(10,2), coeffR decimal(10,2), coeffN decimal(10,2)
        # de verbinding met de MySQL-database van de machine HOTE wordt tot stand gebracht onder de identiteit (USER,PWD)
        # genereert een uitzondering bij een eventuele fout
        
        # verbinding met de MySQL-database
        connexion=MySQLdb.connect(host=HOTE,user=USER,passwd=PWD,db=BASE)
        # er wordt een cursor aangevraagd
        curseur=connexion.cursor()
        
        # leest in blokken uit de tabel TABLE
        requete="select limites,coeffR,coeffN from %s" % (TABLE)
        # voert de query [requete] uit op de database [base] van de verbinding [connexion]
        curseur.execute(requete)
        # verwerking van het resultaat van de query
        ligne=curseur.fetchone()
        self.limites=[]
        self.coeffR=[]
        self.coeffN=[]
        while(ligne):
            # huidige rij
            self.limites.append(ligne[0])
            self.coeffR.append(ligne[1])
            self.coeffN.append(ligne[2])
            # volgende regel
            ligne=curseur.fetchone()
        # afmelden
        connexion.close()

    def getData(self):
        return (self.limites, self.coeffR, self.coeffN)

Opmerkingen:

  • regel 18: de aanvraag SQL SELECT die de gegevens opvraagt uit de database MySQL. De resultatenregels van SELECT worden vervolgens één voor één verwerkt door [curseur.fetchone] (regels 22 en 32) om de tabellen limites, coeffR en coeffN (regels 28-30);
  • de methode getData van de interface van de laag [dao].

10.4. Het consolescript

De code van het consolescript (impots_04) is als volgt:


# -*- coding=utf-8 -*-

# de module met de Impots*-klassen importeren
from impots import *

# ------------------------------------------------ main
# de identiteit van de gebruiker
USER="root"
PWD=""
# de hostmachine van het DBMS
HOTE="localhost"
# identiteit van de database
BASE="dbimpots"
# identiteit van de gegevenstabel
TABLE="impots"
# invoerbestand
DATA="data.txt"
# uitvoerbestand
RESULTATS="resultats.txt"

# instantie van de laag [metier]
try:
    metier=ImpotsMetier(ImpotsMySQL(HOTE,USER,PWD,BASE,TABLE))
except (IOError, ImpotsError) as infos:
    print ("Une erreur s'est produite : {0}".format(infos))
    sys.exit()

# de gegevens die nodig zijn voor de belastingberekening zijn in het bestand IMPOTS geplaatst
# met één regel per tabel in de vorm
# val1:val2:val3,...

# het inlezen van de gegevens
try:
    data=open(DATA,"r")
except:
    print "Impossible d'ouvrir en lecture le fichier des donnees [DATA]"
    sys.exit()

# het resultatenbestand openen
try:
    resultats=open(RESULTATS,"w")
except:  
    print "Impossible de creer le fichier des résultats [RESULTATS]"
    sys.exit()

# hulpprogramma's
u=Utilitaires()

# de huidige regel van het gegevensbestand wordt verwerkt
ligne=data.readline()
while(ligne != ''):
    # eventueel einde-van-regel-teken verwijderen
    ligne=u.cutNewLineChar(ligne)
    # de 3 velden 'gehuwd:kinderen:salaris' worden opgehaald die de regel vormen
    (marie,enfants,salaire)=ligne.split(",")
    enfants=int(enfants)
    salaire=int(salaire)
    # de belasting wordt berekend
    impot=metier.calculer(marie,enfants,salaire)
    # het resultaat wordt opgeschreven
    resultats.write("{0}:{1}:{2}:{3}\n".format(marie,enfants,salaire,impot))
    # er wordt een nieuwe regel ingelezen
    ligne=data.readline()
# de bestanden worden gesloten
data.close()
resultats.close()

Opmerkingen:

  • regel 23: instantiëren van de lagen [dao] en [metier];
  • de rest van de code is bekend.
Resultaten

Dezelfde als bij de vorige versies van de oefening.

https://stahe.github.io
Deze cursus-tutorial, geschreven door Serge Tahé, wordt aan het publiek ter beschikking gesteld volgens de voorwaarden van de Creative Commons Naamsvermelding – Geen commercieel gebruik – Gelijk delen 3.0 ongewijzigd.