Skip to content

10. Ćwiczenie [IMPOTS] z MySQL

10.1. Przeniesienie pliku tekstowego do tabeli MySQL

Poniższy skrypt będzie pobierał dane z następującego pliku tekstowego:

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

do tabeli [impots] w bazie danych MySQL [dbimpots] w następującej postaci:

 

Połączenie z bazą danych [dbimpots] zostanie nawiązane przy użyciu identyfikatora (root,"").

Program (impotstxt2mysql)

Wykorzystamy następującą strukturę:

Skrypt [console], który zamierzamy napisać, będzie korzystał z klasy [ImpotsFile] w celu uzyskania dostępu do danych z pliku tekstowego. Dostęp do bazy danych w trybie zapisu będzie realizowany za pomocą metod omówionych wcześniej.

Kod skryptu wygląda następująco:


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

# import modułu klas podatkowych
from impots import *
import re

# --------------------------------------------------------------------------
def copyToMysql(limites,coeffR,coeffN,HOTE,USER,PWD,BASE,TABLE):
    # kopiuje 3 tabele wartości granicznych: coeffR, coeffN
    # do tabeli TABLE w bazie danych MySQL BASE
    # baza danych MySQL znajduje się na komputerze HOTE
    # użytkownik jest identyfikowany przez USER i PWD

    # zapytania o nazwie SQL umieszczamy na liście
    # usunięcie tabeli
    requetes=["drop table %s" % (TABLE)]
    # utworzenie tabeli
    requete="create table %s (limites decimal(10,2), coeffR decimal(6,2), coeffN decimal(10,2))" % (TABLE)
    requetes.append(requete)
    # wypełnianie
    for i in range(len(limites)):
        # zapytanie o wstawienie
        requetes.append("insert into %s (limites,coeffR,coeffN) values (%s,%s,%s)" % (TABLE,limites[i],coeffR[i],coeffN[i]))
    # wykonuje się polecenia SQL
    return executerCommandes(HOTE,USER,PWD,BASE,requetes,False,False)
    

def executerCommandes(HOTE,ID,PWD,BASE,requetes,suivi=False,arret=True):
    # korzysta z połączenia (HOTE,ID,PWD,BASE)
    # wykonuje na tym połączeniu polecenia SQL zawarte na liście zapytań
    # jeśli śledzenie=True, to po każdym wykonaniu polecenia SQL wyświetlany jest komunikat informujący o jego powodzeniu lub niepowodzeniu
    # jeśli zatrzymanie=False, funkcja zatrzymuje się przy pierwszym napotkanym błędzie, w przeciwnym razie wykonuje wszystkie polecenia SQL
    # funkcja zwraca listę (liczba błędów, błąd1, błąd2, ...)

    # połączenie
    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)]
      
    # żądany jest kursor
    curseur=connexion.cursor()
    # wykonanie zapytań SQL zawartych na liście zapytań
    # są one wykonywane – początkowo bez błędów
    erreurs=[0]
    for i in range(len(requetes)):
        # zapisujemy zapytanie do zmiennej lokalnej
        requete=requetes[i]
        # czy zapytanie jest puste?
        if re.match(r"^\s*$",requete):
            continue
        # wykonanie zapytania i
        erreur=""
        try:
            curseur.execute(requete)
        except Exception, erreur:
            pass
        # czy wystąpił błąd?
        if erreur:
            # kolejny błąd
            erreurs[0]+=1
            # komunikat o błędzie
            msg="%s : Erreur (%s)" % (requete[0:len(requete)-1],erreur)
            erreurs.append(msg)
            # czy wyświetlać komunikat na ekranie, czy nie?
            if suivi:
                print msg
            # zatrzymujemy się?
            if arret:
                return erreurs
        else:
            if suivi: 
                # wyświetlamy zapytanie bez znaku końca linii
                print "%s : Execution reussie" % (cutNewLineChar(requete))
                # informacje o wyniku wykonanego zapytania
                afficherInfos(curseur)
    # zamknięcie połączenia
    try:
        connexion.commit()
        connexion.close()
    except MySQLdb.OperationalError,erreur:
        # kolejny błąd
        erreurs[0]+=1
        # komunikat o błędzie
        msg="%s : Erreur (%s)" % (requete,erreur)
        erreurs.append(msg)

    # powrót
    return erreurs


# ------------------------------------------------ strona główna
# tożsamość użytkownika
USER="root"
PWD=""
# komputer hostujący system zarządzania bazami danych
HOTE="localhost"
# identyfikator bazy danych
BASE="dbimpots"
# identyfikator tabeli danych
TABLE="impots"
# plik danych
IMPOTS="impots.txt"

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

# przenosimy pobrane dane do tabeli MySQL
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"
# koniec
sys.exit()

Uwagi:

  • wiersze 106–110: tworzymy instancję klasy [ImpotsFile] przedstawionej w paragrafie 8.1;
  • wiersz 113: tabele limites, coeffR, coeffN są przenoszone do bazy MySQL;
  • wiersz 8: funkcja copyToMysql wykonuje to przeniesienie. Funkcja copyToMysql tworzy tabelę zapytań do wykonania i zleca ich wykonanie funkcji executerCommandes, wiersz 25;
  • wiersze 28–89: funkcja executerCommandes jest tą samą funkcją, co przedstawiono wcześniej w paragrafie 9.7, z jedną różnicą: zamiast znajdować się w pliku tekstowym, zapytania znajdują się na liście;
Wyniki

Plik tekstowy 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

Wyniki wyświetlane na ekranie:

Transfert opéré

Weryfikacja za pomocą phpMyAdmin:

 

10.2. Program do obliczania podatku

Teraz, gdy dane niezbędne do obliczenia podatku znajdują się w bazie danych, możemy napisać skrypt do obliczania podatku. Ponownie wykorzystujemy architekturę trójwarstwową:

Nowa warstwa [dao] zostanie połączona z warstwami SGBD i MySQL oraz zostanie zaimplementowana przez klasę [ImpotsMySQL]. Będzie ona oferować warstwie [metier] ten sam interfejs co poprzednio, składający się z jednej metody getData, która zwraca krotkę (limity, coeffR, coeffN). W związku z tym warstwa [metier] nie ulegnie zmianie w stosunku do poprzedniej wersji.

10.3. Klasa [ImpotsMySQL]

Warstwa [dao] jest obecnie implementowana przez następującą klasę [ImpotsMySQL] (plik impots.py):


class ImpotsMySQL:

    # konstruktor
    def __init__(self,HOTE,USER,PWD,BASE,TABLE):
        # inicjuje atrybuty graniczne: coeffR, coeffN
        # dane niezbędne do obliczenia podatku zostały umieszczone w tabeli mysqL TABLE
        # należącej do bazy danych BASE. Tabela ma następującą strukturę
        # limity decimal(10,2), coeffR decimal(10,2), coeffN decimal(10,2)
        # połączenie z bazą danych MySQL na serwerze HOTE odbywa się przy użyciu identyfikatora (USER,PWD)
        # w przypadku ewentualnego błędu zgłasza wyjątek
        
        # połączenie z bazą danych MySQL
        connexion=MySQLdb.connect(host=HOTE,user=USER,passwd=PWD,db=BASE)
        # żądanie kursora
        curseur=connexion.cursor()
        
        # odczyt blokowy z tabeli TABLE
        requete="select limites,coeffR,coeffN from %s" % (TABLE)
        # wykonuje zapytanie [requete] w bazie [base] przy użyciu połączenia [connexion]
        curseur.execute(requete)
        # przetwarzanie wyniku zapytania
        ligne=curseur.fetchone()
        self.limites=[]
        self.coeffR=[]
        self.coeffN=[]
        while(ligne):
            # bieżący wiersz
            self.limites.append(ligne[0])
            self.coeffR.append(ligne[1])
            self.coeffN.append(ligne[2])
            # następny wiersz
            ligne=curseur.fetchone()
        # rozłączenie
        connexion.close()

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

Uwagi:

  • wiersz 18: zapytanie SQL SELECT, które pobiera dane z bazy danych MySQL. Wyniki z zapytania SELECT są następnie przetwarzane pojedynczo przez [curseur.fetchone] (wiersze 22 i 32) w celu utworzenia tabel limites, coeffR oraz coeffN (wiersze 28–30);
  • metoda getData interfejsu warstwy [dao].

10.4. Skrypt konsoli

Kod skryptu konsolowego (impots_04) wygląda następująco:


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

# import modułu klas Impots*
from impots import *

# ------------------------------------------------ main
# identyfikator użytkownika
USER="root"
PWD=""
# komputer hostujący system zarządzania bazami danych
HOTE="localhost"
# identyfikator bazy danych
BASE="dbimpots"
# identyfikator tabeli danych
TABLE="impots"
# plik wejściowy
DATA="data.txt"
# plik wyjściowy
RESULTATS="resultats.txt"

# instancja warstwy [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()

# dane niezbędne do obliczenia podatku zostały umieszczone w pliku IMPOTS
# w ilości jednego wiersza na tabelę w postaci
# val1:val2:val3,...

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

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

# narzędzia
u=Utilitaires()

# przetwarzanie bieżącego wiersza pliku danych
ligne=data.readline()
while(ligne != ''):
    # usuwanie ewentualnego znaku końca wiersza
    ligne=u.cutNewLineChar(ligne)
    # pobieranie trzech pól: „żonaty:dzieci:wynagrodzenie”, które tworzą wiersz
    (marie,enfants,salaire)=ligne.split(",")
    enfants=int(enfants)
    salaire=int(salaire)
    # oblicza się podatek
    impot=metier.calculer(marie,enfants,salaire)
    # zapisujemy wynik
    resultats.write("{0}:{1}:{2}:{3}\n".format(marie,enfants,salaire,impot))
    # odczytuje się nowy wiersz
    ligne=data.readline()
# zamykamy pliki
data.close()
resultats.close()

Uwagi:

  • wiersz 23: instancjonowanie warstw [dao] i [metier];
  • pozostała część kodu jest znana.
Wyniki

Takie same jak w przypadku poprzednich wersji ćwiczenia.

https://stahe.github.io
Ten kurs-samouczek, napisany przez Serge'a Tahé, jest udostępniany publicznie na warunkach licencji Creative Commons Uznanie autorstwa – Bez wykorzystania komercyjnego – Na tych samych warunkach 3.0 bez zmian.