Работа с базой данных MySQL на Python

Стандартный интерфейс баз данных Python — это Python DB-API. Python DB-API предоставляет разработчикам интерфейс программирования приложений для баз данных.

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

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

Вы можете посетитьИнтерфейсы и API баз данных Python, чтобы просмотреть подробный список поддерживаемых баз данных.

Для разных баз данных нужно скачивать разные модули DB API. Например, если вам нужен доступ к базам данных Oracle и MySQL, вам нужно скачать модули баз данных Oracle и MySQL.

DB-API — это спецификация. Она определяет ряд обязательных объектов и методов доступа к базам данных, чтобы обеспечить единообразный интерфейс доступа для разнообразных базовых систем баз данных и широкого круга программ интерфейса баз данных.

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

Процесс использования Python DB-API:

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

Что такое MySQLdb?

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

Как установить MySQLdb?

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

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

Если вывод после выполнения выглядит так, как показано ниже, это означает, что модуль MySQLdb не установлен:

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

Чтобы установить MySQLdb, посетитеhttp://sourceforge.net/projects/mysql-python, (для платформ Linux можно посетить:https://pypi.python.org/pypi/MySQL-python) Отсюда вы можете выбрать установочный пакет, подходящий для вашей платформы, разделённый на предкомпилированные бинарные файлы и установочные пакеты с исходным кодом.

Если вы выберете версию с бинарным релизом, процесс установки в основном можно завершить, следуя подсказкам установщика. Если установка из исходного кода, вам нужно перейти в top-level каталог релиза MySQLdb и ввести следующие команды:

$ gunzip MySQL-python-1.2.2.tar.gz
$ tar -xvf MySQL-python-1.2.2.tar
$ cd MySQL-python-1.2.2
$ python setup.py build
$ python setup.py install

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

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

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

Вы создали базу данных TESTDB.
В базе данных TESTDB вы создали таблицу EMPLOYEE.
Поля таблицы EMPLOYEE — это FIRST_NAME, LAST_NAME, AGE, SEX и INCOME.
Имя пользователя, используемое для подключения к базе данных TESTDB, — "testuser", а пароль — "test123". Вы можете задать свои собственные или напрямую использовать имя пользователя root и его пароль. Для авторизации пользователя базы данных MySQL, пожалуйста, используйте команду GRANT.
Вы установили модуль Python MySQLdb на своей машине.
Если вы не знакомы с операторами SQL, вы можете посетить нашУчебник по основам SQL

Пример:

Следующий пример подключается к базе данных TESTDB MySQL:

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# 使用execute方法执行SQL语句
cursor.execute("SELECT VERSION()")

# 使用 fetchone() 方法获取一条数据
data = cursor.fetchone()

print "Database version : %s " % data

# 关闭数据库连接
db.close()

Вывод после выполнения вышеуказанного скрипта выглядит следующим образом:

Database version : 5.0.45

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

Если подключение к базе данных существует, мы можем использовать метод execute() для создания таблиц базы данных, как показано ниже при создании таблицы EMPLOYEE:

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# 如果数据表已经存在使用 execute() 方法删除表。
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# 创建数据表SQL语句
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()

Операция вставки в базу данных

В следующем примере записи вставляются в таблицу EMPLOYEE путём выполнения SQL-инструкции INSERT:

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# SQL 插入语句
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
         LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
   # 执行sql语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# 关闭数据库连接
db.close()

Приведённый выше пример также можно записать в следующей форме:

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# SQL 插入语句
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES (%s, %s, %s, %s, %s )" % \
       ('Mac', 'Mohan', 20, 'M', 2000)
try:
   # 执行sql语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()

# 关闭数据库连接
db.close()

Пример:

В следующем коде используются переменные для передачи параметров в SQL-инструкции:

..................................
user_id = "test123"
password = "password"

con.execute('insert into Login values(%s, %s)' % \
             (user_id, password))
..................................

Операция запроса к базе данных

Python выполняет запросы к MySQL с помощью метода fetchone() для получения одного фрагмента данных и метода fetchall() для получения нескольких фрагментов данных.

fetchone():Этот метод извлекает следующий результирующий набор запроса. Результирующий набор является объектом.
fetchall():Получает все возвращённые строки результата.
rowcount:Это атрибут, доступный только для чтения, и возвращает количество строк, затронутых после выполнения метода execute().

Пример:

Запрос всех данных в таблице EMPLOYEE, где поле salary больше 1000:

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# SQL 查询语句
sql = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > %s" % (1000)
try:
   # 执行SQL语句
   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=%s,sex=%s,income=%s" % \
             (fname, lname, age, sex, income )
except:
   print "Error: unable to fetch data"

# 关闭数据库连接
db.close()

Результат выполнения вышеприведённого скрипта следующий:

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

Операция обновления базы данных

Операция обновления используется для обновления данных в таблицах данных. В следующем примере значение поля AGE записей в таблице EMPLOYEE, у которых поле SEX равно 'M', увеличивается на 1:

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# SQL 更新语句
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()

# 关闭数据库连接
db.close()

Операция удаления

Операция удаления используется для удаления данных из таблиц данных. В следующем примере показано удаление всех данных в таблице EMPLOYEE, где AGE больше 20:

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# SQL 删除语句
sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 提交修改
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()

# 关闭连接
db.close()

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

Механизм транзакций может обеспечивать согласованность данных.

Транзакция должна обладать четырьмя свойствами: атомарностью, согласованностью, изоляцией и долговечностью. Эти четыре свойства обычно называют характеристиками ACID.

Атомарность. Транзакция — это неделимая единица работы; операции, входящие в транзакцию, выполняются либо все, либо ни одна.
Согласованность. Транзакция должна переводить базу данных из одного согласованного состояния в другое согласованное состояние. Согласованность тесно связана с атомарностью.
Изоляция. Выполнение транзакции не может быть нарушено другими транзакциями. То есть операции внутри транзакции и используемые ею данные изолированы от других параллельных транзакций, и одновременно выполняющиеся транзакции не могут мешать друг другу.
Долговечность. Долговечность также называют постоянством; это означает, что после фиксации транзакции её изменения данных в базе данных должны стать постоянными. Последующие операции или сбои не должны оказывать на них никакого влияния.

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

Пример:

# SQL删除记录语句
sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 向数据库提交
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()

Для баз данных, поддерживающих транзакции, в программировании баз данных на Python при создании курсора автоматически начинается неявная транзакция базы данных.

Метод commit() фиксирует все операции обновления курсора, а метод rollback() откатывает все операции текущего курсора. Каждый метод начинает новую транзакцию.

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

DB API определяет некоторые ошибки и исключения для операций с базой данных. В следующей таблице перечислены эти ошибки и исключения:

Исключение	Описание
Предупреждение	Возникает при серьезном предупреждении, например когда вставленные данные усекаются, и т. д. Должен быть подклассом StandardError.
Ошибка	Все классы ошибок, кроме предупреждений. Должен быть подклассом StandardError.
InterfaceError	Возникает при ошибке самого модуля интерфейса базы данных (а не ошибке базы данных). Должен быть подклассом Error.
DatabaseError	Возникает при ошибке, связанной с базой данных. Должен быть подклассом Error.
DataError	Возникает при ошибке обработки данных, например: ошибка деления на ноль, выход данных за допустимые пределы и т. д. Должен быть подклассом DatabaseError.
OperationalError	Относится к ошибкам, которые не зависят от пользователя, но возникают при работе с базой данных. Например: неожиданное отключение, имя базы данных не найдено, сбой обработки транзакции, ошибка выделения памяти и другие ошибки, возникающие при работе с базой данных. Должен быть подклассом DatabaseError.
IntegrityError	Ошибки, связанные с целостностью, например сбой проверки внешнего ключа и т. д. Должен быть подклассом DatabaseError.
InternalError	Внутренние ошибки базы данных, такие как недопустимый курсор, сбой синхронизации транзакции и т. д. Должен быть подклассом DatabaseError.
ProgrammingError	Ошибки программирования, такие как таблица не найдена или уже существует, синтаксические ошибки SQL-запроса, неверное количество параметров и т. д. Должен быть подклассом DatabaseError.
NotSupportedError	Ошибки неподдерживаемых операций, относящиеся к использованию функций или API, которые не поддерживаются базой данных. Например, использование функции .rollback() для объекта соединения, когда база данных не поддерживает транзакции или транзакции были отключены. Должен быть подклассом DatabaseError.

Другие расширения

PythonУглубленное руководство

Работа с базой данных MySQL на Python

Что такое MySQLdb?

Как установить MySQLdb?

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

Пример:

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

Операция вставки в базу данных

Пример:

Операция запроса к базе данных

Пример:

Операция обновления базы данных

Операция удаления

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

Пример:

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