Подключение к базе данных MySQL на Python3 - драйвер PyMySQL

В этой статье рассказывается об использовании Python3PyMySQLдля подключения к базе данных и выполнения простых операций CRUD.

Что такое PyMySQL?

PyMySQL — это библиотека, используемая в Python3.x для подключения к серверу MySQL, тогда как в Python2 используется mysqldb.

PyMySQL соответствует спецификации Python Database API v2.0 и включает клиентскую библиотеку MySQL на чистом Python.

Установка PyMySQL

Перед использованием PyMySQL нужно убедиться, что она установлена.

Адрес загрузки PyMySQL:https://github.com/PyMySQL/PyMySQL。

Если она еще не установлена, можно использовать следующую команду для установки последней версии PyMySQL:

$ pip3 install PyMySQL

Если ваша система не поддерживает команду pip, можно установить её следующими способами:

1. Используйте команду git, чтобы скачать и установить установочный пакет (также можно скачать его вручную):

$ git clone https://github.com/PyMySQL/PyMySQL
$ cd PyMySQL/
$ python3 setup.py install

2. Если нужно указать номер версии, можно использовать команду curl для установки:

$ # X.X 为 PyMySQL 的版本号
$ curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz
$ cd PyMySQL*
$ python3 setup.py install
$ # 现在你可以删除 PyMySQL* 目录

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

Во время установки может появиться сообщение об ошибке "ImportError: No module named setuptools", что означает, что вы не установили setuptools. Вы можете посетитьhttps://pypi.python.org/pypi/setuptoolsчтобы найти способы установки для каждой системы.

Пример установки в системе Linux:

$ wget https://bootstrap.pypa.io/ez_setup.py
$ python3 ez_setup.py

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

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

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

Пример:

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

Пример (Python 3.0+)

#!/usr/bin/python3 import pymysql # Открыть подключение к базе данных db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB') # Используйте метод cursor() для создания объекта курсора cursor cursor = db.cursor() # Используйте метод execute() для выполнения SQL-запроса cursor.execute("SELECT VERSION()") # Используйте метод fetchone() для получения одного фрагмента данных. data = cursor.fetchone() print ("Версия базы данных: %s " % data) # Закрыть подключение к базе данных db.close()

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

Database version : 5.5.20-log

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

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

Пример (Python 3.0+)

#!/usr/bin/python3 import pymysql # Открыть подключение к базе данных db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB') # Используйте метод cursor() для создания объекта курсора cursor cursor = db.cursor() # Используйте метод execute() для выполнения SQL, удалите таблицу, если она существует 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()

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

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

Пример (Python 3.0+)

#!/usr/bin/python3 import pymysql # Открыть подключение к базе данных db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB') # Используйте метод cursor() для получения рабочего курсора cursor = db.cursor() # Инструкция SQL INSERT 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: # Выполнить откат при возникновении ошибки db.rollback() # Закройте соединение с базой данных db.close()

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

Пример (Python 3.0+)

#!/usr/bin/python3 import pymysql # Открыть подключение к базе данных db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB') # Используйте метод cursor() для получения рабочего курсора cursor = db.cursor() # Инструкция SQL INSERT 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) # Выполнить 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 (wage) больше 1000:

Пример (Python 3.0+)

#!/usr/bin/python3 import pymysql # Открыть подключение к базе данных db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB') # Используйте метод 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 ("Ошибка: не удалось получить данные") # Закрыть соединение с базой данных db.close()

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

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

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

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

Пример (Python 3.0+)

#!/usr/bin/python3 import pymysql # Открыть подключение к базе данных db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB') # Используйте метод 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:

Пример (Python 3.0+)

#!/usr/bin/python3 import pymysql # Открыть подключение к базе данных db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB') # Используйте метод 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()

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

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

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

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

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

Пример

Пример (Python 3.0+)

# 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.

Ниже представлена структура наследования исключений:

Exception
|__Warning
|__Error
   |__InterfaceError
   |__DatabaseError
      |__DataError
      |__OperationalError
      |__IntegrityError
      |__InternalError
      |__ProgrammingError
      |__NotSupportedError
Другие расширения