PGCLI: Python package for a interactive Postgres CLI
PGCLI is a Python package that is used as an interactive command-line interface for the PostgreSQL database server. For following with the article, make sure you have PostgreSQL installed and set up in your local system. You can follow the guide for Windows, Linux and macOS from the guide.
Also for performing any operations or testing out the package, create a simple database of your choice, There should be enough data for you to work with as it is just a REPL for running queries in a Postgres database.
As pgcli is a python package, it can be installed with pip.
pip install pgcli
If you want to set up the package in an isolated environment, you can use the virtualenv. Following are the steps to install and setup pgcli in a virtual environment in python
pip install virtualenv virtualenv venv Windows: venv\Scripts\activate Linux/macos: source venv/bin/activate pip install pgcli
To make sure, you have installed pgcli in your system, you can execute the following command:
After executing the command, the output should give out a particular/latest version of the package.
$ pgcli --version Version: 3.4.1
Connecting to a database
Now, you can select a particular database that you want to work with. It should at least contain some tables(at least one) to work with. As we will perform reading records and other operations from pgcli.
pgcli <local_database_name> -U postgres
Here Postgres can be your user name(-U) which by default is postgres and the password is the master password for your postgres server on your system. The local database needs to be there in the postgres databases. I have selected the EMNS database in my local postgres server.
You also have multiple options to select and use in it like the
Get a List of all the databases for a user
We can obtain the list of all the databases in the postgres for a specific user using the list option.
pgcli --list -u postgres
As we can the list of all the databases on the postgres server with the Postgres user.
Running Queries in the repl
We can perform any queries as you would normally perform on the query tool in the pgadmin panel. If you want a simple guide on getting started with Postgres commands and queries, you can check this article.
For example, let us run a simple SELECT query in a particular database.
So, as we can we were able to execute SQL queries in the python package which is a CLI for interacting with the postgres server as a query tool.
We can even connect to a remote postgres database using some parameters or the URL itself.
pgcli 'postgresql://user:password@nlocalhost:port/db_name' OR pgcli -h localhost -U user -W '@postgres' -d db_name
We can use either of the commands to connect to a remote database in pgcli. The first command is using a single string that contains all the config data about the database and the host in a URL format. Whereas in the second command, we have to pass separate data in the form of parameters.
For further options and commands in pgcli, you can get help from the –help command:
This will result in a list of commands and options which are available in the pgcli package to be used from the command line.
Thus, we can get more options like no-password or row limit for displaying only a few records in a query and so on.
So, pgcli is a command-line tool that can be used very similarly to the query tool in the pgAdmin provided by Postgres server.