Python и MySQL: практическое введение
Рассмотрим на практическом примере, как из кода Python делать SQL-запросы к MySQL-серверу: CREATE, SELECT, UPDATE, JOIN и т. д. Обсудить Публикация представляет собой незначительно сокращенный перевод статьи Чайтаньи Баведжи Python and MySQL Database: A Practical Introduction. Материал также адаптирован в виде блокнота Jupyter. *** Большинство приложений в той или иной форме взаимодействует с данными. Поэтому языки программирования (Python не исключение), предоставляют инструменты хранения источников данных и доступа к ним. MySQL — одна из самых популярных систем управления базами данных (СУБД). В прошлом году она заняла второе место после СУБД Oracle в рейтинге баз данных. Используя методы, описанные в этом руководстве, вы сможете эффективно интегрировать базу данных MySQL в приложение на Python. В ходе руководства мы разработаем небольшую базу данных MySQL для системы рейтинга фильмов и узнаем, как забирать из нее данные с помощью Python-кода. К концу этого урока вы сможете: Чтобы получить максимальную отдачу от этого руководства, желательно иметь практические знания о таких концепциях Python, как цикл Сравнение MySQL с другими SQL-базами данных SQL (Structured Query Language) — язык структурированных запросов. SQL является широко используемым языком программирования для управления реляционными базами данных. Возможно, вы слышали о различных СУБД на основе SQL: MySQL, PostgreSQL, SQLite и SQL Server. Все эти базы данных соответствуют стандартам SQL, но отличаются в деталях. В силу открытости исходного кода MySQL быстро стал лидером рынка среди SQL-решений. В настоящее время MySQL используется всеми крупными техническими фирмами, включая Google, LinkedIn, Uber, Netflix, Twitter и другие. Помимо поддержки со стороны open source-сообщества, есть и другие причины успеха MySQL: MySQL использует синтаксис, похожий на стандартный SQL, однако имеющий некоторые важные отличия, описанные в официальной документации. Установка MySQL Server и MySQL Connector Чтобы начать работу с этим руководством, вам необходимо настроить две вещи: MySQL Server и MySQL Connector. MySQL Server предоставит ресурсы, необходимые для работы с базой данных. После запуска сервера вы сможете подключить к нему свое приложение Python с помощью MySQL Connector/Python. Официальная документация описывает рекомендуемые способы загрузки и установки MySQL Server. Есть инструкции для всех популярных операционных систем, включая Windows, macOS, Solaris, Linux и многие другие. Для Windows лучше всего загрузить установщик MySQL и позволить ему позаботиться о процессе. Диспетчер установки также поможет настроить параметры безопасности сервера MySQL. На странице учетных записей будет необходимо ввести пароль для root-записи и при желании добавить других пользователей с различными привилегиями. Настройка учетной записи MySQL С помощью установщиков можно настроить и другие полезные инструменты, например, MySQL Workbench. Удобная альтернатива установке в операционной системе — развернуть MySQL с помощью Docker. Драйвер базы данных — программное обеспечение, позволяющее приложению подключаться и взаимодействовать с СУБД. Такие драйверы обычно поставляются в виде отдельных модулей. Сандартный интерфейс, которому должны соответствовать все драйверы баз данных Python, описан в PEP 249. Драйверы баз данных Python, такие как sqlite3 для SQLite, psycopg для PostgreSQL и MySQL Connector/Python для MySQL, следуют этим правилам. Для установки драйвера (коннектора) воспользуемся менеджером пакетов Проверим результат установки, запустив в терминале Python следующую команду: Если инструкция импорта выполняется без ошибок, значит Установление соединения с сервером MySQL MySQL ― это серверная система управления базами данных. Один сервер может содержать несколько баз данных. Чтобы взаимодействовать с базой данных, мы должны установить соединение с сервером. Рабочий процесс программы Python, которая взаимодействует с базой данных на основе MySQL, в общих чертах выглядит следующим образом: Каким бы ни было приложение, первый шаг ― связать между собой приложение и базу данных. Подключаемся к серверу MySQL из Python Чтобы установить соединение, используем Объект Итак, мы установили соединение между нашей программой и сервером MySQL. Теперь нужно либо создать новую базу данных, либо подключиться к существующей. Создаем новую базу данных Чтобы создать новую базу данных, например, с именем Примечание MySQL обязывает ставить точку с запятой ( Чтобы выполнить SQL-запрос, нам понадобится курсор, который абстрагирует процесс доступа к записям базы данных. MySQL Connector/Python предоставляет соответствующий класс Передадим наш запрос о создании базы данных Запрос Если база данных с таким именем уже существует на сервере, мы получим сообщение об ошибке. Используя тот же объект Приведенный код выведет имена всех баз данных, находящихся на нашем сервере MySQL. Команда Подключение к существующей базе данных Итак, мы создали базу данных под названием Создание, изменение и удаление таблиц В этом разделе мы рассмотрим, как с помощью Python выполнять некоторые базовые запросы: Начнем с создания схемы базы данных для рейтинговой системы фильмов. База данных будет состоять из трех таблиц: 1. 2. 3. Этих трех таблиц достаточно для целей данного руководства. Схема системы рейтинга фильмов Таблицы в базе данных связаны друг с другом: Создание таблиц с помощью оператора CREATE TABLE Чтобы создать новую таблицу в MySQL, нам нужно использовать оператор Если вы раньше встречались с SQL, вам будет понятен смысл приведенного запроса. У диалекта MySQL есть некоторые отличительные черты. Например, MySQL предлагает широкий выбор типов данных, включая Чтобы создать таблицу, необходимо передать указанный запрос в Обратите внимание на оператор Повторим процедуру для таблицы Наконец, создадим таблицу Реализация отношений внешнего ключа в MySQL немного отличается и имеет ограничения в сравнении со стандартным SQL. В MySQL и родитель, и потомок внешнего ключа должны использовать один и тот же механизм хранения ― базовый программный компонент, который система управления базами данных использует для выполнения SQL-операций. MySQL предлагает два вида таких механизмов: InnoDB ― самый популярный механизм хранения по умолчанию. Соблюдая ограничения внешнего ключа, он помогает поддерживать целостность данных. Это означает, что любая CRUD-операция с внешним ключом предварительно проверяется на то, что она не приводит к несогласованности между разными таблицами. Обратите внимание, что таблица Один и тот же курсор можно использовать для нескольких обращений. В этом случае все обращения станут одной атомарной транзакцией. Например, можно выполнить все операторы Мы создали три таблицы и можем просмотреть схему, используя оператор Предполагая, что у вас уже есть объект После выполнения приведенного кода мы должны получить таблицу, содержащую информацию о столбцах в таблице Столбец с именем Как показано в выходных данных, атрибут Для удаления таблиц служит оператор Вставка записей в таблицы Заполним таблицы данными. В этом разделе мы рассмотрим два способа вставки записей с помощью MySQL Connector в коде Python. Первый метод, Первый подход использует тот же метод аблица Предыдущий подход годится, когда количество записей мало, и их можно вставить из кода. Но обычно данные хранятся в файле или генерируются другим сценарием. Вот где пригодится Применим подход для заполнения таблицы Этот код использует Аналогичным образом заполним таблицу Теперь все три таблицы заполнены данными. Следующий шаг ― разобраться, как с этой базой данных взаимодействовать. Чтение записей из базы данных До сих пор мы только создавали элементы базы данных. Пришло время выполнить несколько запросов и найти интересующие нас свойства. В этом разделе мы узнаем, как читать записи из таблиц базы данных с помощью оператора Чтобы получить записи, необходимо отправить в Переменная В приведенном запросе мы используем ключевое слово В MySQL оператору При использовании двух числовых аргументов первый указывает смещение, равное в данном примере 2, а второй ограничивает количество возвращаемых строк до 5. То есть запрос из примера вернет строки с 3 по 7. Фильтрация результатов с помощью WHERE Записи таблицы также можно фильтровать, используя Словосочетание MySQL предоставляет множество операций форматирования строк, таких как Если вы не хотите использовать Снова извлечем названия пяти самых кассовых фильмов с указанием года выпуска, но на этот раз используя Вы могли заметить дополнительный вызов Перед выполнением любых других операторов в том же соединении необходимо очистить все непрочитанные результаты. В противном случае вызывается исключение Обработка нескольких таблиц с помощью оператора JOIN Чтобы узнать названия пяти фильмов с самым высоким рейтингом, выполним следующий запрос: Найти имя рецензента, давшего наибольшее количество оценок, можно так: Как видим, больше всего рецензий написала Mary Cooper. Не имеет значения, насколько сложен запрос ― в конечном счете он обрабатывается сервером MySQL. Процесс выполнения запроса всегда остается прежним: передаем запрос в Обновление и удаление записей из базы данных В этом разделе мы обновим и удалим часть записей. Необходимые строки мы выберем с помощью ключевого слова Представим, что рецензент Amy Farah Fowler вышла замуж за Sheldon Cooper. Она сменила фамилию на Cooper, и нам необходимо обновить базу данных. Для обновления записей в MySQL используется оператор Код передает запрос на обновление в Представим, что мы хотим дать возможность рецензентам изменять оценки. Программа должна знать Указанные запросы сначала обновляют рейтинг, а затем выведут обновленный. Напишем скрипт на Python, который позволит корректировать оценки: modify_ratings.py Чтобы передать несколько запросов одному курсору, мы присваиваем аргументу Если для операции не был получен набор результатов, то Хотя этот код решает поставленную задачу, инструкция Например, если пользователь отправляет Оценка для И снова выходные данные показывают, что указанный рейтинг был изменен на 5.0. Что изменилось? Хакер перехватил запрос на обновление данных. Запрос на обновление, изменит Приведенный код отображает Есть быстрое решение для предотвращения таких атак. Не добавляйте значения запроса, предоставленные пользователем, напрямую в строку запроса. Лучше обнолять сценарий с отправкой значений запроса в качестве аргументов в modify_ratings.py Обратите внимание, что плейсхолдеры Такой подход стоит использовать всегда, когда вы включаете в запрос пользовательский ввод. Не поленитесь узнать и про другие способы предотвращения атак с использованием SQL-инъекций. Процедура удаления записей очень похожа на их обновление. Поскольку Приведенный фрагмент кода выводит пары Другие способы соединения Python и MySQL В этом руководстве мы познакомились с MySQL Connector/Python, который является официально рекомендуемым средством взаимодействия с базой данных MySQL из приложения Python. Вот еще пара популярных коннекторов: Эти драйверы действуют, как интерфейсы между вашей программой и базой данных MySQL. Фактически вы просто отправляете через них свои SQL-запросы. Но многие разработчики предпочитают использовать для управления данными не SQL-запросы, а объектно-ориентированную парадигму. Объектно-реляционное отображение (ORM) — метод, который позволяет запрашивать и управлять данными из базы данных напрямую, используя объектно-ориентированный язык. ORM-библиотека инкапсулирует код, необходимый для управления данными, освобождая разработчиков от необходимости использовать SQL-запросы. Вот самые популярные ORM-библиотеки для связки Python и SQL: Заключение В этом руководстве мы познакомились с применением Python имеет коннекторы и для других СУБД, таких как MongoDB и PostgreSQL. Будем рады узнать, какие еще материалы по Python и базам данных вам были бы интересны.
for, функции, обработка исключений. Также необходимо иметь базовые представления о SQL-запросах, таких как SELECT, DROP, CREATE и JOIN.Установка MySQL Server
Установка MySQL Connector/Python
pip:
pip install mysql-connector-python pip установит коннектор в текущую активную среду. Чтобы работать с проектом изолированным образом, мы рекомендуем настроить виртуальную среду.
import mysql.connector mysql.connector успешно установлен и готов к использованию.connect() из модуля mysql.connector. Эта функция принимает параметры host, user и password, а возвращает объект MySQLConnection. Учетные данные можно получить в результате ввода от пользователя:
from getpass import getpass from mysql.connector import connect, Error try: with connect( host="localhost", user=input("Имя пользователя: "), password=getpass("Пароль: "), ) as connection: print(connection) except Error as e: print(e) MySQLConnection хранится в переменной connection, которую мы будем использовать для доступа к серверу MySQL. Несколько важных моментов:try ... except. Так будет проще перехватить и изучить любые исключения.with ... as ...).getpass, чтобы скрыть вводимый пароль. Хотя это лучше, чем жесткое кодирование, но есть и другие, более безопасные способы хранения конфиденциальной информации, например, использование переменных среды.online_movie_rating, нужно выполнить инструкцию SQL:
CREATE DATABASE online_movie_rating; ;) в конце оператора. Однако MySQL Connector/Python автоматически добавляет точку с запятой в конце каждого запроса. MySQLCursor, экземпляр которого также называется курсором.online_movie_rating:
try: with connect( host="localhost", user=input("Имя пользователя: "), password=getpass("Пароль: "), ) as connection: create_db_query = "CREATE DATABASE online_movie_rating" with connection.cursor() as cursor: cursor.execute(create_db_query) except Error as e: print(e) CREATE DATABASE сохраняется в виде строки в переменной create_db_query, а затем передается на выполнение в cursor.execute().MySQLConnection, что и ранее, выполним запрос SHOW DATABASES, чтобы увидеть все таблицы, хранящиеся в базе данных:
try: with connect( host="localhost", user=input("Введите имя пользователя: "), password=getpass("Введите пароль: "), ) as connection: show_db_query = "SHOW DATABASES" with connection.cursor() as cursor: cursor.execute(show_db_query) for db in cursor: print(db) except Error as e: print(e)
Введите имя пользователя: root Введите пароль: ········ ('information_schema',) ('mysql',) ('online_movie_rating',) ('performance_schema',) ('sys',) SHOW DATABASES в нашем примере также вывела базы данных, которые автоматически создаются сервером MySQL и предоставляют доступ к метаданным баз данных и настройкам сервера.online_movie_rating. Чтобы к ней подключиться, просто дополняем вызов connect() параметром database:
try: with connect( host="localhost", user=input("Имя пользователя: "), password=getpass("Пароль: "), database="online_movie_rating", ) as connection: print(connection) except Error as e: print(e) CREATE TABLE, DROP и ALTER.Определение схемы базы данных
movies ― общая информация о фильмах:idtitlerelease yeargenrecollection_in_mireviewers ― информация о людях, опубликовавших оценки фильмов:idfirst_namelast_nameratings ― информация об оценках фильмов рецензентами:movie_id (foreign key)reviewer_id (foreign key)ratingmovies и reviewers должны иметь отношение «многие ко многим»: один фильм может быть просмотрен несколькими рецензентами, а один рецензент может рецензировать несколько фильмов. Таблица ratings соединяет таблицу фильмов с таблицей рецензентов.CREATE TABLE. Следующий запрос MySQL создаст таблицу movies нашей базы данных online_movie_rating:
CREATE TABLE movies( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(100), release_year YEAR(4), genre VARCHAR(100), collection_in_mil INT ); YEAR, INT, BIGINT и так далее. Кроме того, MySQL использует ключевое слово AUTO_INCREMENT, когда значение столбца должно автоматически увеличиваться при вставке новых записей.cursor.execute():
create_movies_table_query = """ CREATE TABLE movies( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(100), release_year YEAR(4), genre VARCHAR(100), collection_in_mil INT ) """ with connection.cursor() as cursor: cursor.execute(create_movies_table_query) connection.commit() connection.commit(). По умолчанию коннектор MySQL не выполняет автоматическую фиксацию транзакций. В MySQL модификации, упомянутые в транзакции, происходят только тогда, когда мы используем в конце команду COMMIT. Чтобы внести изменения в таблицу, всегда вызывайте этот метод после каждой транзакции.reviewers:
create_reviewers_table_query = """ CREATE TABLE reviewers ( id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(100), last_name VARCHAR(100) ) """ with connection.cursor() as cursor: cursor.execute(create_reviewers_table_query) connection.commit() ratings:
create_ratings_table_query = """ CREATE TABLE ratings ( movie_id INT, reviewer_id INT, rating DECIMAL(2,1), FOREIGN KEY(movie_id) REFERENCES movies(id), FOREIGN KEY(reviewer_id) REFERENCES reviewers(id), PRIMARY KEY(movie_id, reviewer_id) ) """ with connection.cursor() as cursor: cursor.execute(create_ratings_table_query) connection.commit() rollback. К этой категории относятся многие популярные движки MySQL, включая InnoDB и NDB.ratings использует столбцы movie_id и reviewer_id, как два внешних ключа, выступающих вместе в качестве первичного ключа. Эта особенность гарантирует, что рецензент не сможет дважды оценить один и тот же фильм.CREATE TABLE одним курсором, а затем зараз зафиксировать транзакцию:
with connection.cursor() as cursor: cursor.execute(create_movies_table_query) cursor.execute(create_reviewers_table_query) cursor.execute(create_ratings_table_query) connection.commit() Отображение схемы таблиц с использованием оператора DESCRIBE
DESCRIBE.MySQLConnection в переменной connection, мы можем распечатать результаты, полученные с помощью cursor.fetchall(). Этот метод извлекает все строки из последнего выполненного оператора:
show_table_query = "DESCRIBE movies" with connection.cursor() as cursor: cursor.execute(show_table_query) # Fetch rows from last executed query result = cursor.fetchall() for row in result: print(row)
('id', 'int(11)', 'NO', 'PRI', None, 'auto_increment') ('title', 'varchar(100)', 'YES', '', None, '') ('release_year', 'year(4)', 'YES', '', None, '') ('genre', 'varchar(100)', 'YES', '', None, '') ('collection_in_mil', 'int(11)', 'YES', '', None, '') movies. Для каждого столбца выводится информация, о типе данных, является ли столбец первичным ключом и т. д.Изменение схемы таблицы с помощью оператора ALTER
collection_in_mil в таблице movies содержит кассовые сборы фильма в миллионах долларов. Мы можем написать следующую инструкцию MySQL, чтобы изменить тип данных атрибута collection_in_mil с INT на DECIMAL:
ALTER TABLE movies MODIFY COLUMN collection_in_mil DECIMAL(4,1); DECIMAL(4,1) указывает на десятичное число, которое может иметь максимум 4 цифры, из которых 1 соответствует разряду десятых, например, 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" with connection.cursor() as cursor: cursor.execute(alter_table_query) cursor.execute(show_table_query) # Получить строки из последнего выполненного запроса result = cursor.fetchall() print("Схема таблицы movie после внесения изменений:") for row in result: print(row)
Схема таблицы movie после внесения изменений: ('id', 'int(11)', 'NO', 'PRI', None, 'auto_increment') ('title', 'varchar(100)', 'YES', '', None, '') ('release_year', 'year(4)', 'YES', '', None, '') ('genre', 'varchar(100)', 'YES', '', None, '') ('collection_in_mil', 'decimal(4,1)', 'YES', '', None, '') collection_in_mil сменил тип на DECIMAL(4,1). Обратите внимание, что в приведенном выше коде мы дважды вызываем cursor.execute(), но cursor.fetchall() выбирает строки только из последнего выполненного запроса, которым является show_table_query.Удаление таблиц с помощью оператора DROP
DROP TABLE. Удаление таблицы ― необратимый процесс. Если вы выполните приведенный ниже код, вам нужно будет снова вызвать запрос CREATE TABLE для таблицы ratings:
drop_table_query = "DROP TABLE ratings" with connection.cursor() as cursor: cursor.execute(drop_table_query) .execute(), хорошо работает, когда количество записей невелико. Второй, .executemany() лучше подходит для реальных сценариев.Вставка записей с помощью .execute()
cursor.execute(), который мы применяли до сих пор. Пишем запрос INSERT INTO и передаем в cursor.execute():
insert_movies_query = """ INSERT INTO movies (title, release_year, genre, collection_in_mil) VALUES ("Forrest Gump", 1994, "Drama", 330.2), ("3 Idiots", 2009, "Drama", 2.4), ("Eternal Sunshine of the Spotless Mind", 2004, "Drama", 34.5), ("Good Will Hunting", 1997, "Drama", 138.1), ("Skyfall", 2012, "Action", 304.6), ("Gladiator", 2000, "Action", 188.7), ("Black", 2005, "Drama", 3.0), ("Titanic", 1997, "Romance", 659.2), ("The Shawshank Redemption", 1994, "Drama",28.4), ("Udaan", 2010, "Drama", 1.5), ("Home Alone", 1990, "Comedy", 286.9), ("Casablanca", 1942, "Romance", 1.0), ("Avengers: Endgame", 2019, "Action", 858.8), ("Night of the Living Dead", 1968, "Horror", 2.5), ("The Godfather", 1972, "Crime", 135.6), ("Haider", 2014, "Action", 4.2), ("Inception", 2010, "Adventure", 293.7), ("Evil", 2003, "Horror", 1.3), ("Toy Story 4", 2019, "Animation", 434.9), ("Air Force One", 1997, "Drama", 138.1), ("The Dark Knight", 2008, "Action",535.4), ("Bhaag Milkha Bhaag", 2013, "Sport", 4.1), ("The Lion King", 1994, "Animation", 423.6), ("Pulp Fiction", 1994, "Crime", 108.8), ("Kai Po Che", 2013, "Sport", 6.0), ("Beasts of No Nation", 2015, "War", 1.4), ("Andadhun", 2018, "Thriller", 2.9), ("The Silence of the Lambs", 1991, "Crime", 68.2), ("Deadpool", 2016, "Action", 363.6), ("Drishyam", 2015, "Mystery", 3.0) """ with connection.cursor() as cursor: cursor.execute(insert_movies_query) connection.commit() movies теперь заполнена тридцатью записями. В конце код вызывает connection.commit(). Не забывайте вызывать .commit() после выполнения любых изменений в таблице.Вставка записей с помощью .executemany()
.executemany(). Метод принимает два параметра:reviewers:
insert_reviewers_query = """ INSERT INTO reviewers (first_name, last_name) VALUES ( %s, %s ) """ reviewers_records = [ ("Chaitanya", "Baweja"), ("Mary", "Cooper"), ("John", "Wayne"), ("Thomas", "Stoneman"), ("Penny", "Hofstadter"), ("Mitchell", "Marsh"), ("Wyatt", "Skaggs"), ("Andre", "Veiga"), ("Sheldon", "Cooper"), ("Kimbra", "Masters"), ("Kat", "Dennings"), ("Bruce", "Wayne"), ("Domingo", "Cortes"), ("Rajesh", "Koothrappali"), ("Ben", "Glocker"), ("Mahinder", "Dhoni"), ("Akbar", "Khan"), ("Howard", "Wolowitz"), ("Pinkie", "Petit"), ("Gurkaran", "Singh"), ("Amy", "Farah Fowler"), ("Marlon", "Crafford"), ] with connection.cursor() as cursor: cursor.executemany(insert_reviewers_query, reviewers_records) connection.commit() %s в качестве заполнителей для двух строк, которые вставляются в insert_reviewers_query. Заполнители действуют как спецификаторы формата и помогают зарезервировать место для переменной внутри строки.ratings:
insert_ratings_query = """ INSERT INTO ratings (rating, movie_id, reviewer_id) VALUES ( %s, %s, %s) """ ratings_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) ] with connection.cursor() as cursor: cursor.executemany(insert_ratings_query, ratings_records) connection.commit() SELECT.Чтение записей с помощью оператора SELECT
cursor.execute() запрос SELECT и вернуть результат с помощью cursor.fetchall():
select_movies_query = "SELECT * FROM movies LIMIT 5" with connection.cursor() as cursor: cursor.execute(select_movies_query) result = cursor.fetchall() for row in result: print(row)
(1, 'Forrest Gump', 1994, 'Drama', Decimal('330.2')) (2, '3 Idiots', 2009, 'Drama', Decimal('2.4')) (3, 'Eternal Sunshine of the Spotless Mind', 2004, 'Drama', Decimal('34.5')) (4, 'Good Will Hunting', 1997, 'Drama', Decimal('138.1')) (5, 'Skyfall', 2012, 'Action', Decimal('304.6')) result содержит записи, возвращенные с помощью .fetchall(). Это список кортежей, представляющих отдельные записи таблицы.LIMIT, чтобы ограничить количество строк, получаемых от оператора SELECT. Разработчики часто используют LIMIT для разбивки выдачи на страницы при обработке больших объемов данных.LIMIT можно передать два неотрицательных числовых аргумента:
SELECT * FROM movies LIMIT 2,5;
select_movies_query = "SELECT title, release_year FROM movies LIMIT 2, 5" with connection.cursor() as cursor: cursor.execute(select_movies_query) for row in cursor.fetchall(): print(row)
('Eternal Sunshine of the Spotless Mind', 2004) ('Good Will Hunting', 1997) ('Skyfall', 2012) ('Gladiator', 2000) ('Black', 2005) WHERE. Чтобы получить все фильмы с кассовыми сборами свыше 300 млн долларов, выполним следующий запрос:
select_movies_query = """ SELECT title, collection_in_mil FROM movies WHERE collection_in_mil > 300 ORDER BY collection_in_mil DESC """ with connection.cursor() as cursor: cursor.execute(select_movies_query) for movie in cursor.fetchall(): print(movie)
('Avengers: Endgame', Decimal('858.8')) ('Titanic', Decimal('659.2')) ('The Dark Knight', Decimal('535.4')) ('Toy Story 4', Decimal('434.9')) ('The Lion King', Decimal('423.6')) ('Deadpool', Decimal('363.6')) ('Forrest Gump', Decimal('330.2')) ('Skyfall', Decimal('304.6')) ORDER BY в запросе позволяет отсортировать сборы от самого высокого до самого низкого.CONCAT для объединения строк. Например, названия фильмов, чтобы избежать путаницы, обычно отображается вместе с годом выпуска. Получим названия пяти самых прибыльных фильмов вместе с датами их выхода в прокат:
select_movies_query = """ SELECT CONCAT(title, " (", release_year, ")"), collection_in_mil FROM movies ORDER BY collection_in_mil DESC LIMIT 5 """ with connection.cursor() as cursor: cursor.execute(select_movies_query) for movie in cursor.fetchall(): print(movie)
('Avengers: Endgame (2019)', Decimal('858.8')) ('Titanic (1997)', Decimal('659.2')) ('The Dark Knight (2008)', Decimal('535.4')) ('Toy Story 4 (2019)', Decimal('434.9')) ('The Lion King (1994)', Decimal('423.6')) LIMIT и вам не нужно получать все записи, можно использовать методы курсора .fetchone() и .fetchmany():.fetchone() извлекает следующую строку результата в виде кортежа, либо None, если доступных строк больше нет..fetchmany() извлекает следующий набор строк из результата в виде списка кортежей. Для этого ему передается аргумент, по умолчанию равный 1. Если доступных строк больше нет, метод возвращает пустой список..fetchmany():
select_movies_query = """ SELECT CONCAT(title, " (", release_year, ")"), collection_in_mil FROM movies ORDER BY collection_in_mil DESC """ with connection.cursor() as cursor: cursor.execute(select_movies_query) for movie in cursor.fetchmany(size=5): print(movie) cursor.fetchall()
('Avengers: Endgame (2019)', Decimal('858.8')) ('Titanic (1997)', Decimal('659.2')) ('The Dark Knight (2008)', Decimal('535.4')) ('Toy Story 4 (2019)', Decimal('434.9')) ('The Lion King (1994)', Decimal('423.6')) cursor.fetchall(). Мы делаем это, чтобы очистить все оставшиеся результаты, которые не были прочитаны .fetchmany().InternalError.
select_movies_query = """ SELECT title, AVG(rating) as average_rating FROM ratings INNER JOIN movies ON movies.id = ratings.movie_id GROUP BY movie_id ORDER BY average_rating DESC LIMIT 5 """ with connection.cursor() as cursor: cursor.execute(select_movies_query) for movie in cursor.fetchall(): print(movie)
('Night of the Living Dead', Decimal('9.90000')) ('The Godfather', Decimal('9.90000')) ('Avengers: Endgame', Decimal('9.75000')) ('Eternal Sunshine of the Spotless Mind', Decimal('8.90000')) ('Beasts of No Nation', Decimal('8.70000'))
select_movies_query = """ SELECT CONCAT(first_name, " ", last_name), COUNT(*) as num FROM reviewers INNER JOIN ratings ON reviewers.id = ratings.reviewer_id GROUP BY reviewer_id ORDER BY num DESC LIMIT 1 """ with connection.cursor() as cursor: cursor.execute(select_movies_query) for movie in cursor.fetchall(): print(movie)
('Mary Cooper', 4) cursor.execute(), получаем результаты с помощью .fetchall().WHERE.Команда UPDATE
UPDATE:
update_query = """ UPDATE reviewers SET last_name = "Cooper" WHERE first_name = "Amy" """ with connection.cursor() as cursor: cursor.execute(update_query) connection.commit() cursor.execute(), а .commit() вносит необходимые изменения в таблицу reviewers.movie_id, reviewer_id и новый rating. Пример на SQL:
UPDATE ratings SET rating = 5.0 WHERE movie_id = 18 AND reviewer_id = 15; SELECT * FROM ratings WHERE movie_id = 18 AND reviewer_id = 15;
from getpass import getpass from mysql.connector import connect, Error movie_id = input("Enter movie id: ") reviewer_id = input("Enter reviewer id: ") new_rating = input("Enter new rating: ") update_query = """ UPDATE ratings SET rating = "%s" WHERE movie_id = "%s" AND reviewer_id = "%s"; SELECT * FROM ratings WHERE movie_id = "%s" AND reviewer_id = "%s" """ % ( new_rating, movie_id, reviewer_id, movie_id, reviewer_id, ) try: with connect( host="localhost", user=input("Enter username: "), password=getpass("Enter password: "), database="online_movie_rating", ) as connection: with connection.cursor() as cursor: for result in cursor.execute(update_query, multi=True): if result.with_rows: print(result.fetchall()) connection.commit() except Error as e: print(e)
Enter movie id: 18 Enter reviewer id: 15 Enter new rating: 5 Enter username: root Enter password: ········
[(18, 15, Decimal('5.0'))] multi значение True. В этом случае cursor.execute() возвращает итератор. Каждый элемент в итераторе соответствует объекту курсора, который выполняет инструкцию, переданную в запросе. Приведенный код запускает на этом итераторе цикл for, вызывая .fetchall() для каждого объекта курсора..fetchall() вызывает исключение. Чтобы избежать этой ошибки, в приведенном коде мы используем свойство cursor.with_rows, которое указывает, создавала ли строки последняя выполненная операция.WHERE в текущем виде является заманчивой целью для хакеров. Она уязвима для атаки с использованием SQL-инъекции, позволяющей злоумышленникам повредить базу данных или использовать ее не по назначению.movie_id = 18, reviewer_id = 15 и rating = 5.0 в качестве входных данных, то результат будет выглядеть так:
$ python modify_ratings.py Enter movie id: 18 Enter reviewer id: 15 Enter new rating: 5.0 Enter username: <user_name> Enter password: [(18, 15, Decimal('5.0'))] movie_id = 18 и reviewer_id = 15 изменилась на 5.0. Но если бы вы были хакером, вы могли отправить на вход скрытую команду:
$ python modify_ratings.py Enter movie id: 18 Enter reviewer id: 15"; UPDATE reviewers SET last_name = "A Enter new rating: 5.0 Enter username: <user_name> Enter password: [(18, 15, Decimal('5.0'))] last_name всех записей в таблице рецензентов "A":
>>> select_query = """ ... SELECT first_name, last_name ... FROM reviewers ... """ >>> with connection.cursor() as cursor: ... cursor.execute(select_query) ... for reviewer in cursor.fetchall(): ... print(reviewer) ... ('Chaitanya', 'A') ('Mary', 'A') ('John', 'A') ('Thomas', 'A') ('Penny', 'A') ('Mitchell', 'A') ('Wyatt', 'A') ('Andre', 'A') ('Sheldon', 'A') ('Kimbra', 'A') ('Kat', 'A') ('Bruce', 'A') ('Domingo', 'A') ('Rajesh', 'A') ('Ben', 'A') ('Mahinder', 'A') ('Akbar', 'A') ('Howard', 'A') ('Pinkie', 'A') ('Gurkaran', 'A') ('Amy', 'A') ('Marlon', 'A') first_name и last_name для всех записей в таблице проверяющих. Атака с использованием SQL-инъекции повредила эту таблицу, изменив last_name всех записей на «A»..execute():
from getpass import getpass from mysql.connector import connect, Error movie_id = input("Enter movie id: ") reviewer_id = input("Enter reviewer id: ") new_rating = input("Enter new rating: ") update_query = """ UPDATE ratings SET rating = %s WHERE movie_id = %s AND reviewer_id = %s; SELECT * FROM ratings WHERE movie_id = %s AND reviewer_id = %s """ val_tuple = ( new_rating, movie_id, reviewer_id, movie_id, reviewer_id, ) try: with connect( host="localhost", user=input("Enter username: "), password=getpass("Enter password: "), database="online_movie_rating", ) as connection: with connection.cursor() as cursor: for result in 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() проверяет, что значения в кортеже, полученном в качестве аргумента, имеют требуемый тип данных. Если пользователь попытается ввести какие-то проблемные символы, код вызовет исключение:
$ python modify_ratings.py Enter movie id: 18 Enter reviewer id: 15"; UPDATE reviewers SET last_name = "A Enter new rating: 5.0 Enter username: <user_name> Enter password: 1292 (22007): Truncated incorrect DOUBLE value: '15"; UPDATE reviewers SET last_name = "A' Удаление записей: команда DELETE¶
DELETE является необратимой операцией, мы рекомендуем сначала запускать запрос SELECT с тем же фильтром, чтобы убедиться, что вы удаляете нужные записи. Например, чтобы удалить все оценки фильмов, данные reviewer_id = 2, мы можем сначала запустить соответствующий запрос SELECT:
select_movies_query = """ SELECT reviewer_id, movie_id FROM ratings WHERE reviewer_id = 2 """ with connection.cursor() as cursor: cursor.execute(select_movies_query) for movie in cursor.fetchall(): print(movie)
(2, 7) (2, 8) (2, 12) (2, 23) reviewer_id и movie_id для записей в таблице оценок, для которых reviewer_id = 2. Убедившись, что это те записи, которые нужно удалить, выполним запрос DELETE с тем же фильтром:
delete_query = "DELETE FROM ratings WHERE reviewer_id = 2" with connection.cursor() as cursor: cursor.execute(delete_query) connection.commit() MySQL Connector/Python для интеграции базы данных MySQL в ваше приложение Python. Мы также разработали тестовый образец базы данных MySQL и повзаимодействовали с ней непосредственно из Python-кода. Дополнительные сведения можно найти в официальной документации.
- 2 views
- 0 Comment
About Author 01.
Roman Spiridonov
Привет ! Мне 38 лет, я работаю в области информационных технологий более 4 лет. Тут собрано самое интересное.
Свежие комментарии