9. Gebruik van SGBD MySQL
 |
9.1. Installatie van de module MySQLdb
We gaan scripts schrijven die gebruikmaken van een database MySQL:
 |
De Python-functies voor het beheer van een database MySQL zijn ondergebracht in een module MySQLdb die niet is opgenomen in de standaarddistributie van Python. De module moet dus worden gedownload en geïnstalleerd. Hieronder volgt een manier om dit te doen:
 |
- Open in het programmamenu de Python-pakketbeheerder ([1]). Het opdrachtvenster [2] verschijnt dan.
Zoek in de pakketten naar het trefwoord 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.
Alle modules waarvan de naam of beschrijving het trefwoord mysql bevat, zijn weergegeven. De module die ons interesseert is [mysql-python], regel 14. We installeren deze:
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
- regel 5: het pakket mysql-python-1.2.3 is geïnstalleerd in de map "%APPDATA%\Python", waarbij APPDATA de map is die in regel 8 is opgegeven.
Deze bewerking kan mislukken als:
- de gebruikte Python-interpreter een 64-bits versie is;
- het pad %APPDATA% accenten bevat.
9.2. Installatie van MySQL
Er zijn verschillende manieren om SGBD MySQL te installeren. Hier hebben we WampServer gebruikt, een pakket dat verschillende programma's bevat:
- een Apache-webserver. Deze zullen we gebruiken voor het schrijven van webscripts in Python;
- de SGBD MySQL;
- de scripttaal PHP;
- een beheertool voor de SGBD MySQL, geschreven in PHP: phpMyAdmin.
WampServer kan (juni 2011) worden gedownload op het volgende adres:
 |
- in [1] downloadt u de juiste versie van WampServer;
- bij [2] start u het programma na de installatie op. Hierdoor worden de Apache-webserver en SGBD MySQL gestart;
- in [3] kan WampServer, zodra het is gestart, worden beheerd via een pictogram [3] rechtsonder in de taakbalk;
- in [4] start men het beheerprogramma van MySQL.
Er wordt een database [dbpersonnes] aangemaakt:
We maken een gebruiker [admpersonnes] aan met het wachtwoord [nobody]:
 |  |
 |
- in [1], de gebruikersnaam;
- in [2], de machine van SGBD waarop hij rechten krijgt;
- in [3], zijn wachtwoord [nobody];
- in [4], idem;
- in [5] worden aan deze gebruiker geen rechten toegekend;
- in [6] wordt de gebruiker aangemaakt.
 |
- in [7], keert men terug naar de startpagina van phpMyAdmin;
- in [8] gebruik je de link [Privileges] op deze pagina om de gegevens van de gebruiker [admpersonnes] en [9] te wijzigen.
 |
- in [10] geef je aan dat je de gebruiker [admpersonnes] rechten wilt toekennen voor de database [dbpersonnes];
- in [11] wordt de keuze bevestigd.
 |
- met de koppeling [12] [Tout cocher], verleent men de gebruiker [admpersonnes] alle rechten op de database [dbpersonnes] [13];
- we valideren in [14].
Nu hebben we:
- een database MySQL [dbpersonnes];
- een gebruiker [admpersonnes / nobody] die alle rechten heeft op deze database.
We gaan Python-scripts schrijven om de database te gebruiken.
9.3. Verbinding maken met een database MySQL - 1
Programma (mysqldb_01)
# import van de module MySQLdb
import sys
sys.path.append("D:\Programs\ActivePython\site-packages")
import MySQLdb
# verbinding maken met een database MySQL
....
Opmerkingen:
- regels 2-4: scripts die bewerkingen bevatten met de SGBD en MySQL moeten de module MySQLdb importeren. We herinneren ons dat we deze module in de map [%APPDATA%\Python] hebben geïnstalleerd. De map [%APPDATA%\Python] wordt automatisch doorzocht wanneer een Python-code een module opvraagt. In feite worden alle mappen doorzocht die in sys.path zijn gedeclareerd. Hier volgt een voorbeeld dat deze mappen weergeeft:
# -*- coding=utf-8 -*-
import sys
# mappen weergeven van sys.path
for dossier in sys.path:
print dossier
De schermweergave ziet er als volgt uit:
Regel 8: de map [site-packages], waar MySQLdb oorspronkelijk was geïnstalleerd. We verplaatsen de map [site-packages], de map waarin het hulpprogramma pypm de Python-modules installeert. Om een nieuwe map toe te voegen die Python zal doorzoeken op zoek naar modules, voegen we deze toe aan de lijst sys.path:
# module importeren MySQLdb
import sys
sys.path.append("D:\Programs\ActivePython\site-packages")
import MySQLdb
# verbinding maken met een database MySQL
....
Op regel 3 voeg je de map toe waarnaar de module MySQLdb is verplaatst.
De volledige code van het voorbeeld is als volgt:
# module importeren MySQLdb
import sys
sys.path.append("D:\Programs\ActivePython\site-packages")
import MySQLdb
# verbinding maken met een database MySQL
# de gebruikersidentiteit is (admpersonnes,nobody)
user="admpersonnes"
pwd="nobody"
host="localhost"
connexion=None
try:
print "connexion..."
# verbinding
connexion=MySQLdb.connect(host=host,user=user,passwd=pwd)
# vervolging
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
- regels 8-11: het script zal (regel 15) de gebruiker [admpersonnes / nobody] verbinden met SGBD MySQL op de machine [localhost]. Er wordt geen verbinding gemaakt met een specifieke database;
- regels 12-24: de verbinding kan mislukken. Daarom wordt deze uitgevoerd in een try / except / finally-blok;
- regel 15: de methode connect van de module MySQLdb accepteert verschillende benoemde parameters:
- user: gebruiker die eigenaar is van de verbinding [admpersonnes];
- pwd: het wachtwoord van de gebruiker [nobody];
- host: machine van het DBMS MySQL [localhost];
- db: de database waarmee verbinding wordt gemaakt. Optioneel.
- regel 18: als er een uitzondering wordt gegenereerd, is deze van het type [ MySQLdb.OperationalError] en is de bijbehorende foutmelding te vinden in de variabele [message];
- regels 20-23: in de clausule [finally] wordt de verbinding verbroken. In geval van een uitzondering wordt deze opgevangen (regel 23), maar er wordt niets mee gedaan (regel 24).
Resultaten
connexion...
Connexion a MySQL reussie sous l'identite host=localhost,user=admpersonnes,passwd=nobody
9.4. Verbinding met een database MySQL - 2
Programma (mysqldb_02)
# module importeren MySQLdb
import sys
sys.path.append("D:\Programs\ActivePython\site-packages")
import MySQLdb
# ---------------------------------------------------------------------------------
def testeConnexion(hote,login,pwd):
# maakt verbinding en verbreekt vervolgens de verbinding (login, wachtwoord) met de MySQL-database op de hostserver
# genereert de uitzondering MySQLdb.operationalError
# verbinding
connexion=MySQLdb.connect(host=hote,user=login,passwd=pwd)
print "Connexion a MySQL reussie sous l'identite (%s,%s,%s)" % (hote,login,passwd)
# de verbinding wordt verbroken
connexion.close()
print "Fermeture connexion MySQL reussie\n"
# ---------------------------------------------- main
# verbinding met de database MySQL
# de identiteit van de gebruiker
user="admpersonnes"
passwd="nobody"
host="localhost"
# verbindingstest
try:
testeConnexion(host,user,passwd)
except MySQLdb.OperationalError,message:
print message
# met een niet-bestaande gebruiker
try:
testeConnexion(host,"xx","xx")
except MySQLdb.OperationalError,message:
print message
Opmerkingen:
- regels 7-15: een functie die probeert een gebruiker aan te melden bij en vervolgens af te melden van een SGBD MySQL. Geeft het resultaat weer;
- regels 18-34: hoofdprogramma – roept de methode testeConnexion twee keer aan en geeft eventuele uitzonderingen weer.
Resultaten
9.5. Een tabel aanmaken MySQL
Nu we weten hoe we een verbinding kunnen maken met een SGBD MySQL, gaan we SQL-opdrachten via deze verbinding uitvoeren. Hiervoor maken we verbinding met de aangemaakte database [dbpersonnes] en gebruiken we de verbinding om een tabel in de database aan te maken.
Programma (mysqldb_03)
# module importeren MySQLdb
import sys
sys.path.append("D:\Programs\ActivePython\site-packages")
import MySQLdb
# ---------------------------------------------------------------------------------
def executeSQL(connexion,update):
# voert een updateverzoek uit op de verbinding
# er wordt een cursor aangevraagd
curseur=connexion.cursor()
# voert de SQL-query uit op de verbinding
try:
curseur.execute(update)
connexion.commit()
except Exception, erreur:
connexion.rollback()
raise
finally:
curseur.close()
# ---------------------------------------------- main
# verbinding met de database MySQL
# de gebruikersidentiteit
ID="admpersonnes"
PWD="nobody"
# de hostmachine van het DBMS
HOTE="localhost"
# database-identiteit
BASE="dbpersonnes"
# verbinding
try:
connexion=MySQLdb.connect(host=HOTE,user=ID,passwd=PWD,db=BASE)
except MySQLdb.OperationalError,message:
print message
sys.exit()
# verwijdering van de tabel 'personen' indien deze bestaat
# als deze niet bestaat, treedt er een fout op
# dit wordt genegeerd
requete="drop table personnes"
try:
executeSQL(connexion,requete)
except:
pass
# de tabel 'personen' aanmaken
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()
# uitloggen en afsluiten
try:
connexion.close()
except MySQLdb.OperationalError,message:
print message
sys.exit()
Opmerkingen:
- regel 7: de functie executeSQL voert een query SQL uit op een geopende verbinding;
- regel 10: de bewerkingen SQL op de verbinding worden uitgevoerd via een specifiek object dat cursor wordt genoemd;
- regel 10: een cursor wordt opgehaald;
- regel 13: uitvoering van de query SQL;
- regel 14: de huidige transactie wordt bevestigd;
- regel 15: in geval van een uitzondering wordt het foutbericht opgeslagen in de variabele ‘fout’;
- regel 16: de huidige transactie wordt ongeldig gemaakt;
- regel 17: de uitzondering wordt opnieuw gegenereerd;
- regel 19: of er nu een fout is of niet, de cursor wordt gesloten. Hierdoor worden de bijbehorende bronnen vrijgegeven.
Resultaten
create table personnes (prenom varchar(30) NOT NULL, nom varchar(30) NOT NULL, age integer NOT NULL, primary key(nom,prenom)) : requete reussie
Controle met phpMyAdmin:
 |
- de database [dbpersonnes] [1] heeft een tabel [personnes] [2] met de structuur [3] en de primaire sleutel [4].
9.6. De tabel [personnes] vullen
Nadat we eerder de tabel [personnes] hebben aangemaakt, vullen we deze nu in.
Programma (mysqldb_04)
# de module MySQLdb importeren
import sys
sys.path.append("D:\Programs\ActivePython\site-packages")
import MySQLdb
# andere modules
import re
# ---------------------------------------------------------------------------------
def executerCommandes(HOTE,ID,PWD,BASE,SQL,suivi=False,arret=True):
# de verbinding gebruiken (HOTE,ID,PWD,BASE)
# voert op deze verbinding de opdrachten SQL uit die in het tekstbestand SQL staan
# dit bestand is een opdrachtbestand SQL waarin de opdrachten per regel worden uitgevoerd
# als suivi=True is, wordt bij elke uitvoering van een opdracht SQL een melding weergegeven waarin wordt aangegeven of deze is geslaagd of mislukt
# als 'stop'=True, stopt de functie bij de eerste fout die wordt aangetroffen; anders voert ze alle SQL-opdrachten uit
# de functie retourneert een lijst (aantal fouten, fout1, fout2, ...)
# er wordt gecontroleerd of het bestand SQL aanwezig is
data=None
try:
data=open(SQL,"r")
except:
return [1,"Le fichier %s n'existe pas" % (SQL)]
# verbinding
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)]
# er wordt een cursor aangevraagd
curseur=connexion.cursor()
# de query's SQL in het bestand SQL worden uitgevoerd
# ze worden in een array geplaatst
requetes=data.readlines()
# ze worden één voor één uitgevoerd – in eerste instantie geen fouten
erreurs=[0]
for i in range(len(requetes)):
# de huidige aanvraag wordt opgeslagen
requete=requetes[i]
# is de query leeg? Zo ja, ga dan naar de volgende query
if re.match(r"^\s*$",requete):
continue
# het verzoek i wordt uitgevoerd
erreur=""
try:
curseur.execute(requete)
except Exception, erreur:
pass
#is er een fout opgetreden?
if erreur:
# nog een fout
erreurs[0]+=1
# foutmelding
msg="%s : Erreur (%s)" % (requete,erreur)
erreurs.append(msg)
# scherm volgen of niet?
if suivi:
print msg
# stoppen we?
if arret:
return erreurs
else:
if suivi:
print "%s : Execution reussie" % (requete)
# verbinding afsluiten en bronnen vrijgeven
curseur.close()
connexion.commit()
# verbinding verbreken
try:
connexion.close()
except MySQLdb.OperationalError,erreur:
# nog een fout
erreurs[0]+=1
# foutmelding
msg="%s : Erreur (%s)" % (requete,erreur)
erreurs.append(msg)
# terug
return erreurs
# ---------------------------------------------- main
# verbinding met de database MySQL
# de identiteit van de gebruiker
ID="admpersonnes"
PWD="nobody"
# de hostmachine van het DBMS
HOTE="localhost"
# identiteit van de database
BASE="dbpersonnes"
# identiteit van het tekstbestand met de uit te voeren SQL-opdrachten
TEXTE="sql.txt";
# aanmaken en vullen van de tabel
erreurs=executerCommandes(HOTE,ID,PWD,BASE,TEXTE,True,False)
#weergave van het aantal fouten
print "il y a eu %s erreur(s)" % (erreurs[0])
for i in range(1,len(erreurs)):
print erreurs[i]
Resultaten
Het bestand sql.txt:
Er is opzettelijk een fout ingevoegd in regel 5.
De schermresultaten:
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"))
Controle met phpMyAdmin:
- in [1] geeft de koppeling [Afficher] toegang tot de inhoud van de tabel [personnes] [2].
9.7. Willekeurige SQL-query's uitvoeren
Met het volgende script kunt u een batchbestand SQL uitvoeren en het resultaat van elk commando weergeven:
- het resultaat van SELECT als de opdracht SELECT is;
- het aantal gewijzigde regels als de opdracht INSERT, UPDATE of DELETE is.
Programma (mysqldb_05)
# import van de module MySQLdb
import sys
sys.path.append("D:\Programs\ActivePython\site-packages")
import MySQLdb
# andere modules
import re
def cutNewLineChar(ligne):
# het einde-van-regel-teken van [ligne] wordt verwijderd, indien aanwezig
l=len(ligne)
while(ligne[l-1]=="\n" or ligne[l-1]=="\r"):
l-=1
return(ligne[0:l])
# ---------------------------------------------------------------------------------
def afficherInfos(curseur):
# geeft het resultaat van een SQL-query weer
# Was dit een SELECT-query?
if curseur.description:
# er is een beschrijving – dus is het een SELECT
# beschrijving[i] is de beschrijving van kolom nr. i van de SELECT-query
# beschrijvingQZXW2HTMLBW2ldZQXQZXW2HTMLBWzBdZQX is de naam van kolom nr. i van de select
# de veldnamen worden weergegeven
titre=""
for i in range(len(curseur.description)):
titre+=curseur.description[i][0]+","
# de lijst met velden wordt weergegeven zonder de afsluitende komma
print titre[0:len(titre)-1]
# scheidingslijn
print "-"*(len(titre)-1)
# huidige regel van de select
ligne=curseur.fetchone()
while ligne:
print ligne
# volgende regel van de selectielijst
ligne=curseur.fetchone()
else:
# er zijn geen velden – dit was geen select-lijst
print "%s lignes(s) a (ont) ete modifiee(s)" % (curseur.rowcount)
# ---------------------------------------------------------------------------------
def executerCommandes(HOTE,ID,PWD,BASE,SQL,suivi=False,arret=True):
# gebruikt de verbinding (HOTE,ID,PWD,BASE)
# voert via deze verbinding de opdrachten SQL uit die in het tekstbestand SQL staan
# dit bestand is een opdrachtbestand SQL waarin de opdrachten per regel worden uitgevoerd
# als ‘volgen=1’, wordt bij elke uitvoering van een opdracht SQL een melding weergegeven die aangeeft of deze is geslaagd of mislukt
# als 'stop'=1, stopt de functie bij de eerste fout die wordt aangetroffen; anders voert ze alle SQL-opdrachten uit
# de functie retourneert een array (aantal fouten, fout1, fout2, ...)
# er wordt gecontroleerd of het bestand SQL aanwezig is
data=None
try:
data=open(SQL,"r")
except:
return [1,"Le fichier %s n'existe pas" % (SQL)]
# verbinding
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)]
# er wordt een cursor aangevraagd
curseur=connexion.cursor()
# de query's SQL in het bestand SQL worden uitgevoerd
# ze worden in een array geplaatst
requetes=data.readlines()
# ze worden één voor één uitgevoerd – in eerste instantie geen fouten
erreurs=[0]
for i in range(len(requetes)):
# de huidige aanvraag wordt opgeslagen
requete=requetes[i]
# is de query leeg? Zo ja, ga dan naar de volgende query
if re.match(r"^\s*$",requete):
continue
# het verzoek i wordt uitgevoerd
erreur=""
try:
curseur.execute(requete)
except Exception, erreur:
pass
#is er een fout opgetreden?
if erreur:
# nog een fout
erreurs[0]+=1
# foutmelding
msg="%s : Erreur (%s)" % (requete,erreur)
erreurs.append(msg)
# scherm volgen of niet?
if suivi:
print msg
# stoppen we?
if arret:
return erreurs
else:
if suivi:
print "%s : Execution reussie" % (requete)
# informatie over het resultaat van de uitgevoerde aanvraag
afficherInfos(curseur)
# verbinding afsluiten en bronnen vrijgeven
curseur.close()
connexion.commit()
# verbinding verbreken
try:
connexion.close()
except MySQLdb.OperationalError,erreur:
# nog een fout
erreurs[0]+=1
# foutmelding
msg="%s : Erreur (%s)" % (requete,erreur)
erreurs.append(msg)
# terug
return erreurs
# ---------------------------------------------- main
# verbinding met de database MySQL
# de identiteit van de gebruiker
ID="admpersonnes"
PWD="nobody"
# de hostmachine van het DBMS
HOTE="localhost"
# identiteit van de database
BASE="dbpersonnes"
# identiteit van het tekstbestand met de uit te voeren SQL-opdrachten
TEXTE="sql2.txt"
# aanmaken en vullen van de tabel
erreurs=executerCommandes(HOTE,ID,PWD,BASE,TEXTE,True,False)
#weergave van het aantal fouten
print "il y a eu %s erreur(s)" % (erreurs[0])
for i in range(1,len(erreurs)):
print erreurs[i]
Opmerkingen:
- de nieuwigheid is regel 100 van het script: na uitvoering van een opdracht SQL wordt informatie opgevraagd over de cursor die door deze query wordt gebruikt. Deze informatie wordt verstrekt door de functie afficheInfos in de regels 16-40;
- regels 19 en 39: als de uitgevoerde query SQL een SELECT was, is het attribuut [curseur.description] een array waarvan element nr. i het veld nr. i van het resultaat van SELECT beschrijft. Anders is het attribuut [curseur.rowcount] (regel 39) het aantal regels dat is gewijzigd door de query INSERT, UPDATE of DELETE;
- regels 32 en 36: met de methode [curseur.fetchone] kunt u de huidige regel van de SELECT ophalen. Er bestaat een methode [curseur.fetchall] waarmee u ze in één keer kunt ophalen.
Resultaten
Het bestand met de uitgevoerde query's:
De schermresultaten:
Controle PhpMyadmin:
 |