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

Это руководство поможет вам начать работу с MySQL на Python. Вы узнаете об особенностях MySQL на Python и как установить MySQL Connector / 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 вам необходимо скачать и установить его в вашей локальной системе. Доступны версии для различных платформ: Mac OS X, Microsoft Windows, Ubuntu Linux и т.д. Вам просто нужно выбрать нужную платформу и запустить скачивание.

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

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

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

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

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

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

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

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

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

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

Давайте рассмотрим следующий модуль 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, что нельзя назвать хорошей практикой. Поэтому давайте исправим это.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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 используется следующий оператор:

INSERT INTO books(title,isbn)
VALUES('Harry Potter And The Order Of The Phoenix', '9780439358071'),
       ('Gone with the Wind', '9780446675536'),
       ('Pride and Prejudice (Modern Library Classics)', '9780679783268');
Чтобы вставить несколько строк в таблицу в Python используется метод executemany() объекта MySQLCursor. Смотрите следующий код:
	from mysql.connector import MySQLConnection, Error
from python_mysql_dbconfig import read_db_config

def insert_books(books):
    query = "INSERT INTO books(title,isbn) " 
            "VALUES(%s,%s)"

    try:
        conn = MySQLConnection(**db_config)

        cursor = conn.cursor()
        cursor.executemany(query, books)

        conn.commit()
    except Error as e:
        print('Error:', e)

    finally:
        cursor.close()
        conn.close()

def main():
    books = [('Harry Potter And The Order Of The Phoenix', '9780439358071'),
             ('Gone with the Wind', '9780446675536'),
             ('Pride and Prejudice (Modern Library Classics)', '9780679783268')]
    insert_books(books)

if __name__ == '__main__':
    main()

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

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

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

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

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

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

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

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

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

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

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

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

Во-вторых, мы запускаем модуль.

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

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

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

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

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

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

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

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

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

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

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

После запуска приведенного выше модуля, мы снова выполняем оператор SELECT. Строка не возвращается. Это означает, что модуль успешно удалил запись.

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

В этом разделе мы покажем, как вызывать в 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, вам нужно выполнить следующие действия:

Следующий пример демонстрирует, как вызывать хранимую процедуру 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, а именно примеры обновления и чтения данных BLOB.

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

Во-первых, мы разрабатываем функцию с именем 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():

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

Внутри основной функции, мы вызываем функцию update_blob() для обновления столбца фото для автора с идентификатором 144. Чтобы проверить результат, мы выбираем данные из таблицы authors:

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

В этом примере мы выбираем 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.