Skip to content

10. Exercise [IMPOTS] with MySQL

10.1. Transferring a text file to a table MySQL

The following script will transfer data from the following text file:

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 the [impots] table of the following database MySQL [dbimpots]:

 

The connection to the [dbimpots] database will be made using the credentials (root,"").

Program (impotstxt2mysql)

We will use the following architecture:

The [console] script we are going to write will use the [ImpotsFile] class to access the data in the text file. Write access to the database will be performed using the methods discussed previously.

The script code is as follows:


# -*- 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 database mysql BASE
    # base mysql is on machine HOTE
    # the user is identified by USER and PWD
 
    # put SQL queries in a list
    # table deletion
    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 commands
    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 queries contained in the query 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"
 
# instantiation layer [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()

Notes:

  • lines 106–110: the [ImpotsFile] class presented in section 8.1 is instantiated;
  • line 113: the limit arrays, coeffR and coeffN, are transferred to a database named MySQL;
  • Line 8: The function copyToMysql performs this transfer. The function copyToMysql creates an array of requests to be executed and has them executed by the function executerCommandes, line 25;
  • Lines 28–89: The function executerCommandes is the one already presented earlier in section 9.7, with one difference: instead of being in a text file, the queries are in a list;
Results

The text file 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

Screen results:

Transfert opéré

Verification with phpMyAdmin:

 

10.2. The tax calculation program

Now that the data needed for tax calculation is in a database, we can write the tax calculation script. We are again using a three-tier architecture:

The new layer [dao] will be connected to SGBD and MySQL and will be implemented by the class [ImpotsMySQL]. It will provide the [metier] layer with the same interface as before, consisting of the single method getData, which returns the tuple (limits, coeffR, coeffN). Thus, the [metier] layer will not change compared to the previous version.

10.3. The [ImpotsMySQL] class

The [dao] layer is now implemented by the following [ImpotsMySQL] class (file impots.py):


class ImpotsMySQL:
 
    # manufacturer
    def __init__(self,HOTE,USER,PWD,BASE,TABLE):
        # initialise les attributs limites, 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 the HOTE machine is made under the identity (USER,PWD)
        # throws an exception if an error occurs
 
        # connection to the 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)

Notes:

  • Line 18: The query SQL SELECT retrieves data from the database MySQL. The result rows from SELECT are then processed one by one by [curseur.fetchone] (lines 22 and 32) to create the limit tables, coeffR, coeffN (lines 28–30);
  • the getData method of the [dao] layer interface.

10.4. The console script

The console script code (impots_04) is as follows:


# -*- 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()

Notes:

  • Line 23: instantiation of layers [dao] and [metier];
  • the rest of the code is familiar.
Results

The same as with previous versions of the exercise.

https://stahe.github.io
This tutorial written by Serge Tahé is made available to the public under the terms of the Creative Commons Attribution – Non-Commercial – ShareAlike 3.0 Unported.