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

### Начало работы с MySQL на Python <a href="#header-9658-1" id="header-9658-1"></a>

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

#### Введение в MySQL на Python <a href="#header-9658-2" id="header-9658-2"></a>

Для доступа к базе данных 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 <a href="#header-9658-3" id="header-9658-3"></a>

Для работы с коннектором MySQL Python вам необходимо [скачать](http://dev.mysql.com/downloads/connector/python/2.0.html) и установить его в вашей локальной системе. Доступны версии для различных платформ: Mac OS X, Microsoft Windows, Ubuntu Linux и т.д. Вам просто нужно выбрать нужную платформу и запустить скачивание.

#### Установка коннектора MySQL Python <a href="#header-9658-4" id="header-9658-4"></a>

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

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

```
> cd c:temp
```

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

```
c:temp > python setup.py install
```

#### Проверка правильности установки MySQL Connector / Python <a href="#header-9658-5" id="header-9658-5"></a>

После установки коннектора 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 <a href="#header-9658-6" id="header-9658-6"></a>

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

#### Подготовка экземпляра базы данных <a href="#header-9658-7" id="header-9658-7"></a>

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

```
1	CREATE DATABASE python_mysql;
```

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

[Пример базы данных Python MySQL](http://www.mysqltutorial.org/download/16)

#### Подключение к базе данных MySQL с помощью функции connect() <a href="#header-9658-8" id="header-9658-8"></a>

Давайте рассмотрим следующий модуль 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 <a href="#header-9658-9" id="header-9658-9"></a>

В этом примере мы создадим конфигурационный файл базы данных с именем 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 <a href="#header-9658-10" id="header-9658-10"></a>

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

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

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

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

#### Запрос данных с помощью fetchone <a href="#header-9658-11" id="header-9658-11"></a>

Метод 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 <a href="#header-9658-12" id="header-9658-12"></a>

В том случае, если число строк в таблице мало, вы можете использовать для извлечения всех строк из таблицы базы данных метод 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 <a href="#header-9658-13" id="header-9658-13"></a>

Для сравнительно больших таблиц извлечение всех строк и возвращение набора результатов может занять значительное время. Кроме того, для 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 <a href="#header-9658-17" id="header-9658-17"></a>

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

#### Вставка нескольких строк в таблицу <a href="#header-9658-16" id="header-9658-16"></a>

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

#### Вставка одной строки в таблицу <a href="#header-9658-15" id="header-9658-15"></a>

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

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

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

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

#### Вставка данных в Python MySQL <a href="#header-9658-14" id="header-9658-14"></a>

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

![Обновление данных в Python MySQL - 2](https://www.internet-technologies.ru/wp-content/uploads/articles/201411/deistvitelno-li-zapis-i-230255.png)

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

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

#### Удаление данных в MySQL на Python <a href="#header-9658-18" id="header-9658-18"></a>

В этом разделе мы рассмотрим этапы удаления данных из базы данных 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;
```

![Удаление данных в MySQL на Python](https://www.internet-technologies.ru/wp-content/uploads/articles/201411/proverim-tablicu-230326.png)

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

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

#### Вызов в Python хранимых процедур MySQL <a href="#header-9658-19" id="header-9658-19"></a>

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

#### Прежде чем мы начнем <a href="#header-9658-20" id="header-9658-20"></a>

В этом разделе в качестве демонстрации мы создадим две хранимые процедуры. Первая - для получения всех книг с информацией об авторе из таблиц 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 <a href="#header-9658-21" id="header-9658-21"></a>

Для вызова хранимой процедуры в 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 <a href="#header-9658-22" id="header-9658-22"></a>

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

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

#### Обновление в Python BLOB-данных <a href="#header-9658-23" id="header-9658-23"></a>

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

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

![Обновление в Python BLOB-данных](https://www.internet-technologies.ru/wp-content/uploads/articles/201411/pomestit-ee-230337.jpg)

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

```
SELECT * FROM authors
WHERE id = 144;
```

![Обновление в Python BLOB-данных - 2](https://www.internet-technologies.ru/wp-content/uploads/articles/201411/dannie-iz-tablici-230339.png)

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

#### Чтение данных BLOB в Python <a href="#header-9658-24" id="header-9658-24"></a>

В этом примере мы выбираем 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/](https://www.mysqltutorial.org/python-mysql/)[\
](https://www.internet-technologies.ru/team/sergey-benzenko.html)\
\
