Как запустить базу данных sql
Перейти к содержимому

Как запустить базу данных sql

  • автор:

MySQL — как запустить на Windows, или варианты организации рабочего окружения. Часть 1

Мотивационное фото

Рассматривается версия Community. Материал ориентирован на тех, кто желает постичь основы управления базами данных, в частности, MySQL.

Необходимые компетенции: базовые навыки работы с командной строкой (консолью) MS Windows и самой операционной системой ( редактирование реестра, копирование, вставка, удаление объектов, строк в консоли ), 1-2 драгоценных часа жизненного цикла :-).

В статье приведены многочисленные ссылки на разъясняющий материал — не пропадете :-). Вопросы, предложения и комментарии читателей приветствуются. Перед тем, как написать возмущенный комментарий, представьте на минуточку орду изголодавшихся фанатов MySQL на Хабре, жаждущих чьей-то крови :-).

Мотивация

Скорый обзор публикаций по ключевым словам в поисковике выявил ряд материалов — сборников сборной солянки, как мне показалось, в т.ч. ошибок и неточностей. Наиболее интересными по содержанию показались следующие материалы Рунета:

  • Изучаем переменные среды в Windows 10
  • Переменные среды Windows
  • Переменные среды Windows 10-11
  • не могу не отметить инструкцию для MySQL на английском с непереводимым, но очень метким названием «Put MySQL in PATH»
  • а также переведенную, очевидно, машинным способом, с китайского языка статью на портале «Русские блоги» с фотками китайской версии Windows — такая у нас международная любовь к MySQL; своей экзотики, видимо, уже не хватает :-).

Переменная среды́ ( англ. environment variable ) — текстовая переменная операционной системы, хранящая какую-либо информацию, например данные о настройках системы [ 1 ].

Каждый процесс имеет блок среды [окружения], который содержит набор переменных среды и их значений. Существует два типа переменных среды: переменные среды пользователя (устанавливаются для каждого пользователя) и переменные среды системы (устанавливаются для всех) [ 2 ].

Настраивать переменные среды возможно 3-мя способами

При манипуляциях с переменными среды «мануал» настоятельно рекомендует сделать резервные копии реестра и всех изменяемых файлов, т. к. в случае ошибки компьютер может тупо не загрузиться. А лучше скопировать весь ноутбук или что там у вас.

Ну, это они на своих ламеров пусть страх нагоняют! А у нас, у кулхацкеров, любой код с первого раза работает, как часики! :-Е)

Вариант 1 — через «Панель управления» -> «Система» -> «Дополнительные параметры системы» -> «Переменные среды» ( рис. 1 — 4 ) (через «Свойства» Моего-Твоего-Нашего-Вашего-Общего компьютера получается быстрее).

Рис. 1 Рис. 2  Рис. 3  Рис. 4

Здесь необходимо создать новую строковую запись с путем до папки «bin», например

, и перезагрузить компьютер.

 Рис. 5

Вариант 2 — с помощью редактора реестра. Так делают настоящие кулхацкеры :-). Win+R/»Выполнить» -> «regedit» -> «Enter»/»OK». Полный путь: HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Session Manager\Environment ( рис. 6 ).

 Рис. 6

Двойной клик на переменной «Path»-> дописать в конце строки, после знака «;» путь к папке «bin», например

( рис. 7 ), -> перезагрузить компьютер.

Рис. 7

Проверка результатов обновления путей в консоли командой ( рис. 8 ):

После ввода команды «mysqld» курсор начинает моргать, ошибки запуска исполняемого файла по некорректному пути отсутствуют. Это значит, что сервер успешно запущен и к нему можно подключиться из параллельного окна командной строки ( рис. 9 ).

Рис. 8Рис. 9

Вариант 3 — с помощью консольных команд «set» и «setx».

Команда «Setx» Создает или изменяет переменные среды в пользовательской или системной среде без необходимости программирования или написания скриптов, а также извлекает значения разделов реестра и записывает их в текстовые файлы [ 3 ].

Доп. материал по команде «set»:

Особенности команды «Setx» [ 4 ]:

  • переменные, созданные или отредактированные через «Setx»на локальном компьютере, будут доступны в будущих окнах командной строки, но не в текущем;
  • переменные, созданные или отредактированные через «Setx»на удалённом компьютере, будут доступны со следующего сеанса входа.

Команда «set» , которая является внутренней для интерпретатора команд (Cmd.exe), задает переменные среды пользователя только для текущего окна консоли [ 3 ].

Доп. материал по команде «SetX»:

  • SetX — создание или изменение переменных среды Windows
  • How do I add to the Windows PATH variable using setx? Having weird problems

Поскольку основная задача — прописать постоянный путь до исполняемых файлов, который распространялся бы на все процессы и сохранялся после перезагрузки компьютера, команда «set» не представляет большого интереса.

Проверка содержимого строкового параметра «Path» осуществляется с помощью команды

Рис. 10

Как видно на рис. 10 пути выводятся дважды. Очевидно, что представлено содержимое переменных «Path» для среды пользователя и для системного окружения. Проверка через Панель управления подтверждает вывод. Этот факт нужно учитывать при манипуляциях с переменной, особенно при перезаписи путей и удалении подстрок.

При текущем положении попытка запустить MySQL приведет к ошибке ( рис. 11 ).

Рис. 11

Путь до каталога bin устанавливается командой:

setx /m path «%path%;C:\mysql\bin;»

Если каталог «bin» находится в другой директории, запись команды должна соответствовать полному пути! Командная строка должна быть запущена от имени администратора! Иначе будет ошибка ( рис. 12 ).

Рис. 12

Запуск консоли от имени администратора характеризуется адресом «C:\Windows\system32». Успешное добавление пути к каталогу «bin» ( рис. 13 ) можно проверить, только запустив новое ( новый процесс ) окно консоли, чтобы обновить содержание переменных среды.

Рис. 13

Как следует из вывода нового процесса ( консоли ), вначале выводятся пути системного окружения, а затем — пользовательского.

Рис. 14

В этом же, новом окне, или в другом вновь запущенном окне командной строки можно запускать сервер MySQL, теперь указывая лишь имя исполняемого файла «mysqld» ( рис. 15 — 16 )

Рис. 15

Аналогичным образом теперь можно запускать и клиент ( рис. 16 ).

Рис. 16

Варианты с powershell — кому интересно. И на всякий случай — «мануал по powershell от производителя» — еле выпросил. Ну, что ни сделаешь ради любимых читателей :-). Примечательно, что обращение к командам setx в powershell идет через двойное двоеточие аналогично обращению к константам внутри класса, например в PHP:

Итоги и рекомендации

Манипулирование переменными среды ( окружения ), конечно, удобно, т. к. упрощает ввод команд запуска исполняемых файлов, но не во всех случаях. Запустить таким способом, к примеру, 2 или 3 процесса ( сервера ) MySQL одновременно и в одной той же операционной системе уже не получится. Придется запускать каждый процесс, полностью прописывая в командной строке путь до каждой отдельной папки «bin». Кроме того, если запускать MySQL в нескольких экземплярах, как службу, нужно иметь ввиду, что все службы должны иметь уникальные имена. Если же необходимость запускать несколько копий сервера одной и той же СУБД отсутствует, можно смело пользоваться переменными окружения. При частом использовании сервера проще установить MySQL, как службу, и поставить ее на автозапуск, чтобы сервер запускалсся при старте операционной системы. Эти варианты я постараюсь описать в следующих материалах.

Сопутствующие публикации

Источники

  1. Переменная среды
  2. Environment Variables
  3. windows-commands / setx
  4. Команда SETX: создание и редактирование переменных среды Windows

MySQL/Руководство для начинающих

Данный документ поможет пользователю в настройке и использовании MySQL.

  • 1 Начало работы с MySQL
    • 1.1 Введение
    • 1.2 Установка MySQL
    • 2.1 Создание Базы Данных
    • 3.1 Создание таблицы
    • 3.2 Заполнение Базы Данных MySQL
    • 3.3 Запросы к Таблицам MySQL
    • 4.1 Creating users
    • 4.2 Предоставление Привилегий Командой GRANT
    • 4.3 Проверка Пользовательских Разрешений
    • 4.4 Удаление Прав Доступа Пользователя Командой REVOKE
    • 4.5 Удаление Учетных Записей с Использованием DELETE
    • 4.6 Заключение

    Начало работы с MySQL

    Введение

    MySQL — это популярный сервер баз данных, используемый в разных приложениях. SQL означает язык структурированных запросов — (S)tructured (Q)uery (L)anguage, который MySQL использует для коммуникации с другими программами. Сверх того, MySQL имеет свои собственные расширенные функции SQL для того чтобы обеспечить пользователям дополнительный функционал. В этом документе мы рассмотрим как провести первоначальную установку MySQL, настроить базы данных и таблицы, и создать новых пользователей. Давайте начнем с установки.

    Установка MySQL

    Сначала убедитесь что MySQL установлен на вашу систему. В случае если вам требуется определенная функциональность MySQL, убедитесь, что установлены необходимые USE-флаги, так как они помогут в тонкой настройке инсталляции.

    root # emerge —ask mysql

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

    КОД Сообщение einfo MySQL

    You might want to run: "emerge --config =dev-db/mysql-[version]" if this is a new install.

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

    root # emerge —config =dev-db/mysql-[version]

    * MySQL DATADIR is /var/lib/mysql * Press ENTER to create the mysql database and set proper * permissions on it, or Control-C to abort now. Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, issue the following commands to start the server and change the applicable passwords: /etc/init.d/mysql start /usr/bin/mysqladmin -u root -h pegasos password 'new-password' /usr/bin/mysqladmin -u root password 'new-password' Depending on your configuration, a -p option may be needed in the last command. See the manual for more details.

    Заметка
    Если предыдущая команда не выполнится из-за того, что имя хоста установлено в localhost, измените его на другое имя, например gentoo. Обновите файл /etc/conf.d/hostname и перезапустите /etc/init.d/hostname .

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

    Важно
    Начиная с mysql-4.0.24-r2, пароли вводятся во время этапа конфигурации, что делает пароль root более надежным.

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

    Если вы используете OpenRC, выполните данную команду:

    root # /etc/init.d/mysql start

    * Re-caching dependency info (mtimes differ). * Starting mysqld (/etc/mysql/my.cnf) . [ ok ]

    Если вы используете systemd, вместо этого используйте следующую команду:

    root # systemctl restart mysqld.service
    root # systemctl restart mariadb.service

    После этого установите пароль root:

    root # /usr/bin/mysqladmin -u root -h localhost password ‘new-password’

    Теперь вы можете проверить, что пароль root был успешно настроен, попытавшись войти на MySQL-сервер:

    user $ mysql -u root -h localhost -p

    Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 to server version: 4.0.25 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>

    Параметр -u указывает пользователя, который будет выполнять вход. Параметр -h указывает хост. Обычно это будет localhost , если только вы не настраиваете удаленный сервер. И, наконец, -p сообщает клиенту mysql что вы будете вводить пароль для доступа к базе данных. Обратите внимание на приглашение mysql> . Это то место, где вы будете вводить все ваши команды. Теперь, когда мы в командной строке mysql в качестве пользователя root, мы можем начать настраивать нашу базу данных.

    Важно
    Установка mysql по умолчанию приемлема для систем разработки. Для более безопасных значений по умолчанию можно запустить /usr/bin/mysql_secure_installation

    Настройка Базы Данных

    Создание Базы Данных

    Мы вошли и приглашение mysql показано на экране. Сначала, давайте рассмотрим базы данных, которые у нас имеются в настоящий момент. Чтобы это сделать, мы используем команду SHOW DATABASES .

    mysql> SHOW DATABASES;

    +----------+ | Database | +----------+ | mysql | | test | +----------+ 2 rows in set (0.09 sec)

    Важно
    Пожалуйста, запомните что команды MySQL должны оканчиваться точкой с запятой — ;

    Вопреки тому факту, что тестовая база данных уже создана, мы собираемся создать нашу собственную. Базы данных создаются с использованием команды CREATE DATABASE . Мы создадим одну из них под названием gentoo.

    mysql> CREATE DATABASE gentoo;

    Query OK, 1 row affected (0.08 sec)

    Ответ позволяет нам узнать, что команда была выполнена без ошибок. В этом случае, одна строка была изменена. Это является отсылкой к основной базе данных mysql, которая содержит список всех баз данных. Вам не нужно сильно беспокоиться о второстепенных деталях. Последнее число является характеристикой того, насколько быстро был выполнен запрос. Мы можем проверить, что база данных была создана, запустив команду SHOW DATABASES снова.

    mysql> SHOW DATABASES;

    +----------+ | Database | +----------+ | gentoo | | mysql | | test | +----------+ 3 rows in set (0.00 sec)

    В самом деле, наша база данных была создана. Для того чтобы работать с созданием таблиц для нашей новой базы данных gentoo, нам надо выбрать ее в качестве текущей базы данных. Чтобы это сделать, мы используем команду USE . Команда USE принимает имя базы данных, которую вы хотите использовать в качестве текущей. Другой возможностью является ее установка в командной строке после параметра -D . Давайте продолжим и переключимся к базе данных gentoo.

    mysql> USE gentoo;

    Database changed

    Сейчас, текущей базой данных является созданная нами ранее база данных gentoo. Теперь, когда мы ей пользуемся, мы можем начать создавать таблицы и заполнять их информацией.

    Работа с таблицами в MySQL

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

    В структуру MySQL входят базы данных, таблицы, записи, и поля. Базы данных объединяют таблицы, таблицы объединяют записи, записи объединяют поля, которые содержат действительную информацию. Такая структура позволяет пользователям выбирать как они хотят обращаться к своей информации. На данный момент, мы разобрались с базами данных, теперь давайте поработаем с таблицами. Во-первых, таблицы могут быть перечислены, подобно базам данных, с использованием команды SHOW TABLES . Сейчас, в базе данных gentoo не имеется таблиц, как и показывает нам следующая команда:

    mysql> SHOW TABLES;

    Empty set (0.00 sec)

    Это означает нам надо создать какие-либо таблицы. Чтобы это сделать, мы используем команду CREATE TABLE . Однако, эта команда достаточно отличается от простой команды CREATE DATABASE тем что принимает список аргументов. Формат команды следует ниже:

    КОД Синтаксис CREATE TABLE

    CREATE TABLE [table_name] ([field_name] [field_data_type]([size])); 

    table_name — это имя таблицы, которую мы хотим создать. В данном случае, давайте создадим таблицу с именем developers . Эта таблица будет хранить имя разработчика, email адрес и его должность.

    field_name будет хранить имя поля. В этом случае мы имеем три требуемых имени: имя разработчика, email адрес, и должность.

    field_data_type — это то, какой тип информации будет сохранен. Различные доступные форматы могут быть найдены по адресу MySQL Column Types Page . Для наших целей, мы будем использовать тип данных VARCHAR для всех наших полей. VARCHAR — это один из простейших типов данных, когда дело касается работы со строками.

    size — это то, как много данных будет хранить одно поле. В нашем случае, мы будем использовать 128. Это означает, что поле будет иметь данные типа VARCHAR , которые занимают 128 байт. В настоящий момент, вы можете спокойно думать об этом как о 128 символах, хотя существует более техническое объяснение, которое вам предоставит сайт, указанный выше. Теперь, когда мы знаем как мы создадим таблицу, давайте это сделаем.

    mysql> CREATE TABLE developers ( name VARCHAR(128), email VARCHAR(128), job VARCHAR(128));

    Query OK, 0 rows affected (0.11 sec)

    Похоже, наша таблица была успешно создана. Давайте проверим это командой SHOW TABLES :

    mysql> SHOW TABLES;

    +------------------+ | Tables_in_gentoo | +------------------+ | developers | +------------------+ 1 row in set (0.00 sec)

    Да, наша таблица существует. Однако, она, по-видимому, не имеет никакой информации о типах полей, которые мы создали. Для этого, мы используем команду DESCRIBE (или, кратко, DESC ), которая принимает имя таблицы в качестве своих аргументов. Давайте посмотрим что она нам даст для нашей таблицы developers.

    mysql> DESCRIBE developers;

    +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | name | varchar(128) | YES | | NULL | | | email | varchar(128) | YES | | NULL | | | job | varchar(128) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)

    Показаны разные поля и их типы. Также показано несколько дополнительных атрибутов, которые находятся за рамками данного руководства. Пожалуйста, обратитесь за подробностями к Справочному Руководству MySQL . Теперь у нас есть таблица для работы с ней. Давайте продолжим и заполним ее.

    Заполнение Базы Данных MySQL

    Мы заполним таблицу (или добавим в нее данные), используя команду INSERT . Подобно команде CREATE TABLE , она также имеет особый формат:

    КОД Синтаксис INSERT

    INSERT INTO table (col1, col2, . ) VALUES('value1', 'value2', . ); 

    Эта команда используется для вставки записи в таблицу. table содержит таблицу MySQL, в которую мы хотим ввести информацию. Имя таблицы может сопровождаться списком столбцов для вставки данных, VALUES() хранит значения, которые вы хотите вставить в таблицу. Вы можете опустить список столбцов, если вы вставляете значение в каждый из них и если Вы пишете значения в том же порядке, в котором определены столбцы. В данном случае, мы хотим вставить данные в таблицу developers. Давайте добавим примеры записей:

    mysql> INSERT INTO developers VALUES(‘Joe Smith’, ‘joesmith@gentoo.org’, ‘toolchain’);

    Query OK, 1 row affected (0.06 sec) ## (Если вы не знаете порядок столбцов в таблице или хотите вставить неполную запись) mysql> INSERT INTO developers (job, name) VALUES('outsourced', 'Jane Doe'); Query OK, 1 row affected (0.01 sec)

    В соответствии с полученным результатом, кажется, запись была вставлена правильно. Что если мы хотим ввести больше, чем просто одну запись? Это тот случай, когда команда LOAD DATA вступает в действие. Она загружает записи из файла, разделенного символами табуляции. Давайте это попробуем, отредактировав файл в домашнем каталоге пользователя и добавив в него записи. Мы назовем этот файл records.txt . Здесь приведен пример:

    КОД ~/records.txt

    John Doe johndoe@gentoo.org portage Chris White chriswhite@gentoo.org documentation Sam Smith samsmith@gentoo.org amd64

    Важно
    Убедитесь, что вы знаете, с какими данными вы будете работать. Очень небезопасно использовать LOAD DATA , когда вы не уверены насчет содержимого файла!

    Команда LOAD DATA имеет в каком-то смысле пространное определение, но здесь мы используем ее самую простую форму.

    КОД Синтаксис LOAD DATA

    LOAD DATA LOCAL INFILE '/path/to/filename' INTO TABLE table; 

    /path/to/filename — это каталог и имя файла, которые будут использоваться. table — это имя нашей таблицы. В этом случае, наш файл — ~/records.txt , а имя таблицы — developers.

    mysql> LOAD DATA LOCAL INFILE ‘~/records.txt’ INTO TABLE developers;

    Query OK, 3 rows affected (0.00 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

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

    Сработало хорошо. Однако, это просто вставляет записи, и не дает вам какого-либо контроля над MySQL. Множество веб-приложений используют скрипты sql для того чтобы настроить MySQL быстро и легко. Если вы хотите использовать скрипты sql, вам нужно запустить mysql в пакетном режиме, или использовать файл в качестве источника команд. Здесь приведен пример запуска mysql в пакетном режиме:

    user $ mysql -u root -h localhost -p < sqlfile

    Как и в случае с LOAD DATA , убедитесь, что вы можете сказать что делает файл sqlfile . Невозможность этого может скомпрометировать вашу базу данных! Другим способом выполнения этого является использование команды source . Эта команда запустит команды из файла sql, находясь в интерактивном режиме mysql. Здесь показано как использовать sql file в качестве источника команд:

    mysql> source sqlfile;

    Если вы видите, что веб-приложение требует запуск sql файла, две команды выше могут быть использованы для выполнения данной задачи. Мы настроили нашу таблицу, как же нам проверить наши поля? Мы выполним это поиском в нашей таблице с помощью запросов.

    Запросы к Таблицам MySQL

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

    КОД Виды команды SELECT

    ## (Выбрать все записи в таблице) SELECT * FROM table; ## (Выбрать определенные записи в таблице) SELECT * FROM table WHERE field=value; ## (Выбрать определенные поля) SELECT field1,field2,field3 FROM table [WHERE field=value]; 

    Давайте-ка быстро рассмотрим первую форму команды. Она относительно проста и дает общее представление о вашей таблице. Мы продолжим и запустим ее, чтобы посмотреть какие данные у нас есть в настоящий момент.

    mysql> SELECT * FROM developers;

    +-------------+-----------------------+----------------+ | name | email | job | +-------------+-----------------------+----------------+ | Joe Smith | joesmith@gentoo.org | toolchain | | John Doe | johndoe@gentoo.org | portage | | Chris White | chriswhite@gentoo.org | documentation | | Sam Smith | samsmith@gentoo.org | amd64 | | Jane Doe | NULL | Outsourced job | +-------------+-----------------------+----------------+ 5 rows in set (0.00 sec)

    Мы видим как данные, которые мы добавили с INSERT , так и вставленные LOAD DATA . Теперь, давайте предположим что мы просто хотим посмотреть запись для Chris White. Мы можем сделать это с помощью второй формы команды select, как показано ниже.

    mysql> SELECT * FROM developers WHERE name = ‘Chris White’;

    +-------------+-----------------------+---------------+ | name | email | job | +-------------+-----------------------+---------------+ | Chris White | chriswhite@gentoo.org | documentation | +-------------+-----------------------+---------------+ 1 row in set (0.08 sec)

    Как предполагалось, выбрана отдельная запись, которую мы искали. Теперь, предположим, мы только хотели узнать должность и email адрес данной персоны, но не ее имя. Мы можем это выполнить с помощью третьей формы SELECT , как здесь и показано.

    mysql> SELECT email,job FROM developers WHERE name = ‘Chris White’;

    +-----------------------+---------------+ | email | job | +-----------------------+---------------+ | chriswhite@gentoo.org | documentation | +-----------------------+---------------+ 1 row in set (0.04 sec)

    Этот способ выбора намного легче в управлении, особенно с большими объемами информации, как мы увидим позже. А сейчас, будучи mysql пользователем root, мы обладаем неограниченными разрешениями делать с базой данных MySQL то, что мы захотим. В среде выполнения сервера, наличие пользователя с такими привилегиями может вызвать немало проблем. Для того, чтобы контролировать кто и что может делать с базами данных, мы установим привилегии.

    Привилегии MySQL

    Привилегии — это то, каким доступом обладают пользователи к базам данных, таблицам, почти ко всему. На данный момент в базе данных gentoo, учетная запись MySQL root — это единственная учетная запись, которая может получить к ней доступ, учитывая ее разрешения. Теперь, давайте создадим двух обычных пользователей, guest и admin, которые получат доступ к базе данных gentoo и будут работать с информацией, хранящейся в ней. Учетная запись guest будет ограниченной в правах. Все, что он сможет сделать, это получить информацию из базы данных, и только это. admin будет иметь те же самые права на управление, что и root, но только к базе данных gentoo (а не основным базам данных mysql). Перед тем как начать, давайте рассмотрим подробнее этот, в некотором смысле, упрощенный формат команды GRANT .

    Creating users

    The CREATE USER SQL statement will define users and set the authentication method, commonly by password but other plugins may be available.

    An example CREATE USER command is:

    КОД CREATE USER Syntax

    CREATE USER '[user]'@'[host]' IDENTIFIED BY '[password]'; 

    user is the name of the user and host is the hostname the user will be accessing from. In most cases, this will be localhost. To create our users for this example:

    mysql> CREATE USER ‘admin’@’localhost’ IDENTIFIED BY ‘password’;
    mysql> CREATE USER ‘guest’@’localhost’ IDENTIFIED BY ‘password’;

    Важно
    A host of ‘localhost’ does not mean DNS localhost (127.0.0.1) to MySQL. Instead, it refers to the UNIX socket connection and not TCP/IP.

    Предоставление Привилегий Командой GRANT

    Let’s have a closer look at this somewhat simplified format of the GRANT command.

    КОД Синтаксис команды GRANT

    GRANT [privileges] ON database.* TO '[user]'@'[host]' IDENTIFIED BY '[password]'; 

    Во-первых, мы имеем привилегии, которые мы хотим назначить. Исходя из того что мы выучили на данный момент, имеются несколько привилегий, которые вы можете установить:

    • ALL — Дает полный контроль базы данных со всеми привилегиями
    • CREATE — Позволяет пользователям создавать таблицы
    • SELECT — Позволяет пользователям делать запросы к таблицам
    • INSERT — Позволяет пользователям вставлять данные в таблицу
    • SHOW DATABASES — Позволяет пользователям просматривать список баз данных
    • USAGE — Пользователь не имеет привилегий
    • GRANT OPTION — Позволяет пользователям предоставлять привилегии

    Заметка
    Если вы запустили MySQL чтобы передавать данные веб-приложению, CREATE , SELECT , INSERT (обсуждалось здесь же), DELETE и UPDATE (для получения дальнейшей информации посмотрите раздел Справочного Руководства MySQL — Синтаксис GRANT и REVOKE) — это единственные разрешения, которые вам, скорее всего, понадобятся. Большинство делает ошибку, предоставляя все разрешения, когда в этом нет действительной необходимости. Сверьтесь с разработчиками приложения, чтобы посмотреть, не вызовут ли такие разрешения проблемы в общей работе.

    Для пользователя admin, подойдет уровень ALL. Для пользователя guest, SELECT будет достаточно для доступа только на чтение. database — это база данных, над которой пользователь, как мы того желаем, должен иметь эти разрешения. В этом примере, базой данных является gentoo. .* означает все таблицы. Если бы вы хотели, вы могли бы установить права доступа для каждой из таблиц. user — это имя пользователя, а host — имя хоста, с которого пользователь будет получать доступ. В большинстве случаев, это будет localhost. И наконец, password — это пароль пользователя. Учитывая эту информацию, давайте продолжим и создадим наших пользователей.

    mysql> GRANT ALL ON gentoo.* TO ‘admin’@’localhost’ IDENTIFIED BY ‘password’;
    mysql> GRANT SELECT ON gentoo.* TO ‘guest’@’localhost’ IDENTIFIED BY ‘password’;

    Теперь, когда мы настроили наших пользователей, давайте их протестируем. Сначала, мы завершим работу mysql, введя quit в командной строке:

    mysql> quit

    Теперь мы снова в консоли. Сейчас, когда пользователи созданы, давайте продолжим и посмотрим что они могут делать.

    Проверка Пользовательских Разрешений

    Сейчас мы должны попытаться войти в качестве пользователя guest. В настоящий момент, пользователь guest имеет только привилегии SELECT . Это, просто-напросто, сводится к способности поиска в базе данных, и ничему другому. Продолжайте и войдите как пользователь guest.

    user $ mysql -u guest -h localhost -p

    Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 to server version: 4.0.25 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>

    Теперь мы должны протестировать пользовательские ограничения. Давайте переключимся к базе данных gentoo:

    mysql> USE gentoo;

    Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed

    Давайте попробуем сделать что-нибудь, что мы, как предполагается, не должны. Мы попытаемся создать таблицу.

    mysql> CREATE TABLE test (test VARCHAR(20), foobar VARCHAR(2));

    ERROR 1044: Access denied for user: 'guest@localhost' to database 'gentoo'

    Как видите, эта функция не сработала, так как наш пользователь не имеет соответствующих прав доступа. Однако, одной из привилегий, которую мы предоставили, является выражение SELECT . Давайте попробуем:

    mysql> SELECT * FROM developers;

    +-------------+-----------------------+----------------+ | name | email | job | +-------------+-----------------------+----------------+ | Joe Smith | joesmith@gentoo.org | toolchain | | John Doe | johndoe@gentoo.org | portage | | Chris White | chriswhite@gentoo.org | documentation | | Sam Smith | samsmith@gentoo.org | amd64 | | Jane Doe | NULL | Outsourced job | +-------------+-----------------------+----------------+ 5 rows in set (0.00 sec)

    Команда завершилась успешно, и мы мельком увидели, что могут делать пользовательские разрешения. Мы, однако же, также создали и учетную запись admin. Она была создана для того чтобы показать, что даже пользователи, которым предоставлены все права, все же могут иметь ограничения. Завершите работу MySQL и войдите под учетной записью admin.

    mysql> quit
    user $ mysql -u admin -h localhost -p

    Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 to server version: 4.0.25 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>

    Для начала, мы попытаемся создать новую базу данных от учетной записи admin. Этот пользователь будет иметь права доступа схожие с учетной записью root в MySQL, и будет способен выполнить любые изменения выбранной базы данных gentoo. Это протестирует права доступа данного пользователя к главной базе данных MySQL. Вспомните, что ранее мы установили разрешения только для определенной базы данных.

    mysql> CREATE DATABASE gentoo2;

    ERROR 1044: Access denied for user: 'admin@localhost' to database 'gentoo2'

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

    mysql> USE gentoo;

    Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> INSERT INTO developers VALUES('Bob Simmons', 'bobsimmons@gentoo.org', 'python'); Query OK, 1 row affected (0.08 sec)

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

    Удаление Прав Доступа Пользователя Командой REVOKE

    Команда REVOKE позволяет нам запретить доступ пользователю. Мы можем запретить или все права на доступ, или определенные права. В действительности, формат весьма схож с командой GRANT .

    КОД Синтаксис REVOKE

    REVOKE [privileges] ON database.* FROM '[user]'@'[host]'; 

    Здесь, параметры объясняются в разделе команды GRANT . Однако же, в этом разделе, мы собираемся запретить пользователю доступ полностью. Давайте предположим, мы обнаружили, что учетная запись guest вызывает некоторые проблемы с безопасностью. Мы решаем отозвать все привилегии. Мы заходим под учетной записью root и делаем то, что необходимо.

    mysql> REVOKE ALL ON gentoo.* FROM ‘guest’@’localhost’;

    Query OK, 0 rows affected (0.00 sec)

    Заметка
    В данном случае, права доступа пользователя достаточно просты, поэтому их удаление из базы данных не является проблемой. Однако, в более общем случае, вы, скорее всего, использовали бы *.* вместо gentoo.* для того чтобы удалить права доступа пользователя ко всем другим базам данных.

    Давайте теперь выйдем и попытаемся зайти в качестве пользователя guest.

    user $ mysql -u guest -h localhost -p

    Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 to server version: 4.0.25 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>

    Хотя мы и были способны выполнить вход, наши права доступа к gentoo теперь исчезли.

    mysql> USE gentoo;

    ERROR 1044: Access denied for user: 'guest@localhost' to database 'gentoo'

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

    Удаление Учетных Записей с Использованием DELETE

    Таблица пользователей MySQL является перечислением всех пользователей и информации о них. Убедитесь, что вы выполнили вход под root. Затем продолжайте и используйте основную базу данных MySQL.

    DROP USER will delete the record in the user table and all privilege tables. Let’s go ahead and do that:

    mysql> DROP USER ‘guest’@’localhost’;

    Query OK, 1 row affected (0.07 sec)

    Кажется, сработало на отлично. Давайте это протестируем, выйдя и попытаясь войти как пользователь guest.

    mysql> quit
    user $ mysql -u guest -h localhost -p

    Enter password: ERROR 1045: Access denied for user: 'guest@localhost' (Using password: YES)

    Наш пользователь успешно удален!

    Заключение

    В то время как это руководство сфокусировано, в основном, на настройке MySQL из командной строки, доступно несколько альтернатив с графическим интерфейсом:

    • phpMyAdmin — Популярный инструмент администрирования MySQL, основанный на php.
    • mysqlnavigator — Интерфейс QT к MySQL.
    • gmyclient — MySQL клиент, основанный на GNOME.
    • knoda — клиент MySQL для KDE.

    Это завершает вводное руководство к MySQL. Я надеюсь это дало вам лучшее понимание основ MySQL и настройки базы данных.

    This page is based on a document formerly found on our main website gentoo.org.
    The following people contributed to the original document: Chris White, Shyam Mani, Xavier Neys
    They are listed here because wiki history does not allow for any external attribution. If you edit the wiki article, please do not add yourself here; your contributions are recorded on each article’s associated history page.

    2. Подключение к базам данных

    Прежде чем взаимодействовать с любой базой данных через SQL-библиотеку, с ней необходимо связаться. В этом разделе мы рассмотрим, как подключиться из приложения Python к базам данных SQLite , MySQL и PostgreSQL. Рекомендуем сделать собственный .py файл для каждой из трёх баз данных.

    Примечание. Для выполнения разделов о MySQL и PostgreSQL необходимо самостоятельно запустить соответствующие серверы. Для быстрого ознакомления с тем, как запустить сервер MySQL, ознакомьтесь с разделом MySQL в публикации Запуск проекта Django (англ.). Чтобы узнать, как создать базу данных в PostgreSQL, перейдите к разделу Setting Up a Database в публикации Предотвращение атак SQL-инъекций с помощью Python (англ.).

    SQLite

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

    Более того, SQLite база данных не требует сервера и самодостаточна, то есть просто читает и записывает данные в файл. Подключимся с помощью sqlite3 к базе данных:

    import sqlite3 from sqlite3 import Error def create_connection(path): connection = None try: connection = sqlite3.connect(path) print("Connection to SQLite DB successful") except Error as e: print(f"The error '' occurred") return connection 

    Вот как работает этот код:

    • Строки 1 и 2 – импорт sqlite3 и класса Error .
    • Строка 4 определяет функцию create_connection() , которая принимает путь к базе данных SQLite.
    • Строка 7 использует метод connect() и принимает в качестве параметра путь к базе данных SQLite. Если база данных в указанном месте существует, будет установлено соединение. В противном случае по указанному пути будет создана новая база данных и так же установлено соединение.
    • В строке 8 выводится состояние успешного подключения к базе данных.
    • Строка 9 перехватывает любое исключение, которое может быть получено, если методу .connect() не удастся установить соединение.
    • В строке 10 отображается сообщение об ошибке в консоли.

    sqlite3.connect(path) возвращает объект connection . Этот объект может использоваться для выполнения запросов к базе данных SQLite. Следующий скрипт формирует соединение с базой данных SQLite:

    connection = create_connection("E:\\sm_app.sqlite") 

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

    MySQL

    В отличие от SQLite, в Python по умолчанию нет модуля, который можно использовать для подключения к базе данных MySQL. Для этого вам нужно установить драйвер Python для MySQL. Одним из таких драйверов является mysql-connector-python . Вы можете скачать этот модуль Python SQL с помощью pip:

    pip install mysql-connector-python 

    Обратите внимание, что MySQL – это серверная система управления базами данных. Один сервер MySQL может хранить несколько баз данных. В отличие от SQLite, где соединение равносильно порождению БД, формирование базы данных MySQL состоит из двух этапов:

    1. Установка соединения с сервером MySQL.
    2. Выполнение запроса для создания БД.

    Определим функцию, которая будет подключаться к серверу MySQL и возвращать объект подключения:

    import mysql.connector from mysql.connector import Error def create_connection(host_name, user_name, user_password): connection = None try: connection = mysql.connector.connect( host=host_name, user=user_name, passwd=user_password ) print("Connection to MySQL DB successful") except Error as e: print(f"The error '' occurred") return connection connection = create_connection("localhost", "root", "") 

    В приведенном выше коде мы определили новую функцию create_connection() , которая принимает три параметра:

    1. host_name
    2. user_name
    3. user_password

    Модуль mysql.connector определяет метод connect() , используемый в седьмой строке для подключения к серверу MySQL. Как только соединение установлено, объект connection возвращается вызывающей функции. В последней строке функция create_connection() вызывается с именем хоста, именем пользователя и паролем.

    Пока мы только установили соединение. Самой базы ещё нет. Для этого мы определим другую функцию – create_database() , которая принимает два параметра:

    1. Объект connection ;
    2. query – строковый запрос о создании базу данных.

    Вот как выглядит эта функция:

    def create_database(connection, query): cursor = connection.cursor() try: cursor.execute(query) print("Database created successfully") except Error as e: print(f"The error '' occurred") 

    Для выполнения запросов используется объект cursor .

    Создадим базу данных sm_app для нашего приложения на сервере MySQL:

    create_database_query = "CREATE DATABASE sm_app" create_database(connection, create_database_query) 

    Теперь у нас есть база данных на сервере. Однако объект connection , возвращаемый функцией create_connection() подключен к серверу MySQL. А нам необходимо подключиться к базе данных sm_app . Для этого нужно изменить create_connection() следующим образом:

    def create_connection(host_name, user_name, user_password, db_name): connection = None try: connection = mysql.connector.connect( host=host_name, user=user_name, passwd=user_password, database=db_name ) print("Connection to MySQL DB successful") except Error as e: print(f"The error '' occurred") return connection 

    Функция create_connection() теперь принимает дополнительный параметр с именем db_name . Этот параметр указывает имя БД, к которой мы хотим подключиться. Имя теперь можно передать при вызове функции:

    connection = create_connection("localhost", "root", "", "sm_app") 

    Скрипт успешно вызывает create_connection() и подключается к базе данных sm_app .

    PostgreSQL

    Как и в случае MySQL, для PostgreSQL в стандартной библиотеке Python нет модуля для взаимодействия с базой данных. Но и для этой задачи есть решение – модуль psycopg2 :

    pip install psycopg2 

    Определим функцию create_connection() для подключения к базе данных PostgreSQL:

    from psycopg2 import OperationalError def create_connection(db_name, db_user, db_password, db_host, db_port): connection = None try: connection = psycopg2.connect( database=db_name, user=db_user, password=db_password, host=db_host, port=db_port, ) print("Connection to PostgreSQL DB successful") except OperationalError as e: print(f"The error '' occurred") return connection 

    Подключение осуществляется через интерфейс psycopg2.connect() . Далее используем написанную нами функцию:

    connection = create_connection( "postgres", "postgres", "abc123", "127.0.0.1", "5432" ) 

    Теперь внутри дефолтной БД postgres нужно создать базу данных sm_app . Ниже определена соответствующая функция create_database() :

    def create_database(connection, query): connection.autocommit = True cursor = connection.cursor() try: cursor.execute(query) print("Query executed successfully") except OperationalError as e: print(f"The error '' occurred") create_database_query = "CREATE DATABASE sm_app" create_database(connection, create_database_query) 

    Запустив вышеприведенный скрипт, мы увидим базу данных sm_app на своем сервере PostgreSQL. Подключимся к ней:

    connection = create_connection( "sm_app", "postgres", "abc123", "127.0.0.1", "5432" ) 

    Здесь 127.0.0.1 и 5432 это соответственно IP-адресу и порт хоста сервера.

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

    В предыдущем разделе мы увидели, как подключаться к серверам баз данных SQLite, MySQL и PostgreSQL, используя разные библиотеки Python. Мы создали базу данных sm_app на всех трех серверах БД. В данном разделе мы рассмотрим, как формировать таблицы внутри этих трех баз данных.

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

    SQLite

    Для выполнения запросов в SQLite используется метод cursor.execute() . В этом разделе мы определим функцию execute_query() , которая использует этот метод. Функция будет принимать объект connection и строку запроса. Далее строка запроса будет передаваться методу execute( ) . В этом разделе он будет использоваться для формирования таблиц, а в следующих – мы применим его для выполнения запросов на обновление и удаление.

    Примечание. Описываемый далее скрипт – часть того же файла, в котором мы описали соединение с базой данных SQLite.

    Итак, начнем с определения функции execute_query() :

    def execute_query(connection, query): cursor = connection.cursor() try: cursor.execute(query) connection.commit() print("Query executed successfully") except Error as e: print(f"The error '' occurred") 

    Теперь напишем передаваемый запрос ( query ):

    create_users_table = """ CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER, gender TEXT, nationality TEXT ); """ 

    В запросе говорится, что нужно создать таблицу users со следующими пятью столбцами:

    Наконец, чтобы появилась таблица, вызываем execute_query() . Передаём объект connection , который мы описали в предыдущем разделе, вместе с только что подготовленной строкой запроса create_users_table :

    execute_query(connection, create_users_table) 

    Следующий запрос используется для создания таблицы posts:

    create_posts_table = """ CREATE TABLE IF NOT EXISTS posts( id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, description TEXT NOT NULL, user_id INTEGER NOT NULL, FOREIGN KEY (user_id) REFERENCES users (id) ); """ 

    Поскольку между users и posts имеет место отношение один-ко-многим, в таблице появляется ключ user_id , который ссылается на столбец id в таблице users . Выполняем следующий скрипт для построения таблицы posts :

    execute_query(connection, create_posts_table) 

    Наконец, формируем следующим скриптом таблицы comments и likes :

    create_comments_table = """ CREATE TABLE IF NOT EXISTS comments ( id INTEGER PRIMARY KEY AUTOINCREMENT, text TEXT NOT NULL, user_id INTEGER NOT NULL, post_id INTEGER NOT NULL, FOREIGN KEY (user_id) REFERENCES users (id) FOREIGN KEY (post_id) REFERENCES posts (id) ); """ create_likes_table = """ CREATE TABLE IF NOT EXISTS likes ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER NOT NULL, post_id integer NOT NULL, FOREIGN KEY (user_id) REFERENCES users (id) FOREIGN KEY (post_id) REFERENCES posts (id) ); """ execute_query(connection, create_comments_table) execute_query(connection, create_likes_table) 

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

    MySQL

    Так же, как с SQLite, чтобы создать таблицу в MySQL, нужно передать запрос в cursor.execute() . Создадим новый вариант функции execute_query() :

    def execute_query(connection, query): cursor = connection.cursor() try: cursor.execute(query) connection.commit() print("Query executed successfully") except Error as e: print(f"The error '' occurred") 

    Описываем таблицу users :

    create_users_table = """ CREATE TABLE IF NOT EXISTS users ( id INT AUTO_INCREMENT, name TEXT NOT NULL, age INT, gender TEXT, nationality TEXT, PRIMARY KEY (id) ) ENGINE = InnoDB """ execute_query(connection, create_users_table) 

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

    Следующий скрипт составит таблицу posts , содержащую внешний ключ user_id , который ссылается на id столбца таблицы users :

    create_posts_table = """ CREATE TABLE IF NOT EXISTS posts ( id INT AUTO_INCREMENT, title TEXT NOT NULL, description TEXT NOT NULL, user_id INTEGER NOT NULL, FOREIGN KEY fk_user_id (user_id) REFERENCES users(id), PRIMARY KEY (id) ) ENGINE = InnoDB """ execute_query(connection, create_posts_table) 

    Аналогично для создания таблиц comments и likes , передаём соответствующие CREATE -запросы функции execute_query() .

    PostgreSQL

    Применение библиотеки psycopg2 в execute_query() также подразумевает работу с cursor :

    def execute_query(connection, query): connection.autocommit = True cursor = connection.cursor() try: cursor.execute(query) print("Query executed successfully") except OperationalError as e: print(f"The error '' occurred") 

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

    Создадим внутри базы данных sm_app таблицу users :

    create_users_table = """ CREATE TABLE IF NOT EXISTS users ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, age INTEGER, gender TEXT, nationality TEXT ) """ execute_query(connection, create_users_table) 

    Запрос на создание таблицы users в PostgreSQL немного отличается от SQLite и MySQL. Здесь для указания столбцов с автоматическим инкрементом используется ключевое слово SERIAL . Кроме того, отличается способ указания ссылок на внешние ключи:

    create_posts_table = """ CREATE TABLE IF NOT EXISTS posts ( id SERIAL PRIMARY KEY, title TEXT NOT NULL, description TEXT NOT NULL, user_id INTEGER REFERENCES users(id) ) """ execute_query(connection, create_posts_table) 

    4. Вставка записей

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

    SQLite

    Чтобы вставить записи в базу данных SQLite, мы можем использовать ту же execute_query() функцию, что и для создания таблиц. Для этого сначала нужно сохранить в виде строки запрос INSERT INTO . Затем нужно передать объект connection и строковый запрос в execute_query() . Вставим для примера пять записей в таблицу users :

    create_users = """ INSERT INTO users (name, age, gender, nationality) VALUES ('James', 25, 'male', 'USA'), ('Leila', 32, 'female', 'France'), ('Brigitte', 35, 'female', 'England'), ('Mike', 40, 'male', 'Denmark'), ('Elizabeth', 21, 'female', 'Canada'); """ execute_query(connection, create_users) 

    Поскольку мы установили автоинкремент для столбца id , нам не нужно указывать его дополнительно. Таблица users будет автоматически заполнена пятью записями со значениями id от 1 до 5.

    Вставим в таблицу posts шесть записей:

    create_posts = """ INSERT INTO posts (title, description, user_id) VALUES ("Happy", "I am feeling very happy today", 1), ("Hot Weather", "The weather is very hot today", 2), ("Help", "I need some help with my work", 2), ("Great News", "I am getting married", 1), ("Interesting Game", "It was a fantastic game of tennis", 5), ("Party", "Anyone up for a late-night party today?", 3); """ execute_query(connection, create_posts) 

    Важно отметить, что столбец user_id таблицы posts является внешним ключом, который ссылается на столбец таблицы users . Это означает, что столбец user_id должен содержать значение, которое уже существует в столбце id таблицы users . Если его не существует, мы получим сообщение об ошибке.

    Следующий скрипт вставляет записи в таблицы comments и likes :

    create_comments = """ INSERT INTO comments (text, user_id, post_id) VALUES ('Count me in', 1, 6), ('What sort of help?', 5, 3), ('Congrats buddy', 2, 4), ('I was rooting for Nadal though', 4, 5), ('Help with your thesis?', 2, 3), ('Many congratulations', 5, 4); """ create_likes = """ INSERT INTO likes (user_id, post_id) VALUES (1, 6), (2, 3), (1, 5), (5, 4), (2, 4), (4, 2), (3, 6); """ execute_query(connection, create_comments) execute_query(connection, create_likes) 

    MySQL

    Есть два способа вставить записи в базы данных MySQL из приложения Python. Первый подход похож на SQLite. Можно сохранить запрос INSERT INTO в строке, а затем использовать для вставки записей cursor.execute() .

    Ранее мы определили функцию-оболочку execute_query() , которую использовали для вставки записей. Мы можем использовать ту же функцию:

    create_users = """ INSERT INTO `users` (`name`, `age`, `gender`, `nationality`) VALUES ('James', 25, 'male', 'USA'), ('Leila', 32, 'female', 'France'), ('Brigitte', 35, 'female', 'England'), ('Mike', 40, 'male', 'Denmark'), ('Elizabeth', 21, 'female', 'Canada'); """ execute_query(connection, create_users) 

    Второй подход использует метод cursor.executemany() , который принимает два параметра:

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

    Посмотрите на следующий пример, который вставляет две записи в таблицу likes :

    sql = "INSERT INTO likes ( user_id, post_id ) VALUES ( %s, %s )" val = [(4, 5), (3, 4)] cursor = connection.cursor() cursor.executemany(sql, val) connection.commit() 

    Какой подход выбрать – зависит от вас. Если вы не очень хорошо знакомы с SQL, проще использовать метод курсора executemany() .

    PostgreSQL

    В предыдущем подразделе мы познакомились с двумя подходами для вставки записей в таблицы баз данных MySQL. В psycopg2 используется второй подход: мы передаем SQL-запрос с заполнителями и списком записей методу execute() . Каждая запись в списке должна являться кортежем, значения которого соответствуют значениям столбца в таблице БД. Вот как мы можем вставить пользовательские записи в таблицу users :

    users = [ ("James", 25, "male", "USA"), ("Leila", 32, "female", "France"), ("Brigitte", 35, "female", "England"), ("Mike", 40, "male", "Denmark"), ("Elizabeth", 21, "female", "Canada"), ] user_records = ", ".join(["%s"] * len(users)) insert_query = ( f"INSERT INTO users (name, age, gender, nationality) VALUES " ) connection.autocommit = True cursor = connection.cursor() cursor.execute(insert_query, users) 

    Список users содержит пять пользовательских записей в виде кортежей. Затем мы создаём строку с пятью элементами-заполнителями ( %s ), соответствующими пяти пользовательским записям. Строка-заполнитель объединяется с запросом, который вставляет записи в таблицу users . Наконец, строка запроса и пользовательские записи передаются в метод execute() .

    Следующий скрипт вставляет записи в таблицу posts :

    posts = [ ("Happy", "I am feeling very happy today", 1), ("Hot Weather", "The weather is very hot today", 2), ("Help", "I need some help with my work", 2), ("Great News", "I am getting married", 1), ("Interesting Game", "It was a fantastic game of tennis", 5), ("Party", "Anyone up for a late-night party today?", 3), ] post_records = ", ".join(["%s"] * len(posts)) insert_query = ( f"INSERT INTO posts (title, description, user_id) VALUES " ) connection.autocommit = True cursor = connection.cursor() cursor.execute(insert_query, posts) 

    По той же методике можно вставить записи в таблицы comments и likes .

    5. Извлечение данных из записей

    SQLite

    Чтобы выбрать записи в SQLite, можно снова использовать cursor.execute() . Однако после этого потребуется вызвать метод курсора fetchall() . Этот метод возвращает список кортежей, где каждый кортеж сопоставлен с соответствующей строкой в ​​извлеченных записях. Чтобы упростить процесс, напишем функцию execute_read_query() :

    def execute_read_query(connection, query): cursor = connection.cursor() result = None try: cursor.execute(query) result = cursor.fetchall() return result except Error as e: print(f"The error '' occurred") 

    Эта функция принимает объект connection и SELECT -запрос, а возвращает выбранную запись.

    SELECT

    Давайте выберем все записи из таблицы users :

    select_users = "SELECT * from users" users = execute_read_query(connection, select_users) for user in users: print(user) 

    В приведенном выше скрипте запрос SELECT забирает всех пользователей из таблицы users . Результат передается в написанную нами функцию execute_read_query() , возвращающую все записи из таблицы users .

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

    Результат вышеприведенного запроса выглядит следующим образом:

    (1, 'James', 25, 'male', 'USA') (2, 'Leila', 32, 'female', 'France') (3, 'Brigitte', 35, 'female', 'England') (4, 'Mike', 40, 'male', 'Denmark') (5, 'Elizabeth', 21, 'female', 'Canada') 

    Таким же образом вы можете извлечь все записи из таблицы posts :

    select_posts = "SELECT * FROM posts" posts = execute_read_query(connection, select_posts) for post in posts: print(post) 

    Вывод выглядит так:

    (1, 'Happy', 'I am feeling very happy today', 1) (2, 'Hot Weather', 'The weather is very hot today', 2) (3, 'Help', 'I need some help with my work', 2) (4, 'Great News', 'I am getting married', 1) (5, 'Interesting Game', 'It was a fantastic game of tennis', 5) (6, 'Party', 'Anyone up for a late-night party today?', 3) 

    JOIN

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

    select_users_posts = """ SELECT users.id, users.name, posts.description FROM posts INNER JOIN users ON users.id = posts.user_id """ users_posts = execute_read_query(connection, select_users_posts) for users_post in users_posts: print(users_post) 
    (1, 'James', 'I am feeling very happy today') (2, 'Leila', 'The weather is very hot today') (2, 'Leila', 'I need some help with my work') (1, 'James', 'I am getting married') (5, 'Elizabeth', 'It was a fantastic game of tennis') (3, 'Brigitte', 'Anyone up for a late night party today?') 

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

    select_posts_comments_users = """ SELECT posts.description as post, text as comment, name FROM posts INNER JOIN comments ON posts.id = comments.post_id INNER JOIN users ON users.id = comments.user_id """ posts_comments_users = execute_read_query( connection, select_posts_comments_users ) for posts_comments_user in posts_comments_users: print(posts_comments_user) 

    Вывод выглядит так:

    ('Anyone up for a late night party today?', 'Count me in', 'James') ('I need some help with my work', 'What sort of help?', 'Elizabeth') ('I am getting married', 'Congrats buddy', 'Leila') ('It was a fantastic game of tennis', 'I was rooting for Nadal though', 'Mike') ('I need some help with my work', 'Help with your thesis?', 'Leila') ('I am getting married', 'Many congratulations', 'Elizabeth') 

    Из вывода понятно, что имена столбцов не были возвращены методом fetchall() . Чтобы вернуть имена столбцов, нужно забрать атрибут description объекта cursor . Например, следующий список возвращает все имена столбцов для вышеуказанного запроса:

    cursor = connection.cursor() cursor.execute(select_posts_comments_users) cursor.fetchall() column_names = [description[0] for description in cursor.description] print(column_names) 

    Вывод выглядит так:

    ['post', 'comment', 'name'] 

    WHERE

    Теперь мы выполним SELECT -запрос, который возвращает текст поста и общее количество лайков, им полученных:

    select_post_likes = """ SELECT description as Post, COUNT(likes.id) as Likes FROM likes, posts WHERE posts.id = likes.post_id GROUP BY likes.post_id """ post_likes = execute_read_query(connection, select_post_likes) for post_like in post_likes: print(post_like) 
    ('The weather is very hot today', 1) ('I need some help with my work', 1) ('I am getting married', 2) ('It was a fantastic game of tennis', 1) ('Anyone up for a late night party today?', 2) 

    То есть используя запрос WHERE , вы можете возвращать более конкретные результаты.

    MySQL

    Процесс выбора записей в MySQL абсолютно идентичен процессу выбора записей в SQLite:

    def execute_read_query(connection, query): cursor = connection.cursor() result = None try: cursor.execute(query) result = cursor.fetchall() return result except Error as e: print(f"The error '' occurred") 

    Теперь выберем все записи из таблицы users :

    select_users = "SELECT * FROM users" users = execute_read_query(connection, select_users) for user in users: print(user) 

    Вывод будет похож на то, что мы видели с SQLite.

    PostgreSQL

    Процесс выбора записей из таблицы PostgreSQL с помощью модуля psycopg2 тоже похож на SQLite и MySQL. Снова используем cursor.execute() , затем метод fetchall() для выбора записей из таблицы. Следующий скрипт выбирает все записи из таблицы users :

    def execute_read_query(connection, query): cursor = connection.cursor() result = None try: cursor.execute(query) result = cursor.fetchall() return result except OperationalError as e: print(f"The error '' occurred") select_users = "SELECT * FROM users" users = execute_read_query(connection, select_users) for user in users: print(user) 

    Опять же, результат будет похож на то, что мы видели раньше.

    6. Обновление записей таблицы

    SQLite

    Обновление записей в SQLite выглядит довольно просто. Снова можно применить execute_query() . В качестве примера обновим текст поста с id равным 2. Сначала создадим описание для SELECT :

    select_post_description = "SELECT description FROM posts WHERE = execute_read_query(connection, select_post_description) for description in post_description: print(description) 

    Увидим следующий вывод:

    ('The weather is very hot today',) 

    Следующий скрипт обновит описание:

    update_post_description = """ UPDATE posts SET description = "The weather has become pleasant now" WHERE update_post_description) 

    Теперь, если мы выполним SELECT -запрос еще раз, увидим следующий результат:

    ('The weather has become pleasant now',) 

    То есть запись была обновлена.

    MySQL

    Процесс обновления записей в MySQL с помощью модуля mysql-connector-python является точной копией модуля sqlite3 :

    update_post_description = """ UPDATE posts SET description = "The weather has become pleasant now" WHERE update_post_description) 

    PostgreSQL

    Запрос на обновление PostgreSQL аналогичен SQLite и MySQL.

    7. Удаление записей таблицы

    SQLite

    В качестве примера удалим комментарий с id равным 5:

    delete_comment = "DELETE FROM comments WHERE delete_comment) 

    Теперь, если мы извлечем все записи из таблицы comments , то увидим, что пятый комментарий был удален. Процесс удаления в MySQL и PostgreSQL идентичен SQLite:

    Заключение

    В этом руководстве мы разобрались, как применять три распространенные библиотеки Python для работы с реляционными базами данных. Научившись работать с одним из модулей sqlite3 , mysql-connector-python и psycopg2 , вы легко сможете перенести свои знания на другие модули и оперировать любой из баз данных SQLite, MySQL и PostgreSQL.

    Однако это лишь вершина айсберга! Существуют также библиотеки для работы с SQL и объектно-реляционными отображениями, такие как SQLAlchemy и Django ORM, которые автоматизируют задачи взаимодействия Python с базами данных.

    Если вам интересна тематика работы с базами данных с помощью Python, напишите об этом в комментариях – мы подготовим дополнительные материалы.

    Больше полезной информации вы найдете на наших телеграм-каналах «Библиотека питониста» и «Библиотека data scientist’а».

    Практическое руководство. Создание и развертывание в локальной базе данных

    SQL Server предоставляет локальный экземпляр сервера по запросу с именем SQL Server Express Local Database Runtime (LocalDB), который активируется при отладке проекта базы данных SQL Server. Этот экземпляр локального сервера можно использовать в качестве песочницы для создания, тестирования и отладки проекта.

    Он не зависит от любого установленного экземпляра SQL Server и недоступен за пределами SQL Server Data Tools (SSDT). Такое соглашение идеально подходит для разработчиков с ограниченным доступом к рабочим базам данных, но хотелось бы протестировать свои проекты локально, прежде чем авторизованный персонал развертывает их в рабочей среде. Кроме того, при разработке решения базы данных для SQL Azure вы можете использовать удобство, предоставленное этим локальным сервером для разработки и тестирования проекта базы данных локально, прежде чем развертывать его в облаке.

    Ограничения

    База данных под локальным узлом базы данных в SQL Server обозреватель объектов является отражением соответствующего проекта базы данных и не связана с той же базой данных в экземпляре подключенного сервера.

    В следующих процедурах используются сущности, созданные с помощью процедур, которые описывались ранее в разделах Connected Database Development (Разработка подключенной базы данных) и Project-Oriented Offline Database Development (Разработка базы данных вне сети с учетом проекта).

    Использование локальной базы данных

    1. В обозреватель объектов SQL Server под узлом SQL Server появится новый узел с именем Local. Это локальный экземпляр базы данных.
    2. Разверните узлы Локально и Базы данных. Обратите внимание на внешний вид базы данных с тем же именем, что у проекта TradeDev. Разверните узлы в этой базе данных. В окне Операции инструментальных средств для обработки данных отображается состояние операций расширения или импорта, выполняющихся в любой базе данных узла Локальные. Они не содержат таблиц и сущностей, созданных в предыдущих процедурах.
    3. Нажмите клавишу F5 для отладки проекта базы данных TradeDev. По умолчанию SSDT использует экземпляр локального сервера базы данных для отладки проектов базы данных. В этом случае SSDT сначала пытается создать проект, и если нет ошибок, проект (и его сущности) развертываются в локальной базе данных. При отладке того же проекта ssdT обнаруживает изменения с момента последнего сеанса отладки и развертывает только эти изменения в локальной базе данных.
    4. Снова разверните узлы TradeDev в локальном сервере базы данных. На этот раз обратите внимание, что таблицы, представления и функции были развернуты на локальном сервере базы данных.
    5. Щелкните узел правой TradeDev кнопкой мыши и выберите новый запрос.
    6. В области скриптов вставьте этот код и нажмите кнопку «Выполнить запрос «, чтобы запустить запрос.
    SELECT * FROM dbo.GetProductsBySupplier(1); 

    Репликация реальных данных в локальную базу данных

    1. В обозреватель объектов SQL Server разверните подключенный TradeDev экземпляр SQL Server и найдите базу данных. Щелкните правой кнопкой мыши таблицу Suppliers и выберите «Просмотреть данные«.
    2. Нажмите кнопку «Скрипт» (вторая кнопка справа) в верхней части редактора данных. Скопируйте инструкции INSERT из скрипта.
    3. Разверните экземпляр локального сервера и щелкните узел правой TradeDev кнопкой мыши и выберите новый запрос.
    4. Вставьте инструкции INSERT в окно запроса и запустите запрос.
    5. Повторите описанные выше действия, чтобы реплика te данные из Products и Fruits таблиц в подключенной TradeDev базе данных к локальной TradeDev базе данных.
    6. Щелкните правой кнопкой мыши экземпляр сервера Local и выберите Обновить. Просмотрите таблицы с помощью данных представления, чтобы убедиться, что локальная база данных заполнена.
    7. Щелкните правой кнопкой мыши TradeDev узел экземпляра локального сервера и выберите новый запрос.
    8. В области скриптов вставьте этот код и нажмите кнопку «Выполнить запрос «, чтобы запустить запрос.
    SELECT * FROM dbo.GetProductsBySupplier(1); 

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *