10. Übung [Steuerberechnung] mit MySQL
10.1. Übertragen einer Textdatei in eine MySQL-Tabelle
Das folgende Skript überträgt Daten aus der folgenden Textdatei:
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
in der Tabelle [impots] der folgenden MySQL-Datenbank [dbimpots]:
Die Verbindung zur Datenbank [dbimpots] wird mit den Anmeldedaten (root, „“) hergestellt.
Programm (impotstxt2mysql)
Wir werden die folgende Architektur verwenden:
Das [console]-Skript, das wir schreiben werden, nutzt die Klasse [ImpotsFile], um auf die Daten in der Textdatei zuzugreifen. Der Schreibzugriff auf die Datenbank erfolgt mithilfe der zuvor beschriebenen Methoden.
Der Skriptcode lautet wie folgt:
| # -*- coding=utf-8 -*-
# import of Impots class module
from impots import *
import re
# --------------------------------------------------------------------------
def copyToMysql(limites,coeffR,coeffN,HOTE,USER,PWD,BASE,TABLE):
# copy the 3 numerical limit tables, coeffR, coeffN
# in table TABLE of mysql database BASE
# the mysql database is on machine HOTE
# the user is identified by USER and PWD
# put SQL queries in a list
# deletion table
requetes=["drop table %s" % (TABLE)]
# table creation
requete="create table %s (limites decimal(10,2), coeffR decimal(6,2), coeffN decimal(10,2))" % (TABLE)
requetes.append(requete)
# filling
for i in range(len(limites)):
# insertion request
requetes.append("insert into %s (limites,coeffR,coeffN) values (%s,%s,%s)" % (TABLE,limites[i],coeffR[i],coeffN[i]))
# execute SQL orders
return executerCommandes(HOTE,USER,PWD,BASE,requetes,False,False)
def executerCommandes(HOTE,ID,PWD,BASE,requetes,suivi=False,arret=True):
# uses connection (HOTE,ID,PWD,BASE)
# executes on this connection the SQL commands contained in the request list
# if followed=True then each execution of a SQL order is displayed, indicating success or failure
# if arret=False, the function stops on the 1st error encountered, otherwise it executes all sql commands
# the function returns a list (no. of errors, error1, error2, ...)
# connection
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)]
# a cursor is requested
curseur=connexion.cursor()
# execute SQL requests contained in the request list
# we run them - initially no errors
erreurs=[0]
for i in range(len(requetes)):
# put the query in a local variable
requete=requetes[i]
# do we have an empty query?
if re.match(r"^\s*$",requete):
continue
# query execution i
erreur=""
try:
curseur.execute(requete)
except Exception, erreur:
pass
# was there a mistake?
if erreur:
# one more mistake
erreurs[0]+=1
# error msg
msg="%s : Erreur (%s)" % (requete[0:len(requete)-1],erreur)
erreurs.append(msg)
# screen tracking or not?
if suivi:
print msg
# shall we stop?
if arret:
return erreurs
else:
if suivi:
# the query is displayed without its end-of-line marker
print "%s : Execution reussie" % (cutNewLineChar(requete))
# information on the result of the query
afficherInfos(curseur)
# locking connection
try:
connexion.commit()
connexion.close()
except MySQLdb.OperationalError,erreur:
# one more mistake
erreurs[0]+=1
# error msg
msg="%s : Erreur (%s)" % (requete,erreur)
erreurs.append(msg)
# return
return erreurs
# ------------------------------------------------ main
# user identity
USER="root"
PWD=""
# the sgbd host machine
HOTE="localhost"
# base identity
BASE="dbimpots"
# data table identity
TABLE="impots"
# the data file
IMPOTS="impots.txt"
# layer instantiation [dao]
try:
dao=ImpotsFile(IMPOTS)
except (IOError, ImpotsError) as infos:
print ("Une erreur s'est produite : {0}".format(infos))
sys.exit()
# transfer the recovered data to a mysql table
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"
# end
sys.exit()
|
Anmerkungen:
- Zeilen 106–110: Wir instanziieren die in Abschnitt 8.1 vorgestellte Klasse [ImpotsFile];
- Zeile 113: Die Arrays limites, coeffR und coeffN werden in eine MySQL-Datenbank übertragen;
- Zeile 8: Die Funktion
copyToMysql führt diese Übertragung durch. Die Funktion copyToMysql erstellt ein Array mit auszuführenden Abfragen und lässt diese von der Funktion executerCommandes (Zeile 25) ausführen;
- Zeilen 28–89: Die Funktion
executerCommandes ist die bereits in Abschnitt 9.7 vorgestellte, mit einem Unterschied: Anstatt in einer Textdatei zu stehen, befinden sich die Abfragen in einer Liste;
Ergebnisse
Die Textdatei 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
Ausgabe der Ergebnisse:
Überprüfung mit phpMyAdmin:
10.2. Das Steuerberechnungsprogramm
Da die für die Steuerberechnung erforderlichen Daten nun in einer Datenbank vorliegen, können wir das Skript zur Steuerberechnung schreiben. Wir verwenden erneut eine dreistufige Architektur:
Die neue [dao]-Schicht wird mit dem MySQL-DBMS verbunden sein und durch die Klasse [ImpotsMySQL] implementiert werden. Sie wird der [business]-Schicht dieselbe Schnittstelle wie zuvor bereitstellen, bestehend aus der einzigen Methode getData, die das Tupel (limits, coeffR, coeffN) zurückgibt. Somit bleibt die [business]-Schicht gegenüber der vorherigen Version unverändert.
10.3. Die Klasse [ImpotsMySQL]
Die [dao]-Schicht wird nun durch die folgende [ImpotsMySQL]-Klasse (Datei impots.py) implementiert:
| class ImpotsMySQL:
# manufacturer
def __init__(self,HOTE,USER,PWD,BASE,TABLE):
# initializes limit attributes, coeffR, coeffN
# the data required to calculate the tax has been placed in table mysqL TABLE
# belonging to the BASE database. The table has the following structure
# limits decimal(10,2), coeffR decimal(10,2), coeffN decimal(10,2)
# connection to the mysql database on machine HOTE is made as (USER,PWD)
# throws an exception if an error occurs
# connection to mysql database
connexion=MySQLdb.connect(host=HOTE,user=USER,passwd=PWD,db=BASE)
# a cursor is requested
curseur=connexion.cursor()
# block reading of table TABLE
requete="select limites,coeffR,coeffN from %s" % (TABLE)
# executes the query [requete] on the base [base] of the connection [connexion]
curseur.execute(requete)
# query result evaluation
ligne=curseur.fetchone()
self.limites=[]
self.coeffR=[]
self.coeffN=[]
while(ligne):
# current line
self.limites.append(ligne[0])
self.coeffR.append(ligne[1])
self.coeffN.append(ligne[2])
# next line
ligne=curseur.fetchone()
# disconnect
connexion.close()
def getData(self):
return (self.limites, self.coeffR, self.coeffN)
|
Anmerkungen:
- Zeile 18: Die SELECT-SQL-Abfrage, die Daten aus der MySQL-Datenbank abruft. Die Ergebniszeilen der SELECT-Abfrage werden anschließend nacheinander mit [cursor.fetchone] (Zeilen 22 und 32) verarbeitet, um die Arrays limits, coeffR und coeffN (Zeilen 28–30) zu erstellen;
- die getData-Methode der [dao]-Layer-Schnittstelle.
10.4. Das Konsolenskript
Der Code des Konsolenskripts (impots_04) lautet wie folgt:
| # -*- coding=utf-8 -*-
# import of Impots* class module
from impots import *
# ------------------------------------------------ main
# user identity
USER="root"
PWD=""
# the sgbd host machine
HOTE="localhost"
# base identity
BASE="dbimpots"
# data table identity
TABLE="impots"
# input file
DATA="data.txt"
# output file
RESULTATS="resultats.txt"
# instantiation layer [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()
# the data required to calculate the tax has been placed in the IMPOTS file
# one line per table in the form
# val1:val2:val3,...
# reading data
try:
data=open(DATA,"r")
except:
print "Impossible d'ouvrir en lecture le fichier des donnees [DATA]"
sys.exit()
# open results file
try:
resultats=open(RESULTATS,"w")
except:
print "Impossible de creer le fichier des résultats [RESULTATS]"
sys.exit()
# utilities
u=Utilitaires()
# use the current line of the data file
ligne=data.readline()
while(ligne != ''):
# remove any end-of-line marker
ligne=u.cutNewLineChar(ligne)
# we retrieve the 3 fields married:children:salary which form the line
(marie,enfants,salaire)=ligne.split(",")
enfants=int(enfants)
salaire=int(salaire)
# tax calculation
impot=metier.calculer(marie,enfants,salaire)
# enter the result
resultats.write("{0}:{1}:{2}:{3}\n".format(marie,enfants,salaire,impot))
# a new line is read
ligne=data.readline()
# close files
data.close()
resultats.close()
|
Anmerkungen:
- Zeile 23: Instanziierung der [dao]- und [business]-Schichten;
- Der Rest des Codes ist bekannt.
Ergebnisse
Wie bei den vorherigen Versionen der Übung.
