PostgreSQL命令行工具 - pgcli
jopen
9年前
简介
pgcli 是针对PostgreSQL的命令行工具，他的特色是对SQL能够语法高亮显示，并且能对输入进行自动提示。（同时，针对MySQL也有一个类似的命令行工具mycli）。
授权协议： BSD 3-clause license
开发语言： Python
支持系统： Linux ，OS X ， Windows理论上可以但尚未测试过。
安装试用
环境：centos 32bit.
数据库：PostgreSQL9.4
1.安装PostgreSQL
编译安装或者安装包图形化安装均可。可参考：PostgreSQL在Linux下的源码编译安装。
2.安装python
安装python。可参考：python开发环境搭建与连接PostgreSQL数据库。
3.安装python-pip
首先试用yum安装python-pip，结果显示没有可用的包。
[root@localhost ~]# yum install python-pip Loaded plugins: fastestmirror, refresh-packagekit, security Loading mirror speeds from cached hostfile * base: mirrors.163.com * extras: mirrors.cqu.edu.cn * updates: mirrors.163.com base | 3.7 kB 00:00 extras | 3.4 kB 00:00 updates | 3.4 kB 00:00 updates/primary_db | 3.1 MB 00:04 Setting up Install Process No package python-pip available. Error: Nothing to do
然后通过制定具体地址来安装，安装成功：
[root@localhost ~]# curl "https://bootstrap.pypa.io/get-pip.py" -o "get-pip.py" % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 1379k 100 1379k 0 0 67361 0 0:00:20 0:00:20 --:--:-- 2612k [root@localhost ~]# python get-pip.py Collecting pip Retrying (Retry(total=4, connect=None, read=None, redirect=None)) after connection broken by 'ProtocolError('Connection aborted.', gaierror(-2, 'Name or service not known'))': /simple/pip/ /tmp/tmpEAjwcs/pip.zip/pip/_vendor/requests/packages/urllib3/util/ssl_.py:90: InsecurePlatformWarning: A true SSLContext object is not available. This prevents urllib3 from configuring SSL appropriately and may cause certain SSL connections to fail. For more information, see https://urllib3.readthedocs.org/en/latest/security.html#insecureplatformwarning. Downloading pip-7.1.2-py2.py3-none-any.whl (1.1MB) 100% |████████████████████████████████| 1.1MB 209kB/s Collecting setuptools Downloading setuptools-19.1.1-py2.py3-none-any.whl (463kB) 100% |████████████████████████████████| 466kB 617kB/s Collecting wheel Downloading wheel-0.26.0-py2.py3-none-any.whl (63kB) 100% |████████████████████████████████| 65kB 1.4MB/s Collecting argparse (from wheel) Downloading argparse-1.4.0-py2.py3-none-any.whl Installing collected packages: pip, setuptools, argparse, wheel Successfully installed argparse-1.4.0 pip-7.1.2 setuptools-19.1.1 wheel-0.26.0 4.安装pgcli
[root@localhost ~]# pip install pgcli
报错：
Error: pg_config executable not found. Please add the directory containing pg_config to the PATH or specify the full executable path with the option: python setup.py build_ext --pg-config /path/to/pg_config build ... or with the pg_config option in 'setup.cfg'. ---------------------------------------- Command python setup.py egg_info failed with error code 1 in /tmp/pip-build/psycopg2
解决方法：pg_config在PostgreSQL的bin目录下， 所以要在环境变量PATH中配置bin路径。
[root@localhost ~]# export PATH=$PATH:/opt/PostgreSQL/9.4/bin/
重新执行安装即可：
[root@localhost ~]# pip install pgcli
5.运行pgcli并连接PostgreSQL执行查询
[root@localhost ~]# pgcli --help Usage: pgcli [OPTIONS] [DATABASE] [USERNAME] Options: -h, --host TEXT Host address of the postgres database. -p, --port INTEGER Port number at which the postgres instance is listening. -U, --user TEXT User name to connect to the postgres database. -W, --password Force password prompt. -w, --no-password Never prompt for password. -v, --version Version of pgcli. -d, --dbname TEXT database name to connect to. --pgclirc TEXT Location of pgclirc file. --help Show this message and exit. [root@localhost ~]# pgcli -h -p 5433 -U postgres could not translate host name "-p" to address: Name or service not known [root@localhost ~]# pgcli -h 127.0.0.1 -p 5433 -U postgres -d postgres Password: Version: 0.20.1 Chat: https://gitter.im/dbcli/pgcli Mail: https://groups.google.com/forum/#!forum/pgcli Home: http://pgcli.com postgres> Time: 0.000s postgres> select count(*) from public.a0; +---------+ | count | |---------| | 0 | +---------+ SELECT 1 Time: 0.030s
图1.可以看到pgcli对sql语句有语法高亮显示。
图2.可以看到pgcli对输入有自动提示功能（在简单的测试环境下反应速度很理想的，其他情况有待测试）。
备注：本文仅限于测试试用，需要结合其他测试工具进一步测试。不适用于生产环境。