Skip to content

16. Korzystanie z SGBD MySQL

Image

16.1. Instalacja SGBD i MySQL

Aby uzyskać dostęp do SGBD i MySQL, zainstalujemy oprogramowanie Laragon.

16.1.1. Instalacja Laragon

Laragon to pakiet zawierający kilka programów:

  • serwer WWW Apache. Będziemy go używać do pisania skryptów internetowych w języku Python;
  • SGBD MySQL;
  • język skryptowy PHP, którego nie będziemy używać;
  • serwer Redis, który zapewnia pamięć podręczną dla aplikacji internetowych. Będziemy go używać;

Laragon można pobrać (luty 2020 r.) pod następującym adresem:

https://laragon.org/download/

Image

Image

  • Instalacja [1-5] tworzy następującą strukturę katalogów:

Image

  • w [6] znajduje się folder instalacyjny PHP (nieużywany w niniejszym dokumencie);

Uruchomienie pliku [Laragon] powoduje wyświetlenie następującego okna:

Image

  • [1]: menu główne programu Laragon;
  • [2]: przycisk [Start All] uruchamia serwer WWW Apache oraz SGBD i MySQL;
  • [3]: przycisk [WEB] wyświetla stronę internetową [http://localhost];
  • [4]: przycisk [Database] umożliwia zarządzanie SGBD i MySQL za pomocą narzędzia [phpMyAdmin]. Należy je najpierw zainstalować;
  • [5]: przycisk [Terminal] otwiera terminal poleceń;
  • [6]: przycisk [Root] otwiera Eksploratora Windows z kursorem w folderze [<laragon>/www], który jest katalogiem głównym witryny internetowej [http://localhost]. To właśnie tam należy umieścić statyczne aplikacje internetowe obsługiwane przez serwer Apache w Laragonie;

16.1.2. Tworzenie bazy danych

Poniżej pokazujemy, jak utworzyć bazę danych oraz użytkownika o nazwie MySQL za pomocą narzędzia Laragon.

Image

  • Po uruchomieniu Laragon [1] można zarządzać za pomocą menu [2];
  • w [3-5] należy zainstalować narzędzie [phpMyAdmin] do zarządzania MySQL, jeśli nie zostało ono jeszcze zainstalowane;

Image

  • w [6] uruchamia się serwer WWW Apache oraz SGBD i MySQL;
  • w [7] uruchamia się serwer Apache;
  • w [8] uruchamia się SGBD oraz MySQL;

Image

  • w [8-10] tworzy się bazę danych o nazwie [dbpersonnes] [11]. Zbudujemy bazę danych osób;

Image

  • w [11] będziemy zarządzać właśnie utworzoną bazą danych;

Image

  • operacja [Bases de données] wysyła zapytanie internetowe do URL, [http://localhost/phpmyadmin] i [12]. Odpowiada serwer WWW Apache z Laragon. URL i [http://localhost/phpmyadmin] to wersje URL narzędzia [phpMyAdmin], które zainstalowaliśmy wcześniej jako [5]. Narzędzie to umożliwia zarządzanie bazami danych MySQL;
  • domyślnie dane logowania administratora bazy to: root [13] bez hasła [14];

Image

  • w [16] znajduje się baza danych, którą utworzyliśmy wcześniej;

Image

  • na razie mamy bazę [dbpersonnes], [17], która jest pusta, oraz [18];

Tworzymy użytkownika o nazwie [admpersonnes] z hasłem [nobody], który będzie miał pełne uprawnienia do bazy danych [dbpersonnes]:

Image

  • jako [19] znajdujemy się w bazie [dbpersonnes];
  • w [20] wybieramy zakładkę [Privileges];
  • w [21-22] widać, że użytkownik [root] ma wszystkie uprawnienia do bazy [dbpersonnes];
  • w [23] tworzy się nowego użytkownika;

Image

  • w [25-26] użytkownik otrzyma identyfikator [admdbpersonnes];
  • w [27-29] jego hasło będzie brzmiało [nobody];
  • w przypadku [30], phpMyAdmin sygnalizuje, że hasło jest bardzo słabe (łatwe do złamania). W środowisku produkcyjnym zaleca się wygenerowanie silnego hasła o wartości [31];
  • w przypadku [32] określa się, że użytkownik [admdbpersonnes] powinien posiadać wszystkie uprawnienia do bazy [dbpersonnes];
  • w pliku [33] zatwierdza się podane informacje;

Image

  • w [35], phpMyAdmin wskazuje, że użytkownik został utworzony;
  • w [36], zlecenie SQL, które zostało wydane w bazie;
  • w [37] użytkownik [admpersonnes] posiada wszystkie uprawnienia do bazy danych [dbpersonnes];

Obecnie mamy:

  • bazę danych MySQL oraz [dbpersonnes];
  • użytkownika [admpersonnes/nobody], który posiada wszystkie uprawnienia do tej bazy danych;

16.2. Instalacja pakietu [mysql-connector-python]

Napiszemy skrypty w języku Python, aby wykorzystać utworzoną wcześniej bazę danych o następującej strukturze:

Image

Łącznik służy do oddzielenia kodu w języku Python od wykorzystywanej bazy danych SGBD. Istnieją łączniki dla różnych baz danych SGBD, a wszystkie one są zgodne z tym samym interfejsem. Również gdy w powyższym przykładzie zastąpimy SGBD i MySQL przez SGBD i PostgreSQL, architektura przyjmuje następujący kształt:

Image

Ponieważ wszystkie złącza SGBD są zgodne z tym samym interfejsem, skrypt w języku Python zazwyczaj nie wymaga modyfikacji. W rzeczywistości większość elementów SGBD posiada własnościowe złącze SQL:

  • są zgodne ze standardem SQL (Structured Query Language);
  • ale rozszerzają go, ponieważ nie jest on wystarczający, o własne rozszerzenia języka;

Dlatego często zdarza się, że przy zmianie SGBD konieczne jest wprowadzenie modyfikacji SQL w skryptach.

Python z natury nie oferuje możliwości zarządzania bazą danych MySQL. W tym celu należy pobrać pakiet. Istnieje ich kilka. W tym przypadku wykorzystamy pakiet [mysql-connector-python], który jest oficjalnym łącznikiem firmy Oracle, właściciela MySQL.

Instalacja pakietu odbędzie się w oknie [Terminal] programu PyCharm:

Image

  • folder o nazwie [2] nie ma znaczenia dla dalszych czynności;

W terminalu wpisujemy polecenie [pip search MySQL]:

  • [pip] (Package Installer for Python) to narzędzie do instalacji pakietów Pythona. Narzędzie [pip] łączy się z repozytorium zawierającym pakiety Pythona;
  • [search MySQL]: pobiera listę pakietów zawierających w nazwie termin [MySQL] (wielkość liter nie ma znaczenia);

Wyniki polecenia są następujące:


mysql (0.0.2)                                                   - Virtual package for MySQL-python
jx-mysql (3.49.20042)                                           - jx-mysql - JSON Expressions for MySQL
weibo-mysql (0.1)                                               - insert mysql
bits-mysql (1.0.3)                                              - BITS MySQL
MySQL-python (1.2.5)                                            - Python interface to MySQL
deployfish-mysql (0.2.13)                                       - Deployfish MySQL plugin
mtstat-mysql (0.7.3.3)                                          - MySQL Plugins for mtstat
bottle-mysql (0.3.1)                                            - MySQL integration for Bottle.
WintxDriver-MySQL (2.0.0-1)                                     - MySQL support for Wintx
py-mysql (1.0)                                                  - Operating Mysql for Python.
mysql-utilities (1.4.3)                                         - MySQL Utilities 1.4.3 (part of MySQL Workbench Distribution 6.0.0)
….                                        - Tool to move slices of data from one MySQL store to another
mysql-tracer (2.0.2)                                            - A MySQL client to run queries, write execution reports and export results
mysql-utils (0.0.2)                                             - A simple MySQL library including a set of utility APIs for Python database programming
mysql-connector-repackaged (0.3.1)                              - MySQL driver written in Python
dffml-source-mysql (0.0.5)                                      - DFFML Source for MySQL Protocol
mysql-connector-python (8.0.19)                                 - MySQL driver written in Python
  INSTALLED: 8.0.19 (latest)
prometheus-mysql-exporter (0.2.0)                               - MySQL query Prometheus exporter
backwork-backup-mysql (0.3.0)                                   - Backwork plug-in for MySQL backups.
django-mysql-manager (0.1.4)                                    - django-mysql-manager is a Django based management interface for MySQL users and databases.
….                                              - mysql operate

C:\Data\st-2020\dev\python\cours-2020\v-01>

Wyświetlono wszystkie moduły, których nazwa lub opis zawiera słowo kluczowe MySQL. Ten, którego będziemy używać (luty 2020 r.), to [mysql-connector-python], wiersz 17. Aby go zainstalować, należy wpisać w terminalu polecenie [pip install -U mysql-connector-python]:


C:\Data\st-2020\dev\python\cours-2020\v-01>pip install -U mysql-connector-python
Collecting mysql-connector-python
  Using cached mysql_connector_python-8.0.19-py2.py3-none-any.whl (355 kB)
Requirement already satisfied, skipping upgrade: protobuf==3.6.1 in c:\myprograms\python38\lib\site-packages (from mysql-connector-python) (3.6.1)
Requirement already satisfied, skipping upgrade: dnspython==1.16.0 in c:\myprograms\python38\lib\site-packages (from mysql-connector-python) (1.16.0)
Requirement already satisfied, skipping upgrade: six>=1.9 in c:\users\serge\appdata\roaming\python\python38\site-packages (from protobuf==3.6.1->mysql-connector-python) (1.14.0)
Requirement already satisfied, skipping upgrade: setuptools in c:\myprograms\python38\lib\site-packages (from protobuf==3.6.1->mysql-connector-python) (41.2.0)
Installing collected packages: mysql-connector-python
Successfully installed mysql-connector-python-8.0.19
  • wiersz 1: opcja [install -U] (U = aktualizacja) pobiera najnowsze wersje różnych pakietów powiązanych z pakietem [mysql-connector-python];

Aby sprawdzić, jakie pakiety są zainstalowane w środowisku Python na naszym komputerze, wpisujemy polecenie [pip list]:


C:\Data\st-2020\dev\python\cours-2020\v-01>pip list
Package                Version
---------------------- ----------
asgiref                3.2.3
astroid                2.3.3
atomicwrites           1.3.0
attrs                  19.3.0
certifi                2019.11.28
…
MarkupSafe             1.1.1
mccabe                 0.6.1
more-itertools         8.1.0
mysql-connector-python 8.0.19
mysqlclient            1.4.6
packaging              20.0
pip                    20.0.1
pipenv                 2018.11.26
…
  • wiersz 13: rzeczywiście mamy pakiet [mysql-connector-python];

Aby dowiedzieć się, jak korzystać z pakietu [mysql-connector-python] w celu zarządzania bazą danych MySQL, należy przejść na stronę internetową pakietu |https://dev.mysql.com/doc/connector-python/en/|. Poniżej przedstawiono serię przykładów.

16.3. Skrypt [mysql_01]: połączenie z bazą danych MySQL – 1

Skrypt [mysql_01] przedstawia pierwszy etap korzystania z bazy danych. Pozwoli nam on sprawdzić, czy jesteśmy w stanie połączyć się z utworzoną wcześniej bazą [dbpersonnes].


# import modułu mysql.connector
from mysql.connector import connect, DatabaseError, InterfaceError

# połączenie z bazą danych MySql [dbpersonnes]
# tożsamość użytkownika to (admpersonnes,nobody)
USER = "admpersonnes"
PWD = "nobody"
HOST = "localhost"
DATABASE = "dbpersonnes"

# zaczynamy
connexion = None
try:
    print("Connexion au SGBD MySQL en cours...")
    # logowanie
    connexion = connect(host=HOST, user=USER, password=PWD, database=DATABASE)
    # monitorowanie
    print(
        f"Connexion MySQL réussie à la base database={DATABASE}, host={HOST} sous l'identité user={USER}, passwd={PWD}")
except (InterfaceError, DatabaseError) as erreur:
    # wyświetlany jest błąd
    print(f"L'erreur suivante s'est produite : {erreur}")
finally:
    # zamykanie połączenia, jeśli zostało otwarte
    if connexion:
        connexion.close()

Uwagi

  • wiersz 2: importujemy niektóre funkcje i klasy z modułu [mysql.connector];
  • wiersze 6–7: identyfikatory użytkownika, który będzie się łączył;
  • wiersz 8: serwer, na którym znajduje się baza danych. Konektor MySQL umożliwia bowiem pracę z bazą zdalną;
  • wiersz 9: nazwa bazy danych, z którą chcemy się połączyć;
  • wiersze 11–26: skrypt połączy (wiersz 16) użytkownika [admpersonnes / nobody] z bazą danych [dbpersonnes];
  • wiersze 20–26: połączenie może się nie powieść. Dlatego realizujemy je w bloku try / except / finally;
  • wiersz 16: metoda connect modułu [mysq.connector] akceptuje różne parametry nazwane:
    • user: użytkownik będący właścicielem połączenia [admpersonnes];
    • password: hasło użytkownika [nobody];
    • host: komputer użytkownika SGBD, MySQL, [localhost];
    • database: baza danych, z którą nawiązywane jest połączenie. Opcjonalne.
  • wiersz 20: jeśli zostanie zgłoszony wyjątek, ma on typ [DatabaseError] lub [InterfaceError];
  • wiersze 23–26: w klauzuli [finally] zamyka się połączenie;

Wyniki

1
2
3
4
5
C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\venv\Scripts\python.exe C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020/databases/mysql/mysql_01.py
Connexion au SGBD MySQL en cours...
Connexion MySQL réussie à la base database=dbpersonnes, host=localhost sous l'identité user=admpersonnes, passwd=nobody

Process finished with exit code 0

16.4. skrypt [mysql_02]: połączenie z bazą danych MySQL – 2

W tym nowym skrypcie połączenie z bazą danych zostało wyodrębnione do funkcji:


# import modułu mysql.connector
from mysql.connector import DatabaseError, InterfaceError, connect


# ---------------------------------------------------------------------------------
def connexion(host: str, database: str, login: str, pwd: str):
    # loguje się, a następnie wylogowuje (login, hasło) z bazy danych [database] na serwerze [host]
    # rzuca wyjątek DatabaseError w przypadku wystąpienia problemu
    connexion = None
    try:
        # połączenie
        connexion = connect(host=host, user=login, password=pwd, database=database)
        print(
            f"Connexion réussie à la base database={database}, host={host} sous l'identité user={login}, passwd={pwd}")
    finally:
        # zamyka się połączenie, jeśli zostało otwarte
        if connexion:
            connexion.close()
            print("Déconnexion réussie\n")


# ---------------------------------------------- main
# dane logowania
USER = "admpersonnes"
PASSWD = "nobody"
HOST = "localhost"
DATABASE = "dbpersonnes"

# logowanie istniejącego użytkownika
try:
    connexion(host=HOST, login=USER, pwd=PASSWD, database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    # wyświetlany jest komunikat o błędzie
    print(erreur)

# logowanie użytkownika, który nie istnieje
try:
    connexion(host=HOST, login="xx", pwd="xx", database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    # wyświetlany jest komunikat o błędzie
    print(erreur)

Uwagi:

  • wiersze 6–19: funkcja [connexion], która próbuje zalogować, a następnie wylogować użytkownika z bazy danych [dbpersonnes]. Wyświetla wynik;
  • wiersze 29–41: program główny – dwukrotnie wywołuje metodę connexion i wyświetla ewentualne wyjątki;

Wyniki

1
2
3
4
5
6
7
C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\venv\Scripts\python.exe C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020/databases/mysql/mysql_02.py
Connexion MySQL réussie à la base database=dbpersonnes, host=localhost sous l'identité user=admpersonnes, passwd=nobody
Déconnexion MySQL réussie

1045 (28000): Access denied for user 'xx'@'localhost' (using password: YES)

Process finished with exit code 0

16.5. skrypt [mysql_03]: utworzenie tabeli MySQL

Skoro już wiemy, jak nawiązać połączenie z bazą SGBD MySQL, możemy zacząć wysyłać polecenia SQL przez to połączenie. W tym celu połączymy się z utworzoną bazą danych [dbpersonnes] i wykorzystamy to połączenie do utworzenia tabeli w tej bazie.


# importy
import sys

from mysql.connector import DatabaseError, InterfaceError, connect
from mysql.connector.connection import MySQLConnection


# ---------------------------------------------------------------------------------
def execute_sql(connexion: MySQLConnection, update: str):
    # wykonuje zapytanie o aktualizację połączenia
    curseur = None
    try:
        # żądanie kursora
        curseur = connexion.cursor()
        # wykonuje zapytanie aktualizacyjne na połączeniu
        curseur.execute(update)
    finally:
        # zamknięcie kursora, jeśli został uzyskany
        if curseur:
            curseur.close()


# ---------------------------------------------- main
# dane logowania do połączenia
# tożsamość użytkownika
ID = "admpersonnes"
PWD = "nobody"
# komputer hosta systemu zarządzania bazami danych
HOST = "localhost"
# nazwa bazy danych
DATABASE = "dbpersonnes"

# przechodzimy krok po kroku
try:
    # połączenie
    connexion = connect(host=HOST, user=ID, password=PWD, database=DATABASE)
    # tryb AUTOCOMMIT
    connexion.autocommit = True
except (InterfaceError, DatabaseError) as erreur:
    # wyświetlanie błędu
    print(f"L'erreur suivante s'est produite : {erreur}")
    # zamykanie
    sys.exit()

# usunięcie tabeli osób, jeśli istnieje
# jeśli nie istnieje, wystąpi błąd – jest on ignorowany
requête = "drop table personnes"
try:
    execute_sql(connexion, requête)
except (InterfaceError, DatabaseError):
    pass

# utworzenie tabeli osób
requête = "create table personnes (id int PRIMARY KEY, prenom varchar(30) NOT NULL, nom varchar(30) NOT NULL, age integer NOT NULL, " \
          "unique(nom,prenom)) "
try:
    # wykonanie zapytania
    execute_sql(connexion, requête)
    # wyświetlenie
    print(f"{requête} : requête réussie")
except (InterfaceError, DatabaseError) as erreur:
    # wyświetlanie błędu
    print(f"L'erreur suivante s'est produite : {erreur}")
finally:
    # rozłączamy się
    connexion.close()

Uwagi:

  • wiersz 9: funkcja execute_sql wykonuje zapytanie SQL na otwartym połączeniu;
  • wiersz 14: operacje SQL na połączeniu są wykonywane za pośrednictwem specjalnego obiektu zwanego kursorem;
  • wiersz 14: uzyskanie kursora;
  • wiersz 16: wykonanie zapytania SQL;
  • wiersze 17–20: niezależnie od tego, czy wystąpił błąd, czy nie, kursor jest zamykany. Powoduje to zwolnienie zasobów z nim powiązanych. Jeśli wystąpi wyjątek, nie jest on tutaj obsługiwany. Zostanie on przekazany do kodu wywołującego;
  • wiersze 33–43: nawiązanie połączenia z bazą danych;
  • wiersz 38: tryb AUTOCOMMIT=True dla połączenia oznacza, że każde wykonanie zapytania odbywa się w ramach automatycznej transakcji. Trybem domyślnym jest AUTOCOMMIT=False, gdzie to programista jest odpowiedzialny za zarządzanie transakcjami. Transakcja to mechanizm obejmujący wykonanie wielu zapytań od 1 do n. Albo wszystkie zakończą się powodzeniem, albo żadne. Jeśli więc zapytania od 1 do i zakończą się powodzeniem, ale zapytanie i+1 zakończy się niepowodzeniem, wówczas zapytania od 1 do i zostaną „cofnięte”, aby baza danych powróciła do stanu sprzed wykonania zapytania 1;
  • w tym przypadku mamy dwa zapytania SQL (wiersze 49, 58). Każde z nich zostanie wykonane w ramach transakcji. Niepowodzenie drugiego zapytania nie ma żadnego wpływu na pierwsze;
  • wiersze 45–51: wykonywane jest polecenie SQL [drop table personnes]. Usuwa ono tabelę o nazwie [personnes]. Jeśli tabela ta nie istnieje, może zostać zgłoszony błąd. Błąd ten jest ignorowany (wiersz 51);
  • wiersze 53–55: polecenie utworzenia tabeli [personnes]. Tabelę można postrzegać jako zbiór wierszy i kolumn. Polecenie utworzenia określa nazwy kolumn:
    • [id]: identyfikator całkowitoliczbowy. Będzie on unikalny dla każdej osoby. Będzie to klucz główny (PRIMARY KEY). Oznacza to, że w tabeli ta kolumna nie zawiera dwóch identycznych wartości i może służyć do identyfikacji osoby;
    • [nom]: ciąg znaków o długości maksymalnie 30 znaków;
    • [prenom]: ciąg znaków o długości nieprzekraczającej 30 znaków;
    • [age]: liczba całkowita;
    • atrybut [NOT NULL] dla każdej z tych kolumn oznacza, że w wierszu tabeli żadna z trzech kolumn nie może być pusta;
    • parametr [unique(nom,prenom)] nazywany jest ograniczeniem. W tym przypadku ograniczeniem dotyczącym wierszy jest to, że krotka (nazwisko, imię) w danym wierszu musi być unikalna w tabeli. Oznacza to, że w tabeli można jednoznacznie zidentyfikować osobę, której nazwisko i imię są znane;
  • wiersze 56–60: wykonanie polecenia SQL;
  • wiersze 61–63: obsługa ewentualnego wyjątku;
  • wiersze 64–66: rozłączamy się z bazą danych;

Wyniki

1
2
3
4
C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\venv\Scripts\python.exe C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020/databases/mysql/mysql_03.py
create table personnes (id int PRIMARY KEY, prenom varchar(30) NOT NULL, nom varchar(30) NOT NULL, age integer NOT NULL, unique(nom,prenom))  : requête réussie

Process finished with exit code 0

Weryfikacja za pomocą [phpMyAdmin]:

Image

  • baza danych [dbpersonnes] [1] zawiera tabelę [personnes] [2] o strukturze [3-4], kluczem głównym [5] oraz ograniczeniem unikalności [6];

16.6. skrypt [mysql_04]: wykonanie pliku poleceń SQL

Po wcześniejszym utworzeniu tabeli [personnes], teraz wypełniamy ją, a następnie przetwarzamy za pomocą poleceń SQL.

Chcemy wykonać polecenia SQL z pliku tekstowego:

Image

Zawartość pliku [commandes.sql] jest następująca:


# usunięcie tabeli [personnes]
drop table personnes
# utworzenie tabeli osób
create table personnes (prenom varchar(30) not null, nom varchar(30) not null, age integer not null, primary key (nom,prenom))
# dodano dwie osoby
insert into personnes(prenom, nom, age) values('Paul','Langevin',48)
insert into personnes(prenom, nom, age) values ('Sylvie','Lefur',70)
# wyświetlenie tabeli
select prenom, nom, age from personnes
# celowy błąd
xx
# dodanie trzech osób
insert into personnes(prenom, nom, age) values ('Pierre','Nicazou',35)
insert into personnes(prenom, nom, age) values ('Geraldine','Colou',26)
insert into personnes(prenom, nom, age) values ('Paulette','Girond',56)
# wyświetlenie tabeli
select prenom, nom, age from personnes
# lista osób w porządku alfabetycznym według nazwisk, a w przypadku identycznych nazwisk – w porządku alfabetycznym według imion
select nom,prenom from personnes order by nom asc, prenom desc
# lista osób w przedziale wiekowym [20,40] w porządku malejącym według wieku
# a następnie, przy tym samym wieku, w porządku alfabetycznym według nazwisk, a przy identycznych nazwiskach – w porządku alfabetycznym według imion
select nom,prenom,age from personnes where age between 20 and 40 order by age desc, nom asc, prenom asc
# dodanie pani Bruneau
insert into personnes(prenom, nom, age) values('Josette','Bruneau',46)
# aktualizacja jej wieku
update personnes set age=47 where nom='Bruneau'
# lista osób o nazwisku Bruneau
select nom,prenom,age from personnes where nom='Bruneau'
# usunięcie pani Bruneau
delete from personnes where nom='Bruneau'
# lista osób o nazwisku Bruneau
select nom,prenom,age from personnes where nom='Bruneau'

Najpierw definiujemy funkcje, które umieszczamy w module, aby móc z nich ponownie korzystać:

Image

Skrypt [mysql_module] wygląda następująco:


# importy
from mysql.connector import DatabaseError, InterfaceError
from mysql.connector.connection import MySQLConnection
from mysql.connector.cursor import MySQLCursor


# ---------------------------------------------------------------------------------
def afficher_infos(curseur: MySQLCursor):
     # wyświetla wynik polecenia SQL
    …


# ---------------------------------------------------------------------------------
def execute_list_of_commands(connexion: MySQLConnection, sql_commands: list,
                             suivi: bool = False, arrêt: bool = True, with_transaction: bool = True):
    # korzysta z otwartego połączenia [connexion]
    # wykonuje na tym połączeniu polecenia SQL zawarte na liście [sql_commands]
    # ten plik zawiera polecenia SQL, które mają być wykonywane po jednym w każdym wierszu
    # 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” = True, funkcja zatrzymuje się przy pierwszym napotkanym błędzie, w przeciwnym razie wykonuje wszystkie polecenia SQL
    # jeśli with_transaction=True, to każdy błąd anuluje wszystkie wcześniej wykonane polecenia SQL
    # jeśli with_transaction=False, to błąd nie ma żadnego wpływu na wcześniej wykonane polecenia SQL
    # funkcja zwraca listę [erreur1, erreur2, ...]

    ….


# ---------------------------------------------------------------------------------
def execute_file_of_commands(connexion: MySQLConnection, sql_filename: str,
                             suivi: bool = False, arrêt: bool = True, with_transaction: bool = True):
    # wykorzystuje otwarte połączenie [connexion]
    # wykonuje na tym połączeniu polecenia SQL zawarte w pliku tekstowym sql_filename
    # plik ten zawiera polecenia SQL, które mają być wykonywane po jednym na wiersz
    # 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” = True, funkcja zatrzymuje się przy pierwszym napotkanym błędzie, w przeciwnym razie wykonuje wszystkie polecenia SQL
    # jeśli with_transaction=True, to każdy błąd anuluje wszystkie wcześniej wykonane polecenia SQL
    # jeśli with_transaction=False, to błąd nie ma żadnego wpływu na wcześniej wykonane polecenia SQL
    # funkcja zwraca listę [erreur1, erreur2, ...]

    # przetwarzanie pliku SQL
    try:
        # otwarcie pliku w trybie odczytu
        file = open(sql_filename, "r")
        # przetwarzanie
        return execute_list_of_commands(connexion, file.readlines(), suivi, arrêt, with_transaction)
    except BaseException as erreur:
        # zwracany jest tablica błędów
        return [f"Le fichier {sql_filename} n'a pu être être exploité : {erreur}"]

Uwagi:

  • wiersz 29: funkcja [execute_file_of_commands] wykonuje polecenia SQL zawarte w pliku tekstowym o nazwie [sql_filename]:
  • znaczenie parametrów można znaleźć w komentarzach w wierszach 31–38;
  • wiersze 40–48: przetwarzany jest plik tekstowy o nazwie [sql_filename];
  • wiersz 43: otwarcie pliku;
  • wiersz 34: wywołanie funkcji [execute_list_of_commands], która wykonuje polecenia SQL przekazane jej w postaci listy. Lista ta składa się w tym przypadku z listy wszystkich wierszy pliku tekstowego [file.readlines()] (wiersz 45);

Funkcja [execute_list_of_commands] wygląda następująco:


# ---------------------------------------------------------------------------------
def execute_list_of_commands(connexion: MySQLConnection, sql_commands: list,
                             suivi: bool = False, arrêt: bool = True, with_transaction: bool = True):
    # wykorzystuje otwarte połączenie [connexion]
    # wykonuje na tym połączeniu polecenia SQL zawarte na liście [sql_commands]
    # ten plik zawiera polecenia SQL, które mają być wykonywane po jednym na wiersz
    # 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” = True, funkcja zatrzymuje się przy pierwszym napotkanym błędzie, w przeciwnym razie wykonuje wszystkie polecenia SQL
    # jeśli with_transaction=True, to każdy błąd anuluje wszystkie wcześniej wykonane polecenia SQL
    # jeśli with_transaction=False, to błąd nie ma żadnego wpływu na wcześniej wykonane polecenia SQL
    # funkcja zwraca listę [erreur1, erreur2, ...]

    # inicjalizacje
    curseur = None
    connexion.autocommit = not with_transaction
    erreurs = []
    try:
        # potrzebny jest suwak
        curseur = connexion.cursor()
        # wykonanie plików sql_commands i SQL zawartych w pliku sql_commands
        # są one wykonywane pojedynczo
        for command in sql_commands:
            # usuwa się spacje na początku i na końcu bieżącego polecenia
            command = command.strip()
            # czy mamy puste polecenie lub komentarz? Jeśli tak, przechodzimy do następnego polecenia
            if command == '' or command[0] == "#":
                continue
            # wykonanie bieżącego polecenia
            error = None
            try:
                curseur.execute(command)
            except (InterfaceError, DatabaseError) as erreur:
                error = erreur
            # czy wystąpił błąd?
            if error:
                # kolejny błąd
                msg = f"{command} : Erreur ({error})"
                erreurs.append(msg)
                # czy wyświetlać komunikaty na ekranie, czy nie?
                if suivi:
                    print(msg)
                # zatrzymać się?
                if with_transaction or arrêt:
                    # wyświetlamy listę błędów
                    return erreurs
            else:
                # brak błędu
                if suivi:
                    print(f"[{command}] : Exécution réussie")
                # wyświetla się wynik polecenia
                afficher_infos(curseur)
        # zwracamy tabelę błędów
        return erreurs
    finally:
        # zamknięcie kursora
        if curseur:
            curseur.close()
        # zatwierdza się / anuluje transakcję, jeśli istnieje
        if with_transaction:
            if erreurs:
                # anulowanie
                connexion.rollback()
            else:
                # zatwierdzenie
                connexion.commit()

Uwagi

  • wiersz 2: funkcja [execute_list_of_commands] wykonuje polecenia SQL zawarte na liście [sql_commands]:
  • znaczenie parametrów można znaleźć w komentarzach do wierszy 4–11;
  • wiersz 2: otrzymane połączenie to otwarte połączenie z bazą danych;
  • wiersz 15: jeśli chcemy, aby wszystkie polecenia z listy [sql_commands] były wykonywane w ramach transakcji, należy pracować w trybie AUTOCOMMIT=False. W przeciwnym razie będziemy pracować w trybie AUTOCOMMIT=True, a wówczas każde polecenie z listy [sqlCommands] zostanie wykonane w ramach automatycznej transakcji i nie będzie transakcji globalnej;
  • wiersz 19: żądamy kursora w celu wykonania różnych poleceń SQL;
  • wiersze 22–51: polecenia są wykonywane pojedynczo;
  • wiersze 26–27: akceptowane są puste wiersze oraz komentarze na liście poleceń SQL. W takim przypadku polecenie jest po prostu ignorowane;
  • wiersze 30–33: wykonanie bieżącego zapytania;
  • wiersze 35–45: obsługa ewentualnego błędu podczas wykonywania bieżącego zapytania;
  • wiersze 37–38: błąd jest dodawany do tablicy błędów;
  • wiersze 40–41: jeśli zażądano monitorowania, wyświetlany jest komunikat o błędzie;
  • wiersze 43–45: jeśli kod wywołujący zażądał zatrzymania po pierwszym błędzie lub zażądał użycia transakcji, należy zatrzymać program. Zwracamy tablicę błędów;
  • wiersze 46–51: przypadek, w którym nie wystąpił błąd wykonania bieżącego zapytania;
  • wiersze 48–49: jeśli zażądano monitorowania, wyświetla się wykonane zapytanie z adnotacją „pomyślnie”;
  • wiersze 50–51: wyświetla się wynik wykonanego zapytania. Do funkcji [afficher_infos] wrócimy nieco później;
  • wiersze 54–65: klauzula [finally] jest wykonywana w każdym przypadku, niezależnie od tego, czy wystąpił wyjątek, czy nie;
  • wiersze 56–57: zamknięcie kursora. Powoduje to zwolnienie zasobów przydzielonych temu kursorowi;
  • wiersze 59–65: obsługiwany jest przypadek, w którym kod wywołujący zażądał wykonania poleceń SQL w ramach transakcji;
  • wiersz 60: sprawdzamy, czy lista [erreurs] jest pusta, co oznacza, że nie wystąpił żaden wyjątek. W takim przypadku transakcja jest zatwierdzana (wiersz 65), w przeciwnym razie jest anulowana (wiersz 62);

Funkcja [afficher_infos] wyświetla wynik zapytania:


# ---------------------------------------------------------------------------------
def afficher_infos(curseur: MySQLCursor):
    print(type(curseur))
    # wyświetla wynik polecenia SQL
    # czy była to instrukcja SELECT?
    if curseur.description:
        # kursor ma opis – oznacza to, że wykonał polecenie SELECT
        # opis[i] to opis kolumny nr i z zapytania SELECT
        # opisQZXW2HTMLBW2ldZQXQZXW2HTMLBWzBdZQX to nazwa kolumny nr i w zapytaniu SELECT
        # wyświetlane są nazwy pól
        titre = ""
        for i in range(len(curseur.description)):
            titre += curseur.description[i][0] + ", "
        # wyświetla listę pól bez końcowego przecinka
        print(titre[0:len(titre) - 1])
        # linia oddzielająca
        print("*" * (len(titre) - 1))
        # bieżący wiersz listy wyboru
        ligne = curseur.fetchone()
        while ligne:
            # wyświetla się
            print(ligne)
            # kolejny wiersz listy rozwijanej
            ligne = curseur.fetchone()
        # linia oddzielająca
        print("*" * (len(titre) - 1))
    else:
        # kursor nie ma pola [description] – wykonał więc polecenie SQL
        # polecenie aktualizacji (wstaw, usuń, zaktualizuj)
        print(f"nombre de lignes modifiées : {curseur.rowcount}")

Uwagi

  • wiersz 1: parametrem funkcji jest kursor, który właśnie wykonał polecenie SQL. W zależności od tego, czy polecenie to jest poleceniem SELECT, czy też poleceniem aktualizacji INSERT, UPDATE, DELETE, zawartość kursora jest różna;
  • wiersz 6: jeśli kursor zawiera pole [description], oznacza to, że wykonał polecenie SELECT, a [description] opisuje pola żądane w SELECT:
    • description[i] opisuje pole nr i wymagane przez SELECT. Jest to lista;
    • opis[i][0] to nazwa pola nr i;
  • wiersze 11–17: wyświetlana jest nazwa pól żądanych przez SELECT;
  • wiersze 18–24: przetwarzany jest wynik SELECT;
  • wiersze 20, 24: wynik SELECT jest przetwarzany sekwencyjnie. Wynik ten stanowi zbiór wierszy. Bieżący wiersz jest uzyskiwany przez [curseur.fetchone()] (wiersz 19). W ten sposób otrzymujemy krotkę;
  • wiersze 27–30: jeśli kursor nie posiada pola [description], oznacza to, że wykonał polecenie aktualizacji INSERT, UPDATE, DELETE. Można wówczas ustalić, ile wierszy w tabeli zostało zmodyfikowanych w wyniku wykonania tego polecenia;
  • wiersz 30: [curseur.rowcount] to właśnie ta liczba;

Główny skrypt [mysql-04] wykorzystuje moduł [mysql_module], który właśnie opisaliśmy:

Image

Plik [config_04] konfiguruje kontekst wykonania skryptu [mysql_04]:


def configure():
    import os

    # absolutna ścieżka do folderu pliku konfiguracyjnego
    script_dir = os.path.dirname(os.path.abspath(__file__))
    # konfiguracja katalogów w ścieżce systemowej
    absolute_dependencies = [
        # foldery lokalne
        f"{script_dir}/shared",
    ]

    # ustalenie ścieżki systemowej
    from myutils import set_syspath
    set_syspath(absolute_dependencies)

    # przywracamy konfigurację
    return {
        # plik poleceń SQL
        "commands_filename": f"{script_dir}/data/commandes.sql",
        # dane logowania do bazy danych
        "host": "localhost",
        "database": "dbpersonnes",
        "user": "admpersonnes",
        "password": "nobody"
    }

Skrypt [mysql_04] ma następującą treść:


# pobieramy konfigurację aplikacji
import config_04

config = config_04.configure()

# ścieżka systemowa jest skonfigurowana – można przeprowadzić importy
import sys
from mysql_module import execute_file_of_commands
from mysql.connector import connect, DatabaseError, InterfaceError

# ---------------------------------------------- main
# sprawdzanie składni wywołania
# argv[0] true / false
args = sys.argv
erreur = len(args) != 2
if not erreur:
    with_transaction = args[1].lower()
    erreur = with_transaction != "true" and with_transaction != "false"
# błąd?
if erreur:
    print(f"syntaxe : {args[0]} true / false")
    sys.exit()

# obliczanie tekstu
with_transaction = with_transaction == "true"
if with_transaction:
    texte = "avec transaction"
else:
    texte = "sans transaction"

# logi ekranowe
print("--------------------------------------------------------------------")
print(f"Exécution du fichier SQL {config['commands_filename']} {texte}")
print("--------------------------------------------------------------------")

# wykonywanie poleceń SQL z pliku
connexion = None
try:
    # połączenie z bazą danych
    connexion = connect(host=config['host'], user=config['user'], password=config['password'],
                        database=config['database'])
    # wykonanie pliku poleceń SQL
    erreurs = execute_file_of_commands(connexion, config["commands_filename"], suivi=True, arrêt=False,
                                       with_transaction=with_transaction)
except (InterfaceError, DatabaseError) as erreur:
     # wyświetlenie błędu
    print(f"L'erreur fatale suivante s'est produite : {erreur}")
    # zakończenie działania
    sys.exit()
finally:
    # zamknięcie połączenia, jeśli zostało otwarte
    if connexion:
        connexion.close()

# wyświetlenie liczby błędów
print("--------------------------------------------------------------------")
print(f"Exécution terminée")
print("--------------------------------------------------------------------")
print(f"Il y a eu {len(erreurs)} erreur(s)")
# wyświetlenie błędów
for erreur in erreurs:
    print(erreur)

Uwagi

  • wiersze 1–4: konfiguracja skryptu;
  • wiersz 8: import modułu [mysql_module] opisanego powyżej:
  • wiersze 12–22: skrypt [mysql-04] oczekuje parametru, który musi przyjmować jedną z wartości [true / false]. Parametr ten określa, czy plik poleceń SQL ma zostać wykonany w ramach transakcji (true), czy nie (false);
  • wiersz 14: parametry przekazane przez użytkownika do skryptu znajdują się na liście [sys.argv];
  • wiersz 15: wymagane są dwa parametry, na przykład [mysql-04 true]. Nazwa skryptu liczy się jako parametr;
  • wiersze 17–18: jeśli rzeczywiście są dwa parametry, drugi z nich musi być ciągiem znaków o wartości „true” lub „false”;
  • wiersze 24–29: obliczanie tekstu wyświetlanego w wierszu 33;
  • wiersze 39–44: wykonywane są polecenia z pliku [./data/commandes.sql];
  • wiersze 45–49: jeśli wystąpi błąd podczas nawiązywania połączenia (wiersz 40) lub błąd nieobsługiwany przez skrypt [execute_file_of_commands], wyświetla się komunikat o błędzie i przerywa się całe działanie;
  • wiersze 55–62: w przypadku pomyślnego wykonania wyświetlana jest liczba błędów napotkanych podczas wykonywania poleceń SQL;

Wykonanie nr 1

Najpierw przeprowadzamy wykonanie bez transakcji. W tym celu utworzymy konfigurację wykonania tak, jak to zrobiono w akapicie dotyczącym |configuration d’un contexte d’exécution|:

Image

  • w [1-4] tworzymy konfigurację wykonania w języku Python;

Image

  • [5]: nazwa konfiguracji wykonania;
  • [6]: ścieżka do skryptu, który ma zostać uruchomiony;
  • [7]: parametry skryptu;
  • [8]: katalog uruchomienia;

Ta konfiguracja odpowiada zatem uruchomieniu pliku SQL z transakcją. Należy użyć przycisku [Apply], aby zatwierdzić konfigurację.

W ten sam sposób tworzymy konfigurację uruchomienia [mysql mysql-04 without_transaction]:

Image

Ta konfiguracja odpowiada zatem uruchomieniu pliku SQL bez transakcji. Należy użyć przycisku [Apply], aby zatwierdzić konfigurację.

Najpierw uruchamiamy wersję bez transakcji:

Image

Wyniki są następujące:


C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\venv\Scripts\python.exe C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020/databases/mysql/mysql_04.py false
--------------------------------------------------------------------
Exécution du fichier SQL C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\databases\mysql/data/commandes.sql sans transaction
--------------------------------------------------------------------
[drop table personnes] : Exécution réussie
nombre de lignes modifiées : 0
[create table personnes (id int primary key, prenom varchar(30) not null, nom varchar(30) not null, age integer not null, unique (nom,prenom))] : Exécution réussie
nombre de lignes modifiées : 0
[insert into personnes(id, prenom, nom, age) values(1, 'Paul','Langevin',48)] : Exécution réussie
nombre de lignes modifiées : 1
[insert into personnes(id, prenom, nom, age) values (2, 'Sylvie','Lefur',70)] : Exécution réussie
nombre de lignes modifiées : 1
[select prenom, nom, age from personnes] : Exécution réussie
prenom, nom, age,
*****************
('Paul', 'Langevin', 48)
('Sylvie', 'Lefur', 70)
*****************
xx : Erreur (1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx' at line 1)
[insert into personnes(id, prenom, nom, age) values (3, 'Pierre','Nicazou',35)] : Exécution réussie
nombre de lignes modifiées : 1
[insert into personnes(id, prenom, nom, age) values (4, 'Geraldine','Colou',26)] : Exécution réussie
nombre de lignes modifiées : 1
[insert into personnes(id, prenom, nom, age) values (5, 'Paulette','Girond',56)] : Exécution réussie
nombre de lignes modifiées : 1
[select prenom, nom, age from personnes] : Exécution réussie
prenom, nom, age,
*****************
('Paul', 'Langevin', 48)
('Sylvie', 'Lefur', 70)
('Pierre', 'Nicazou', 35)
('Geraldine', 'Colou', 26)
('Paulette', 'Girond', 56)
*****************
[select nom,prenom from personnes order by nom asc, prenom desc] : Exécution réussie
nom, prenom,
************
('Colou', 'Geraldine')
('Girond', 'Paulette')
('Langevin', 'Paul')
('Lefur', 'Sylvie')
('Nicazou', 'Pierre')
************
[select nom,prenom,age from personnes where age between 20 and 40 order by age desc, nom asc, prenom asc] : Exécution réussie
nom, prenom, age,
*****************
('Nicazou', 'Pierre', 35)
('Colou', 'Geraldine', 26)
*****************
[insert into personnes(id, prenom, nom, age) values(6, 'Josette','Bruneau',46)] : Exécution réussie
nombre de lignes modifiées : 1
[update personnes set age=47 where nom='Bruneau'] : Exécution réussie
nombre de lignes modifiées : 1
[select nom,prenom,age from personnes where nom='Bruneau'] : Exécution réussie
nom, prenom, age,
*****************
('Bruneau', 'Josette', 47)
*****************
[delete from personnes where nom='Bruneau'] : Exécution réussie
nombre de lignes modifiées : 1
[select nom,prenom,age from personnes where nom='Bruneau'] : Exécution réussie
nom, prenom, age,
*****************
*****************
--------------------------------------------------------------------
Exécution terminée
--------------------------------------------------------------------
Il y a eu 1 erreur(s)
xx : Erreur (1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx' at line 1)

Process finished with exit code 0

Uwagi:

  • wiersz 19: widać, że po wystąpieniu błędu wykonanie poleceń SQL zostało kontynuowane, ponieważ odbywało się ono bez transakcji i z parametrem [arrêt=False]. Wszystkie polecenia SQL zostały zatem wykonane. Powinna więc istnieć tabela [personnes] odzwierciedlająca to wykonanie;

Weryfikacja za pomocą phpMyAdmin:

Image

Wykonanie nr 2

Teraz uruchamiamy konfigurację [mysql mysql-04 with_transaction]. Wyniki są następujące:


C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\venv\Scripts\python.exe C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020/databases/mysql/mysql_04.py true
--------------------------------------------------------------------
Exécution du fichier SQL C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\databases\mysql/data/commandes.sql avec transaction
--------------------------------------------------------------------
[drop table personnes] : Exécution réussie
nombre de lignes modifiées : 0
[create table personnes (id int primary key, prenom varchar(30) not null, nom varchar(30) not null, age integer not null, unique (nom,prenom))] : Exécution réussie
nombre de lignes modifiées : 0
[insert into personnes(id, prenom, nom, age) values(1, 'Paul','Langevin',48)] : Exécution réussie
nombre de lignes modifiées : 1
[insert into personnes(id, prenom, nom, age) values (2, 'Sylvie','Lefur',70)] : Exécution réussie
nombre de lignes modifiées : 1
[select prenom, nom, age from personnes] : Exécution réussie
prenom, nom, age,
*****************
('Paul', 'Langevin', 48)
('Sylvie', 'Lefur', 70)
*****************
xx : Erreur (1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx' at line 1)
--------------------------------------------------------------------
Exécution terminée
--------------------------------------------------------------------
Il y a eu 1 erreur(s)
xx : Erreur (1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx' at line 1)

Process finished with exit code 0

Uwagi:

  • wiersz 19: widać, że po wystąpieniu błędu nie są już wykonywane polecenia SQL, ponieważ wykonanie odbywało się w ramach transakcji, a po napotkaniu pierwszego błędu cofnęliśmy transakcję i zatrzymaliśmy wykonywanie poleceń SQL. Oznacza to, że wyniki poleceń z wierszy 9, 11 i 13 zostały cofnięte. Tabela [personnes] powinna zatem być pusta;

Sprawdzenie za pomocą phpMyAdmin:

Image

  • w [5] widać, że tabele [personnes] i [2] są puste;

16.7. skrypt [mysql_05]: wykorzystanie zapytań parametrycznych

Skrypt [mysql_05] wprowadza pojęcie zapytań z parametrami:


# importy
from mysql.connector import connect, DatabaseError, InterfaceError

# identyfikator użytkownika
ID = "admpersonnes"
PWD = "nobody"
# komputer hosta systemu zarządzania bazami danych
HOST = "localhost"
# nazwa bazy danych
BASE = "dbpersonnes"

# lista osób (nazwisko, imię, wiek)
personnes = []
for i in range(5):
    personnes.append((i, f"n0{i}", f"p0{i}", i + 10))
personnes.append((40, "d'Aboot", "Y'éna", 18))
# kolejna lista osób
autresPersonnes = []
for i in range(5):
    autresPersonnes.append((i + 100, f"n1{i}", f"p1{i}", i + 20))
autresPersonnes.append((200, "d'Aboot", "F'ilhem", 34))

# dostęp do SGBD
connexion = None
try:
    # logowanie
    connexion = connect(host=HOST, user=ID, password=PWD, database=BASE)
    # kursor
    curseur = connexion.cursor()
    # usuwanie istniejących rekordów
    curseur.execute("delete from personnes")
    # dodawanie osób pojedynczo przy użyciu przygotowanego zapytania
    for personne in personnes:
        curseur.execute("insert into personnes(id,nom,prenom,age) values(%s,%s,%s,%s)", personne)
    # masowe dodawanie listy osób
    curseur.executemany("insert into personnes(id,nom,prenom,age) values(%s, %s,%s,%s)", autresPersonnes)
    # zatwierdzenie transakcji
    connexion.commit()
except (DatabaseError, InterfaceError) as erreur:
    # wyświetlenie błędu
    print(f"L'erreur suivante s'est produite : {erreur}")
    # anulowanie transakcji
    if connexion:
        connexion.rollback()
finally:
    # zamknięcie sesji
    if connexion:
        connexion.close()

Uwagi

  • wiersze 12–21: tworzy się dwie listy osób, które mają zostać uwzględnione w bazie danych [dbpersonnes];
  • wiersz 27: nawiązywanie połączenia z bazą danych;
  • wiersz 31: usunięcie zawartości tabeli [personnes];
  • wiersze 33–34: wstawianie osób za pomocą zapytania z parametrami. W wierszu 34 pierwszym parametrem jest polecenie SQL, które ma zostać wykonane. Jest ono niekompletne. Zawiera parametry [%s], które zostaną zastąpione pojedynczo i w kolejności wartościami z listy podanej w drugim parametrze;
  • wiersz 36: dodawanie osób, tym razem przy użyciu pojedynczego polecenia [curseur.executemany]. Drugim parametrem polecenia [executemany] jest wówczas lista list;

Zalety zapytań parametrycznych wynikają z dwóch czynników:

  • są one wykonywane szybciej niż zapytania „sztywne”, które trzeba analizować przy każdym wykonaniu. Zapytanie parametryczne [executemany] jest analizowane tylko raz. Następnie jest wykonywane n razy bez ponownej analizy;
  • parametry wprowadzone do zapytania parametrycznego są sprawdzane. Jeśli zawierają znaki zarezerwowane, takie jak na przykład apostrof, są one „chronione”, aby nie zakłócały wykonania polecenia SQL. Aby to sprawdzić, na liście umieszczono nazwiska i imiona zawierające apostrofy (wiersze 16 i 21);

Wyniki uzyskane w phpMyAdmin są następujące:

Image

  • należy zauważyć, że ciągi zawierające apostrof, znak zarezerwowany w SQL, zostały poprawnie wstawione. Zapytanie z parametrami „zabezpieczyło” je. Bez zapytania z parametrami musielibyśmy wykonać tę pracę samodzielnie;
https://stahe.github.io
Ten kurs-samouczek, napisany przez Serge'a Tahé, jest udostępniany publicznie na warunkach licencji Creative Commons Uznanie autorstwa – Zakaz wykorzystania komercyjnego – Na tych samych warunkach 3.0 bez zmian.