Работа с MySQL в Python через стандартный DB-API

Стандарт Python DB-API

Стандарт Python для интерфейсов баз данных — Python DB-API. Большинство интерфейсов баз данных Python соответствуют этому стандарту.

Вы можете выбрать подходящую базу данных для своего приложения. Python Database API поддерживает широкий спектр серверов баз данных:

GadFly
mSQL
MySQL
PostgreSQL
Microsoft SQL Server 2000
Informix
Interbase
Oracle
Sybase

Список доступных интерфейсов: Python Database Interfaces and APIs. Для каждой базы данных нужно загрузить отдельный модуль DB API. Например, для доступа к Oracle и MySQL нужны оба модуля.

DB API предоставляет минимальный стандарт для работы с базами данных, используя структуры и синтаксис Python где возможно. API включает:

Импорт модуля API.
Получение соединения с базой данных.
Выполнение SQL-операторов и хранимых процедур.
Закрытие соединения.

Мы изучим все концепции на примере MySQL, поэтому поговорим о модуле MySQLdb.

Что такое MySQLdb?

MySQLdb — интерфейс для подключения к серверу базы данных MySQL из Python. Он реализует Python Database API v2.0 и построен поверх MySQL C API.

Установка MySQLdb

Перед началом убедитесь, что MySQLdb установлен. Выполните в скрипте:

#!/usr/bin/python
import MySQLdb

Если результат такой:

Traceback (most recent call last):
  File "test.py", line 3, in 
    import MySQLdb
ImportError: No module named MySQLdb

— модуль не установлен.

Команды установки:

# Ubuntu
$ sudo apt-get install python-pip python-dev libmysqlclient-dev

# Fedora
$ sudo dnf install python python-devel mysql-devel redhat-rpm-config gcc

# Через pip (в командной строке Python)
pip install MySQL-python

Примечание: нужны права root для установки.

Подключение к базе данных

Перед подключением к MySQL убедитесь в следующем:

Создана база данных TESTDB.
Создана таблица EMPLOYEE в TESTDB.
Таблица имеет поля FIRST_NAME, LAST_NAME, AGE, SEX, INCOME.
Пользователь testuser с паролем test123 имеет доступ к TESTDB.
Модуль MySQLdb установлен корректно.
Вы изучили основы MySQL.

Пример подключения

#!/usr/bin/python
import MySQLdb

# Открытие соединения
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")

# Создание курсора
cursor = db.cursor()

# Выполнение SQL-запроса
cursor.execute("SELECT VERSION()")

# Получение одной строки
data = cursor.fetchone()
print("Database version : %s " % data)

# Закрытие соединения
db.close()

Результат (пример):

Database version : 5.0.45

Если соединение установлено, возвращается объект Connection и сохраняется в db, иначе db = None. Объект db используется для создания объекта cursor, который выполняет SQL-запросы. В конце соединение закрывается и ресурсы освобождаются.

Создание таблицы базы данных

После установки соединения можно создавать таблицы или записи с помощью метода execute курсора.

Пример: создание таблицы EMPLOYEE

#!/usr/bin/python
import MySQLdb

db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")
cursor = db.cursor()

# Удалить таблицу, если существует
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# Создать таблицу
sql = """CREATE TABLE EMPLOYEE (
    FIRST_NAME CHAR(20) NOT NULL,
    LAST_NAME CHAR(20),
    AGE INT,
    SEX CHAR(1),
    INCOME FLOAT
)"""
cursor.execute(sql)
db.close()

Операция INSERT

Необходима для создания записей в таблице.

Пример: статический SQL

#!/usr/bin/python
import MySQLdb

db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")
cursor = db.cursor()

sql = """INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()
db.close()

Пример: динамический SQL

#!/usr/bin/python
import MySQLdb

db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")
cursor = db.cursor()

sql = "INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) \
       VALUES ('%s', '%s', '%d', '%c', '%d')" % \
       ('Mac', 'Mohan', 20, 'M', 2000)
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()
db.close()

Пример: передача параметров напрямую

user_id = "test123"
password = "password"
con.execute('insert into Login values("%s", "%s")' % (user_id, password))

Операция READ

READ — получение полезной информации из базы данных.

После установки соединения можно делать запросы. Доступны методы:

fetchone() — извлекает следующую строку результата запроса. Результирующий набор — объект, возвращаемый при запросе таблицы через курсор.
fetchall() — извлекает все строки результирующего набора. Если некоторые строки уже извлечены, возвращает оставшиеся.
rowcount — атрибут только для чтения, возвращает количество строк, затронутых методом execute().

Пример: запрос записей с зарплатой > 1000

#!/usr/bin/python
import MySQLdb

db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")
cursor = db.cursor()

sql = "SELECT * FROM EMPLOYEE WHERE INCOME > '%d'" % (1000)
try:
    cursor.execute(sql)
    results = cursor.fetchall()
    for row in results:
        fname = row[0]
        lname = row[1]
        age = row[2]
        sex = row[3]
        income = row[4]
        print("fname=%s,lname=%s,age=%d,sex=%s,income=%d" % (fname, lname, age, sex, income))
except:
    print("Error: unable to fetch data")
db.close()

Результат:

fname=Mac, lname=Mohan, age=20, sex=M, income=2000

Операция UPDATE

UPDATE — обновление одной или нескольких записей, уже имеющихся в базе.

Процедура обновляет все записи с SEX = 'M', увеличивая AGE на один год.

Пример

#!/usr/bin/python
import MySQLdb

db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")
cursor = db.cursor()

sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()
db.close()

Операция DELETE

DELETE — удаление записей из базы. Процедура удаляет все записи из EMPLOYEE, где AGE > 20.

Пример

#!/usr/bin/python
import MySQLdb

db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")
cursor = db.cursor()

sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()
db.close()

Выполнение транзакций

Транзакции — механизм обеспечения согласованности данных. Свойства (ACID):

Атомарность (Atomicity) — либо транзакция завершается полностью, либо ничего не происходит.
Согласованность (Consistency) — транзакция начинается в согласованном состоянии и оставляет систему в согласованном состоянии.
Изоляция (Isolation) — промежуточные результаты невидимы вне текущей транзакции.
Надежность (Durability) — после фиксации эффекты устойчивы даже после сбоя системы.

Python DB API 2.0 предоставляет два метода: commit и rollback.

Пример

sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()

Операция COMMIT

Commit дает «зеленый свет» базе для финализации изменений. После этого откат невозможен.

db.commit()

Операция ROLLBACK

Если изменения не устраивают и нужно вернуть всё назад — используйте rollback().

db.rollback()

Отключение от базы данных

Для разрыва соединения используйте close().

db.close()

Если соединение закрыто пользователем через close(), любые незавершенные транзакции откатываются самой БД. Однако лучше явно вызывать commit или rollback, чем полагаться на низкоуровневую реализацию.

Обработка ошибок

Источники ошибок много: синтаксическая ошибка в SQL, сбой соединения, вызов fetch для уже отмененного или завершенного дескриптора.

DB API определяет ряд исключений, которые должны существовать в каждом модуле базы данных:

№	Исключение и описание
1	Warning — нефатальные проблемы. Должен наследовать StandardError.
2	Error — базовый класс ошибок. Должен наследовать StandardError.
3	InterfaceError — ошибки в модуле базы данных, не в самой БД. Наследует Error.
4	DatabaseError — ошибки в базе данных. Наследует Error.
5	DataError — подкласс DatabaseError, ошибки в данных.
6	OperationalError — подкласс DatabaseError: ошибки вроде потери соединения. Обычно вне контроля скриптера.
7	IntegrityError — подкласс DatabaseError: нарушение реляционной целостности (уникальность, внешние ключи).
8	InternalError — подкласс DatabaseError: внутренние ошибки модуля (например, курсор больше не активен).
9	ProgrammingError — подкласс DatabaseError: плохое имя таблицы и прочие ошибки программиста.
10	NotSupportedError — подкласс DatabaseError: попытка вызвать неподдерживаемую функциональность.

Ваши скрипты должны обрабатывать эти ошибки. Перед использованием убедитесь, что ваш MySQLdb поддерживает нужное исключение. Подробнее — в спецификации DB API 2.0.

Регулярные выражения в Python: практическое руководство Сетевое программирование в Python: сокеты и продвинутые протоколы

Python