Пособие по 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
SELECT * FROM books
WHERE id = 37;

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

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

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

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

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

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

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 ***')

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

  • Подключиться к серверу базы данных MySQL, создав новый объект MySQLConnection;

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

  • Закрыть курсор и соединение с базой данных.

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

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

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

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

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

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

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

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()

Оператор MySQL INSERT позволяет вставить сразу несколько строк с помощью синтаксиса VALUES. Вам просто нужно включить несколько списков значений столбцов. Каждый список заключен в скобки и разделен запятыми. Например, чтобы вставить несколько книг в таблицу 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.

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

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()

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

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

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

  • Подключиться к серверу базы данных MySQL, создав новый объект MySQLConnection;

  • Инициировать объект MySQLCursor из объекта MySQLConnection;

  • Выполнить оператор INSERT для вставки данных в соответствующую таблицу;

  • Закрыть соединение с базой данных.

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

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

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

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

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()

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

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

В-третьих, мы выбираем запись книги, снова выполнив оператор 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.

https://www.mysqltutorial.org/python-mysql/

PreviousРезультат запиту у зміннуNextЗмінні

Last updated