Пособие по MySQL на Python

Начало работы с MySQL на Python

Это руководство поможет вам начать работу с MySQL на Python . Вы узнаете об особенностях MySQL на Python и как установить MySQL Connector / Python на вашей локальной системе.

Введение в MySQL на Python

Для доступа к базе данных MySQL из Python , вам нужен драйвер базы данных. MySQL Connector / Python является стандартизированным драйвером базы данных, предоставляемым MySQL .

MySQL Connector / Python поддерживает почти все функции, предоставляемые MySQL версии 5.7 . Он позволяет конвертировать значения параметров между Python и MySQL , например, Python DateTime и MySQL DATETIME .

MySQL Connector / Python разработан специально для MySQL . Он поддерживает все расширения MySQL для стандартного SQL , такие как условие LIMIT .

MySQL Connector / Python позволяет сжимать поток данных между Python и сервером базы данных MySQL с использованием сжатия протоколов. Он поддерживает соединения с использованием сокета TCP / IP и безопасные TCP / IP соединения, использующие SSL .

MySQL Connector / Python представляет собой API , реализованнный с помощью чистого Python . Это означает, что вам не нужно устанавливать какую-либо клиентскую библиотеку MySQL или модули Python , кроме стандартной библиотеки.

В этом пособии мы будем рассматривать MySQL / Python 2.0 , который поддерживает Python версий 2.6, 2.7 и 3 .3 .

Скачивание коннектора MySQL Python

Для работы с коннектором MySQL Python вам необходимо скачать и установить его в вашей локальной системе. Доступны версии для различных платформ: Mac OS X , Microsoft Windows , Ubuntu Linux и т.д. Вам просто нужно выбрать нужную платформу и запустить скачивание.

Установка коннектора MySQL Python

Процесс установки коннектора MySQL Python довольно прост. Например, чтобы установить его в среде Windows , нужно выполнить следующие действия:

• Распаковать загруженный файл во временный каталог, например, C: Temp ;

• Открыть окно консоли и переключиться на папку, в которую вы распаковали коннектор:

• В папке C: Temp использовать следующую команду:

Проверка правильности установки MySQL Connector / Python

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

• Откройте командную строку Python ;
• Введите следующий код:

Если на экране появится приведенный ниже текст, значит, вы успешно установили коннектор MySQL Python на вашей системе:

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

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

В этом разделе вы узнаете о различных способах подключения к базам данных MySQL из Python с использованием MySQL Connector / Python API .

Подготовка экземпляра базы данных

Во-первых, для этого пособия мы создаем новую базу данных с именем python_mysql . Чтобы создать новую базу данных, вы можете запустить MySQL Workbench или клиентский инструмент MySQL и использовать оператор CREATE DATABASE следующим образом:

Во-вторых, вам нужно загрузить данные в базу данных python_mysql.sql из файла python_mysql.sql .

Пример базы данных Python MySQL

Подключение к базе данных MySQL с помощью функции connect()

Давайте рассмотрим следующий модуль Python (python_mysql_connect1.py) :

Давайте рассмотрим этот модуль в деталях:

• Во-первых, мы импортируем объекты mysql.connector и Error из пакета MySQL Connector / Python ;
• Во-вторых, для подключения к базе данных MySQL мы используем функцию connect() , которая принимает следующие параметры: хост, база данных, пользователь и пароль. Функция connect() устанавливает соединение с базой данных python_mysql и возвращает объект MySQLConnection ;
• В-третьих, мы проверяем, было ли успешно установлено соединение с базой данных MySQL с помощью метода is_connected() . В случае возникновения исключений, например, если сервер базы данных не доступен, база данных не существует, имя пользователя или пароль неверны и т.д., Python вызовет исключение Error . Мы обрабатываем это исключение, используя блок try except ;
• В-четвертых, если не произошло исключение, мы закрываем соединение с базой данных, вызвав метод Close() объекта MySQLConnection .

Для тестирования модуля python_mysql_connect1.py, используется следующая команда:

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

Подключение к базе данных MySQL с помощью объекта MySQLConnection

В этом примере мы создадим конфигурационный файл базы данных с именем config.ini и определим раздел с четырьмя параметрами следующим образом:

Мы можем создать новый модуль с именем python_mysql_dbconfig.py , который считывает конфигурацию базы данных из файла config.ini и возвращает словарь следующим образом:

Обратите внимание, что мы использовали пакет ConfigureParser , чтобы считать файл конфигурации.

Давайте проверим этот модуль в REPL :

Он работает, как ожидалось.

Теперь мы можем создать новый модуль python_mysql_connect2.py , который использует объект MySQLConnection для подключения к базе данных python_mysql :

Давайте рассмотрим приведенный выше код более подробно:

• Во-первых, мы импортировали необходимые объекты, в том числе MySQLConnection , Error из пакета MySQL Connector / Python и read_db_config из модуля python_mysql_dbconfig , который мы разработали;
• Во-вторых, внутри функции Connect() , мы считали конфигурацию базы данных и использовали ее для создания нового экземпляра объекта MySQLConnection . Остальная часть кода работает аналогично первому примеру.

Когда мы запускаем python_mysql_connect2 в окне консоли, мы получаем следующий результат:

В этом разделе мы рассмотрели, как подключаться к базам данных MySQL с помощью функцию connect() и объекта MySQLConnection . Оба способа дают тот же результат — устанавливают соединение с базой данных MySQL и возвращают объект MySQLConnection .

Запросы Python MySQL

В этом разделе мы покажем, как запрашивать данные из базы данных MySQL в Python с использованием MySQL Connector / Python API, таких как fetchone(), fetchmany() и fetchall().

Для запроса данных из базы данных MySQL из Python вам нужно сделать следующее:

• Подключиться к базе данных MySQL , вы получаете объект MySQLConnection ;
• Установить экземпляр объекта MySQLCursor из объекта MySQLConnection ;
• Использовать курсора для выполнения запроса путем вызова метода execute() ;
• Использовать методы fetchone() , fetchmany() и fetchall() для выборки данных из результативного набора;
• Закрыть курсор, а также подключение к базе данных, вызвав метод close() соответствующего объекта.

Мы расскажем, как использовать методы fetchone() , fetchmany() и fetchall() более подробно в следующих разделах.

Запрос данных с помощью fetchone

Метод fetchone() возвращает следующую строку набора результатов запроса или Non e в случае, если строк не осталось. Давайте посмотрим на следующий код:

Давайте рассмотрим его более подробно:

• Во-первых, мы подключаемся к базе данных, создав новый объект MySQLConnection ;
• Во-вторых, из объекта MySQLConnection мы устанавливаем новый объект MySQLCursor ;
• В-третьих, мы выполняем запрос, который выбирает все строки из таблицы books ;
• В-четвертых, мы вызываем метод fetchone() , чтобы выбрать следующую строку из набора результатов. В блоке while loop мы выводим содержимое строки и переходим к следующей строке, пока все строки не будут выбраны;
• В-пятых, мы закрываем курсор и объект подключения через вызов метода close() соответствующего объекта.

Запрос данных с помощью fetchall

В том случае, если число строк в таблице мало, вы можете использовать для извлечения всех строк из таблицы базы данных метод fetchall() . Рассмотрим следующий код:

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

Запрос данных с помощью fetchmany

Для сравнительно больших таблиц извлечение всех строк и возвращение набора результатов может занять значительное время. Кроме того, для fetchall() необходимо выделение достаточного объема памяти для хранения всего набора результатов. Это не слишком эффективно.

MySQL Connector / Python предоставляет нам метод fetchmany() , который возвращает следующее количество строк (n) набора результатов, что позволяет нам эффективно использовать объем памяти за оптимальное время. Давайте рассмотрим, как используется метод fetchmany() .

Во-первых, мы разрабатываем генератор, который разбивает вызовы базы данных на серию вызовов fetchmany() следующим образом:

Во-вторых, мы можем использовать генератор iter_row() для извлечения 10 строк за раз, как это показано ниже:

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

Вставка данных в Python MySQL

В этом разделе мы расскажем, как вставлять данные в таблицы MySQL с использованием MySQL Connector / Python API .

Чтобы вставить новые строки в таблицу MySQL необходимо выполнить следующие действия:

• Подключиться к серверу базы данных MySQL , создав новый объект MySQLConnection ;
• Инициировать объект MySQLCursor из объекта MySQLConnection ;
• Выполнить оператор INSERT для вставки данных в соответствующую таблицу;
• Закрыть соединение с базой данных.

MySQL Connector / Python предоставляет API , который позволяет вставить за один раз одну или несколько строк в таблицу. Давайте рассмотрим каждый метод более подробно.

Вставка одной строки в таблицу

Следующий код вставляет новую книгу в таблицу books :

В приведенном выше коде мы:

• Во-первых, импортируем объекты MySQLConnection и Error из пакета MySQL Connector / Python и функцию read_db_config() из модуля python_mysql_dbconfig ;
• Во-вторых, определяем новую функцию под названием insert_book() , которая принимает два аргумента: название и ISBN . Внутри функции insert_book() , мы готовим оператор INSERT ( запрос ) и данные ( аргументы ), которые мы будем вставлять в таблицу books . Обратите внимание, что данные, которые мы передаем в функцию, это кортеж;
• В-третьих, внутри блока try except мы создаем новое подключение, выполняем оператор и утверждаем изменения. Обратите внимание, что вы должны вызвать метод commit() явно для того, чтобы изменения в базу данных были внесены. В случае, если новая строка была вставлена успешно, мы можем получить последний вставленный id столбца АUTO INCREMENT с помощью свойство lastrowid объекта MySQLCursor ;
• В-четвертых, в конце функции insert_book() мы закрываем курсор и соединение с базой данных;
• В-пятых, в функции main() мы вызываем функцию insert_book() и передаем title и isbn , чтобы вставить новую строку в таблицу books .

Вставка нескольких строк в таблицу

Оператор MySQL INSERT позволяет вставить сразу несколько строк с помощью синтаксиса VALUES . Вам просто нужно включить несколько списков значений столбцов. Каждый список заключен в скобки и разделен запятыми. Например, чтобы вставить несколько книг в таблицу books используется следующий оператор:

Логика в этом примере аналогична логике первого примера. Только вместо вызова метода execute() мы используем метод executemany() .

В функции main() мы передаем список кортежей, каждый из которых содержит название и ISBN книги.

Вызвав метод executemany() объекта MySQLCursor, MySQL Connector / Python переводит оператор INSERT в оператор, который содержит несколько списков значений.

В этом разделе мы рассмотрели, как вставить одну или несколько строк в таблицу MySQL в Python .

Обновление данных в Python MySQL

В этом разделе мы рассмотрим действия, необходимые для обновления данных в таблице MySQL с помощью MySQL Connector / Python API .

Для обновления данных в таблице MySQL в Python , вам нужно выполнить следующие действия:

• Подключиться к серверу базы данных MySQL , создав новый объект MySQLConnection ;
• Создать новый объект MySQLCursor из объекта MySQLConnection и вызвать метод execute() объекта MySQLCursor . Чтобы утвердить изменения, нужно вызвать метод commit() объекта MySQLConnection после вызова метода execute() . В противном случае никакие изменения в базу данных внесены не будут;
• Закрыть курсор и соединение с базой данных.

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

В этом модуле мы использовали функцию read_db_config() из модуля python_mysql_dbconfig , который мы создали в разделе Подключение к базе данных через Python.

Внутри оператора UPDATE мы размещаем два заполнителя ( % ), один для названия книги, второй — для ID книги. Мы передали оба кортежа оператора UPDATE ( query ) и ( title,id ) в метод execute() . Коннектор интерпретирует запрос следующим образом:

Важно помнить, что мы всегда должны использовать заполнители ( % ) внутри любых операторов SQL , которые содержат информацию пользователей. Это помогает нам предотвратить потенциально вредоносные действия.

Давайте проверим наш новый модуль, чтобы убедиться, если он работает.

Во-первых, мы выбираем книгу с ID 37 :

В-третьих, мы выбираем запись книги, снова выполнив оператор SELECT , чтобы увидеть, действительно ли запись изменилась.

В этом разделе вы рассказали, как обновлять данные с помощью MySQL Connector / Python API .

Удаление данных в MySQL на Python

В этом разделе мы рассмотрим этапы удаления данных из базы данных MySQL с помощью MySQL Python .

Для удаления строк в таблице MySQL через Python вам нужно совершить следующие действия:

• Подключиться к серверу базы данных MySQL , создав новый объект MySQLConnection ;
• Создать новый объект MySQLCursor из объекта MySQLConnection и вызвать метод execute() объекта MySQLCursor . Чтобы утвердить изменения, нужно вызвать метод commit() объекта MySQLConnection после вызова метода execute() ;
• Закрыть курсора и соединение с базой данных, вызвав метод close() соответствующего объекта.

В следующем примере показано, как удалить книгу с указанным ID :

Обратите внимание, что мы используем функцию read_db_config() из модуля python_mysql_dbconfig , который мы разработали в предыдущих разделах.

Так как нам нужно удалить из таблицы books конкретную строку, мы должны разместить заполнитель ( % ) на стороне оператора DELETE .

Когда мы вызываем метод execute() , мы передаем ему и оператор DELETE и кортеж ( book_id, ). Коннектор интерпретирует оператор DELETE в следующую форму:

Вы должны всегда использовать заполнители внутри любого запроса, который вы передаете в метод execute() .
Это помогает нам предотвратить потенциально вредоносные действия.

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

В этом разделе мы рассмотрели, как удалить данные из таблицы MySQL с использованием MySQL Connector / Python API.

Вызов в Python хранимых процедур MySQL

В этом разделе мы покажем, как вызывать в Python хранимые процедуры MySQL с использованием MySQL Connector / Python API .

Прежде чем мы начнем

В этом разделе в качестве демонстрации мы создадим две хранимые процедуры. Первая — для получения всех книг с информацией об авторе из таблиц books и authors :

Хранимая процедура find_all() содержит оператор SELECT с условием JOIN , который извлекает название, ISBN и полное имя автора из таблиц books и authors . Когда мы выполняем хранимую процедуру find_all() , она возвращает следующий результат:

find_by_isbn() принимает два параметра: первый параметр ISBN ( параметр IN ), второй — заголовок ( OUT параметр ). Когда вы передаете в хранимую процедуру ISBN , вы получаете название книги, например:

Вызов хранимых процедур из Python

Для вызова хранимой процедуры в Python , вам нужно выполнить следующие действия:

• Подключиться к серверу базы данных MySQL , создав новый объект MySQLConnection ;
• Создать новый объект MySQLCursor из объекта MySQLConnection , вызвав метод cursor() ;
• Вызвать метод callproc() объекта MySQLCursor . Вы передаете имя хранимой процедуры в качестве первого аргумента метода callproc() . Если для хранимой процедуры требуются параметры, вы должны передать их список в качестве второго аргумента метода callproc() . В случае, если хранимая процедура возвращает набор результатов, вы можете ссылаться на метод stored_results() объекта MySQLCursor , чтобы получить итератор списка и перебрать этот набор результатов с помощью метода fetchall() ;
• Закрыть курсора и подключение к базе данных, как всегда.

Следующий пример демонстрирует, как вызывать хранимую процедуру find_all()в Python и выводить набор результатов:

В следующем примере показано, как вызвать хранимую процедуру find_by_isbn() :

Для хранимой процедуры find_by_isbn() требуются два параметра, следовательно, мы должны передать список ( args ), который содержит два элемента: первый из них ISBN ( 1236400967773 ), а второй 0 . Второй элемент списке аргументов ( 0 ) — это просто заполнитель содержащий параметр p_title .

Метод callproc() возвращает список ( result_args ), который содержит два элемента, где второй элемент ( result_args[1] ) содержит значение параметра p_title .

В этом разделе мы рассмотрели, как вызываются хранимые процедуры через Python с использованием метода callproc() объекта MySQLCursor .

Работа в Python MySQL с BLOB

В этом разделе мы рассмотрим, как работать в Python с данными MySQL BLOB , а именно примеры обновления и чтения данных BLOB .

В таблице authors содержится столбец с именем photo , в котором хранятся данные типа BLO B. Мы считаем данные из файла изображения и обновим ими столбец photo .

Обновление в Python BLOB-данных

Во-первых, мы разрабатываем функцию с именем read_file() , которая считывает файл и возвращает содержимое файла:

Во-вторых, мы создаем новую функцию под названием update_blob() , которая обновляет фото автора, указанного с помощью author_id .

Давайте подробно рассмотрим этот код:

• Во-первых, мы вызываем функцию read_file() , которая считывает данные из файла и возвращает их;
• Во-вторых, мы составляем оператор UPDATE , который обновляет столбец фото автора, указанного с помощью author_id . Переменная args — это кортеж, который содержит данные файла и author_id . Мы передаем эту переменную в метод execute() вместе с query ;
• В-третьих, внутри блока try except мы подключаемся к базе данных, устанавливаем курсор и выполняем запрос с args . Для того чтобы изменения вступили в силу, мы вызываем метод commit() объекта MySQLConnection ;
• В-четвертых, мы закрываем курсора и соединение с базой данных в блоке finally .

Обратите внимание, что мы импортировали объекты MySQLConnection и Error из пакета MySQL Connector / Python и функцию read_db_config() из модуля python_mysql_dbconfig , который мы разработали в предыдущих разделах.

Давайте протестируем функцию update_blob() :

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

Чтение данных BLOB в Python

В этом примере мы выбираем BLOB -данные из таблицы авторов и записываем их в файл.

Во-первых, мы разрабатываем функцию write_file() , которая записывает двоичные данные в файл следующим образом:

Во-вторых, мы создаем новую функцию под названием read_blob() :

Функция read_blob() считывает BLOB -данные из таблицы authors и записывает их в файл, указанный в параметре имени файла.

Этот код действует очень просто:

• Во-первых, мы составляем оператор SELECT , который извлекает фотографию конкретного автора;
• Во-вторых, мы получаем конфигурацию базы данных, вызвав функцию read_db_config() ;
• В-третьих, внутри блока try except мы подключаемся к базе данных, устанавливаем курсор и выполняем запрос. После того, как мы получили BLOB -данные, мы используем функцию write_file() , чтобы записать их в файл, указанный в имени файла;
• В-четвертых, в конечном блоке мы закрываем курсор и соединение с базой данных.

Теперь, давайте проверим функцию read_blob() :

Если вы откроете папку вывода в проекте и увидите там картинку, это означает, что вы успешно считали BLOB -данные.
В этом разделе, мы рассказали, как обновлять и считывать BLOB -данные в MySQL из Python с использованием MySQL Connector / API .