9. Korzystanie z SGBD MySQL
 |
9.1. Instalacja modułu MySQLdb
Napiszemy skrypty wykorzystujące bazę danych MySQL:
 |
Funkcje języka Python służące do zarządzania bazą danych MySQL są zawarte w module MySQLdb, który nie jest częścią podstawowej dystrybucji języka Python. Należy zatem pobrać i zainstalować ten moduł. Oto jeden ze sposobów postępowania:
 |
- w menu programów uruchom menedżera pakietów Pythona o nazwie [1]. Pojawi się wówczas okno poleceń [2].
W pakietach należy wyszukać słowo kluczowe mysql:
C:\Documents and Settings\st>pypm search mysql
Get: [pypm-be.activestate.com] :repository-index:
Get: [pypm-free.activestate.com] :repository-index:
autosync: synced 2 repositories
chartio Setup wizard and connection client for connecting
chartio-setup Setup wizard and connection client for connecting
cns.recipe.zmysqlda Recipe for installing ZMySQLDA
collective.recipe.zmysqlda Recipe for installing ZMySQLDA
django-mysql-manager DESCRIPTION_DESCRIPTION_DESCRIPTION
jaraco.mysql MySQLDB-compatible MySQL wrapper by Jason R. Coomb
lovely.testlayers mysql, postgres nginx, memcached cassandra test la
mtstat-mysql MySQL Plugins for mtstat
mysql-autodoc Generate HTML documentation from a mysql database
mysql-python Python interface to MySQL
mysqldbda MySQL Database adapter
products.zmysqlda MySQL Zope2 adapter.
pymysql Pure Python MySQL Driver
pymysql-sa PyMySQL dialect for SQLAlchemy.
pymysql3 Pure Python MySQL Driver
sa-mysql-dt Alternative implementation of DateTime column for
schemaobject Iterate over a MySQL database schema as a Python o
schemasync A MySQL Schema Synchronization Utility
simplestore A datastore layer built on top of MySQL in Python.
sqlbean A auto maping ORM for MYSQL and can bind with memc
sqlwitch sqlwitch offers idiomatic SQL generation on top of
tiddlywebplugins.mysql MySQL-based store for tiddlyweb
tiddlywebplugins.mysql2 MySQL-based store for tiddlyweb
zest.recipe.mysql A Buildout recipe to setup a MySQL database.
Wyświetlono listę wszystkich modułów, których nazwa lub opis zawiera słowo kluczowe mysql. Ten, który nas interesuje, to [mysql-python], wiersz 14. Instalujemy go:
C:\Documents and Settings\st>pypm install mysql-python
The following packages will be installed into "%APPDATA%\Python" (2.7):
mysql-python-1.2.3
Hit: [pypm-free.activestate.com] mysql-python 1.2.3
Installing mysql-python-1.2.3
C:\Documents and Settings\st>echo %APPDATA%
C:\Documents and Settings\st\Application Data
- wiersz 5: pakiet mysql-python-1.2.3 został zainstalowany w folderze „%APPDATA%\Python”, gdzie APPDATA to folder wskazany w wierszu 8.
Operacja ta może się nie powieść, jeśli:
- używany interpreter języka Python jest wersją 64-bitową;
- ścieżka %APPDATA% zawiera znaki akcentowane.
9.2. Instalacja MySQL
Istnieje wiele sposobów instalacji pakietu SGBD MySQL. W tym przypadku skorzystaliśmy z pakietu WampServer, który zawiera kilka programów:
- serwer WWW Apache. Wykorzystamy go do pisania skryptów internetowych w języku Python;
- pakiet SGBD MySQL;
- język skryptowy PHP;
- narzędzie administracyjne SGBD MySQL napisane w języku PHP: phpMyAdmin.
WampServer można pobrać (czerwiec 2011 r.) pod następującym adresem:
 |
- w przypadku pliku [1] należy pobrać odpowiednią wersję pliku WampServer;
- w przypadku pliku [2], po zainstalowaniu należy go uruchomić. Spowoduje to uruchomienie serwera WWW Apache oraz plików SGBD i MySQL;
- w przypadku [3], po uruchomieniu, WampServer można zarządzać za pomocą ikony [3] umieszczonej w prawym dolnym rogu paska zadań;
- w [4] uruchamia się narzędzie administracyjne MySQL.
Tworzy się bazę danych [dbpersonnes]:
Tworzymy użytkownika o nazwie [admpersonnes] z hasłem [nobody]:
 |  |
 |
- w [1] – nazwa użytkownika;
- na [2] – komputer o identyfikatorze SGBD, na którym przyznano mu uprawnienia;
- w [3] – jego hasło [nobody];
- w [4] – to samo;
- w [5] nie przyznaje się temu użytkownikowi żadnych uprawnień;
- w przypadku [6] – użytkownik ten zostaje utworzony.
 |
- w [7] powraca się na stronę główną phpMyAdmin;
- w [8] korzysta się z linku [Privileges] na tej stronie, aby przejść do edycji danych użytkownika [admpersonnes] i [9].
 |
- w [10] określa się, że chce się przyznać użytkownikowi [admpersonnes] uprawnienia do bazy danych [dbpersonnes];
- w pliku [11] zatwierdza się ten wybór.
 |
- za pomocą linku [12] [Tout cocher], przyznajemy użytkownikowi [admpersonnes] wszystkie uprawnienia do bazy danych [dbpersonnes] [13];
- zatwierdzamy w [14].
Teraz mamy:
- bazy danych MySQL i [dbpersonnes];
- użytkownika o nazwie [admpersonnes / nobody], który posiada pełne uprawnienia do tej bazy danych.
Napiszemy skrypty w języku Python, aby korzystać z tej bazy danych.
9.3. Połączenie z bazą danych MySQL – 1
Program (mysqldb_01)
# import modułu MySQLdb
import sys
sys.path.append("D:\Programs\ActivePython\site-packages")
import MySQLdb
# połączenie z bazą danych MySQL
....
Uwagi:
- wiersze 2–4: skrypty zawierające operacje z bazami SGBD i MySQL muszą importować moduł MySQLdb. Pamiętamy, że zainstalowaliśmy ten moduł w folderze [%APPDATA%\Python]. Folder [%APPDATA%\Python] jest automatycznie przeszukiwany, gdy kod w języku Python żąda modułu. W rzeczywistości przeszukiwane są wszystkie foldery zadeklarowane w sys.path. Oto przykład wyświetlający te foldery:
# -*- coding=utf-8 -*-
import sys
# wyświetlanie folderów sys.path
for dossier in sys.path:
print dossier
Wyświetlany ekran wygląda następująco:
Wiersz 8 – folder [site-packages], w którym pierwotnie zainstalowano MySQLdb. Przenosimy folder [site-packages], który jest folderem, w którym narzędzie pypm instaluje moduły Pythona. Aby dodać nowy folder, który Python będzie przeszukiwał w poszukiwaniu modułów, dodajemy go do listy sys.path:
# import modułu MySQLdb
import sys
sys.path.append("D:\Programs\ActivePython\site-packages")
import MySQLdb
# połączenie z bazą danych MySQL
....
W wierszu 3 dodajemy katalog, do którego został przeniesiony moduł MySQLdb.
Pełny kod przykładu wygląda następująco:
# import modułu MySQLdb
import sys
sys.path.append("D:\Programs\ActivePython\site-packages")
import MySQLdb
# połączenie z bazą danych MySQL
# tożsamość użytkownika to (admpersonnes,nobody)
user="admpersonnes"
pwd="nobody"
host="localhost"
connexion=None
try:
print "connexion..."
# logowanie
connexion=MySQLdb.connect(host=host,user=user,passwd=pwd)
# monitorowanie
print "Connexion a MySQL reussie sous l'identite host={0},user={1},passwd={2}".format(host,user,pwd)
except MySQLdb.OperationalError,message:
print "Erreur : {0}".format(message)
finally:
try:
connexion.close()
except:
pass
- wiersze 8–11: skrypt zaloguje (wiersz 15) użytkownika [admpersonnes / nobody] do SGBD MySQL na komputerze [localhost]. Nie łączy go z konkretną bazą danych;
- wiersze 12–24: połączenie może się nie powieść. Dlatego realizuje się je w bloku try / except / finally;
- wiersz 15: metoda connect modułu MySQLdb akceptuje różne parametry nazwane:
- user: użytkownik będący właścicielem połączenia [admpersonnes];
- pwd: hasło użytkownika [nobody];
- host: komputer, na którym znajduje się system zarządzania bazami danych MySQL [localhost];
- db: baza danych, z którą nawiązywany jest połączenie. Opcjonalne.
- wiersz 18: jeśli zostanie zgłoszony wyjątek, będzie on typu [ MySQLdb.OperationalError], a powiązany komunikat o błędzie będzie znajdował się w zmiennej [message];
- wiersze 20–23: w klauzuli [finally] zamyka się połączenie. W przypadku wystąpienia wyjątku jest on przechwytywany (wiersz 23), ale nie podejmuje się żadnych działań (wiersz 24).
Wyniki
connexion...
Connexion a MySQL reussie sous l'identite host=localhost,user=admpersonnes,passwd=nobody
9.4. Połączenie z bazą danych MySQL – 2
Program (mysqldb_02)
# import modułu MySQLdb
import sys
sys.path.append("D:\Programs\ActivePython\site-packages")
import MySQLdb
# ---------------------------------------------------------------------------------
def testeConnexion(hote,login,pwd):
# loguje się, a następnie wylogowuje (login, hasło) z systemu zarządzania bazami danych MySQL na serwerze hosta
# wywołuje wyjątek MySQLdb.operationalError
# połączenie
connexion=MySQLdb.connect(host=hote,user=login,passwd=pwd)
print "Connexion a MySQL reussie sous l'identite (%s,%s,%s)" % (hote,login,passwd)
# zamykanie połączenia
connexion.close()
print "Fermeture connexion MySQL reussie\n"
# ---------------------------------------------- main
# połączenie z bazą danych MySQL
# tożsamość użytkownika
user="admpersonnes"
passwd="nobody"
host="localhost"
# test połączenia
try:
testeConnexion(host,user,passwd)
except MySQLdb.OperationalError,message:
print message
# z nieistniejącym użytkownikiem
try:
testeConnexion(host,"xx","xx")
except MySQLdb.OperationalError,message:
print message
Uwagi:
- wiersze 7–15: funkcja, która próbuje zalogować, a następnie wylogować użytkownika z serwera SGBD MySQL. Wyświetla wynik;
- wiersze 18–34: program główny – dwukrotnie wywołuje metodę testeConnexion i wyświetla ewentualne wyjątki.
Wyniki
9.5. Tworzenie tabeli MySQL
Teraz, gdy wiemy już, jak nawiązać połączenie z SGBD MySQL, zaczynamy 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.
Program (mysqldb_03)
# import modułu MySQLdb
import sys
sys.path.append("D:\Programs\ActivePython\site-packages")
import MySQLdb
# ---------------------------------------------------------------------------------
def executeSQL(connexion,update):
# wykonuje zapytanie aktualizacyjne dotyczące połączenia
# żądanie kursora
curseur=connexion.cursor()
# wykonuje zapytanie SQL na połączeniu
try:
curseur.execute(update)
connexion.commit()
except Exception, erreur:
connexion.rollback()
raise
finally:
curseur.close()
# ---------------------------------------------- main
# połączenie z bazą danych MySQL
# identyfikator użytkownika
ID="admpersonnes"
PWD="nobody"
# komputer hosta systemu zarządzania bazami danych
HOTE="localhost"
# identyfikator bazy danych
BASE="dbpersonnes"
# połączenie
try:
connexion=MySQLdb.connect(host=HOTE,user=ID,passwd=PWD,db=BASE)
except MySQLdb.OperationalError,message:
print message
sys.exit()
# usunięcie tabeli „osoby”, jeśli istnieje
# jeśli nie istnieje, wystąpi błąd
# ignorujemy to
requete="drop table personnes"
try:
executeSQL(connexion,requete)
except:
pass
# utworzenie tabeli osób
requete="create table personnes (prenom varchar(30) NOT NULL, nom varchar(30) NOT NULL, age integer NOT NULL, primary key(nom,prenom))"
try:
executeSQL(connexion,requete)
except MySQLdb.OperationalError,message:
print message
sys.exit()
# rozłączamy się i wychodzimy
try:
connexion.close()
except MySQLdb.OperationalError,message:
print message
sys.exit()
Uwagi:
- wiersz 7: funkcja executeSQL wykonuje zapytanie SQL na otwartym połączeniu;
- wiersz 10: operacje SQL na połączeniu są wykonywane za pośrednictwem specjalnego obiektu zwanego kursorem;
- wiersz 10: uzyskanie kursora;
- wiersz 13: wykonanie zapytania SQL;
- wiersz 14: bieżąca transakcja zostaje zatwierdzona;
- wiersz 15: w przypadku wystąpienia wyjątku komunikat o błędzie jest zapisywany w zmiennej „erreur”;
- wiersz 16: bieżąca transakcja zostaje unieważniona;
- wiersz 17: wyjątek jest ponownie zgłaszany;
- wiersz 19: niezależnie od tego, czy wystąpił błąd, czy nie, kursor zostaje zamknięty. Powoduje to zwolnienie zasobów z nim powiązanych.
Wyniki
create table personnes (prenom varchar(30) NOT NULL, nom varchar(30) NOT NULL, age integer NOT NULL, primary key(nom,prenom)) : requete reussie
Weryfikacja za pomocą phpMyAdmin:
 |
- Baza danych [dbpersonnes] [1] zawiera tabelę [personnes] [2] o strukturze [3] i kluczu głównym [4].
9.6. Wypełnianie tabeli [personnes]
Po wcześniejszym utworzeniu tabeli [personnes] przystępujemy teraz do jej wypełnienia.
Program (mysqldb_04)
# import modułu MySQLdb
import sys
sys.path.append("D:\Programs\ActivePython\site-packages")
import MySQLdb
# inne moduły
import re
# ---------------------------------------------------------------------------------
def executerCommandes(HOTE,ID,PWD,BASE,SQL,suivi=False,arret=True):
# wykorzystanie połączenia (HOTE,ID,PWD,BASE)
# wykonuje na tym połączeniu polecenia SQL zawarte w pliku tekstowym SQL
# plik ten jest plikiem poleceń 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 „arret” ma wartość „True”, funkcja zatrzymuje się po napotkaniu pierwszego błędu; w przeciwnym razie wykonuje wszystkie polecenia SQL
# funkcja zwraca listę (liczba błędów, błąd1, błąd2, ...)
# sprawdzana jest obecność pliku SQL
data=None
try:
data=open(SQL,"r")
except:
return [1,"Le fichier %s n'existe pas" % (SQL)]
# 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)]
# żądanie kursora
curseur=connexion.cursor()
# wykonanie zapytań SQL zawartych w pliku SQL
# umieszczamy je w tablicy
requetes=data.readlines()
# są one wykonywane pojedynczo – początkowo bez błędów
erreurs=[0]
for i in range(len(requetes)):
# zapisujemy bieżące zapytanie
requete=requetes[i]
# czy zapytanie jest puste? Jeśli tak, przechodzimy do następnego zapytania
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,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:
print "%s : Execution reussie" % (requete)
# zamknięcie połączenia i zwolnienie zasobów
curseur.close()
connexion.commit()
# rozłączamy się
try:
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
# ---------------------------------------------- main
# połączenie z bazą danych MySQL
# identyfikator użytkownika
ID="admpersonnes"
PWD="nobody"
# komputer główny systemu zarządzania bazą danych
HOTE="localhost"
# identyfikator bazy danych
BASE="dbpersonnes"
# identyfikator pliku tekstowego z poleceniami SQL do wykonania
TEXTE="sql.txt";
# utworzenie i wypełnienie tabeli
erreurs=executerCommandes(HOTE,ID,PWD,BASE,TEXTE,True,False)
#wyświetlenie liczby błędów
print "il y a eu %s erreur(s)" % (erreurs[0])
for i in range(1,len(erreurs)):
print erreurs[i]
Wyniki
Plik sql.txt:
W wierszu 5 celowo wprowadzono błąd.
Wyniki na ekranie:
drop table personnes : Execution reussie
create table personnes (prenom varchar(30) not null, nom varchar(30) not null, age integer not null, primary key (nom,prenom)) : Execution reussie
insert into personnes values('Paul','Langevin',48) : Execution reussie
insert into personnes values ('Sylvie','Lefur',70) : Execution reussie
xx : Erreur ((1064, "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 values ('Pierre','Nicazou',35) : Execution reussie
insert into personnes values ('Geraldine','Colou',26) : Execution reussie
insert into personnes values ('Paulette','Girond',56) : Execution reussie
il y a eu 1 erreur(s)
xx : Erreur ((1064, "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"))
Weryfikacja za pomocą phpMyAdmin:
- w [1] link [Afficher] pozwala uzyskać zawartość tabeli [personnes] [2].
9.7. Wykonywanie dowolnych zapytań SQL
Poniższy skrypt pozwala na wykonanie pliku poleceń SQL i wyświetlenie wyniku każdego z nich:
- wynik polecenia SELECT, jeśli poleceniem jest SELECT;
- liczba zmodyfikowanych wierszy, jeśli poleceniem jest INSERT, UPDATE, DELETE.
Program (mysqldb_05)
# import modułu MySQLdb
import sys
sys.path.append("D:\Programs\ActivePython\site-packages")
import MySQLdb
# inne moduły
import re
def cutNewLineChar(ligne):
# usuwa się znak końca linii z [ligne], jeśli istnieje
l=len(ligne)
while(ligne[l-1]=="\n" or ligne[l-1]=="\r"):
l-=1
return(ligne[0:l])
# ---------------------------------------------------------------------------------
def afficherInfos(curseur):
# wyświetla wynik zapytania SQL
# czy była to instrukcja SELECT?
if curseur.description:
# jest opis – a więc jest to zapytanie SELECT
# opis[i] to opis kolumny nr i w zapytaniu SELECT
# opisQZXW2HTMLBW2ldZQXQZXW2HTMLBWzBdZQX to nazwa kolumny nr i w polu wyboru
# 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żąca linia w liście wyboru
ligne=curseur.fetchone()
while ligne:
print ligne
# następny wiersz listy rozwijanej
ligne=curseur.fetchone()
else:
# brak pól – nie była to lista rozwijana
print "%s lignes(s) a (ont) ete modifiee(s)" % (curseur.rowcount)
# ---------------------------------------------------------------------------------
def executerCommandes(HOTE,ID,PWD,BASE,SQL,suivi=False,arret=True):
# wykorzystuje połączenie (HOTE,ID,PWD,BASE)
# wykonuje na tym połączeniu polecenia SQL zawarte w pliku tekstowym SQL
# plik ten zawiera polecenia SQL, które mają być wykonywane po jednym na wiersz
# jeśli śledzenie=1, to po każdym wykonaniu polecenia SQL wyświetlany jest komunikat informujący o jego powodzeniu lub niepowodzeniu
# jeśli „zatrzymanie” = 1, funkcja zatrzymuje się po napotkaniu pierwszego błędu, w przeciwnym razie wykonuje wszystkie polecenia SQL
# funkcja zwraca tablicę (liczba błędów, błąd1, błąd2, ...)
# sprawdzana jest obecność pliku SQL
data=None
try:
data=open(SQL,"r")
except:
return [1,"Le fichier %s n'existe pas" % (SQL)]
# 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)]
# żądanie kursora
curseur=connexion.cursor()
# wykonanie zapytań SQL zawartych w pliku SQL
# umieszczamy je w tablicy
requetes=data.readlines()
# są one wykonywane pojedynczo – początkowo bez błędów
erreurs=[0]
for i in range(len(requetes)):
# zapisujemy bieżące zapytanie
requete=requetes[i]
# czy zapytanie jest puste? Jeśli tak, przechodzimy do następnego zapytania
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,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:
print "%s : Execution reussie" % (requete)
# informacje o wyniku wykonanego zapytania
afficherInfos(curseur)
# zamknięcie połączenia i zwolnienie zasobów
curseur.close()
connexion.commit()
# rozłączamy się
try:
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
# ---------------------------------------------- main
# połączenie z bazą danych MySQL
# identyfikator użytkownika
ID="admpersonnes"
PWD="nobody"
# komputer hostujący system zarządzania bazami danych
HOTE="localhost"
# identyfikator bazy danych
BASE="dbpersonnes"
# identyfikator pliku tekstowego z poleceniami SQL do wykonania
TEXTE="sql2.txt"
# utworzenie i wypełnienie tabeli
erreurs=executerCommandes(HOTE,ID,PWD,BASE,TEXTE,True,False)
#wyświetlenie liczby błędów
print "il y a eu %s erreur(s)" % (erreurs[0])
for i in range(1,len(erreurs)):
print erreurs[i]
Uwagi:
- nowością jest wiersz 100 skryptu: po wykonaniu polecenia SQL żądane są informacje o kursorze używanym przez to zapytanie. Informacje te są dostarczane przez funkcję afficheInfos w wierszach 16–40;
- wiersze 19 i 39: jeśli wykonane zapytanie SQL było zapytaniem typu SELECT, atrybut [curseur.description] jest tablicą, której element nr i opisuje pole nr i wyniku zapytania SELECT. W przeciwnym razie atrybut [curseur.rowcount] (wiersz 39) to liczba wierszy zmodyfikowanych przez zapytanie INSERT, UPDATE lub DELETE;
- wiersze 32 i 36: metoda [curseur.fetchone] pozwala pobrać bieżący wiersz z SELECT. Istnieje metoda [curseur.fetchall], która pozwala pobrać je wszystkie naraz.
Wyniki
Plik z wykonanymi zapytaniami:
Wyniki wyświetlane na ekranie:
Weryfikacja PhpMyadmin:
 |