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

2014-11-22

Начало работы с 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;

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

> cd c:temp

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

c:temp > python setup.py install

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

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

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

>>> import mysql.connector
>>> mysql.connector.connect(host='localhost',database='mysql',user='root',password='')

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

<mysql.connector.connection.MySQLConnection object at 0x0187AE50>

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

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

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

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

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

1	CREATE DATABASE python_mysql;

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

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

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

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

import mysql.connector
from mysql.connector import Error

def connect():
    """ Connect to MySQL database """
    try:
        conn = mysql.connector.connect(host='localhost',
                                       database='python_mysql',
                                       user='root',
                                       password='secret')
        if conn.is_connected():
            print('Connected to MySQL database')

    except Error as e:
        print(e)

    finally:
        conn.close()

if __name__ == '__main__':
    connect()

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

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

>python python_mysql_connect1.py
Connected to MySQL database

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

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

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

[mysql]
host = localhost
database = python_mysql
user = root
password =

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

from configparser import ConfigParser
 
 
def read_db_config(filename='config.ini', section='mysql'):
    """ Read database configuration file and return a dictionary object
    :param filename: name of the configuration file
    :param section: section of database configuration
    :return: a dictionary of database parameters
    """
    # create parser and read ini configuration file
    parser = ConfigParser()
    parser.read(filename)
 
    # get section, default to mysql
    db = {}
    if parser.has_section(section):
        items = parser.items(section)
        for item in items:
            db[item[0]] = item[1]
    else:
        raise Exception('{0} not found in the {1} file'.format(section, filename))
 
    return db

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

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

>>> from python_mysql_dbconfig import read_db_config
>>> read_db_config()
{'password': '', 'host': 'localhost', 'user': 'root', 'database': 'python_mysql'}

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

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

from mysql.connector import MySQLConnection, Error
from python_mysql_dbconfig import read_db_config

def connect():
    """ Connect to MySQL database """

    db_config = read_db_config()

    try:
        print('Connecting to MySQL database...')
        conn = MySQLConnection(**db_config)

        if conn.is_connected():
            print('connection established.')
        else:
            print('connection failed.')

    except Error as error:
        print(error)

    finally:
        conn.close()
        print('Connection closed.')

if __name__ == '__main__':
    connect()

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

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

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

>python python_mysql_connect2.py
Connecting to MySQL database...
connection established.
Connection closed.

В этом разделе мы рассмотрели, как подключаться к базам данных 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() возвращает следующую строку набора результатов запроса или None в случае, если строк не осталось. Давайте посмотрим на следующий код:

from mysql.connector import MySQLConnection, Error
from python_mysql_dbconfig import read_db_config

def query_with_fetchone():
    try:
        dbconfig = read_db_config()
        conn = MySQLConnection(**dbconfig)
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM books")

        row = cursor.fetchone()

        while row is not None:
            print(row)
            row = cursor.fetchone()

    except Error as e:
        print(e)

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

if __name__ == '__main__':
    query_with_fetchone()

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

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

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

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

from mysql.connector import MySQLConnection, Error
from python_mysql_dbconfig import read_db_config

def query_with_fetchall():
    try:
        dbconfig = read_db_config()
        conn = MySQLConnection(**dbconfig)
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM books")
        rows = cursor.fetchall()

        print('Total Row(s):', cursor.rowcount)
        for row in rows:
            print(row)

    except Error as e:
        print(e)

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

if __name__ == '__main__':
    query_with_fetchall()

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

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

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

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

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

def iter_row(cursor, size=10):
    while True:
        rows = cursor.fetchmany(size)
        if not rows:
            break
        for row in rows:
            yield row

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

def query_with_fetchmany():
    try:
        dbconfig = read_db_config()
        conn = MySQLConnection(**dbconfig)
        cursor = conn.cursor()

        cursor.execute("SELECT * FROM books")

        for row in iter_row(cursor, 10):
            print(row)

    except Error as e:
        print(e)

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

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

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

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

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

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

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

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

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

from mysql.connector import MySQLConnection, Error
from python_mysql_dbconfig import read_db_config

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

    try:
        db_config = read_db_config()
        conn = MySQLConnection(**db_config)

        cursor = conn.cursor()
        cursor.execute(query, args)

        if cursor.lastrowid:
            print('last insert id', cursor.lastrowid)
        else:
            print('last insert id not found')

        conn.commit()
    except Error as error:
        print(error)

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

def main():
   insert_book('A Sudden Light','9781439187036')

if __name__ == '__main__':
    main()

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

Во-первых, импортируем объекты 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.

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

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

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

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

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

from mysql.connector import MySQLConnection, Error
from python_mysql_dbconfig import read_db_config

def update_book(book_id, title):
    # read database configuration
    db_config = read_db_config()

    # prepare query and data
    query = """ UPDATE books
                SET title = %s
                WHERE id = %s """

    data = (title, book_id)

    try:
        conn = MySQLConnection(**db_config)

        # update book title
        cursor = conn.cursor()
        cursor.execute(query, data)

        # accept the changes
        conn.commit()

    except Error as error:
        print(error)

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

if __name__ == '__main__':
    update_book(37, 'The Giant on the Hill *** TEST ***')

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

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

UPDATE books
SET title = 'The Giant on the Hill *** TEST ***'
WHERE id = 37

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

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

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

SELECT * FROM books
WHERE 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:

from mysql.connector import MySQLConnection, Error
from python_mysql_dbconfig import read_db_config

def delete_book(book_id):
    db_config = read_db_config()

    query = "DELETE FROM books WHERE id = %s"

    try:
        # connect to the database server
        conn = MySQLConnection(**db_config)

        # execute the query
        cursor = conn.cursor()
        cursor.execute(query, (book_id,))

        # accept the change
        conn.commit()

    except Error as error:
        print(error)

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

if __name__ == '__main__':
    delete_book(102)

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

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

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

DELETE FROM books WHERE id = 102

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

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

SELECT * FROM books
WHERE id = 102;

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

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

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

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

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

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

DELIMITER $$

USE python_mysql$$

CREATE PROCEDURE find_all()
BEGIN
 SELECT title, isbn, CONCAT(first_name,' ',last_name) AS author
 FROM books
 INNER JOIN book_author ON book_author.book_id =  books.id
 INNER JOIN AUTHORS ON book_author.author_id = authors.id;
END$$

DELIMITER ;

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

CALL find_all();
[IMG=http://www.mysqltutorial.org/wp-content/uploads/2014/10/python_mysql_stored_procedure_example.png?cf1f9d]
Вторая хранимая процедура с именем find_by_isbn() используется, чтобы найти книгу по ISBN следующим образом:
	DELIMITER $$

CREATE PROCEDURE find_by_isbn(IN p_isbn VARCHAR(13),OUT p_title VARCHAR(255))
    BEGIN
 SELECT title INTO p_title FROM books
 WHERE isbn = p_isbn;
    END$$

DELIMITER ;

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

CALL find_by_isbn('1235927658929',@title);
SELECT @title;

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

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

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

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

from mysql.connector import MySQLConnection, Error
from python_mysql_dbconfig import read_db_config

def call_find_all_sp():
    try:
        db_config = read_db_config()
        conn = MySQLConnection(**db_config)
        cursor = conn.cursor()

        cursor.callproc('find_all')

        # print out the result
        for result in cursor.stored_results():
            print(result.fetchall())

    except Error as e:
        print(e)

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

if __name__ == '__main__':
    call_find_all_sp()

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

from mysql.connector import MySQLConnection, Error
from python_mysql_dbconfig import read_db_config

def call_find_by_isbn():
    try:
        db_config = read_db_config()
        conn = MySQLConnection(**db_config)
        cursor = conn.cursor()

        args = ['1236400967773', 0]
        result_args = cursor.callproc('find_by_isbn', args)

        print(result_args[1])

    except Error as e:
        print(e)

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

if __name__ == '__main__':
    call_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, в котором хранятся данные типа BLOB. Мы считаем данные из файла изображения и обновим ими столбец photo.

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

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

def read_file(filename):
    with open(filename, 'rb') as f:
        photo = f.read()
    return photo

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

from mysql.connector import MySQLConnection, Error
from python_mysql_dbconfig import read_db_config

def update_blob(author_id, filename):
    # read file
    data = read_file(filename)

    # prepare update query and data
    query = "UPDATE authors " 
            "SET photo = %s " 
            "WHERE id  = %s"

    args = (data, author_id)

    db_config = read_db_config()

    try:
        conn = MySQLConnection(**db_config)
        cursor = conn.cursor()
        cursor.execute(query, args)
        conn.commit()
    except Error as e:
        print(e)
    finally:
        cursor.close()
        conn.close()

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

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

def main():
    update_blob(144, "picturesgarth_stein.jpg")

if __name__ == '__main__':
    main()

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

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

SELECT * FROM authors
WHERE id = 144;

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

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

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

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

def write_file(data, filename):
    with open(filename, 'wb') as f:
        f.write(data)

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

def read_blob(author_id, filename):
    # select photo column of a specific author
    query = "SELECT photo FROM authors WHERE id = %s"

    # read database configuration
    db_config = read_db_config()

    try:
        # query blob data form the authors table
        conn = MySQLConnection(**db_config)
        cursor = conn.cursor()
        cursor.execute(query, (author_id,))
        photo = cursor.fetchone()[0]

        # write blob data into a file
        write_file(photo, filename)

    except Error as e:
        print(e)

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

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

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

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

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

def main():
    read_blob(144,"outputgarth_stein.jpg")

if __name__ == '__main__':
    main()

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

George

Блок else никогда не отработает, так как находиться в блоке try, и после ошики не будет выполняться.

{code:python3}
if conn.is_connected():
print('connection established.')
else:
print('connection failed.')
{code}

Ответить

denis

Полезная статья, большое спасибо.

Анон

Отлично!

ИИ

Илья И.

Спасибо за полезную и познавательную статью!) Теперь настало время попробовать это всё на деле (:smile:)

Спасибо за полезную и познавательную статью! Теперь настало время пробовать всё это на деле (:smile:)

Elen

Очень познавательная статья. Спасибо.

в параграфе "Вставка нескольких строк в таблицу" пропущена строчка: db_config = read_db_config() перед conn = MySQLConnection(**db_config)