PHP-подключение к БД MySQL (Два Способа с Примерами)

Поговорим о том, как работает БД MSSQL, зачем она нужна и как настроить свой SQL-сервер Microsoft.

Работаете c программами на PHP в Linux и у вас есть задача по интегрированию MySQL на сайт на PHP? Тогда это руководство для вас. 😉

Что такое база данных

Базы данных позволяют хранить, добавлять, модифицировать и находить информацию по запросу программиста.

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

Что такое SQL

SQL – это язык для «общения» с базами данных. Специальный синтаксис, позволяющий при помощи несложных команд запрашивать информацию из БД или, наоборот, добавлять в нее новый контент. SQL часто используется в реляционных базах данных, где соседние элементы тесно связаны друг с другом и организуются при помощи таблиц. Схожий синтаксис используется в БД MySQL, PostgresSQL и SQL Server от Microsoft (собственно, это и есть наша MSSQL).

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

Создание Базы Данных MySQL (При необходимости)

Этот шаг можно пропустить, если у вас уже есть база данных MySQL. Если вы являетесь пользователем Hostinger, вы можете легко создать новую БД через hPanel — панель управления хостингом Hostinger, следуя простой инструкции:

1. Найдите меню «Базы данных MySQL» в разделе «Базы данных».
2. Заполните все необходимые поля и нажмите «Создать».
   

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

Запишите учётные данные только что созданной базы данных 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) 

PHP-подключение к БД MySQL с PDO

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

1. В public_html создайте файл с названием pdoconfig.php и вставьте следующий код. Как всегда, не забудьте заменить значения плейсхолдеров информацией из вашей базы данных. Сохраните и закройте его, когда закончите.

   <?php $host = 'localhost'; $dbname = 'databasename'; $username = 'username'; $password = 'password';

2. Создайте ещё один файл и назовите его databaseconnect.php в том же каталоге, но со следующим кодом. Если вы назвали предыдущий файл по-другому, не забудьте изменить значение после require_once.

   <?php
   require_once 'pdoconfig.php';
   try { $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password); echo "Connected to $dbname at $host successfully.";
   } catch (PDOException $pe) { die("Could not connect to the database $dbname :" . $pe->getMessage());
   }

Объяснение Скрипта PDO

Для подключения к базе данных PDO необходимо создать новый объект PDO с именем источника данных (DSN), именем пользователя и паролем.

DSN определяет тип базы данных, имя базы данных и любую другую информацию, относящуюся к базе данных, если это необходимо. Это переменные и значения, указанные нами в файле dbconfig.php, на которые один раз ссылается строка require_once в файле databaseconnect.php.

Если соединение установлено успешно, вы увидите сообщение «Connected to $dbname at $host successfully». Однако, если попытка не удалась, код в блоке catch покажет простое сообщение об ошибке и завершит скрипт.

Обновление и удаление записей из базы данных

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

Два способа PHP-подключения к БД MySQL

Есть два метода подключения к базе данных MySQL с помощью PHP: MySQLi и PDO.

MySQLi расшифровывается как MySQL Improved. Это эксклюзивное расширение MySQL, которое добавляет новые функции в интерфейс базы данных. Функции MySQLi являются как процедурными, так и объектно-ориентированными, причём первую парадигму расширение унаследовало от более ранней версии MySQL.

Сама MySQL разбивает задачу на линейные, пошаговые процедуры, что затрудняет внесение изменений, поскольку вам приходится редактировать код сверху. Между тем MySQLi рассматривает данные как набор взаимозаменяемых объектов с функциями, позволяя пользователям легко добавлять или удалять данные.

PDO расшифровывается как PHP Data Object, или объект данных PHP. В отличие от MySQLi, PDO является только объектно-ориентированным методом. Он поддерживает ряд различных типов баз данных, использующих PHP, таких как MySQL, MSSQL, Informix и PostgreSQL.

Исходные функции mysql_ устарели. Их лучше не использовать, поскольку они небезопасны и больше не поддерживаются.

Одна из наиболее важных функций, которую поддерживают оба метода — это подготовленные выражения (prepared statements). Она сокращает время, необходимое MySQL для выполнения повторяемого запроса. Эта функция также используется для предотвращения SQL-инъекций при внесении изменений в базу данных.

Какой бы метод вы ни использовали, вам понадобится правильная информация для подключения к созданной вами базе данных MySQL. Здесь вам пригодятся ранее сохранённые данные БД.

Вам также потребуется правильное имя сервера, или имя хоста для конфигурации. Hostinger использует “localhost” в качестве имени хоста своего сервера MySQL. Это имя, которое вы будете использовать, если загрузите свой PHP-скрипт на тот же сервер, что и база данных.

Отображение схемы таблиц с использованием оператора 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. Для каждого столбца выводится информация, о типе данных, является ли столбец первичным ключом и т. д.

Появление возможных ошибок, и как с ними справиться

1. При выполнении команды создания проверяйте, те ли кавычки используете. Ведь ` и ‘ — это разные знаки.
2. Если при выполнении программы есть какая-либо ошибка в SQL-запросе, связанная с форматом, то при присваивании $sql

$sql=”CREATE TABLE student(“.“USN INT NOT NULL,”.“Name VARCHAR(100) NOT NULL,”.“Marks VARCHAR(40) NOT NULL);”;

поставьте в конце запроса вместо точки точку с запятой.

3. Если возникли проблемы с командами MySQLi, выполните следующие команды:

Затем запишите свою версию PHP. Для этого в первой из следующих четырех команд замените x.x на свою версию PHP:

sudo apt-get install phpx.x-mysqli
sudo service apache2 restart
sudo phpenmod mysqli
sudo service apache2 restart

Так, если у вас версия PHP 7.4, то команда будет sudo apt-get install php7.4-mysqli.

4. Каждый раз, когда заходите на свой сайт на PHP, запускайте в каталоге, где находится программа на PHP, следующую команду:

php -S localhost:8000

И при получении какой-либо ошибки, например такой:

Запускайте эту команду:

И после выполнения php -S localhost:8000 он заработает. Так как процесс, задействующий этот порт, будет уничтожен и его можно будет использовать.

MySQL и MSSQL – одно и то же?

Нет. Как уже было отмечено выше, существует множество баз данных. MySQL чаще остальных упоминается в сети, так как является наиболее популярным вариантом БД. Она имеет открытый исходный код и распространяется бесплатно, поэтому и сыскала любовь массовой аудитории. 

MSSQL – это продукт Microsoft, существующий как в коммерческой, так и в бесплатной версии. И несмотря на то, что оба продукта используют схожий синтаксис, они отличаются друг от друга с точки зрения функциональности и в плане поддержки со стороны разработчиков. 

Также существует PostgreSQL – еще одна популярная база данных с высоким уровнем отказоустойчивости и большой коллекцией расширений. 

Проверка Подключения и Устранение Распространённых Ошибок

Чтобы проверить, успешно ли установлено соединение, войдите в свой домен так: vashdomen/databaseconnect.php. Если вы назвали PHP-файл другим именем, обязательно укажите правильное название.

Если всё работает хорошо, вы увидите «Connected successfully» или другой вариант этого сообщения.

В случае возникновения проблемы при попытке установить соединение, вы увидите сообщения об ошибке. Они отличаются для MySQLi и PDO.

Ошибка при Неправильном Пароле

Эта ошибка возникает, если мы меняем пароль или какие-либо учётные данные в PHP-коде (но не меняем их в реальной базе данных).

Если вы видите сообщение «Access denied» или «Could not connect to database», сопровождаемое “(using password: YES)”, первое, что нужно сделать, это проверить данные для доступа к БД. Возможно, вы сделали опечатку или пропустили какую-то часть.

Не Удаётся Подключиться к MySQL-серверу

Если вы видите сообщение «Can’t connect to MySQL server on ‘server’ (110)» в MySQLi, это означает, что скрипт не получил ответа от сервера. Это происходит, когда мы устанавливаем «server» вместо «localhost» в качестве $servername, и имя не распознаётся.

И, конечно же, всегда важно помнить одно золотое правило устранения ошибок: проверять журнал ошибок вашего сайта.

Журнал находится в той же папке, где запущен скрипт. Например, если мы запускаем сценарий в public_html, error_log будет в той же папке.

Вставка записей в таблицы

Заполним таблицы данными. В этом разделе мы рассмотрим два способа вставки записей с помощью MySQL Connector в коде Python.

Первый метод, .execute(), хорошо работает, когда количество записей невелико. Второй, .executemany() лучше подходит для реальных сценариев.

Создание, изменение и удаление таблиц

В этом разделе мы рассмотрим, как с помощью Python выполнять некоторые базовые запросы: CREATE TABLE, DROP и ALTER.

Вставка записей с помощью. 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() после выполнения любых изменений в таблице.

Другие способы соединения 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 и т. д. Читайте наш туториал по SQLAlchemy.
• peewee ― легкая и быстрая ORM-библиотека с простой настройкой, что очень полезно, когда ваше взаимодействие с базой данных ограничивается извлечением нескольких записей. Если нужно скопировать отдельные записи из базы данных MySQL в csv-файл, то лучший выбор ― peewee.
• Django ORM ― одна из самых мощных составляющих веб-фреймворка Django, позволяющая простым образом взаимодействовать с различными базами данных SQLite, PostgreSQL и MySQL. Многие приложения на основе Django используют Django ORM для моделирования данных и базовых запросов, однако для более сложных задач разработчики обычно используют SQLAlchemy.

Чтение записей из базы данных

До сих пор мы только создавали элементы базы данных. Пришло время выполнить несколько запросов и найти интересующие нас свойства. В этом разделе мы узнаем, как читать записи из таблиц базы данных с помощью оператора SELECT.

Сравнение MySQL с другими SQL-базами данных

SQL (Structured Query Language) — язык структурированных запросов. SQL является широко используемым языком программирования для управления реляционными базами данных. Возможно, вы слышали о различных СУБД на основе SQL: MySQL, PostgreSQL, SQLite и SQL Server. Все эти базы данных соответствуют стандартам SQL, но отличаются в деталях.

Помимо поддержки со стороны open source-сообщества, есть и другие причины успеха MySQL:

1. Простота установки. MySQL разработан, чтобы быть удобным для пользователя. Базу данных легко создать и настроить. MySQL доступен для основных операционных систем, включая Windows, macOS, Linux и Solaris.
2. Скорость. MySQL имеет репутацию быстрого решения для баз данных. Еще эта СУБД хорошо масштабируется.
3. Права пользователя и безопасность. MySQL позволяет устанавливать уровни безопасности паролей, добавлять и удалять привилегии учетным записям пользователей. Управление правами пользователей выглядит существенно проще, чем у многих других СУБД, таких как PostgreSQL, где управление файлами конфигурации, требует некоторой сноровки.

MySQL использует синтаксис, похожий на стандартный SQL, однако имеющий некоторые важные отличия, описанные в официальной документации.

Необходимые условия

PHP и установленный веб-сервер (например, Apache).

Обработка нескольких таблиц с помощью оператора JOIN

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

 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) 

Как видим, больше всего рецензий написала Mary Cooper.

Не имеет значения, насколько сложен запрос ― в конечном счете он обрабатывается сервером MySQL. Процесс выполнения запроса всегда остается прежним: передаем запрос в cursor.execute(), получаем результаты с помощью .fetchall().

Чтение записей с помощью оператора 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 для разбивки выдачи на страницы при обработке больших объемов данных.

В MySQL оператору LIMIT можно передать два неотрицательных числовых аргумента:

 SELECT * FROM movies LIMIT 2,5; 

При использовании двух числовых аргументов первый указывает смещение, равное в данном примере 2, а второй ограничивает количество возвращаемых строк до 5. То есть запрос из примера вернет строки с 3 по 7.

 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) 

Как настроить MSSQL?

Чтобы начать работать с Microsoft SQL Server, надо раздобыть копию самого сервера, а также установить программу для управления базами данных Microsoft через графический интерфейс. 

О том, как запустить SQL-сервер подробно рассказывается на официальном сайте Microsoft. Там же есть и бесплатный дистрибутив платформы под названием Developer. Для первого знакомства с Microsoft SQL Server, отладки и тестирования кода Developer-версии будет достаточно. Загружаем ее и запускаем.

Затем устанавливаем приложение SQL Server Management Studio. Это графическая оболочка для редактирования информации в SQL-таблицах базы данных Microsoft. 

Подключение к SQL-серверу

Для начала откроем утилиту SQL Server Management Studio. Все манипуляции мы будем проделывать через нее. При первом же запуске эта программа попросит вас подключиться к SQL-серверу. В интерфейсе программы он называется Database Engine. 

Приложение запросит ряд данных для подключения. 

• Тип сервера (можно оставить по умолчанию, так как пока мы работаем с локальной копией MSSQL).

• Имя сервера. Нужно его заменить, если вы переименовывали сервер, когда запускали собственную копию базы данных на локальной машине. 

Указав необходимую информацию, нажимаем на кнопку Connect. Через несколько секунд соединение должно быть установлено, а в окне Object Explorer появится информация о SQL-сервере и его содержимом. 

Создание базы

Важный момент заключается в том, что SQL-сервер и база данных – не одно и то же. Поэтому, запустив сервер и подключившись к нему, вам все еще необходимо создать базу самостоятельно. 

Для этого в SQL Server Management Studio:

• Правой кнопкой мыши кликаем по названию сервера в окне Object Explorer.

• В списке опций находим пункт New Query.

• Появится окно для ввода запроса. Здесь как раз поддерживается SQL-синтаксис – с помощью него мы и создадим новую базу данных.

USE master
GO
IF NOT EXISTS (
SELECT name
FROM sys.databases
WHERE name = N'databaseName'
)
CREATE DATABASE [databaseName]
GO

Только вместо databaseName укажите название БД на свой вкус, не меняя соседних символов и других участков кода.

• После этого активируем запрос, нажав на кнопку Execute в левой части интерфейса программы или на клавишу F5. 

Создание таблиц

А еще таблицы визуально напоминают Excel, поэтому для многих будет привычным подобный интерфейс.

Чтобы создать новую таблицу:

• Кликаем по выпадающему меню со списком доступных баз данных (это поле под основным меню справа от значка в виде вилки). 

• В появившемся списке выбираем свою базу.

• Снова делаем SQL-запрос к базе:

USE [myTable]
IF OBJECT_ID('dbo.Posts', 'U') IS NOT NULL
DROP TABLE dbo.Posts
GO
CREATE TABLE dbo.Posts
(
postId INT NOT NULL PRIMARY KEY, -- primary key column
Title [NVARCHAR](50) NOT NULL,
Category [NVARCHAR](50) NOT NULL,
Content [NVARCHAR](50) NOT NULL
);
GO

• Активируем запрос, нажав на клавишу F5.

После этого в Object Explorer появится новое поле, отражающие вновь созданную таблицу со всеми необходимыми поддиректориями (колонками, строками и т.п.). 

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

Сами по себе таблицы не особо полезны, если в них нет какого-либо контента. Контент добавляется с помощью строк. Строка – это набор данных, включающий в себя информацию по пунктам, соответствующим колонкам в таблице. А колонки в таблице – это список параметров, что мы указали выше, когда создавали таблицу (в нашем случае это были пункты postId, Title, Category и Content). 

Чтобы добавить строки в таблицу, снова воспользуемся SQL-выражением:

INSERT INTO dbo.Customers
([postId],[Title], [Category], [Content])
VALUES
( 1, N'About apes', N'Animals', N'Apes are cool'),
( 2, N'Story', N'Articles', N'The story about dead women killer'),
( 3, N'Very important article', N'News', N'Not so important information'),
GO

В запросе мы указываем наименования колонок, которые будут заполнены данными из списка запросов. В поле VALUE как раз указываются нужные данные в заранее определенном порядке. То есть первый элемент в строке станет Id, второй элемент будет Title и т.п. 

Создание запросов к базе данных MSSQL

До этого мы делали SQL-запросы, которые позволяют модифицировать базу. То есть добавлять в нее новую информацию, создавать таблицы, колонки и строки. Но немаловажным является умение добывать информацию из БД, чтобы отображать ее в интерфейсе приложения или веб-сайта. 

SELECT * FROM dbo.Posts

В блоке Results отобразятся все строки из таблицы. Здесь же можно их отредактировать, не покидая визуальный редактор. 

Программное подключение

Информацию для других языков можно найти в официальной документации и на Stack Overflow.

Подключаемся к серверу MySQL из Python

 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 ...).
• Никогда не следует встраивать учетные данные (имя пользователя и пароль) в строковом виде в скрипт Python. Это плохая практика для развертывания, которая представляет серьезную угрозу безопасности. Приведенный код запрашивает для входа учетные данные. Для этого используется встроенный модуль getpass, чтобы скрыть вводимый пароль. Хотя это лучше, чем жесткое кодирование, но есть и другие, более безопасные способы хранения конфиденциальной информации, например, использование переменных среды.

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

Установка MySQL Server и MySQL Connector

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

Установка MySQL Connector/Python

Драйвер базы данных — программное обеспечение, позволяющее приложению подключаться и взаимодействовать с СУБД. Такие драйверы обычно поставляются в виде отдельных модулей. Сандартный интерфейс, которому должны соответствовать все драйверы баз данных Python, описан в PEP 249. Драйверы баз данных Python, такие как sqlite3 для SQLite, psycopg для PostgreSQL и MySQL Connector/Python для MySQL, следуют этим правилам.

Для установки драйвера (коннектора) воспользуемся менеджером пакетов pip:

 pip install mysql-connector-python 

pip установит коннектор в текущую активную среду. Чтобы работать с проектом изолированным образом, мы рекомендуем настроить виртуальную среду.

Проверим результат установки, запустив в терминале Python следующую команду:

 import mysql.connector 

Если инструкция импорта выполняется без ошибок, значит mysql.connector успешно установлен и готов к использованию.

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

1. Подключаемся к серверу MySQL.
2. Создаем новую базу данных (при необходимости).
3. Соединяемся с базой данных.
4. Выполняем SQL-запрос, собираем результаты.
5. Сообщаем базе данных, если в таблицу внесены изменения.
6. Закрываем соединение с сервером MySQL.

Каким бы ни было приложение, первый шаг ― связать между собой приложение и базу данных.

Определение схемы базы данных

Начнем с создания схемы базы данных для рейтинговой системы фильмов. База данных будет состоять из трех таблиц:

1. movies ― общая информация о фильмах:

• title
• release year
• genre
• collection_in_mi

2. reviewers ― информация о людях, опубликовавших оценки фильмов:

1. first_name
2. last_name

3. ratings ― информация об оценках фильмов рецензентами:

1. movie_id (foreign key)
2. reviewer_id (foreign key)
3. rating

Этих трех таблиц достаточно для целей данного руководства.

Таблицы в базе данных связаны друг с другом: movies и reviewers должны иметь отношение «многие ко многим»: один фильм может быть просмотрен несколькими рецензентами, а один рецензент может рецензировать несколько фильмов. Таблица ratings соединяет таблицу фильмов с таблицей рецензентов.

Команда UPDATE

Представим, что рецензент Amy Farah Fowler вышла замуж за Sheldon Cooper. Она сменила фамилию на Cooper, и нам необходимо обновить базу данных. Для обновления записей в MySQL используется оператор 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; 

Указанные запросы сначала обновляют рейтинг, а затем выведут обновленный. Напишем скрипт на Python, который позволит корректировать оценки:

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

И снова выходные данные показывают, что указанный рейтинг был изменен на 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' 

Такой подход стоит использовать всегда, когда вы включаете в запрос пользовательский ввод. Не поленитесь узнать и про другие способы предотвращения атак с использованием SQL-инъекций.

Какой вариант SQL выбрать?

Вариант Microsoft отличается от конкурентов заточенностью под корпоративные задачи. В частности, решение MSSQL может похвастать повышенной надежностью. Пользователь не вправе вносить изменения в базу данных, пока она функционирует и используется приложением. Добавлять данные на SQL-сервер необходимо в момент, когда БД выключена. 

При этом MSSQL Server позволяет прерывать запросы, в то время как основной конкурент базы в лице MySQL такого не допускает. 

Но зато решение Microsoft стоит денег, и бесплатно разместить у себя SQL-сервер не получится (есть пробная версия и специальный вариант MSSQL для разработчиков, но использовать их в коммерческих целях нельзя). 

Установка MySQL Server

Официальная документация описывает рекомендуемые способы загрузки и установки MySQL Server. Есть инструкции для всех популярных операционных систем, включая Windows, macOS, Solaris, Linux и многие другие.

Для Windows лучше всего загрузить установщик MySQL и позволить ему позаботиться о процессе. Диспетчер установки также поможет настроить параметры безопасности сервера MySQL. На странице учетных записей будет необходимо ввести пароль для root-записи и при желании добавить других пользователей с различными привилегиями.

С помощью установщиков можно настроить и другие полезные инструменты, например, MySQL Workbench. Удобная альтернатива установке в операционной системе — развернуть MySQL с помощью Docker.

Вставка записей с помощью. executemany()

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

1. Запрос, содержащий заполнители для записей, которые необходимо вставить.
2. Список записей для вставки.

Применим подход для заполнения таблицы 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() 

Теперь все три таблицы заполнены данными. Следующий шаг ― разобраться, как с этой базой данных взаимодействовать.

PHP-подключение к БД MySQL с MySQLi

Выполните следующие действия, чтобы подключить PHP-скрипт к MySQL посредством MySQLi:

1. Перейдите в Файловый менеджер -> public_html.
2. Создайте новый файл, щёлкнув на соответствующую иконку в верхнем меню.
3. Сохраните его как databaseconnect.php. Вы можете заменить имя на любое другое, просто убедитесь, что в качестве расширения используется php.
4. Дважды щёлкните по файлу, чтобы открыть его. Скопируйте и вставьте в него следующие строки кода. Замените первые четыре значения после <?php учётными данными, которые вы указали ранее.

<?php
$servername = "localhost";
$database = "databasename";
$username = "username";
$password = "password";
// Создаем соединение
$conn = mysqli_connect($servername, $username, $password, $database);
// Проверяем соединение
if (!$conn) { die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
mysqli_close($conn);
?>

Объяснение Кода MySQLi

Основным методом, используемым в этом скрипте, является mysqli_connect (). Это внутренняя функция PHP для установления нового соединения с сервером MySQL.

Если попытка соединения была неудачной, выполняется функция die(). Она убивает наш скрипт и выдаёт сообщение об ошибке подключения, которое мы прописали. По умолчанию в сообщении об ошибке подключения MySQL будет указано «Connection failed», за которым следует точное сообщение об ошибке с описанием проблемы.

С другой стороны, если MySQL-соединение установлено успешно, мы увидим сообщение «Connected successfully».

Последняя часть кода, mysqli_close, позволяет закрыть соединение с базой данных вручную. Если вы ничего не укажите, соединения MySQL закроются автоматически после завершения скрипта.

Код для создания таблицы при загрузке сайта

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

Как было указано в необходимых условиях, в качестве хоста будет использоваться Apache2, поэтому выполняем такие команды:

cd /var/www/html
vim sqltest.php

С их помощью создается файл и открывается редактор Vim.

Копируем и вставляем в редактор следующий код:

В строках 7, 8 и 9 объявляются переменные, в которых сохраняются имя пользователя, пароль и localhost. Дальше они задействуются в строке 10 и передаются в качестве параметров в команду mysqli_connect, которой инициируется соединение с сервером MySQL. Если соединение установить не удалось, для отображения ошибки используется оператор if и mysqli_error(). Затем пишется запрос на создание таблицы, который присваивается переменной sql. И запускается запрос для выбора базы данных test. После чего запускается mysqli_query для создания таблицы посредством передачи в качестве параметров переменных sql и connection.

Если все работает правильно, получаем сообщение об успешном создании таблицы.

Если при открытии сайта сообщения не видно, переходим в каталог, в котором создан файл:

И выполняем в терминале следующую команду:

php -S localhost:8000

Теперь сообщение об успешном создании таблицы будет видно.

Проверим, выполнив в терминале команду:

use test;
desc student;

Создание таблиц с помощью оператора CREATE TABLE

Чтобы создать новую таблицу в MySQL, нам нужно использовать оператор 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
); 

Если вы раньше встречались с SQL, вам будет понятен смысл приведенного запроса. У диалекта MySQL есть некоторые отличительные черты. Например, MySQL предлагает широкий выбор типов данных, включая 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() 

Реализация отношений внешнего ключа в MySQL немного отличается и имеет ограничения в сравнении со стандартным SQL. В MySQL и родитель, и потомок внешнего ключа должны использовать один и тот же механизм хранения ― базовый программный компонент, который система управления базами данных использует для выполнения SQL-операций. MySQL предлагает два вида таких механизмов:

1. Транзакционные механизмы хранения безопасны для транзакций и позволяют откатывать транзакции с помощью простых команд, таких как rollback. К этой категории относятся многие популярные движки MySQL, включая InnoDB и NDB.
2. Нетранзакционные механизмы хранения для отмены операторов, зафиксированных в базе данных, опираются на ручной код. Это, например MyISAM и MEMORY.

InnoDB ― самый популярный механизм хранения по умолчанию. Соблюдая ограничения внешнего ключа, он помогает поддерживать целостность данных. Это означает, что любая CRUD-операция с внешним ключом предварительно проверяется на то, что она не приводит к несогласованности между разными таблицами.

Обратите внимание, что таблица 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() 

Удаление записей

Процедура удаления записей очень похожа на их обновление. Поскольку 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() 

Еще одна программа на PHP

И вот еще одна программа на PHP для добавления новых записей в таблицу, попробуйте.

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

create table book(Acc_no INTEGER(20),Title varchar(50),Author varchar(50),Publisher varchar(50),Edition INTEGER(3));

<html>
<head><title>Deatails</title></head>
<body>
<form action = "phpfile.php" method=”post”>
<h1>ENTER DETAILS</h1>
ACCNO<input type="text"name="Acc_no"> <br>
<br>
TITLE<input type="text"name="Title"> <br>
<br>
AUTHOR<input type="text"name="Author"> <br>
<br>
EDITION<input type="text"name="Edition"> <br>
<br>
PUBLICATION<input type="text"name="Publisher"> <br>
<br>
<input type="submit"value="SUBMIT">
<input type="reset"value="CLEAR">
</form>
</body>
</html>

Читайте нас в Telegram, VK и Яндекс.Дзен

Изменение схемы таблицы с помощью оператора 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) 

Создаем новую базу данных

Чтобы создать новую базу данных, например, с именем online_movie_rating, нужно выполнить инструкцию SQL:

 CREATE DATABASE online_movie_rating; 

MySQL обязывает ставить точку с запятой (;) в конце оператора. Однако MySQL Connector/Python автоматически добавляет точку с запятой в конце каждого запроса.

Чтобы выполнить SQL-запрос, нам понадобится курсор, который абстрагирует процесс доступа к записям базы данных. 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',) 

Приведенный код выведет имена всех баз данных, находящихся на нашем сервере MySQL. Команда SHOW DATABASES в нашем примере также вывела базы данных, которые автоматически создаются сервером MySQL и предоставляют доступ к метаданным баз данных и настройкам сервера.

Создание таблицы при загрузке сайта

Для начала у вас должен быть установлен PHP. Чтобы получить версию, запускаем:

Вот такой ответ должен прийти:

Теперь установим сервер MySQL. Для этого открываем терминал и запускаем:

sudo apt-get install mysql-server

Вводим пароль, чтобы начать установку, и нажимаем на Enter.

Проверяем, установлен ли MySQL, этой командой:

Если установка прошла успешно, выводится примерно следующее:

Теперь для доступа к базе данных с использованием программы на PHP нужно передать в качестве аргументов в SQL-запросе имя пользователя и пароль. Поэтому создадим пользователя с паролем и предоставим ему права доступа к базе данных.

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

Теперь предоставим пользователю разрешения на доступ к базе данных, а заодно убедимся, что эти права доступа отображаются (для этого задействуем команду flush):

И для доступа к MySQL в Google Cloud:

Для привязки ко всем адресам запускаем это:

#bind-address = 127.0.0.1

Для создания нового пользователя и пароля нужно выполнить указанные выше команды. После добавления нового пользователя выходим и перезапускаем сервер MySQL:

exit
service mysql restart

Обратите внимание: в случае с PHP версией 7+ для правильного выполнения команд MySQL необходимо использовать расширение MySQLi. Функции MySQLi позволяют получать доступ к серверам баз данных MySQL.

Для установки расширения MySQLi запускаем:

Sudo apt Install php-mysqli

Теперь все готово к выполнению программ на PHP с интегрированием MySQL. Переходим к решению сформулированной выше задачи.

Первым делом создадим базу данных на сервере MySQL. Для этого подключимся к серверу MySQL:

Для того, чтобы создать таблицу, сначала нужно создать базу данных. Следующей командой создаем базу данных с названием «test»:

create database test;

Дальше эту базу данных нужно выбрать:

Фильтрация результатов с помощью WHERE

Записи таблицы также можно фильтровать, используя 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 в запросе позволяет отсортировать сборы от самого высокого до самого низкого.

MySQL предоставляет множество операций форматирования строк, таких как 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.

Итоги

В этом руководстве мы показали, как происходит PHP-подключение к БД MySQL с помощью MySQLi и PHP Data Objects (PDO).

Надеемся, что это руководство было полезно для тех, кто только начинает заниматься веб-разработкой. В конце концов, подключение к базе данных — это первый и самый важный шаг при работе с более сложными скриптами и конфигурациями.

Если у вас возникнут какие-либо вопросы после прочтения, не стесняйтесь и напишите нам в комментариях ниже.

Ольга вже близько восьми років працює менеджером у сфері IT, три з яких вона займається SEO. Написання технічних завдань та інструкцій — один з її основних обов’язків. Її хобі — дізнаватися щось нове і створювати цікаві та корисні статті про сучасні технології, веброзробку, мови програмування, пошукову оптимізацію сайтів та багато іншого.

Заключение

В этом руководстве мы познакомились с применением MySQL Connector/Python для интеграции базы данных MySQL в ваше приложение Python. Мы также разработали тестовый образец базы данных MySQL и повзаимодействовали с ней непосредственно из Python-кода. Дополнительные сведения можно найти в официальной документации.

Python имеет коннекторы и для других СУБД, таких как MongoDB и PostgreSQL. Будем рады узнать, какие еще материалы по Python и базам данных вам были бы интересны.

Вместо заключения

Теперь вы можете создать локальную копию базы данных Microsoft и добавить в нее всю необходимую информацию. Даже если вы никогда не работали с БД, вы быстро поймете, насколько это логичный и удобный интерфейс для манипуляции контентом. Главное – практиковаться.