PGCLI: Postgres из терминала

Введение

Вы когда-нибудь пользовались базой данных Postgres и знаете ли вы, что вам не нужно запускать PGAdmin каждый раз, когда вы хотите написать SQL-запросы, вы можете записывать эти запросы даже в терминале с помощью пакета python. Это PGCLI, который может выступать в качестве окна редактора для написания SQL-запросов к базам данных Postgres. Мы можем просто выполнять SQL-запросы в PSQL-подобной оболочке с дополнительными возможностями, такими как автозавершение, режимы редактирования текста, опции ввода/вывода файлов и т.д.

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

PGCLI

PGCLi — это пакет python, который действует как CLI для выполнения SQL-запросов в базе данных Postgres. PGCLI позволяет нам взаимодействовать с любой базой данных Postgres через командную строку, это может быть удаленная или локальная база данных, вы можете получить доступ к ней прямо из терминала. Пакет действует как CLI для доступа и выполнения любых SQL-запросов, поэтому мы можем просто предоставить опции/параметры для подключения базы данных и доступа к схеме и связанным с ней данным.

Установка

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

pipx install pgcli

Нам даже не нужно устанавливать его при использовании pipx, так как он каждый раз будет создавать свежий экземпляр pgcli в виртуальной среде.

Теперь мы можем запустить pgcli с параметрами с помощью команды pipx run. В приведенной ниже команде мы использовали опции --help, чтобы перечислить все команды и другие опции, доступные с командой pgcli.

pipx run pgcli --help

Если эта команда работает нормально, значит, мы готовы к подключению и взаимодействию с базой данных с помощью pgcli.

Список локальных баз данных

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

pipx run pgcli --host localhost --user postgres --list

OR

pipx run pgcli -h localhost -U postgres -l

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

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

Для подключения к отдельной базе данных Postgres у нас есть два варианта: мы можем использовать строку URL базы данных postgres или разобрать параметры команды по отдельности. Проще использовать вариант с URL, потому что он объединяет многие вещи в одну строку, а не записывает каждый параметр.

Подключение с помощью URI подключения Postgres

Мы можем просто передать URI подключения postgres для доступа к базе данных postgres с помощью pgcli. URI имеет определенный шаблон, в котором нужно указать такие значения, как имя хоста, пароль, имя пользователя, порт и имя базы данных.

postgresql://username:password@hostname:port/database_name

Выше приведена структура URI подключения postgres, мы разбираем имя пользователя, пароль для этого имени пользователя, имя хоста с портом, и имя базы данных. Эти параметры, если они правильно разобраны, позволяют получить прямой доступ к базе данных.

pipx run pgcli postgresql://postgres:postgres@localhost:5432/techstructive_blog

Эта команда выведет нас на экран, где мы окажемся в оболочке PSQL в указанной базе данных или хосте. Для подключения к локальной базе данных имя хоста, скорее всего, будет localhost, а порт базы данных postgres обычно 5432, но могут быть и исключения. Мы можем выйти из PGCLI с помощью CTRL + D или exit.

При использовании удаленной базы данных может быть указано имя хоста и другие детали, например, у нас может быть приложение Django, в этом проекте у нас будет database_name, port, и детали, указанные в настройках DATABASES, эти поля могут быть проверены и может быть создан URI подключения postgres.

Рассмотрим пример.

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": "blog",
        "USER": "meet",
        "PASSWORD": "postgres",
        "HOST": "localhost",
    }
}

У нас есть все поля, указанные в файле настроек проекта django. Мы можем использовать их для построения URI подключения к базе данных postgres.

pgcli postgresql://meet:postgres@localhost:5432/blog

Итак, вот как вы можете извлекать параметры из других технологий и фреймворков для работы с URIs postgres.

Подключение с помощью опций/параметров CLI

Мы можем разобрать такие параметры, как hostname, password и database name вручную с помощью опций CLI, которые предоставляет PGCLI.

pipx run pgcli --username postgres --host localhost --port 5432 --dbname techstructive_blog

OR

pipx run pgcli -u postgres -h localhost -p 5432 -d techstructive_blog

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

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

Вывод списка всех баз данных при подключении к серверу

Допустим, мы не знаем имени базы данных, мы просто подключены к серверу баз данных, мы можем получить список баз данных внутри оболочки с помощью команды l+. Эта команда похожа на параметр --list в команде PGCLI, но разница лишь в том, что мы выполняем ее из оболочки psql, а не из оболочки терминала.

# Let's say we connected to a database server

pipx run pgcli --username postgres --host localhost --port 5432


# List out all databases in that server

l+

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

Список всех таблиц в базе данных

Мы можем получить список всех таблиц, существующих в базе данных, в которую мы вошли, с помощью команды dt. Мы можем просто ввести эту команду в подсказку, которая появится после входа в базу данных postgres.

dt

Мы можем использовать d <имя_таблицы> для описания деталей предоставленной таблицы. Мы получим обратно список атрибутов внутри таблицы и индексы отношений таблицы, а также детали ссылающейся таблицы.

У нас также есть другие команды для отображения типов, схем, ролей, представлений и т.д. с префиксом d, подробный список можно найти в документации PGCLI.

Выполнение запросов

Мы можем выполнять SQL-запросы в приглашении PGCLI, как обычно это делается в оболочке PSQL. Есть и дополнительные преимущества, такие как определенные режимы редактирования текста и автозавершение. Мы выполним несколько запросов, а также попробуем другие варианты редактирования и сохранения запросов.

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

SELECT * FROM table_name

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

Режимы написания в PGCLI

Мы можем использовать два режима в подсказке PGCLI, один — это режим Emacs, который используется по умолчанию, а другой — Vi-режим для поклонников Vim. Мы можем переключаться между этими режимами с помощью клавиши F4. Режим Emacs-mode — это обычный режим набора текста без каких-либо режимов, макросов быстрого доступа и прочего. Хотя хорошо, что PGCLI предлагает VI-режим, он отлично подходит для людей, использующих Vim, при использовании Shell для редактирования запроса этот режим может быть использован.

Мы можем использовать привязку клавиш hjkl для перемещения и другие клавиши обычного режима, такие как w для перемещения слова, или b для перемещения слова в обратном направлении, e в конец слова и т.д. Мы можем использовать i или a для входа в режим вставки, dd для удаления всей строки, cc или S для удаления текущей строки и входа в режим вставки, yy для удаления строки, p для вставки, u для отмены и т.д.

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

Автодополнение в оболочке PGCLI

Оболочка PGCLI shell имеет функцию автозавершения, которая предоставляет некоторые предложения на основе последних запросов, выполненных команд или опций по умолчанию. Вы можете переключить автозавершение с помощью клавиши F2, официально она называется Smart-Completion, как вы увидите в левом нижнем углу оболочки.

Выбор редактора для написания запросов

Для написания SQL-запросов мы можем использовать различные редакторы. Мы можем написать запрос из редактора, используя команду e. По умолчанию выбирается редактор, который будет установлен в одной из ваших переменных окружения $EDITOR или $VISUAL. Вы можете задать их вручную в файле bashrc, экспортировав значение VISUAL или EDITOR как имя вашего редактора.

export VISUAL=vim

OR

export EDITOR=vim

OR

export VISUAL=gedit

Здесь мы можем задать переменную окружения VISUAL в качестве имени редактора или пути к нему. Переменная окружения VISUAL предпочтительна в первую очередь. Более подробную информацию можно найти в документации по команде редактора.

После того, как мы настроили редактор, мы окажемся внутри редактора после ввода команды e. Мы можем писать запросы в редакторе и выходить из него после завершения работы. После закрытия редактора текст будет считан из редактора и введен в оболочку psql. Это заставит нас ждать команды enter для выполнения запроса.

Мы можем даже выполнить несколько запросов одновременно, если используем ; в конце каждого запроса. Также мы можем нажать v в обычном режиме VI, чтобы открыть текущий запрос в редакторе по умолчанию.

Сохранение вывода запроса в файл

Допустим, мы написали запрос в оболочке psql и хотим сохранить результат в файл. Мы можем сделать это с помощью команды o, при этом нам нужно указать имя файла, в который будет сохранен вывод запросов.

ПРИМЕЧАНИЕ: Как только мы используем команду o, все запросы начнут добавлять результаты в указанный файл. Чтобы остановить это поведение, вы можете использовать команду o (без указания имени файла), чтобы прекратить добавление результатов в файл.

# Start recording the output of queries to file
o filename


# Stop recording the output of queries to file
o

Вывод запросов PGCLI в файл

Выполнение запроса из файла

Мы можем даже выполнять запросы, хранящиеся в файле, используя команду i, за которой следует имя файла. Это загрузит запросы из файла и отобразит вывод каждого запроса.

i file_name

Резюме

В этой небольшой статье мы смогли изучить основы пакета PGCLI в python. С помощью этого пакета мы можем взаимодействовать с базой данных Postgres, которая может находиться на вашем локальном сервере или где-то удаленно. С помощью PSQL-подобной оболочки, предоставляемой инструментом PGCLI, мы смогли писать SQL-запросы, выполнять команды, записывать/читать запросы из файлов и т.д. Этот инструмент очень удобен для тех, кто работает с базами данных postgres и кому нужно просмотреть данные или схему в этой базе данных.

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

Заключение

Это был мой опыт использования инструмента PGCLI и изучения различных возможностей, которые он предоставляет и которые можно использовать для быстрого взаимодействия с базой данных Postgres. Надеюсь, вы узнали что-то новое из этой статьи, если у вас есть какие-либо вопросы (НЕ SQL-запросы) или отзывы, пожалуйста, дайте мне знать в комментариях или на моих социальных страницах. Спасибо за прочтение и счастливого кодинга 🙂