PostgreSQL的命令行工具Pgcli-摩杜云开发者社区

概念描述

Pgcli 是 PostgreSQL 的命令行工具，相比于 PostgreSQL 原生的命令行工具 psql，具有智能提示和语法高亮等功能。
Pgcli 是基于 python-prompt-toolkit 编写的。
网站：https://www.pgcli.com/
https://github.com/dbcli/pgcli
以下测试的环境：操作系统版本：CentOS 7.6。数据库版本：PostgreSQL 14.3。Pgcli版本：3.4.1。

测试验证

1. 安装 Pgcli

需要提前安装 Python 环境，使用 pip 命令安装 Pgcli，建议使用 Python 3 环境，此处忽略 Python 3 的安装方式。
使用 pip 命令安装 Pgcli

[root@pgtest1 ~]# pip3 install -U pgcli
WARNING: Running pip install with root privileges is generally not a good idea. Try `pip3 install --user` instead.
Collecting pgcli
  Downloading https://files.pythonhosted.org/packages/45/e9/ad61afc28f596cce196f91d036f6f1035a37bc510c47c48998631bd36e2f/pgcli-3.4.1-py3-none-any.whl (76kB)
    100% |████████████████████████████████| 81kB 348kB/s
Collecting click>=4.1 (from pgcli)
  Downloading https://files.pythonhosted.org/packages/4a/a8/0b2ced25639fb20cc1c9784de90a8c25f9504a7f18cd8b5397bd61696d7d/click-8.0.4-py3-none-any.whl (97kB)
    100% |████████████████████████████████| 102kB 577kB/s
Collecting Pygments>=2.0 (from pgcli)
  Downloading https://files.pythonhosted.org/packages/5c/8e/1d9017950034297fffa336c72e693a5b51bbf85141b24a763882cf1977b5/Pygments-2.12.0-py3-none-any.whl (1.1MB)
    100% |████████████████████████████████| 1.1MB 2.1MB/s
Collecting sqlparse<0.5,>=0.3.0 (from pgcli)
  Downloading https://files.pythonhosted.org/packages/05/40/d836d55fb3f467243ee839ab7b814822fda522cd395fa41e282684e71ee5/sqlparse-0.4.2-py3-none-any.whl (42kB)
    100% |████████████████████████████████| 51kB 5.4MB/s
Collecting configobj>=5.0.6 (from pgcli)
  Downloading https://files.pythonhosted.org/packages/64/61/079eb60459c44929e684fa7d9e2fdca403f67d64dd9dbac27296be2e0fab/configobj-5.0.6.tar.gz
Collecting pendulum>=2.1.0 (from pgcli)
  Downloading https://files.pythonhosted.org/packages/6f/d0/3b9ebd15ae3d4e079d6174ee49b19c113189558d3c5e1e641d03bc4560d2/pendulum-2.1.2-cp36-cp36m-manylinux1_x86_64.whl (154kB)
    100% |████████████████████████████████| 163kB 4.7MB/s
Collecting prompt-toolkit<4.0.0,>=2.0.6 (from pgcli)
  Downloading https://files.pythonhosted.org/packages/3f/2d/dcb44d69f388ca2ee1a4a4d3c204ab66b36975c0d5166781eaeeff76b882/prompt_toolkit-3.0.29-py3-none-any.whl (381kB)
    100% |████████████████████████████████| 389kB 3.4MB/s
Collecting setproctitle>=1.1.9 (from pgcli)
  Downloading https://files.pythonhosted.org/packages/8f/71/1017f29259f486f963535213b2b81645da35edd14de3539084e2d291d16b/setproctitle-1.2.3-cp36-cp36m-manylinux_2_5_x86_64.manylinux1_x86_64.manylinux_2_17_x86_64.manylinux2014_x86_64.whl
Collecting cli-helpers[styles]>=2.2.1 (from pgcli)
  Downloading https://files.pythonhosted.org/packages/b9/d9/f91e2594970c010f086d565c718ba6bf982dedfa003903b2db59e548c8ba/cli_helpers-2.2.1-py3-none-any.whl
Collecting psycopg2>=2.8 (from pgcli)
  Downloading https://files.pythonhosted.org/packages/d1/1e/b450599a27b1809bccbd4e369f397cb18dc56b875778d961f9ae180b54b7/psycopg2-2.9.3.tar.gz (380kB)
    100% |████████████████████████████████| 389kB 3.3MB/s
Collecting pgspecial<2.0.0,>=1.13.1 (from pgcli)
  Downloading https://files.pythonhosted.org/packages/6f/7a/ef03095ebe066e6f6969d35e38428e8ec6fd81a1e5a82cf29ff757d6b56e/pgspecial-1.13.1-py3-none-any.whl
Collecting importlib-metadata; python_version < "3.8" (from click>=4.1->pgcli)
  Downloading https://files.pythonhosted.org/packages/a0/a1/b153a0a4caf7a7e3f15c2cd56c7702e2cf3d89b1b359d1f1c5e59d68f4ce/importlib_metadata-4.8.3-py3-none-any.whl
Requirement already up-to-date: six in /usr/local/lib/python3.6/site-packages (from configobj>=5.0.6->pgcli)
Collecting pytzdata>=2020.1 (from pendulum>=2.1.0->pgcli)
  Downloading https://files.pythonhosted.org/packages/e0/4f/4474bda990ee740a020cbc3eb271925ef7daa7c8444240d34ff62c8442a3/pytzdata-2020.1-py2.py3-none-any.whl (489kB)
    100% |████████████████████████████████| 491kB 3.1MB/s
Requirement already up-to-date: python-dateutil<3.0,>=2.6 in /usr/local/lib/python3.6/site-packages (from pendulum>=2.1.0->pgcli)
Requirement already up-to-date: wcwidth in /usr/local/lib/python3.6/site-packages (from prompt-toolkit<4.0.0,>=2.0.6->pgcli)
Collecting tabulate[widechars]>=0.8.2 (from cli-helpers[styles]>=2.2.1->pgcli)
  Downloading https://files.pythonhosted.org/packages/92/4e/e5a13fdb3e6f81ce11893523ff289870c87c8f1f289a7369fb0e9840c3bb/tabulate-0.8.10-py3-none-any.whl
Requirement already up-to-date: zipp>=0.5 in /usr/local/lib/python3.6/site-packages (from importlib-metadata; python_version < "3.8"->click>=4.1->pgcli)
Collecting typing-extensions>=3.6.4; python_version < "3.8" (from importlib-metadata; python_version < "3.8"->click>=4.1->pgcli)
  Downloading https://files.pythonhosted.org/packages/45/6b/44f7f8f1e110027cf88956b59f2fad776cca7e1704396d043f89effd3a0e/typing_extensions-4.1.1-py3-none-any.whl
Installing collected packages: typing-extensions, importlib-metadata, click, Pygments, sqlparse, configobj, pytzdata, pendulum, prompt-toolkit, setproctitle, tabulate, cli-helpers, psycopg2, pgspecial, pgcli
  Found existing installation: typing-extensions 3.10.0.2
    Uninstalling typing-extensions-3.10.0.2:
      Successfully uninstalled typing-extensions-3.10.0.2
  Found existing installation: importlib-metadata 4.8.1
    Uninstalling importlib-metadata-4.8.1:
      Successfully uninstalled importlib-metadata-4.8.1
  Found existing installation: click 8.0.3
    Uninstalling click-8.0.3:
      Successfully uninstalled click-8.0.3
  Running setup.py install for configobj ... done
  Found existing installation: psycopg2 2.9.1
    Uninstalling psycopg2-2.9.1:
      Successfully uninstalled psycopg2-2.9.1
  Running setup.py install for psycopg2 ... done
Successfully installed Pygments-2.12.0 cli-helpers-2.2.1 click-8.0.4 configobj-5.0.6 importlib-metadata-4.8.3 pendulum-2.1.2 pgcli-3.4.1 pgspecial-1.13.1 prompt-toolkit-3.0.29 psycopg2-2.9.3 pytzdata-2020.1 setproctitle-1.2.3 sqlparse-0.4.2 tabulate-0.8.10 typing-extensions-4.1.1
[root@pgtest1 ~]#

2. 使用 Pgcli 登录数据库

指定参数登录数据库

[postgres@pgtest1 ~]$ pgcli -h pgtest1 -p 5432 -U postgres -d postgres
Server: PostgreSQL 14.3
Version: 3.4.1
Home: http://pgcli.com
postgres@pgtest1:postgres> \l
+-----------+----------+----------+------------+------------+-----------------------+
| Name      | Owner    | Encoding | Collate    | Ctype      | Access privileges     |
|-----------+----------+----------+------------+------------+-----------------------|
| orcl      | postgres | UTF8     | en_US.utf8 | en_US.utf8 | <null>                |
| postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | <null>                |
| template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres           |
|           |          |          |            |            | postgres=CTc/postgres |
| template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres           |
|           |          |          |            |            | postgres=CTc/postgres |
| test      | postgres | UTF8     | en_US.utf8 | en_US.utf8 | <null>                |
+-----------+----------+----------+------------+------------+-----------------------+
SELECT 5
Time: 0.012s
postgres@pgtest1:postgres>

使用连接串登录数据库，可以配置一些需要的参数

-- 连接串的配置格式：pgcli postgresql://[user[:password]@][netloc][:port][/dbname][?extra=value[&other=other-value]]
[postgres@pgtest1 ~]$ pgcli postgresql://postgres:postgres@pgtest1:5432/postgres
Server: PostgreSQL 14.3
Version: 3.4.1
Home: http://pgcli.com
postgres@pgtest1:postgres>
-- 配置参数示例
pgcli postgres://amjith:pa$$w0rd@example.com:5432/app_db?sslmode=verify-ca&sslrootcert=/myrootcert

更多详细参数可以使用帮助命令查看

[postgres@pgtest1 ~]$ pgcli --help
Usage: pgcli [OPTIONS] [DBNAME] [USERNAME]

Options:
  -h, --host TEXT            Host address of the postgres database.
  -p, --port INTEGER         Port number at which the postgres instance is
                             listening.
  -U, --username TEXT        Username to connect to the postgres database.
  -u, --user TEXT            Username to connect to the postgres database.
  -W, --password             Force password prompt.
  -w, --no-password          Never prompt for password.
  --single-connection        Do not use a separate connection for completions.
  -v, --version              Version of pgcli.
  -d, --dbname TEXT          database name to connect to.
  --pgclirc FILE             Location of pgclirc file.
  -D, --dsn TEXT             Use DSN configured into the [alias_dsn] section
                             of pgclirc file.
  --list-dsn                 list of DSN configured into the [alias_dsn]
                             section of pgclirc file.
  --row-limit INTEGER        Set threshold for row limit prompt. Use 0 to
                             disable prompt.
  --less-chatty              Skip intro on startup and goodbye on exit.
  --prompt TEXT              Prompt format (Default: "\u@\h:\d> ").
  --prompt-dsn TEXT          Prompt format for connections using DSN aliases
                             (Default: "\u@\h:\d> ").
  -l, --list                 list available databases, then exit.
  --auto-vertical-output     Automatically switch to vertical output mode if
                             the result is wider than the terminal width.
  --warn [all|moderate|off]  Warn before running a destructive query.
  --ssh-tunnel TEXT          Open an SSH tunnel to the given address and
                             connect to the database from it.
  --help                     Show this message and exit.

pgcli 还支持许多与登录选项相同的环境变量，例如：PGHOST, PGPORT, PGUSER, PGPASSWORD, PGDATABASE
pgcli 还支持 SSL 相关的环境变量，所以如果你需要通过 ssl 连接来连接 postgres 数据库，你可以像这样设置

export PGSSLMODE="verify-full"
export PGSSLCERT="/your-path-to-certs/client.crt"
export PGSSLKEY="/your-path-to-keys/client.key"
export PGSSLROOTCERT="/your-path-to-ca/ca.crt"
pgcli -h localhost -p 5432 -U username postgres

3、功能

3.1. 智能提示

默认开启，一旦你开始输入，REPL 就会弹出一个建议菜单。这些建议是基于光标位置的上下文敏感的。

FROM 关键字后会智能提示 schema 和表名，仅建议来自当前数据库 schema 和表名。
WHERE 关键字后会智能提示来自当前表的列名
INSERT INTO 同样会提示列名
别名提示列名
模糊匹配智能提示的内容

3.2. 语法高亮

PostgreSQL的命令行工具Pgcli_Pgcli

高亮的颜色可以通过配置文件自定义，使用配置参数 syntax_style 进行修改。
例如 syntax_style = fruity
各种参数值的配置后显示示例：https://www.pgcli.com/syntax

3.3. 支持psql中有限的元命令

pgcli 仅支持以下元命令

postgres@/tmp:postgres> \?
+--------------------------------------+------------------------------------------------+
| Command                              | Description                                    |
|--------------------------------------+------------------------------------------------|
| \! [command]                         | Pass commands to shell.                        |
| \#                                   | Refresh auto-completions.                      |
| \?                                   | Show Commands.                                 |
| \T [format]                          | Change the table format used to output results |
| \c[onnect] database_name             | Change to a new database.                      |
| \conninfo                            | Get connection details                         |
| \copy [tablename] to/from [filename] | Copy data between a file and a table.          |
| \d[+] [pattern]                      | List or describe tables, views and sequences.  |
| \dD[+] [pattern]                     | List or describe domains.                      |
| \dE[+] [pattern]                     | List foreign tables.                           |
| \dF[+] [pattern]                     | List text search configurations.               |
| \dT[S+] [pattern]                    | List data types                                |
| \db[+] [pattern]                     | List tablespaces.                              |
| \ddp [pattern]                       | Lists default access privilege settings.       |
| \df[+] [pattern]                     | List functions.                                |
| \di[+] [pattern]                     | List indexes.                                  |
| \dm[+] [pattern]                     | List materialized views.                       |
| \dn[+] [pattern]                     | List schemas.                                  |
| \dp [pattern]                        | List roles.                                    |
| \ds[+] [pattern]                     | List sequences.                                |
| \dt[+] [pattern]                     | List tables.                                   |
| \du[+] [pattern]                     | List roles.                                    |
| \dv[+] [pattern]                     | List views.                                    |
| \dx[+] [pattern]                     | List extensions.                               |
| \e [file]                            | Edit the query with external editor.           |
| \h                                   | Show SQL syntax and help.                      |
| \i filename                          | Execute commands from file.                    |
| \l[+] [pattern]                      | List databases.                                |
| \n[+] [name] [param1 param2 ...]     | List or execute named queries.                 |
| \nd [name]                           | Delete a named query.                          |
| \np name_pattern                     | Print a named query.                           |
| \ns name query                       | Save a named query.                            |
| \o [filename]                        | Send all query results to file.                |
| \pager [command]                     | Set PAGER. Print the query results via PAGER.  |
| \pset [key] [value]                  | A limited version of traditional \pset         |
| \q                                   | Quit pgcli.                                    |
| \refresh                             | Refresh auto-completions.                      |
| \sf[+] FUNCNAME                      | Show a function's definition.                  |
| \timing                              | Toggle timing of commands.                     |
| \watch [sec=2]                       | Execute query every `sec` seconds.             |
| \x                                   | Toggle expanded output.                        |
| quit                                 | Quit pgcli.                                    |
+--------------------------------------+------------------------------------------------+

\l 查看数据库信息
\d 查看表的信息
\du 显示角色信息
等等

3.4. 键绑定

有两种类型的键绑定，分别是 Emacs 模式(默认)和 Vi 模式，可以通过配置文件 (~/.config/pgcli/config) 更改或通过 F4 键切换。

在 Emacs 模式下，您可以按 Ctrl-a 转到行首，按 Ctrl-e 转到行尾等等。启用 Vi 模式后，您可以在 REPL 命令行中使用 Vi 提供的模态编辑功能，例如按[Esc]键进入正常模式，您可以使用^转到行首和$行尾，按下i将使您进入插入模式。

这个快捷键一般用在查询显示结果超过一整页的时候，例如输入 /df 命令的输入结果在一页中显示不全(\pager less)，此时就可以使用键绑定

PostgreSQL的命令行工具Pgcli_Pgcli_06

3.5. 命名查询

postgres@/tmp:postgres> \n
Named Queries are a way to save frequently used queries
with a short name. Think of them as favorites.
Examples:

    # Save a new named query.
    > \ns simple select * from abc where a is not Null;

    # List all named queries.
    > \n
    +--------+----------------------------------------+
    | Name   | Query                                  |
    |--------+----------------------------------------|
    | simple | SELECT * FROM xyzb where a is not null |
    +--------+----------------------------------------+

    # Run a named query.
    > \n simple
    +-----+
    |   a |
    |-----|
    |  50 |
    +-----+

    # Delete a named query.
    > \nd simple
    simple: Deleted

位置参数，命名查询支持 shell 样式的参数替换，将带有参数的命名查询保存为占位符，(e.g. $1, $2, $3, etc.)

# 保存一个新的命名查询
postgres@/tmp:postgres> \ns emp_by_empno select * from emp_range where empno = '$1'
Saved.
# 查询已保存的命名查询
postgres@/tmp:postgres> \n
+--------------+--------------------------------------------+
| Name         | Query                                      |
|--------------+--------------------------------------------|
| emp_by_empno | select * from emp_range where empno = '$1' |
+--------------+--------------------------------------------+
# 调用命名查询，传入位置参数
postgres@/tmp:postgres> \n emp_by_empno 7902
> select * from emp_range where empno = '$1'
+-------+-------+---------+------+---------------------+---------+--------+--------+
| empno | ename | job     | mgr  | hiredate            | sal     | comm   | deptno |
|-------+-------+---------+------+---------------------+---------+--------+--------|
| 7902  | FORD  | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | <null> | 20     |
+-------+-------+---------+------+---------------------+---------+--------+--------+
# 如果传入的参数值存在空格，可以使用双引号将参数值引起来
postgres@/tmp:postgres> \n
+-----------------+-----------------------------------------------+
| Name            | Query                                         |
|-----------------+-----------------------------------------------|
| emp_by_empno    | select * from emp_range where empno = '$1'    |
| emp_by_hiredate | select * from emp_range where hiredate = '$1' |
+-----------------+-----------------------------------------------+

postgres@/tmp:postgres> \n emp_by_hiredate 1981-12-03 00:00:00
Bad arguments
query does not have substitution parameter $2:
  select * from emp_range where hiredate = '1981-12-03'
  
postgres@/tmp:postgres> \n emp_by_hiredate "1981-12-03 00:00:00"
> select * from emp_range where hiredate = '$1'
+-------+-------+---------+------+---------------------+---------+--------+--------+
| empno | ename | job     | mgr  | hiredate            | sal     | comm   | deptno |
|-------+-------+---------+------+---------------------+---------+--------+--------|
| 7900  | JAMES | CLERK   | 7698 | 1981-12-03 00:00:00 | 950.00  | <null> | 30     |
| 7902  | FORD  | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | <null> | 20     |
+-------+-------+---------+------+---------------------+---------+--------+--------+

参数聚合，命名查询还支持通过两个占位符聚合参数。 $*用于原始聚合，$@用于字符串聚合，前者将使用聚合参数的原始值，后者将引用每个聚合值。
原始聚合

# 删除命名查询
postgres@/tmp:postgres> \nd emp_by_empno
emp_by_empno: Deleted
# 创建一个原始聚合的命名查询
postgres@/tmp:postgres> \ns emp_by_empno select * from emp_range where empno in ($*)
Saved.
# 调用命名查询，只需在查询名称后添加任何（至少一个）参数
postgres@/tmp:postgres> \n emp_by_empno 7521 7902
> select * from emp_range where empno in ($*)
+-------+-------+----------+------+---------------------+---------+--------+--------+
| empno | ename | job      | mgr  | hiredate            | sal     | comm   | deptno |
|-------+-------+----------+------+---------------------+---------+--------+--------|
| 7521  | WARD  | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30     |
| 7902  | FORD  | ANALYST  | 7566 | 1981-12-03 00:00:00 | 3000.00 | <null> | 20     |
+-------+-------+----------+------+---------------------+---------+--------+--------+

字符串聚合

# 创建一个字符串聚合的命名查询
postgres@/tmp:postgres> \ns emp_by_ename select * from emp_range where ename in ($@)
Saved.
# 调用命名查询，只需在查询名称后添加任何（至少一个）参数，同样如果传入的参数值存在空格，可以使用双引号将参数值引起来
postgres@/tmp:postgres> \n emp_by_ename SMITH JONES KING
> select * from emp_range where ename in ($@)
+-------+-------+-----------+--------+---------------------+---------+--------+--------+
| empno | ename | job       | mgr    | hiredate            | sal     | comm   | deptno |
|-------+-------+-----------+--------+---------------------+---------+--------+--------|
| 7369  | SMITH | CLERK     | 7902   | 1980-12-17 00:00:00 | 800.00  | <null> | 20     |
| 7566  | JONES | MANAGER   | 7839   | 1981-04-02 00:00:00 | 2975.00 | <null> | 20     |
| 7839  | KING  | PRESIDENT | <null> | 1981-11-17 00:00:00 | 5000.00 | <null> | 10     |
+-------+-------+-----------+--------+---------------------+---------+--------+--------+

位置参数与参数聚合的结合使用，位置参数替换发生在聚合之前，这意味着位置参数可以放在查询中的参数聚合之后

# 创建一个命名查询，位置参数(empno = $1)在参数聚合(ename in ($@))的后面
postgres@/tmp:postgres> \ns emp_by_ename_and_empno select * from emp_range where ename in ($@) and empno = $1
Saved.
# 调用命名查询，位置参数值要放在参数聚合值的前面
postgres@/tmp:postgres> \n emp_by_ename_and_empno 7839 SMITH JONES KING
> select * from emp_range where ename in ($@) and empno = $1
+-------+-------+-----------+--------+---------------------+---------+--------+--------+
| empno | ename | job       | mgr    | hiredate            | sal     | comm   | deptno |
|-------+-------+-----------+--------+---------------------+---------+--------+--------|
| 7839  | KING  | PRESIDENT | <null> | 1981-11-17 00:00:00 | 5000.00 | <null> | 10     |
+-------+-------+-----------+--------+---------------------+---------+--------+--------+

3.6. 编辑命令 \e

\e 启动一个编辑器来编辑当前查询
编辑当前查询

postgres@/tmp:postgres> select * from emp_range where ename \e
select * from emp_range where ename

# Type your query above this line.

如果当前查询为空，则编辑上一个查询

postgres@/tmp:postgres> select * from emp_range where ename in ('SMITH','JONES','KING') and empno = 7839;
+-------+-------+-----------+--------+---------------------+---------+--------+--------+
| empno | ename | job       | mgr    | hiredate            | sal     | comm   | deptno |
|-------+-------+-----------+--------+---------------------+---------+--------+--------|
| 7839  | KING  | PRESIDENT | <null> | 1981-11-17 00:00:00 | 5000.00 | <null> | 10     |
+-------+-------+-----------+--------+---------------------+---------+--------+--------+
SELECT 1
Time: 0.008s

-- 当前查询为空，则编辑上一个查询
postgres@/tmp:postgres> \e
select * from emp_range where ename in ('SMITH','JONES','KING') and empno = 7839;

# Type your query above this line.

在编辑器中打开一个现有文件以及文件的内容

[postgres@pgtest1 ~]$ cat test.sql
select * from emp_range where ename in ('SMITH','JONES','KING') and empno = 7839

postgres@/tmp:postgres> \e test.sql
postgres@/tmp:postgres> select * from emp_range where ename in ('SMITH','JONES','KING') and empno = 7839

3.7. 多行模式

pgcli 的默认行为是在按下回车键后立即执行查询，不管是否存在分号(;)结尾。

postgres@/tmp:postgres> select * from emp_range where ename in ('SMITH','JONES','KING') and empno = 7839
+-------+-------+-----------+--------+---------------------+---------+--------+--------+
| empno | ename | job       | mgr    | hiredate            | sal     | comm   | deptno |
|-------+-------+-----------+--------+---------------------+---------+--------+--------|
| 7839  | KING  | PRESIDENT | <null> | 1981-11-17 00:00:00 | 5000.00 | <null> | 10     |
+-------+-------+-----------+--------+---------------------+---------+--------+--------+

可以通过配置文件配置多行模式或者使用<F3>临时启动多行模式，多行模式意味着只有出现分号(;)才会执行查询

3.8. 历史命令搜索

向上/向下箭头可用于浏览历史记录。
<Ctrl+r> 输入关键词进行搜索

3.9. 大型结果集的显示

配置查询结果的输出形式，例如less, more，使您可以一次轻松地查看大型结果集一页。
使用 \pager命令配置

postgres@/tmp:postgres> \pager less
postgres@/tmp:postgres> \pager more

在配置文件中配置参数 pager，以下是一些常用less选项和配置示例

# -X     退出时将文件内容留在屏幕上。
# -Fless 如果整个输出可以显示在一个屏幕上，则退出。
# -R     以“原始”形式显示 ANSI 颜色转义序列。
# -S     禁用换行,横向滚动查看长线。

# 这是 pgcli 用户中流行的选项。
export LESS="-XFR"
# 一些 pgcli 用户喜欢禁用换行。
export LESS="-SRXF"

3.10. 配置提示

在配置文件中配置参数 prompt 修改提示
默认的提示信息：user@localhost:db_name>，即 prompt = '\u@\h:\d> ’
示例，修改提示信息，添加时间的显示

[postgres@pgtest1 pgcli]$ vi config
prompt = "\t \u@\h:\d> "

[postgres@pgtest1 ~]$ pgcli
Server: PostgreSQL 14.3
Version: 3.4.1
Home: http://pgcli.com
06/28/22 01:25:35 postgres@/tmp:postgres>

3.11. pgcli 集成在 IPython

Pgcli 可以在IPython控制台中运行。在处理查询时，在不离开 IPython 控制台的情况下进入 pgcli 会话，迭代查询，然后退出 pgcli 以在 IPython 工作区中查找查询结果可能会很有用。

安装 IPython

[root@pgtest1 ~]# pip3 install ipython-sql
WARNING: Running pip install with root privileges is generally not a good idea. Try `pip3 install --user` instead.
Collecting ipython-sql
  Downloading https://files.pythonhosted.org/packages/f3/e4/39dff0f3d426d2aab04fba6f10edf57567656dbd9f760f097f4fd56de204/ipython_sql-0.4.1-py3-none-any.whl
Collecting prettytable<1 (from ipython-sql)
  Downloading https://files.pythonhosted.org/packages/ef/30/4b0746848746ed5941f052479e7c23d2b56d174b82f4fd34a25e389831f5/prettytable-0.7.2.tar.bz2
Collecting ipython-genutils>=0.1.0 (from ipython-sql)
  Downloading https://files.pythonhosted.org/packages/fa/bc/9bd3b5c2b4774d5f33b2d544f1460be9df7df2fe42f352135381c347c69a/ipython_genutils-0.2.0-py2.py3-none-any.whl
Requirement already satisfied: six in /usr/local/lib/python3.6/site-packages (from ipython-sql)
Collecting sqlalchemy>=0.6.7 (from ipython-sql)
  Downloading https://files.pythonhosted.org/packages/eb/b6/b8579f5a39712fee884db2bdb9e726437b0cc2f2cb57430613651282f3eb/SQLAlchemy-1.4.39-cp36-cp36m-manylinux_2_5_x86_64.manylinux1_x86_64.manylinux_2_12_x86_64.manylinux2010_x86_64.whl (1.6MB)
    100% |████████████████████████████████| 1.6MB 611kB/s
Collecting ipython>=1.0 (from ipython-sql)
  Downloading https://files.pythonhosted.org/packages/53/09/958a4802489d28b2484114ee6414c7502ef57de6f2dbc9095b718640060c/ipython-7.16.3-py3-none-any.whl (783kB)
    100% |████████████████████████████████| 788kB 265kB/s
Requirement already satisfied: sqlparse in /usr/local/lib/python3.6/site-packages (from ipython-sql)
Requirement already satisfied: importlib-metadata; python_version < "3.8" in /usr/local/lib/python3.6/site-packages (from sqlalchemy>=0.6.7->ipython-sql)
Collecting greenlet!=0.4.17; python_version >= "3" and (platform_machine == "aarch64" or (platform_machine == "ppc64le" or (platform_machine == "x86_64" or (platform_machine == "amd64" or (platform_machine == "AMD64" or (platform_machine == "win32" or platform_machine == "WIN32")))))) (from sqlalchemy>=0.6.7->ipython-sql)
  Downloading https://files.pythonhosted.org/packages/76/5a/a6a693096353c1c17932b21ae864a0280e420fadd2f14399a00b085d3d1b/greenlet-1.1.2-cp36-cp36m-manylinux1_x86_64.whl (162kB)
    100% |████████████████████████████████| 163kB 542kB/s
Collecting backcall (from ipython>=1.0->ipython-sql)
  Downloading https://files.pythonhosted.org/packages/4c/1c/ff6546b6c12603d8dd1070aa3c3d273ad4c07f5771689a7b69a550e8c951/backcall-0.2.0-py2.py3-none-any.whl
Requirement already satisfied: pygments in /usr/local/lib/python3.6/site-packages (from ipython>=1.0->ipython-sql)
Requirement already satisfied: setuptools>=18.5 in /usr/lib/python3.6/site-packages (from ipython>=1.0->ipython-sql)
Requirement already satisfied: prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0 in /usr/local/lib/python3.6/site-packages (from ipython>=1.0->ipython-sql)
Collecting traitlets>=4.2 (from ipython>=1.0->ipython-sql)
  Downloading https://files.pythonhosted.org/packages/ca/ab/872a23e29cec3cf2594af7e857f18b687ad21039c1f9b922fac5b9b142d5/traitlets-4.3.3-py2.py3-none-any.whl (75kB)
    100% |████████████████████████████████| 81kB 808kB/s
Collecting jedi<=0.17.2,>=0.10 (from ipython>=1.0->ipython-sql)
  Downloading https://files.pythonhosted.org/packages/c3/d4/36136b18daae06ad798966735f6c3fb96869c1be9f8245d2a8f556e40c36/jedi-0.17.2-py2.py3-none-any.whl (1.4MB)
    100% |████████████████████████████████| 1.4MB 214kB/s
Collecting decorator (from ipython>=1.0->ipython-sql)
  Downloading https://files.pythonhosted.org/packages/d5/50/83c593b07763e1161326b3b8c6686f0f4b0f24d5526546bee538c89837d6/decorator-5.1.1-py3-none-any.whl
Collecting pexpect; sys_platform != "win32" (from ipython>=1.0->ipython-sql)
  Downloading https://files.pythonhosted.org/packages/39/7b/88dbb785881c28a102619d46423cb853b46dbccc70d3ac362d99773a78ce/pexpect-4.8.0-py2.py3-none-any.whl (59kB)
    100% |████████████████████████████████| 61kB 75kB/s
Collecting pickleshare (from ipython>=1.0->ipython-sql)
  Downloading https://files.pythonhosted.org/packages/9a/41/220f49aaea88bc6fa6cba8d05ecf24676326156c23b991e80b3f2fc24c77/pickleshare-0.7.5-py2.py3-none-any.whl
Requirement already satisfied: typing-extensions>=3.6.4; python_version < "3.8" in /usr/local/lib/python3.6/site-packages (from importlib-metadata; python_version < "3.8"->sqlalchemy>=0.6.7->ipython-sql)
Requirement already satisfied: zipp>=0.5 in /usr/local/lib/python3.6/site-packages (from importlib-metadata; python_version < "3.8"->sqlalchemy>=0.6.7->ipython-sql)
Requirement already satisfied: wcwidth in /usr/local/lib/python3.6/site-packages (from prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0->ipython>=1.0->ipython-sql)
Collecting parso<0.8.0,>=0.7.0 (from jedi<=0.17.2,>=0.10->ipython>=1.0->ipython-sql)
  Downloading https://files.pythonhosted.org/packages/93/d1/e635bdde32890db5aeb2ffbde17e74f68986305a4466b0aa373b861e3f00/parso-0.7.1-py2.py3-none-any.whl (109kB)
    100% |████████████████████████████████| 112kB 226kB/s
Collecting ptyprocess>=0.5 (from pexpect; sys_platform != "win32"->ipython>=1.0->ipython-sql)
  Downloading https://files.pythonhosted.org/packages/22/a6/858897256d0deac81a172289110f31629fc4cee19b6f01283303e18c8db3/ptyprocess-0.7.0-py2.py3-none-any.whl
Installing collected packages: prettytable, ipython-genutils, greenlet, sqlalchemy, backcall, decorator, traitlets, parso, jedi, ptyprocess, pexpect, pickleshare, ipython, ipython-sql
  Found existing installation: prettytable 2.2.1
    Uninstalling prettytable-2.2.1:
      Successfully uninstalled prettytable-2.2.1
  Running setup.py install for prettytable ... done
Successfully installed backcall-0.2.0 decorator-5.1.1 greenlet-1.1.2 ipython-7.16.3 ipython-genutils-0.2.0 ipython-sql-0.4.1 jedi-0.17.2 parso-0.7.1 pexpect-4.8.0 pickleshare-0.7.5 prettytable-0.7.2 ptyprocess-0.7.0 sqlalchemy-1.4.39 traitlets-4.3.3

运行 ipython 并加载pgcli.magic扩展

[postgres@pgtest1 ~]$ ipython
Python 3.6.8 (default, Nov 16 2020, 16:55:22)
Type 'copyright', 'credits' or 'license' for more information
IPython 7.16.3 -- An enhanced Interactive Python. Type '?' for help.

In [1]: %load_ext pgcli.magic

连接到数据库并构造查询

# 连接串配置格式：postgresql://username:password@hostname/dbname

In [2]: %pgcli postgresql://postgres:postgres@192.168.0.31:5432/postgres
Connected: postgres@postgres
Server: PostgreSQL 14.3
Version: 3.4.1
Home: http://pgcli.com
06/28/22 01:39:59 postgres@192:postgres> select * from emp_range where ename in ('SMITH','JONES','KING') and empno = 7839;
+-------+-------+-----------+--------+---------------------+---------+--------+--------+
| empno | ename | job       | mgr    | hiredate            | sal     | comm   | deptno |
|-------+-------+-----------+--------+---------------------+---------+--------+--------|
| 7839  | KING  | PRESIDENT | <null> | 1981-11-17 00:00:00 | 5000.00 | <null> | 10     |
+-------+-------+-----------+--------+---------------------+---------+--------+--------+
SELECT 1
Time: 0.014s

退出 pgcli 会话Ctrl + D并找到查询结果：

06/28/22 01:40:24 postgres@192:postgres>
Goodbye!
1 rows affected.
Out[2]: [(Decimal('7839'), 'KING', 'PRESIDENT', None, '1981-11-17 00:00:00', Decimal('5000.00'), None, Decimal('10'))]

结果存储在特殊的局部变量_中，并且可以分配给您选择的变量：

In [3]: my_result = _

In [4]: print(my_result)
+-------+-------+-----------+------+---------------------+---------+------+--------+
| empno | ename |    job    | mgr  |       hiredate      |   sal   | comm | deptno |
+-------+-------+-----------+------+---------------------+---------+------+--------+
|  7839 |  KING | PRESIDENT | None | 1981-11-17 00:00:00 | 5000.00 | None |   10   |
+-------+-------+-----------+------+---------------------+---------+------+--------+

3.12. SSH隧道

有时，本地计算机无法直接访问您要连接的数据库。例如，只有 SSH 端口可能对外部连接开放，或者数据库可能位于专用网络中，并且只有一台机器可以与之通信。对于这些情况，您可以要求 Pgcli 为您创建到该中间机器的 SSH 隧道。

第一个场景：

----------------------------------------------------------------------

                            |
-------------+              |     +----------+ my.server.com
    LOCAL    |              |     |  REMOTE  | :22 SSH
    CLIENT   | <== SSH Tunnel ==> |  SERVER  | :5432 Postgres instance
-------------+              |     +----------+
                            |
                         FIREWALL (only port 22 is open)

----------------------------------------------------------------------

在这种情况下，要连接到远程数据库，您将执行以下操作：

$ pgcli postgresql://user:password@localhost/mydatabase --ssh-tunnel my.server.com

第二个场景：

--------------------------------------------------------------------------------------------------


-------------+                    +----------+ my.server.com   +---------+ my.private.server.com
    LOCAL    |                    |  REMOTE  | :1022 SSH       | PRIVATE | :7777 Postgres instance
    CLIENT   | <== SSH Tunnel ==> |  SERVER  | <=============> | SERVER  |
-------------+                    +----------+                 +---------+
                                  my.server.com is the
                                  only server with access
                                  to my.private.server.com

--------------------------------------------------------------------------------------------------

在这种情况下，要连接到远程数据库，您将执行以下操作：

$ pgcli postgresql://user:password@my.private.server.com:7777/mydatabase \
    --ssh-tunnel john:mypass@myserver.com:1022

配置

在配置文件中，您可以匹配要为其自动打开 SSH 隧道的主机（使用正则表达式）。

注意事项

sshtunnel 尚不支持ProxyJump指令，这是我们用来创建 SSH 隧道的库。一种解决方法是用指令替换ProxyJump指令ProxyCommand。示例：ProxyJump %r@bastion.server.com 变成 ProxyCommand ssh %r@bastion.server.com -W %h:%p.
使用的 Python SSH 实现paramiko 尚不支持包含指令。解决方法是将所有内容放在主配置文件中。sshtunnel

4、配置文件

首次启动 pgcli 时会自动创建一个配置文件，配置文件的路径是 ~/.config/pgcli/config，以下是配置文件的默认配置，有需要可以自定义配置

[postgres@pgtest1 pgcli]$ cat config
# vi: ft=dosini
[main]

# Enables context sensitive auto-completion. If this is disabled, all
# possible completions will be listed.
smart_completion = True

# Display the completions in several columns. (More completions will be
# visible.)
wider_completion_menu = False

# Multi-line mode allows breaking up the sql statements into multiple lines. If
# this is set to True, then the end of the statements must have a semi-colon.
# If this is set to False then sql statements can't be split into multiple
# lines. End of line (return) is considered as the end of the statement.
multi_line = False

# If multi_line_mode is set to "psql", in multi-line mode, [Enter] will execute
# the current input if the input ends in a semicolon.
# If multi_line_mode is set to "safe", in multi-line mode, [Enter] will always
# insert a newline, and [Esc] [Enter] or [Alt]-[Enter] must be used to execute
# a command.
multi_line_mode = psql

# Destructive warning mode will alert you before executing a sql statement
# that may cause harm to the database such as "drop table", "drop database",
# "shutdown", "delete", or "update".
# Possible values:
# "all" - warn on data definition statements, server actions such as SHUTDOWN, DELETE or UPDATE
# "moderate" - skip warning on UPDATE statements, except for unconditional updates
# "off" - skip all warnings
destructive_warning = all

# Enables expand mode, which is similar to `\x` in psql.
expand = False

# Enables auto expand mode, which is similar to `\x auto` in psql.
auto_expand = False

# If set to True, table suggestions will include a table alias
generate_aliases = False

# log_file location.
# In Unix/Linux: ~/.config/pgcli/log
# In Windows: %USERPROFILE%\AppData\Local\dbcli\pgcli\log
# %USERPROFILE% is typically C:\Users\{username}
log_file = default

# keyword casing preference. Possible values: "lower", "upper", "auto"
keyword_casing = auto

# casing_file location.
# In Unix/Linux: ~/.config/pgcli/casing
# In Windows: %USERPROFILE%\AppData\Local\dbcli\pgcli\casing
# %USERPROFILE% is typically C:\Users\{username}
casing_file = default

# If generate_casing_file is set to True and there is no file in the above
# location, one will be generated based on usage in SQL/PLPGSQL functions.
generate_casing_file = False

# Casing of column headers based on the casing_file described above
case_column_headers = True

# history_file location.
# In Unix/Linux: ~/.config/pgcli/history
# In Windows: %USERPROFILE%\AppData\Local\dbcli\pgcli\history
# %USERPROFILE% is typically C:\Users\{username}
history_file = default

# Default log level. Possible values: "CRITICAL", "ERROR", "WARNING", "INFO"
# and "DEBUG". "NONE" disables logging.
log_level = INFO

# Order of columns when expanding * to column list
# Possible values: "table_order" and "alphabetic"
asterisk_column_order = table_order

# Whether to qualify with table alias/name when suggesting columns
# Possible values: "always", "never" and "if_more_than_one_table"
qualify_columns = if_more_than_one_table

# When no schema is entered, only suggest objects in search_path
search_path_filter = False

# Default pager.
# By default 'PAGER' environment variable is used
# pager = less -SRXF

# Timing of sql statements and table rendering.
timing = True

# Show/hide the informational toolbar with function keymap at the footer.
show_bottom_toolbar = True

# Table format. Possible values: psql, plain, simple, grid, fancy_grid, pipe,
# ascii, double, github, orgtbl, rst, mediawiki, html, latex, latex_booktabs,
# textile, moinmoin, jira, vertical, tsv, csv.
# Recommended: psql, fancy_grid and grid.
table_format = psql

# Syntax Style. Possible values: manni, igor, xcode, vim, autumn, vs, rrt,
# native, perldoc, borland, tango, emacs, friendly, monokai, paraiso-dark,
# colorful, murphy, bw, pastie, paraiso-light, trac, default, fruity
syntax_style = default

# Keybindings:
# When Vi mode is enabled you can use modal editing features offered by Vi in the REPL.
# When Vi mode is disabled emacs keybindings such as Ctrl-A for home and Ctrl-E
# for end are available in the REPL.
vi = False

# Error handling
# When one of multiple SQL statements causes an error, choose to either
# continue executing the remaining statements, or stopping
# Possible values "STOP" or "RESUME"
on_error = STOP

# Set threshold for row limit. Use 0 to disable limiting.
row_limit = 1000

# Truncate long text fields to this value for tabular display (does not apply to csv).
# Leave unset to disable truncation. Example: "max_field_width = "
# Be aware that formatting might get slow with values larger than 500 and tables with
# lots of records.
max_field_width = 500

# Skip intro on startup and goodbye on exit
less_chatty = False

# Postgres prompt
# \t - Current date and time
# \u - Username
# \h - Short hostname of the server (up to first '.')
# \H - Hostname of the server
# \d - Database name
# \p - Database port
# \i - Postgres PID
# \# - "@" sign if logged in as superuser, '>' in other case
# \n - Newline
# \dsn_alias - name of dsn alias if -D option is used (empty otherwise)
# \x1b[...m - insert ANSI escape sequence
# eg: prompt = '\x1b[35m\u@\x1b[32m\h:\x1b[36m\d>'
prompt = '\u@\h:\d> '

# Number of lines to reserve for the suggestion menu
min_num_menu_lines = 4

# Character used to left pad multi-line queries to match the prompt size.
multiline_continuation_char = ''

# The string used in place of a null value.
null_string = '<null>'

# manage pager on startup
enable_pager = True

# Use keyring to automatically save and load password in a secure manner
keyring = True

# Custom colors for the completion menu, toolbar, etc.
[colors]
completion-menu.completion.current = 'bg:#ffffff #000000'
completion-menu.completion = 'bg:#008888 #ffffff'
completion-menu.meta.completion.current = 'bg:#44aaaa #000000'
completion-menu.meta.completion = 'bg:#448888 #ffffff'
completion-menu.multi-column-meta = 'bg:#aaffff #000000'
scrollbar.arrow = 'bg:#003333'
scrollbar = 'bg:#00aaaa'
selected = '#ffffff bg:#6666aa'
search = '#ffffff bg:#4444aa'
search.current = '#ffffff bg:#44aa44'
bottom-toolbar = 'bg:#222222 #aaaaaa'
bottom-toolbar.off = 'bg:#222222 #888888'
bottom-toolbar.on = 'bg:#222222 #ffffff'
search-toolbar = 'noinherit bold'
search-toolbar.text = 'nobold'
system-toolbar = 'noinherit bold'
arg-toolbar = 'noinherit bold'
arg-toolbar.text = 'nobold'
bottom-toolbar.transaction.valid = 'bg:#222222 #00ff5f bold'
bottom-toolbar.transaction.failed = 'bg:#222222 #ff005f bold'
# These three values can be used to further refine the syntax highlighting.
# They are commented out by default, since they have priority over the theme set
# with the `syntax_style` setting and overriding its behavior can be confusing.
# literal.string = '#ba2121'
# literal.number = '#666666'
# keyword = 'bold #008000'

# style classes for colored table output
output.header = "#00ff5f bold"
output.odd-row = ""
output.even-row = ""
output.null = "#808080"

# Named queries are queries you can execute by name.
[named queries]

# DSN to call by -D option
[alias_dsn]
# example_dsn = postgresql://[user[:password]@][netloc][:port][/dbname]

# Format for number representation
# for decimal "d" - 12345678, ",d" - 12,345,678
# for float "g" - 123456.78, ",g" - 123,456.78
[data_formats]
decimal = ""
float = ""

5. 操作日志

操作日志默认开启，并以 ~/.config/pgcli/log 的形式存储在主文件夹中。

知识总结

Pgcli 命令行工具提供了一下功能：

智能提示，对于记不住某些命令和表名列名的DBA，提供了友好的帮助。
语法高亮，让命令不再单调。
兼容了psql的一些常用的元命令，让操作更加方便。
键绑定，针对大型的结果集可以灵活处理。
\n 命名查询，让重复执行的查询简单化。
\e 编辑SQL语句
单行和多行模式灵活切换
历史命令搜索
大型结果集的灵活显示
提示信息
pgcli 集成在 IPython
支持SSH隧道远程访问数据库
记录操作命令，方便命令的审计

参考文档

网站：https://www.pgcli.com/
https://github.com/dbcli/pgcli