Эта статья была опубликована в рамках Блогатон по Data Science
Введение
Давайте посмотрим на практический пример того, как делать SQL-запросы к серверу MySQL из кода Python: CREATE, SELECT, UPDATE, JOIN и т. Д.
Большинство приложений в той или иной форме взаимодействуют с данными. Следовательно, языки программирования (Python не исключение) предоставляют инструменты для хранения источников данных и доступа к ним. MySQL - одна из самых фантастических и богатых систем управления базами данных (СУБД). В прошлом году он занял второе место после Oracle в рейтинге баз данных.
Используя методы, описанные в этом руководстве, вы можете эффективно интегрировать базу данных MySQL в свое приложение Python. В этом руководстве мы разработаем небольшую базу данных MySQL для системы рейтинга фильмов и узнаем, как извлекать из нее данные с помощью кода Python.
Что вы узнаете после этого урока:
-
Подключите ваше приложение к базе данных MySQL
-
Получение данных через запрос необходимых данных из базы данных
-
Обработка исключений, возникающих при доступе к базе данных
Чтобы получить максимальную отдачу от этого руководства, желательно иметь практические знания о таких концепциях Python, как циклы, функции и обработка исключений. Вы также должны иметь базовое представление о SQL-запросах, таких как, и. для SELECT DROP CREATE JOIN
Сравнение MySQL с другими базами данных SQL
SQL означает язык структурированных запросов, широко используемый язык программирования для управления реляционными базами данных. Возможно, вы слышали о различных СУБД на основе SQL: MySQL, PostgreSQL, SQLite и SQL Server. Все эти базы данных соответствуют стандартам SQL, но отличаются друг от друга деталями.
Благодаря открытому исходному коду MySQL быстро стал лидером на рынке решений SQL. MySQL в настоящее время используется большинством известных технических фирм, таких как Google, LinkedIn, Uber, Netflix, Twitter и другими.
Помимо поддержки сообщества разработчиков ПО с открытым исходным кодом, есть и другие причины успеха MySQL:
-
Простота установки - MySQL удобен в использовании. Базу данных легко создать и настроить. MySQL доступен для основных операционных систем, включая Windows, macOS, Linux и Solaris.
-
Скорость. MySQL имеет репутацию быстрого решения для баз данных. Эта СУБД также хорошо масштабируется.
-
Права пользователя и безопасность - MySQL позволяет вам устанавливать уровни безопасности паролей, добавлять и удалять привилегии для учетных записей пользователей. Управление правами пользователей выглядит намного проще, чем во многих других СУБД, таких как PostgreSQL, где управление файлами конфигурации требует определенных навыков.
Установка MySQL Server и MySQL Connector
MySQL Server и MySQL Connector - единственные два программного обеспечения, которые вам понадобятся для начала работы с этим руководством. Сервер MySQL предоставит ресурсы, необходимые для работы с базой данных. После запуска сервера вы сможете подключить к нему свое приложение Python с помощью MySQL Connector / Python.
Установка сервера MySQL
Официальная документация описывает рекомендуемые способы загрузки и установки MySQL Server. Есть инструкции для всех популярных операционных систем, включая Windows, macOS, Solaris, Linux и многие другие.
Для Windows лучше всего загрузить установщик MySQL и позволить ему позаботиться о процессе. Менеджер установки также поможет вам настроить параметры безопасности для вашего сервера MySQL. На странице учетных записей вам нужно будет ввести пароль для учетной записи root и, при желании, добавить других пользователей с другими привилегиями.
Настройка учетной записи MySQL
Другие полезные инструменты, такие как MySQL Workbench, можно настроить с помощью установщиков. Удобной альтернативой установке в операционной системе является развертывание MySQL с помощью Docker.
Установка MySQL Connector / Python
Драйвер базы данных - программное обеспечение, позволяющее приложению подключаться к СУБД и взаимодействовать с ней. Эти драйверы обычно поставляются в виде отдельных модулей. Стандартный интерфейс, которому должны соответствовать все драйверы базы данных Python, описан в PEP 249. Для установки драйвера (коннектора) мы воспользуемся менеджером пакетов: pip
pip установить mysql-connector-python
pip установит коннектор в текущую активную среду. Чтобы работать с проектом изолированно, мы рекомендуем настроить виртуальную среду.
Давайте проверим результат установки, выполнив следующую команду в терминале Python:
импортировать mysql.connector
Если оператор импорта выполняется без ошибок, значит, он успешно установлен и готов к использованию. MySQL.connector
Установление соединения с сервером MySQL
MySQL - это серверная система управления базами данных. Один сервер может содержать несколько баз данных. Чтобы взаимодействовать с базой данных, мы должны установить соединение с сервером. Пошаговое взаимодействие программы Python с базой данных на основе MySQL выглядит так:
-
Подключаемся к серверу MySQL.
-
Создаем новую базу данных (при необходимости).
-
Подключаемся к базе.
-
Выполняем SQL-запрос, собираем результаты.
-
Сообщаем базе данных, если в таблицу были внесены изменения.
-
Наконец, просто закройте соединение с сервером MySQL.
Каким бы ни было приложение, первым делом необходимо связать приложение и базу данных вместе.
Подключение к серверу MySQL из Python
Чтобы установить соединение, воспользуйтесь модулем. Эта функция принимает параметры, и, и возвращает объект. Учетные данные могут быть получены в результате ввода от пользователя: connect () mysql.connector host user password MySQLConnection
from getpass import getpass from mysql.connector import connect, Error try: with connect (host = "localhost", user = input ("Username:"), password = getpass ("Password:"),) as connection: print (connection ) кроме ошибки как e: print (e)
Объект хранится в переменной, которую мы будем использовать для доступа к серверу MySQL. Несколько важных моментов: подключение MySQLConnection
-
Оберните все соединения с базой данных в блоки. Это упростит обнаружение и изучение любых исключений. попробуй ... кроме
-
Не забудьте закрыть соединение после завершения доступа к базе данных. Неиспользуемые открытые соединения приводят к неожиданным ошибкам и проблемам с производительностью. Для этого в коде используется диспетчер контекста (с… как…).
-
Никогда не следует встраивать учетные данные (имя пользователя и пароль) в строковую форму в скрипт Python. Это плохая практика развертывания и создает серьезную угрозу безопасности. В приведенном выше коде запрашиваются учетные данные для входа. Для этого используется встроенный модуль, скрывающий введенный пароль.
Итак, мы установили связь между нашей программой и сервером MySQL. Теперь вам нужно либо создать новую базу данных, либо подключиться к существующей.
Создать новую базу данных
Чтобы создать новую базу данных, например, с именем, вам необходимо выполнить оператор SQL: online_movie_rating
СОЗДАТЬ БАЗУ ДАННЫХ online_movie_rating;
Внимание
MySQL требует, чтобы вы ставили точку с запятой (😉 в конце оператора. Однако MySQL Connector / Python автоматически добавляет точку с запятой в конце каждого запроса.
Чтобы выполнить SQL-запрос, нам понадобится курсор, который абстрагирует процесс доступа к записям базы данных. MySQL Connector / Python предоставляет соответствующий класс, экземпляр которого также называется курсором. MySQLCursor
Передадим наш запрос на создание базы данных: online_movie_rating
попробуйте: с помощью connect (host = "localhost", user = input ("Имя пользователя:"), password = getpass ("Пароль:"),) в качестве соединения: create_db_query = "СОЗДАТЬ БАЗУ ДАННЫХ online_movie_rating" с помощью connection.cursor () в качестве курсора : cursor.execute (create_db_query) кроме ошибки как e: print (e)
Запрос сохраняется в виде строки в переменной и затем передается для выполнения в CREATE DATABASE create_db_query cursor.execute ()
Если база данных с таким именем уже существует на сервере, мы получим сообщение об ошибке. Используя тот же объект, что и раньше, давайте запустим запрос, чтобы увидеть все таблицы, хранящиеся в базе данных: MySQLConnection SHOW DATABASES
попробуйте: с помощью connect (host = "localhost", user = input ("Имя пользователя:"), password = getpass ("Пароль:"),) в качестве соединения: show_db_query = "ПОКАЗАТЬ БАЗЫ ДАННЫХ" с помощью connection.cursor () в качестве курсора: cursor.execute (show_db_query) для db в курсоре: print (db), кроме ошибки как e: print (e)
ВЫВОД
Введите имя пользователя: root
Введите пароль: ········
('схема_информации',)
('MySQL',)
('online_movie_rating',)
('схема_производительности',)
('sys',)
Приведенный выше код напечатает имена всех баз данных, расположенных на нашем сервере MySQL. Команда в нашем примере также сбрасывает базы данных, которые автоматически создаются сервером MySQL и предоставляют доступ к метаданным базы данных и настройкам сервера. ПОКАЗАТЬ БАЗЫ ДАННЫХ
Подключение к существующей базе данных
Итак, мы создали базу данных под названием. Для подключения к нему мы просто дополняем вызов параметром: online_movie_rating connect () database
попробуйте: с помощью connect (host = "localhost", user = input ("Username:"), password = getpass ("Password:"), database = "online_movie_rating",) as connection: print (соединение), кроме ошибки как e: печать (е)
Создание, изменение и удаление таблиц
В этом разделе мы обсудим, как использовать Python для выполнения некоторых основных запросов: ',' и '.' СОЗДАТЬ ТАБЛИЦУ АЛЬТЕР
Определение схемы базы данных
Начнем с создания схемы базы данных для рейтинговой системы фильмов. Возьмем базу данных, состоящую из трех таблиц:
1. фильмы - общие сведения о фильмах:
-
Id
-
+
-
название
-
год выпуска
-
жанре
-
коллекция_in_mi
2. Рецензенты - информация о людях, опубликовавших рейтинги фильмов:
-
id
-
имя
-
Фамилия
3. Рейтинги - информация о рейтингах фильмов рецензентами:
-
movie_id (внешний ключ)
-
reviewer_id (внешний ключ)
-
рейтинг
Этих трех таблиц достаточно для целей данного руководства.
Схема системы рейтинга фильмов
Таблицы в базе данных связаны друг с другом: фильмы и рецензенты должны иметь отношение «многие ко многим»: один фильм может просматривать несколько рецензентов, а один рецензент может просматривать несколько фильмов. Таблица рейтингов соединяет таблицу фильмов с таблицей рецензента.
Создание таблиц с помощью оператора CREATE TABLE
Чтобы создать новую таблицу в MySQL, нам нужно использовать оператор. Следующий запрос MySQL создаст нашу таблицу базы данных: CREATE TABLE movies online_movie_rating
СОЗДАТЬ ТАБЛИЦУ фильмов (id INT AUTO_INCREMENT PRIMARY KEY, название VARCHAR (100), release_year YEAR (4), жанр VARCHAR (100), collection_in_mil INT);
Если вы уже сталкивались с SQL раньше, вы поймете смысл приведенного выше запроса. Диалект MySQL имеет некоторые отличительные особенности. Например, MySQL предлагает широкий спектр типов данных, в том числе и так далее. Кроме того, MySQL использует ключевое слово, когда значение столбца должно автоматически увеличиваться при вставке новых записей. YEAR INT BIGINT AUTO_INCREMENT
Для создания таблицы необходимо передать указанный запрос в cursor.execute ()
create_movies_table_query = "" "СОЗДАТЬ ТАБЛИЦЫ фильмы (id INT AUTO_INCREMENT PRIMARY KEY, заголовок VARCHAR (100), release_year YEAR (4), жанр VARCHAR (100), collection_in_mil INT)" "" с connection.cursor () в качестве курсора: cursor. выполнить (create_movies_table_query) connection.commit ()
Обратите внимание на оператора. По умолчанию соединитель MySQL не выполняет автоматическую фиксацию транзакций. В MySQL модификации, упомянутые в транзакции, происходят только тогда, когда мы используем команду в конце. Чтобы внести изменения в таблицу, всегда вызывайте этот метод после каждой транзакции. connection.commit () COMMIT
Повторим процедуру для таблицы: обозреватели
create_reviewers_table_query = «» »
CREATE TABLE проверяющие (id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR (100), last_name VARCHAR (100)) "" "с connection.cursor () в качестве курсора: cursor.execute (create_reviewers_table_query) connection.commit () Наконец, давайте создадим рейтинги таблицы: create_ratings_table_query = "" "СОЗДАТЬ ТАБЛИЦУ рейтингов (movie_id INT, reviewer_id INT, рейтинг DECIMAL (2,1), FOREIGN KEY (movie_id) ССЫЛКИ фильмы (id), FOREIGN KEY (reviewer_id) ССЫЛКИ на обозревателей (id), PRIMARY KEY (movie_id, reviewer_id)) "" "с connection.cursor () в качестве курсора: cursor.execute (create_ratings_table_query) connection.commit ()
Реализация отношений внешнего ключа в MySQL немного отличается и имеет ограничения по сравнению со стандартным SQL. В MySQL и родитель, и потомок внешнего ключа должны использовать один и тот же механизм хранения - базовый программный компонент, который система управления базой данных использует для выполнения операций SQL. MySQL предлагает два типа таких механизмов:
-
Механизмы хранения транзакций безопасны для транзакций и позволяют откатывать транзакции с помощью простых команд, таких как. В эту категорию попадают многие популярные движки MySQL, включая InnoDB и NDB. откат
-
Нетранзакционные механизмы хранения полагаются на ручной код для отмены операторов, переданных в базу данных. Это, например, MyISAM и MEMORY.
InnoDB - самый популярный механизм хранения по умолчанию. Обеспечивая ограничения внешнего ключа, он помогает поддерживать целостность данных. Это означает, что любая операция CRUD с внешним ключом предварительно проверяется, чтобы гарантировать, что она не приведет к несогласованности между различными таблицами.
Обратите внимание, что в таблице используются столбцы и два внешних ключа, действующих вместе как первичный ключ. Эта функция гарантирует, что рецензент не сможет дважды оценить один и тот же фильм. рейтинги movie_id reviewer_id
Один и тот же курсор можно использовать для нескольких совпадений. В этом случае все вызовы станут одной атомарной транзакцией. Например, вы можете выполнить все операторы с одним курсором, а затем зафиксировать транзакцию сразу:
СОЗДАТЬ ТАБЛИЦУ с connection.cursor () в качестве курсора: cursor.execute (create_movies_table_query) cursor.execute (create_reviewers_table_query) cursor.execute (create_ratings_table_query) connection.commit ()
Отображение схемы таблицы с помощью оператора DESCRIBE
Мы создали три таблицы и можем просмотреть схему с помощью оператора. ОПИСЫВАТЬ
Предполагая, что у вас уже есть объект в переменной, мы можем распечатать полученные результаты. Этот метод извлекает все строки из последнего выполненного оператора: MySQLConnection connection cursor.fetchall ()
show_table_query = "DESCRIBE movies" с connection.cursor () в качестве курсора: cursor.execute (show_table_query) # Извлечь строки из последнего выполненного запроса result = cursor.fetchall () для строки в результате: print (row)
ВЫВОД
('id', 'int (11)', 'NO', 'PRI', None, 'auto_increment')
('title', 'varchar (100)', 'YES', ”, None,”)
('release_year', 'year (4)', 'YES', ”, None,”)
('жанр', 'varchar (100)', 'ДА', ”, Нет,”)
('collection_in_mil', 'int (11)', 'YES', ”, None,”)
После выполнения вышеуказанного кода мы должны получить таблицу, содержащую информацию о столбцах в таблице. Для каждого столбца отображается информация о типе данных, о том, является ли столбец первичным ключом и т. Д. фильмы
Изменение схемы таблицы с помощью оператора ALTER
В столбце названия таблицы указаны кассовые сборы фильма в миллионах долларов. Мы можем написать следующий оператор MySQL, чтобы изменить тип данных атрибута с на collection_in_mil movies collection_in_mil INT DECIMAL
ИЗМЕНИТЬ ТАБЛИЦУ фильмы ИЗМЕНИТЬ КОЛОНК collection_in_mil DECIMAL (4,1);
DECIMAL (4,1) указывает десятичное число, которое может состоять максимум из четырех цифр, из которых одна соответствует, например, десятому разряду и т. Д. d. 120.1 3.4 38.0
alter_table_query = "" "ALTER TABLE movies MODIFY COLUMN collection_in_mil DECIMAL (4,1)" "" show_table_query = "DESCRIBE movies" с connection.cursor () в качестве курсора: cursor.execute (alter_table_query) cursor.execute (show_table_query) # Получить строки из последнего выполненного запроса result = cursor.fetchall () print ("Схема таблицы фильмов после модификации:") для строки в результате: print (row) Схема таблицы фильмов после внесения изменений: ('id', 'int (11) ',' NO ',' PRI ', None,' auto_increment ') (' title ',' varchar (100) ',' YES ',' ', None,' ') (' release_year ',' год (4) ',' ДА ',' ', Нет,' ') (' жанр ',' varchar (100) ',' ДА ',' ', Нет,' ') (' collection_in_mil ',' decimal (4,1) ',' ДА ',' ', Нет,' ')
Как показано в выходных данных, атрибут также изменил свой тип. Обратите внимание, что в приведенном выше коде мы вызываем дважды, но выбираем строки только из последнего выполненного запроса, то есть. collection_in_mil DECIMAL (4,1) cursor.execute () cursor.fetchall () show_table_query
Удаление таблиц с помощью оператора DROP
Для удаления таблиц используйте оператор. Удаление стола - необратимый процесс. Если вы запустите приведенный ниже код, вам нужно будет снова вызвать запрос к таблице: DROP TABLE CREATE TABLE рейтинги
drop_table_query = "УКАЗАТЬ ТАБЛИЦУ рейтингов" с connection.cursor () в качестве курсора: cursor.execute (drop_table_query)
Вставка записей в таблицы
Наполним таблицы данными. В этом разделе мы рассмотрим два способа вставки записей с помощью коннектора MySQL в код Python.
Первый способ хорошо работает при небольшом количестве записей. Второй лучше подходит для реальных сценариев. .execute () .executemany ()
Вставка записей с помощью .execute ()
Первый подход использует тот же метод, который мы использовали до сих пор. Пишем запрос и отправляем его в cursor.execute () INSERT INTO cursor.execute ()
insert_movies_query = "" "INSERT INTO фильмы (название, год_выпуска, жанр, collection_in_mil) ЗНАЧЕНИЯ (" Форрест Гамп ", 1994," Драма ", 330.2), (" 3 идиота ", 2009," Драма ", 2.4), (" Вечное сияние чистого разума », 2004 г.,« Драма », 34.5), (« Добрая Уилл Хантинг », 1997 г.,« Драма », 138.1), (« Скайфолл », 2012 г.,« Действие », 304.6), (« Гладиатор » », 2000,« Боевик », 188.7), (« Черный », 2005,« Драма », 3.0), (« Титаник », 1997,« Романс », 659.2), (« Искупление Шоушенка », 1994,« Драма »). ", 28.4), (" Удаан ", 2010," Драма ", 1.5), (" Один дома ", 1990," Комедия ", 286.9), (" Касабланка ", 1942," Романс ", 1.0), (" Мстители: Финал », 2019,« Боевик », 858.8), (« Ночь живых мертвецов », 1968,« Ужас », 2.5), (« Крестный отец », 1972,« Преступление », 135.6), (« Хайдер », 2014,« Действие », 4.2), (« Начало », 2010,« Приключение », 293.7), (« Зло », 2003,« Ужас », 1.3), (« История игрушек 4 », 2019,« Анимация. ", 434.9), (" Air Force One ", 1997," Драма ", 138.1), (" Темный рыцарь ", 2008," Действие ", 535.4), (" Bhaag Milkha Bhaag ", 2013," Sport ", 4.1), ("Т он Король Лев », 1994,« Анимация », 423.6), (« Криминальное чтиво », 1994,« Криминал », 108.8), (« Кай По Че », 2013,« Спорт », 6.0), (« Звери нет. Нация », 2015,« Война », 1.4), (« Андадун », 2018,« Триллер », 2.9), (« Молчание ягнят », 1991,« Преступление », 68.2), (« Дэдпул », 2016 , "Action", 363.6), ("Drishyam", 2015, "Mystery", 3.0) "" "с connection.cursor () в качестве курсора: cursor.execute (insert_movies_query) connection.commit ()
Таблица заполнена тридцатью записями. В конце концов, код звонит. Не забудьте позвонить после внесения любых изменений в таблицу. фильмы connection.commit () .commit ()
Вставка записей с помощью .executemany ()
Предыдущий подход хорошо подходит для записи меньшего размера, которую можно легко вставить с помощью кода. Но обычно данные хранятся в файле или генерируются другим скриптом. Вот где это пригодится. Метод принимает два параметра: .executemany ().
-
Запрос, содержащий заполнители для вставляемых записей.
-
Список записей для вставки.
Давайте воспользуемся подходом к заполнению таблицы: рецензенты
insert_reviewers_query = "" "ВСТАВИТЬ рецензентов (first_name, last_name) VALUES (% s,% s)" "" reviewers_records = [("Chaitanya", "Baweja"), ("Mary", "Cooper"), ("John "," Уэйн "), (" Томас "," Стоунмен "), (" Пенни "," Хофштадтер "), (" Митчелл "," Марш "), (" Уайатт "," Скэгс "), (" Андре "," Вейга "), (" Шелдон "," Купер "), (" Кимбра "," Мастерс "), (" Кэт "," Деннингс "), (" Брюс "," Уэйн "), (" Доминго "," Кортес "), (" Раджеш "," Кутраппали "), (" Бен "," Глокер "), (" Махиндер "," Дони "), (" Акбар "," Хан "), (" Ховард »,« Воловиц »), (« Пинки »,« Пети »), (« Гуркаран »,« Сингх »), (« Эми »,« Фара Фаулер »), (« Марлон »,« Краффорд »),] с connection.cursor () в качестве курсора: cursor.executemany (insert_reviewers_query, reviewers_records) connection.commit ()
Этот код имеет заполнители для двух строк, которые вставляются в. Заполнители действуют как спецификаторы формата и помогают зарезервировать место для переменной в строке. % s insert_reviewers_query
Так же заполним таблицу: рейтинги
insert_ratings_query = "" "ВСТАВИТЬ рейтинги (rating, movie_id, reviewer_id) VALUES (% s,% s,% s)" "" rating_records = [(6.4, 17, 5), (5.6, 19, 1), (6.3 , 22, 14), (5.1, 21, 17), (5.0, 5, 5), (6.5, 21, 5), (8.5, 30, 13), (9.7, 6, 4), (8.5, 24 , 12), (9.9, 14, 9), (8.7, 26, 14), (9.9, 6, 10), (5.1, 30, 6), (5.4, 18, 16), (6.2, 6, 20 ), (7.3, 21, 19), (8.1, 17, 18), (5.0, 7, 2), (9.8, 23, 3), (8.0, 22, 9), (8.5, 11, 13), (5.0, 5, 11), (5.7, 8, 2), (7.6, 25, 19), (5.2, 18, 15), (9.7, 13, 3), (5.8, 18, 8), (5.8 , 30, 15), (8.4, 21, 18), (6.2, 23, 16), (7.0, 10, 18), (9.5, 30, 20), (8.9, 3, 19), (6.4, 12 , 2), (7.8, 12, 22), (9.9, 15, 13), (7.5, 20, 17), (9.0, 25, 6), (8.5, 23, 2), (5.3, 30, 17 ), (6.4, 5, 10), (8.1, 5, 21), (5.7, 22, 1), (6.3, 28, 4), (9.8, 13, 1)] с connection.cursor () в качестве курсора : cursor.executemany (insert_ratings_query, rating_records) connection.commit ()
Все три таблицы теперь заполнены данными. Следующий шаг - выяснить, как взаимодействовать с этой базой данных.
Чтение записей из базы данных
Пока мы только создали элементы базы данных. Пришло время выполнить несколько запросов и найти интересующие нас свойства. В этом разделе мы узнаем, как читать записи из таблиц базы данных с помощью оператора. ВЫБРАТЬ
Чтение записей с помощью оператора SELECT
Для получения записей нужно отправить запрос и вернуть результат с помощью: cursor.execute () SELECT cursor.fetchall ()
select_movies_query = "SELECT * FROM movies LIMIT 5" с connection.cursor () в качестве курсора: cursor.execute (select_movies_query) result = cursor.fetchall () для строки в результате: print (row)
ВЫВОД
(1, 'Форрест Гамп', 1994, 'Драма', десятичный ('330.2'))
(2, '3 идиота', 2009, 'Драма', десятичный ('2.4'))
(3, 'Вечное сияние чистого разума', 2004, 'Драма', десятичный ('34.5 '))
(4, 'Умница Уилл Хантинг', 1997, 'Драма', десятичный ('138.1'))
(5, 'Skyfall', 2012, 'Action', Decimal ('304.6'))
Переменная содержит возвращенные записи. Это список кортежей, представляющих отдельные записи в таблице. результат .fetchall ()
В приведенном выше запросе мы используем ключевое слово, чтобы ограничить количество строк, получаемых от оператора. Разработчики часто используются для разбивки вывода на страницы при обработке больших объемов данных. LIMIT SELECT LIMIT
В MySQL оператору можно передать два неотрицательных числовых аргумента: LIMIT
ВЫБРАТЬ * ИЗ фильмов LIMIT 2,5;
При использовании двух числовых аргументов первый указывает смещение, которое в этом примере равно 2, а второй ограничивает количество возвращаемых строк до 5. То есть запрос из примера вернет строки с 3 по 7.
select_movies_query = "ВЫБЕРИТЕ заголовок, год выпуска из фильмов LIMIT 2, 5" с connection.cursor () в качестве курсора: cursor.execute (select_movies_query) для строки в cursor.fetchall (): print (row)
ВЫВОД
('Вечное сияние чистого разума', 2004 г.)
('Умница Уилл Хантинг', 1997)
('Skyfall', 2012)
('Гладиатор', 2000)
('Черный', 2005)
Фильтрация результатов с помощью WHERE
Записи в таблице также можно фильтровать с помощью. Чтобы получить все фильмы с кассовыми сборами более 300 миллионов долларов, выполните следующий запрос: ГДЕ
select_movies_query = "" "ВЫБРАТЬ заголовок, collection_in_mil ИЗ фильмов WHERE collection_in_mil> 300 ORDER BY collection_in_mil DESC" "" с connection.cursor () в качестве курсора: cursor.execute (select_movies_query) для фильма в cursor.fetchall (): print (фильм) ('Мстители: Финал', десятичный ('858.8')) ('Титаник', десятичный ('659.2')) ('Темный рыцарь', десятичный ('535.4')) ('История игрушек 4', десятичный (' 434.9 ')) (' Король Лев ', десятичный (' 423.6 ')) (' Дэдпул ', десятичный (' 363.6 ')) (' Форрест Гамп ', десятичный (' 330.2 ')) (' Skyfall ', десятичный ( '304.6'))
Фраза в запросе позволяет отсортировать комиссию от наибольшей к наименьшей. СОРТИРОВАТЬ ПО
MySQL предоставляет множество операций форматирования строк, например, для конкатенации строк. Например, названия фильмов обычно отображаются вместе с годом выпуска, чтобы избежать путаницы. Назовем пять самых прибыльных фильмов вместе с датами их выхода: CONCAT
select_movies_query = "" "SELECT CONCAT (title," (", release_year,") "), collection_in_mil ИЗ фильмов ORDER BY collection_in_mil DESC LIMIT 5" "" с connection.cursor () в качестве курсора: cursor.execute (select_movies_query) для фильма в cursor.fetchall (): print (фильм)
ВЫВОД
('Мстители: Финал (2019)', десятичный ('858.8'))
('Титаник (1997)', десятичный ('659.2'))
('Темный рыцарь (2008)', Decimal ('535.4'))
('История игрушек 4 (2019)', десятичное ('434.9'))
('Король Лев (1994)', десятичный ('423.6'))
Если вы не хотите использовать и вам не нужно получать все записи, вы можете использовать методы курсора и: LIMIT .fetchone () .fetchmany ()
-
.fetchone () Извлекает следующую строку результата в виде кортежа или, если доступных строк больше нет. Никто
-
.fetchmany () Извлекает список следующего набора строк в виде кортежа. Для этого ему передается аргумент, который по умолчанию равен 1. Если доступных строк больше нет, метод возвращает пустой список.
Опять же, извлеките названия пяти самых кассовых фильмов по годам, но на этот раз используя: .fetchmany ()
select_movies_query = "" "SELECT CONCAT (title," (", release_year,") "), collection_in_mil FROM movies ORDER BY collection_in_mil DESC" "" с connection.cursor () в качестве курсора: cursor.execute (select_movies_query) для фильма в курсоре .fetchmany (size = 5): print (фильм) cursor.fetchall ()
ВЫВОД
('Мстители: Финал (2019)', десятичный ('858.8'))
('Титаник (1997)', десятичный ('659.2'))
('Темный рыцарь (2008)', Decimal ('535.4'))
('История игрушек 4 (2019)', десятичное ('434.9'))
('Король Лев (1994)', десятичный ('423.6'))
Возможно, вы заметили дополнительную проблему. Мы делаем это, чтобы удалить оставшиеся непрочитанные результаты. курсор.fetchall () .fetchmany ()
Перед выполнением любых других операторов в том же соединении необходимо удалить все непрочитанные результаты. В противном случае выдается исключение. Внутренняя ошибка
ПРИСОЕДИНЯЙТЕСЬ к нескольким таблицам
Чтобы узнать названия пяти фильмов с самым высоким рейтингом, выполните следующий запрос:
select_movies_query = "" "ВЫБЕРИТЕ заголовок, AVG (рейтинг) как средний_ рейтинг ИЗ рейтингов ВНУТРЕННЕЕ ПРИСОЕДИНЯЙТЕСЬ к фильмам НА movies.id = рейтинг.movie_id ГРУППА ПО movie_id ORDER BY average_rating DESC LIMIT 5" "" с connection.cursor () в качестве курсора: курсора. выполнить (select_movies_query) для фильма в cursor.fetchall (): print (movie)
ВЫВОД
('Ночь живых мертвецов', десятичное ('9.90000'))
('Крестный отец', десятичное ('9.90000'))
('Мстители: Финал', десятичное ('9.75000'))
('Вечное сияние чистого разума', десятичное ('8.90000'))
('Звери без нации', десятичное ('8.70000'))
Вы можете найти имя рецензента с наибольшим количеством оценок, например:
select_movies_query = "" "SELECT CONCAT (first_name," ", last_name), COUNT (*) as num FROM reviewers ВНУТРЕННИЕ ПРИСОЕДИНЯЙТЕСЬ к рейтингам ON reviewers.id = rating.reviewer_id ГРУППА ПО reviewer_id ORDER BY num DESC LIMIT 1" "" с подключением. cursor () в качестве курсора: cursor.execute (select_movies_query) для фильма в cursor.fetchall (): print (movie) ('Мэри Купер', 4)
Как видите, большинство обзоров написала Мэри Купер.
Процесс выполнения запроса всегда остается прежним: мы передаем запрос, чтобы получить результаты, используя. курсор.execute () .fetchall ()
Обновление и удаление записей из базы данных
В этом разделе мы обновим и удалим некоторые записи. Мы выберем нужные строки с помощью ключевого слова. КУДА
Команда ОБНОВЛЕНИЕ
Представьте себе обозревателя Эми Фара Фаулер замужем за Шелдоном Купером. Она сменила фамилию на Купер, и нам нужно обновить базу данных. Для обновления записей в MySQL используйте оператор: UPDATE
update_query = "" "ОБНОВЛЕНИЕ обозревателей SET last_name =" Cooper "WHERE first_name =" Amy "" "" с connection.cursor () в качестве курсора: cursor.execute (update_query) connection.commit ()
Код передает запрос на обновление и вносит необходимые изменения в таблицу. cursor.execute () .commit () обозреватели
Допустим, мы хотим разрешить рецензентам изменять оценки. Программу нужно знать и новую. Пример SQL: movie_id reviewer_id rating
ОБНОВЛЕНИЕ рейтингов УСТАНОВИТЕ рейтинг = 5.0, ГДЕ movie_id = 18 AND reviewer_id = 15; ВЫБЕРИТЕ * ИЗ оценок, ГДЕ movie_id = 18 AND reviewer_id = 15; Указанные запросы сначала обновляют рейтинг, а затем выводят обновленный. Давайте напишем сценарий Python, который позволит нам настраивать оценки: modify_ratings.py из getpass import getpass из mysql.connector import connect, Error movie_id = input ("Введите идентификатор фильма:") reviewer_id = input ("Введите идентификатор обозревателя:" ) new_rating = input ("Введите новый рейтинг:") update_query = "" "ОБНОВИТЬ рейтинги SET rating ="% s "WHERE movie_id ="% s "AND reviewer_id ="% s "; ВЫБРАТЬ * ИЗ оценок WHERE movie_id ="% s "AND reviewer_id ="% s "" ""% (new_rating, movie_id, reviewer_id, movie_id, reviewer_id,) попробуйте: с помощью connect (host = "localhost", user = input ("Введите имя пользователя:"), пароль = getpass ("Введите пароль:"), database = "online_movie_rating",) как соединение: с connection.cursor () в качестве курсора: для результата в cursor.execute (update_query, multi = True): if result.with_rows: print (result. fetchall ()) connection.commit () кроме ошибки как e: print (e)
ВЫВОД
Введите id фильма: 18
Введите идентификатор рецензента: 15
Введите новую оценку: 5
Введите имя пользователя: root
Введите пароль: ········
[(18, 15, десятичный ('5.0'))]
Чтобы передать несколько запросов одному и тому же курсору, мы присваиваем значение аргументу. В этом случае он возвращает итератор. Каждый элемент в итераторе соответствует объекту курсора, который выполняет инструкцию, переданную в запросе. Приведенный выше код запускает цикл на этом итераторе, вызывая каждый объект курсора. multi True cursor.execute () для .fetchall ()
Если для операции не был получен набор результатов, создается исключение. Чтобы избежать этой ошибки, в приведенном выше коде мы используем свойство, которое указывает, создавала ли последняя выполненная операция строки. .fetchall () курсор.with_rows
Хотя этот код выполняет свою работу, инструкция в ее нынешнем виде является заманчивой целью для хакеров. Он уязвим для атаки с использованием SQL-инъекции, которая может позволить злоумышленникам повредить базу данных или использовать ее не по назначению. КУДА
Например, если пользователь отправляет и в качестве входных данных, результат будет выглядеть следующим образом: movie_id = 18 reviewer_id = 15 рейтингов = 5.0.
$ Pythonmodify_ratings.py
Введите id фильма: 18
Введите идентификатор рецензента: 15
Введите новую оценку: 5.0
Введите имя пользователя:
Введите пароль:
[(18, 15, десятичный ('5.0'))]
Счет за и тоже изменился. Но если бы вы были хакером, вы могли бы отправить на вход скрытую команду: movie_id = 18 reviewer_id = 15 5.0
$ Pythonmodify_ratings.py
Введите id фильма: 18
Введите идентификатор рецензента: 15 ″; ОБНОВЛЕНИЕ обозревателей SET last_name = «A
Введите новую оценку: 5.0
Введите имя пользователя:
Введите пароль:
[(18, 15, десятичный ('5.0'))]
Опять же, выходные данные показывают, что заявленный рейтинг был изменен на 5.0. Что изменилось?
Хакер перехватил запрос на обновление данных. Запрос на обновление изменит все записи в таблице рецензента: last_name «A»
>>> select_query = «» »
… ВЫБЕРИТЕ first_name, last_name
… ОТ рецензентов
… «» »
>>> с connection.cursor () в качестве курсора:
… Cursor.execute (select_query)
… Для обозревателя в cursor.fetchall ():
… Распечатать (рецензент)
...
("Чайтанья", "А")
('Мэри', 'А')
('Джон', 'А')
('Томас', 'А')
('Пенни', 'А')
('Митчелл', 'А')
('Вятт', 'А')
('Андре', 'А')
('Шелдон', 'А')
(«Кимбра», «А»)
('Кэт', 'А')
('Брюс', 'А')
('Доминго', 'А')
('Раджеш', 'А')
('Бен', 'А')
('Махиндер', 'А')
('Акбар', 'А')
('Ховард', 'А')
('Пинки', 'А')
('Гуркаран', 'А')
(Эми, А)
('Марлон', 'А')
Приведенный выше код отображает и для всех записей в таблице рецензентов. Атака с использованием SQL-инъекции повредила эту таблицу, изменив все записи на «A». first_name last_name last_name
Есть быстрое решение для предотвращения таких атак. Не добавляйте значения запроса, предоставленные пользователем, непосредственно в строку запроса. Лучше обновить скрипт, отправив значения запроса в качестве аргументов в .execute ()
modify_ratings.py из getpass import getpass из mysql.connector import connect, Ошибка movie_id = input ("Введите идентификатор фильма:") reviewer_id = input ("Введите идентификатор обозревателя:") new_rating = input ("Введите новый рейтинг:") update_query = "" "ОБНОВИТЬ рейтинги SET rating =% s WHERE movie_id =% s AND reviewer_id =% s; SELECT * FROM rating WHERE movie_id =% s AND reviewer_id =% s" "" val_tuple = (new_rating, movie_id, reviewer_id, movie_id, reviewer_id ,) попробуйте: с помощью connect (host = "localhost", user = input ("Введите имя пользователя:"), password = getpass ("Введите пароль:"), database = "online_movie_rating",) в качестве соединения: с помощью connection.cursor ( ) как курсор: для результата в cursor.execute (update_query, val_tuple, multi = True): if result.with_rows: print (result.fetchall ()) connection.commit () except Error as e: print (e)
Обратите внимание, что заполнители больше не заключаются в строковые кавычки. проверяет, что значения в кортеже, заданном в качестве аргумента, имеют требуемый тип данных. Если пользователь попытается ввести некоторые проблемные символы, код выдаст исключение:% s cursor.execute ()
ВЫВОД
$ Pythonmodify_ratings.py
Введите id фильма: 18
Введите идентификатор рецензента: 15 ″; ОБНОВЛЕНИЕ обозревателей SET last_name = «A
Введите новую оценку: 5.0
Введите имя пользователя:
Введите пароль:
1292 (22007): некорректное усеченное значение DOUBLE: '15 ”;
Этот подход следует всегда использовать, когда вы включаете пользовательский ввод в запрос. Найдите время, чтобы узнать о других способах предотвращения атак с использованием SQL-инъекций.
Удаление записей: команда УДАЛИТЬ
Процедура удаления записей очень похожа на их обновление. Поскольку это необратимая операция, мы рекомендуем сначала запустить запрос с тем же фильтром, чтобы убедиться, что вы удаляете нужные записи. Например, чтобы удалить все рейтинги фильмов и данные, мы можем сначала запустить соответствующий запрос: DELETE SELECT reviewer_id = 7 SELECT
select_movies_query = "" "SELECT reviewer_id, movie_id FROM rating WHERE review_id = 7" "" с connection.cursor () в качестве курсора: cursor.execute (select_movies_query) для фильма в cursor.fetchall (): print (фильм)
ВЫВОД
(2, 7)
(2, 8)
(2, 12)
(2, 23)
Приведенный выше фрагмент кода отображает пару записей и оценок в таблице, для которых. Убедившись, что это записи, которые нужно удалить, давайте выполним запрос с тем же фильтром: reviewer_id movie_id reviewer_id = 2 DELETE
delete_query = "УДАЛИТЬ ИЗ оценок WHERE reviewer_id = 2" с connection.cursor () в качестве курсора: cursor.execute (delete_query) connection.commit ()
Другие способы соединения Python и MySQL
В этом руководстве мы представили MySQL Connector / Python, который является официально рекомендуемым средством взаимодействия с базой данных MySQL из приложения Python. Вот еще пара других популярных разъемов:
-
mysqlclient - это библиотека, которая является конкурентом официального коннектора и активно дополняется новыми функциями. Поскольку ядро библиотеки написано на C, она имеет лучшую производительность, чем официальный коннектор на чистом Python. Большой недостаток в том, что mysqlclient довольно сложно настроить и установить, особенно в Windows.
-
MySQLdb - это устаревшее программное обеспечение, которое до сих пор используется в коммерческих приложениях. Написан на C и более быстром MySQL Connector / Python, но доступен только для Python 2.
Эти драйверы действуют как интерфейсы между вашей программой и базой данных MySQL. Фактически, вы просто отправляете через них свои SQL-запросы. Однако многие разработчики предпочитают использовать объектно-ориентированную парадигму для управления данными, а не запросы SQL.
Объектно-реляционное сопоставление (ORM) - это процесс, который позволяет не только выполнять запросы, но и управлять данными из базы данных напрямую с помощью ООП. Библиотека ORM инкапсулирует код, необходимый для управления данными, освобождая разработчиков от необходимости использовать SQL-запросы. Вот самые популярные библиотеки ORM для объединения Python и SQL:
-
SQLAlchemy - это ORM, который упрощает взаимодействие между Python и другими базами данных SQL. Вы можете создавать разные движки для разных баз данных, таких как MySQL, PostgreSQL, SQLite и т. Д.
-
peewee - это легкая и быстрая библиотека ORM с простой конфигурацией, которая очень полезна, когда ваше взаимодействие с базой данных ограничивается выборкой нескольких записей. Если вам нужно скопировать отдельные записи из базы данных MySQL в файл CSV, то peewee - лучший выбор.
-
Django ORM - одна из самых мощных частей веб-фреймворка Django, позволяющая легко взаимодействовать с различными базами данных SQLite, PostgreSQL и MySQL. Многие приложения на основе Django используют Django ORM для моделирования данных и базовых запросов, однако для более сложных задач разработчики обычно используют SQLAlchemy.
Заключение
В этом руководстве мы рассмотрели, как интегрировать базу данных MySQL в ваше приложение Python. Мы также разработали тестовый образец базы данных MySQL и взаимодействовали с ней непосредственно из кода Python. Python имеет коннекторы для других СУБД, таких как MongoDB и PostgreSQL. Мы будем рады узнать, какие еще материалы по Python и базам данных вас заинтересуют.
Медиа, показанные в этой статье, не принадлежат Analytics Vidhya и используются по усмотрению автора.
Похожие страницы:
- '
- "
- 100
- 11
- 2016
- 2019
- 7
- 9
- доступ
- Учетная запись
- Действие
- активный
- дополнительный
- Приключение.
- Военно-воздушные силы
- Все
- Позволяющий
- анализ
- аналитика
- анимация
- Применение
- Приложения
- Аргументы
- гайд
- нападки
- ЛУЧШЕЕ
- Черный
- Коробка
- Театральная касса
- призывают
- заботится
- Привлекайте
- вызов
- изменение
- ребенок
- код
- Column
- комедия
- коммерческая
- Связь
- сообщество
- компонент
- замешательство
- Коммутация
- Пара
- Создающий
- Полномочия
- Преступление
- данным
- анализ данных
- управление данными
- База данных
- базы данных
- Финики
- мертвый
- подробность
- развивать
- застройщиков
- Django
- Docker
- долларов
- Драма
- водитель
- Падение
- Окружающая среда
- Оценки
- и т.д
- выполнение
- БЫСТРО
- Особенность
- Особенности
- Сборы
- Рассказы
- фигура
- фильм
- пленки
- в заключение
- First
- форма
- формат
- Рамки
- функция
- Общие
- хорошо
- захват
- группы
- инструкция
- хакер
- Хакеры
- Управляемость
- удобный
- здесь
- Спрятать
- Главная
- Как
- How To
- HTTPS
- В том числе
- информация
- взаимодействие
- изоляция
- IT
- работа
- присоединиться
- Основные
- ключи
- Король
- знания
- язык
- Языки
- большой
- вести
- УЧИТЬСЯ
- Библиотека
- Ограниченный
- LINK
- Linux
- Список
- MacOS
- основной
- Создание
- управление
- Манипуляция
- рынок
- Market Leader
- материалы
- Медиа
- миллиона
- MongoDB
- Самые популярные
- кино
- Кино
- имена
- Netflix
- Предложения
- Официальный представитель в Грузии
- смещение
- открытый
- открытый исходный код
- операционный
- операционная система
- операционные системы
- Операционный отдел
- оракул
- заказ
- Другие контрактные услуги
- парадигма
- Пароль
- Люди
- производительность
- Популярное
- FitPartner™
- Программирование
- языки программирования
- Проект
- собственность
- Питон
- ассортимент
- рейтинги
- причины
- учет
- Отношения
- Полезные ресурсы
- Итоги
- Возвращает
- обзоре
- Отзывы
- Снижение
- Катить
- Run
- Бег
- Наука
- безопасность
- набор
- установка
- просто
- небольшой
- So
- Software
- Solaris
- Решения
- Space
- Спорт
- SQL
- SQL Injection
- стандартов
- Начало
- и политические лидеры
- заявление
- диск
- успех
- солнечный свет
- дополнять
- поддержка
- система
- системы
- цель
- технологии
- тестXNUMX
- время
- игрушка
- сделка
- Сделки
- учебник
- Обновление ПО
- us
- пользователей
- ценностное
- Вид
- Виртуальный
- Уязвимый
- войны
- Web
- КТО
- окна
- в
- Работа
- работает
- год