9. Використання SGBD MySQL
 |
9.1. Встановлення модуля MySQLdb
Ми будемо писати скрипти з використанням бази даних MySQL:
 |
Функції Python для роботи з базою даних MySQL інкапсульовані в модуль MySQLdb, який не входить до початкового дистрибутива Python. Тому необхідно завантажити та встановити цей модуль. Ось один із способів зробити це:
 |
- у меню програм запустіть [1] — менеджер пакетів Python. З’явиться вікно команд [2].
Здійсніть пошук за ключовим словом 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.
Було перелічено всі модулі, у назві або описі яких міститься ключове слово mysql. Нас цікавить модуль [mysql-python], рядок 14. Встановлюємо його:
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
- рядок 5: пакет mysql-python-1.2.3 було встановлено в папку «%APPDATA%\Python», де APPDATA — це папка, вказана в рядку 8.
Ця операція може завершитися невдачею, якщо:
- використовується 64-бітна версія інтерпретатора Python;
- шлях %APPDATA% містить символи з діакритичними знаками.
9.2. Встановлення MySQL
Існує кілька способів встановлення SGBD MySQL. Тут ми використовували WampServer — пакет, що об’єднує кілька програм:
- веб-сервер Apache. Ми використовуватимемо його для написання веб-скриптів на Python;
- SGBD та MySQL;
- скриптова мова PHP;
- інструмент адміністрування SGBD MySQL, написаний на PHP: phpMyAdmin.
WampServer можна завантажити (червень 2011 р.) за наступною адресою:
 |
- у [1] завантажується відповідна версія WampServer;
- у [2] після встановлення запускають програму. Це запустить веб-сервер Apache та SGBD MySQL;
- у [3] після запуску WampServer можна адмініструвати за допомогою іконки [3], розташованої внизу праворуч на панелі завдань;
- у [4] запускається інструмент управління MySQL.
Створюється база даних [dbpersonnes]:
Створюємо користувача [admpersonnes] з паролем [nobody]:
 |  |
 |
- в [1] — ім'я користувача;
- на [2] — комп'ютер SGBD, на якому йому надано права;
- у [3] — його пароль [nobody];
- у [4] — те саме;
- у [5] цьому користувачеві не надаються жодні права;
- у [6] — його створюють.
 |
- у [7] — повернення на головну сторінку phpMyAdmin;
- у [8] використовується посилання [Privileges] з цієї сторінки, щоб перейти до редагування даних користувачів [admpersonnes] та [9].
 |
- у [10] вказується, що користувачеві [admpersonnes] надаються права на базу даних [dbpersonnes];
- у [11] підтверджується вибір.
 |
- за допомогою посилання [12] [Tout cocher] користувачеві [admpersonnes] надаються всі права на базу даних [dbpersonnes] [13];
- підтверджуємо в [14].
Тепер ми маємо:
- базу даних MySQL та [dbpersonnes];
- користувача [admpersonnes / nobody], який має всі права на цю базу даних.
Ми напишемо скрипти на Python для роботи з базою даних.
9.3. Підключення до бази даних MySQL — 1
Програма (mysqldb_01)
# імпорт модуля MySQLdb
import sys
sys.path.append("D:\Programs\ActivePython\site-packages")
import MySQLdb
# підключення до бази даних MySQL
....
Примітки:
- рядки 2–4: скрипти, що містять операції з SGBD та MySQL, повинні імпортувати модуль MySQLdb. Нагадаємо, що ми встановили цей модуль у папку [%APPDATA%\Python]. Папка [%APPDATA%\Python] автоматично обшукується, коли код Python запитує модуль. Насправді обшукуються всі папки, оголошені в sys.path. Ось приклад, який відображає ці папки:
# -*- coding=utf-8 -*-
import sys
# перегляд папок sys.path
for dossier in sys.path:
print dossier
На екрані відображається наступне:
У рядку 8 — папка [site-packages], куди спочатку було встановлено MySQLdb. Ми переміщуємо папку [site-packages] — це папка, куди утиліта pypm встановлює модулі Python. Щоб додати нову папку, яку Python буде перевіряти на наявність модулів, додаємо її до списку sys.path:
# імпорт модуля MySQLdb
import sys
sys.path.append("D:\Programs\ActivePython\site-packages")
import MySQLdb
# підключення до бази даних MySQL
....
У рядку 3 додаємо папку, куди було переміщено модуль MySQLdb.
Повний код прикладу виглядає так:
# імпорт модуля MySQLdb
import sys
sys.path.append("D:\Programs\ActivePython\site-packages")
import MySQLdb
# підключення до бази даних MySQL
# ідентифікатор користувача — (admpersonnes,nobody)
user="admpersonnes"
pwd="nobody"
host="localhost"
connexion=None
try:
print "connexion..."
# вхід
connexion=MySQLdb.connect(host=host,user=user,passwd=pwd)
# відстеження
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
- рядки 8–11: скрипт підключить (рядок 15) користувача [admpersonnes / nobody] до SGBD MySQL на машині [localhost]. Його не підключають до конкретної бази даних;
- рядки 12–24: підключення може завершитися невдачею. Тому його виконують у блоці try / except / finally;
- рядок 15: метод connect модуля MySQLdb приймає різні іменовані параметри:
- user: користувач, якому належить з’єднання [admpersonnes];
- pwd: пароль користувача [nobody];
- host: машина СГБД MySQL [localhost];
- db: база даних, до якої здійснюється підключення. Необов’язкове.
- рядок 18: якщо генерується виняток, він має тип [ MySQLdb.OperationalError], а відповідне повідомлення про помилку міститься у змінній [message];
- рядки 20–23: у блоці [finally] закривається з’єднання. У разі виникнення винятку його перехоплюють (рядок 23), але ніяких дій не виконують (рядок 24).
Результати
connexion...
Connexion a MySQL reussie sous l'identite host=localhost,user=admpersonnes,passwd=nobody
9.4. Підключення до бази даних MySQL - 2
Програма (mysqldb_02)
# імпорт модуля MySQLdb
import sys
sys.path.append("D:\Programs\ActivePython\site-packages")
import MySQLdb
# ---------------------------------------------------------------------------------
def testeConnexion(hote,login,pwd):
# входить, а потім виходить (login, pwd) із СКБД MySQL на хост-сервері
# викликає виняток MySQLdb.operationalError
# підключення
connexion=MySQLdb.connect(host=hote,user=login,passwd=pwd)
print "Connexion a MySQL reussie sous l'identite (%s,%s,%s)" % (hote,login,passwd)
# закриття з'єднання
connexion.close()
print "Fermeture connexion MySQL reussie\n"
# ---------------------------------------------- main
# підключення до бази даних MySQL
# ідентифікатор користувача
user="admpersonnes"
passwd="nobody"
host="localhost"
# тест підключення
try:
testeConnexion(host,user,passwd)
except MySQLdb.OperationalError,message:
print message
# з неіснуючим користувачем
try:
testeConnexion(host,"xx","xx")
except MySQLdb.OperationalError,message:
print message
Примітки:
- рядки 7–15: функція, яка намагається підключити, а потім відключити користувача до SGBD MySQL. Виводить результат;
- рядки 18–34: головна програма — двічі викликає метод testeConnexion і виводить можливі винятки.
Результати
9.5. Створення таблиці MySQL
Тепер, коли ми знаємо, як встановити з’єднання з SGBD MySQL, почнемо надсилати команди SQL через це з’єднання. Для цього ми під’єднаємося до створеної бази даних [dbpersonnes] і використаємо це з’єднання для створення таблиці в базі даних.
Програма (mysqldb_03)
# імпорт модуля MySQLdb
import sys
sys.path.append("D:\Programs\ActivePython\site-packages")
import MySQLdb
# ---------------------------------------------------------------------------------
def executeSQL(connexion,update):
# виконує запит на оновлення з'єднання
# запит на курсор
curseur=connexion.cursor()
# виконує SQL-запит через з'єднання
try:
curseur.execute(update)
connexion.commit()
except Exception, erreur:
connexion.rollback()
raise
finally:
curseur.close()
# ---------------------------------------------- main
# підключення до бази даних MySQL
# ідентифікатор користувача
ID="admpersonnes"
PWD="nobody"
# хост-комп'ютер СГБД
HOTE="localhost"
# ідентифікатор бази даних
BASE="dbpersonnes"
# підключення
try:
connexion=MySQLdb.connect(host=HOTE,user=ID,passwd=PWD,db=BASE)
except MySQLdb.OperationalError,message:
print message
sys.exit()
# видалення таблиці «особи», якщо вона існує
# якщо вона не існує, виникне помилка
# ігнорується
requete="drop table personnes"
try:
executeSQL(connexion,requete)
except:
pass
# створення таблиці «особи»
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()
# виходимо з системи та завершуємо роботу
try:
connexion.close()
except MySQLdb.OperationalError,message:
print message
sys.exit()
Примітки:
- рядок 7: функція executeSQL виконує запит SQL через відкрите з’єднання;
- рядок 10: операції SQL над з’єднанням виконуються через спеціальний об’єкт, який називається курсором;
- рядок 10: отримання курсора;
- рядок 13: виконання запиту SQL;
- рядок 14: поточна транзакція підтверджується;
- рядок 15: у разі виникнення винятку повідомлення про помилку зберігається у змінній «error»;
- рядок 16: поточна транзакція скасовується;
- рядок 17: виняток перезапускається;
- рядок 19: незалежно від того, чи сталася помилка, курсор закривається. Це звільняє пов’язані з ним ресурси.
Результати
create table personnes (prenom varchar(30) NOT NULL, nom varchar(30) NOT NULL, age integer NOT NULL, primary key(nom,prenom)) : requete reussie
Перевірка за допомогою phpMyAdmin:
 |
- база даних [dbpersonnes] [1] містить таблицю [personnes] [2], яка має структуру [3] та первинний ключ [4].
9.6. Заповнення таблиці [personnes]
Після того, як ми раніше створили таблицю [personnes], тепер заповнюємо її.
Програма (mysqldb_04)
# імпорт модуля MySQLdb
import sys
sys.path.append("D:\Programs\ActivePython\site-packages")
import MySQLdb
# інші модулі
import re
# ---------------------------------------------------------------------------------
def executerCommandes(HOTE,ID,PWD,BASE,SQL,suivi=False,arret=True):
# використовує з'єднання (HOTE,ID,PWD,BASE)
# виконує через це з'єднання команди SQL, що містяться у текстовому файлі SQL
# цей файл є файлом команд SQL, які виконуються по одній на рядок
# якщо «супровід»=True, то після кожного виконання команди SQL виводиться повідомлення про її успіх або невдачу
# якщо arret=True, функція зупиняється при першій виявленій помилці, інакше вона виконує всі команди SQL
# функція повертає список (кількість помилок, помилка1, помилка2, ...)
# перевіряється наявність файлу SQL
data=None
try:
data=open(SQL,"r")
except:
return [1,"Le fichier %s n'existe pas" % (SQL)]
# підключення
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)]
# запит курсора
curseur=connexion.cursor()
# виконуються запити SQL, що містяться у файлі SQL
# їх поміщають у масив
requetes=data.readlines()
# виконуємо їх по черзі — спочатку помилок немає
erreurs=[0]
for i in range(len(requetes)):
# зберігаємо поточний запит
requete=requetes[i]
# чи запит порожній? Якщо так, переходимо до наступного запиту
if re.match(r"^\s*$",requete):
continue
# виконання запиту i
erreur=""
try:
curseur.execute(requete)
except Exception, erreur:
pass
#чи сталася помилка?
if erreur:
# ще одна помилка
erreurs[0]+=1
# повідомлення про помилку
msg="%s : Erreur (%s)" % (requete,erreur)
erreurs.append(msg)
# відстежувати на екрані чи ні?
if suivi:
print msg
# зупиняємося?
if arret:
return erreurs
else:
if suivi:
print "%s : Execution reussie" % (requete)
# закриття з'єднання та звільнення ресурсів
curseur.close()
connexion.commit()
# відключення
try:
connexion.close()
except MySQLdb.OperationalError,erreur:
# ще одна помилка
erreurs[0]+=1
# повідомлення про помилку
msg="%s : Erreur (%s)" % (requete,erreur)
erreurs.append(msg)
# повернення
return erreurs
# ---------------------------------------------- main
# підключення до бази даних MySQL
# ідентифікатор користувача
ID="admpersonnes"
PWD="nobody"
# хост-машина СГБД
HOTE="localhost"
# ідентифікатор бази даних
BASE="dbpersonnes"
# ідентифікатор текстового файлу команд SQL, що підлягають виконанню
TEXTE="sql.txt";
# створення та заповнення таблиці
erreurs=executerCommandes(HOTE,ID,PWD,BASE,TEXTE,True,False)
# виведення кількості помилок
print "il y a eu %s erreur(s)" % (erreurs[0])
for i in range(1,len(erreurs)):
print erreurs[i]
Результати
Файл sql.txt:
У рядку 5 навмисно введено помилку.
Результати на екрані:
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"))
Перевірка за допомогою phpMyAdmin:
- у [1] посилання [Afficher] дозволяє отримати вміст таблиці [personnes] [2].
9.7. Виконання довільних запитів SQL
Наступний скрипт дозволяє виконати файл команд SQL та відобразити результат кожної з них:
- результат команди SELECT, якщо команда має вигляд SELECT;
- кількість змінених рядків, якщо команда — INSERT, UPDATE, DELETE.
Програма (mysqldb_05)
# імпорт модуля MySQLdb
import sys
sys.path.append("D:\Programs\ActivePython\site-packages")
import MySQLdb
# інші модулі
import re
def cutNewLineChar(ligne):
# видалення позначки кінця рядка з [ligne], якщо вона існує
l=len(ligne)
while(ligne[l-1]=="\n" or ligne[l-1]=="\r"):
l-=1
return(ligne[0:l])
# ---------------------------------------------------------------------------------
def afficherInfos(curseur):
# виводить результат SQL-запиту
# чи це був запит SELECT?
if curseur.description:
# є опис — отже, це SELECT
# description[i] — це опис стовпця № i у запиті SELECT
# описQZXW2HTMLBW2ldZQXQZXW2HTMLBWzBdZQX — це назва стовпця № i у списку
# виводяться назви полів
titre=""
for i in range(len(curseur.description)):
titre+=curseur.description[i][0]+","
# виводиться список полів без кінцевої коми
print titre[0:len(titre)-1]
# розділювальний рядок
print "-"*(len(titre)-1)
# поточний рядок списку
ligne=curseur.fetchone()
while ligne:
print ligne
# наступний рядок списку
ligne=curseur.fetchone()
else:
# полів немає — це не був список
print "%s lignes(s) a (ont) ete modifiee(s)" % (curseur.rowcount)
# ---------------------------------------------------------------------------------
def executerCommandes(HOTE,ID,PWD,BASE,SQL,suivi=False,arret=True):
# використовує з'єднання (HOTE,ID,PWD,BASE)
# виконує через це з'єднання команди SQL, що містяться у текстовому файлі SQL
# цей файл є файлом команд SQL, які виконуються по одній на рядок
# якщо «супровід»=1, то після кожного виконання команди SQL виводиться повідомлення про успіх або невдачу
# якщо «зупинка» = 1, функція зупиняється при першій виявленій помилці, інакше вона виконує всі команди SQL
# функція повертає масив (кількість помилок, помилка1, помилка2, ...)
# перевіряється наявність файлу SQL
data=None
try:
data=open(SQL,"r")
except:
return [1,"Le fichier %s n'existe pas" % (SQL)]
# вхід
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)]
# запит курсора
curseur=connexion.cursor()
# виконуються запити SQL, що містяться у файлі SQL
# вони поміщаються в масив
requetes=data.readlines()
# виконуємо їх по черзі — спочатку помилок немає
erreurs=[0]
for i in range(len(requetes)):
# зберігаємо поточний запит
requete=requetes[i]
# чи запит порожній? Якщо так, переходимо до наступного запиту
if re.match(r"^\s*$",requete):
continue
# виконання запиту i
erreur=""
try:
curseur.execute(requete)
except Exception, erreur:
pass
#чи сталася помилка?
if erreur:
# ще одна помилка
erreurs[0]+=1
# повідомлення про помилку
msg="%s : Erreur (%s)" % (requete,erreur)
erreurs.append(msg)
# відстежувати на екрані чи ні?
if suivi:
print msg
# зупинитися?
if arret:
return erreurs
else:
if suivi:
print "%s : Execution reussie" % (requete)
# інформація про результат виконаного запиту
afficherInfos(curseur)
# закриття з'єднання та звільнення ресурсів
curseur.close()
connexion.commit()
# відключення
try:
connexion.close()
except MySQLdb.OperationalError,erreur:
# ще одна помилка
erreurs[0]+=1
# повідомлення про помилку
msg="%s : Erreur (%s)" % (requete,erreur)
erreurs.append(msg)
# повернення
return erreurs
# ---------------------------------------------- main
# підключення до бази даних MySQL
# ідентифікатор користувача
ID="admpersonnes"
PWD="nobody"
# хост-машина СГБД
HOTE="localhost"
# ідентифікатор бази даних
BASE="dbpersonnes"
# ідентифікатор текстового файлу команд SQL, що підлягають виконанню
TEXTE="sql2.txt"
# створення та заповнення таблиці
erreurs=executerCommandes(HOTE,ID,PWD,BASE,TEXTE,True,False)
# виведення кількості помилок
print "il y a eu %s erreur(s)" % (erreurs[0])
for i in range(1,len(erreurs)):
print erreurs[i]
Примітки:
- нововведенням є рядок 100 скрипта: після виконання команди SQL запитується інформація про курсор, який використовується цим запитом. Ця інформація надається функцією afficheInfos у рядках 16–40;
- рядки 19 та 39: якщо виконаний запит SQL був запитом SELECT, атрибут [curseur.description] є масивом, елемент № i якого описує поле № i результату запиту SELECT. В іншому випадку атрибут [curseur.rowcount] (рядок 39) — це кількість рядків, змінених запитом INSERT, UPDATE або DELETE;
- рядки 32 та 36: метод [curseur.fetchone] дозволяє отримати поточний рядок із SELECT. Існує метод [curseur.fetchall], який дозволяє отримати їх усі одразу.
Результати
Файл виконаних запитів:
Результати на екрані:
Перевірка PhpMyadmin:
 |