Postgres CLI with autocompletion and syntax highlighting

Overview

A REPL for Postgres

Build Status Code coverage report Latest Version Code Health Gitter Chat

This is a postgres client that does auto-completion and syntax highlighting.

Home Page: http://pgcli.com

MySQL Equivalent: http://mycli.net

screenshots/pgcli.gif

screenshots/image01.png

Quick Start

If you already know how to install python packages, then you can simply do:

$ pip install -U pgcli

or

$ sudo apt-get install pgcli # Only on Debian based Linux (e.g. Ubuntu, Mint, etc)
$ brew install pgcli  # Only on macOS

If you don't know how to install python packages, please check the detailed instructions.

If you are restricted to using psycopg2 2.7.x then pip will try to install it from a binary. There are some known issues with the psycopg2 2.7 binary - see the psycopg docs for more information about this and how to force installation from source. psycopg2 2.8 has fixed these problems, and will build from source.

Usage

$ pgcli [database_name]

or

$ pgcli postgresql://[user[:password]@][netloc][:port][/dbname][?extra=value[&other=other-value]]

Examples:

$ pgcli local_database

$ pgcli postgres://amjith:[email protected]:5432/app_db?sslmode=verify-ca&sslrootcert=/myrootcert

For more details:

$ 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 PATH          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 / --no-warn      Warn before running a destructive query.
  --help                  Show this message and exit.

pgcli also supports many of the same environment variables as psql for login options (e.g. PGHOST, PGPORT, PGUSER, PGPASSWORD, PGDATABASE).

The SSL-related environment variables are also supported, so if you need to connect a postgres database via ssl connection, you can set set environment like this:

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

Features

The pgcli is written using prompt_toolkit.

  • Auto-completes as you type for SQL keywords as well as tables and columns in the database.

  • Syntax highlighting using Pygments.

  • Smart-completion (enabled by default) will suggest context-sensitive completion.

    • SELECT * FROM <tab> will only show table names.
    • SELECT * FROM users WHERE <tab> will only show column names.
  • Primitive support for psql back-slash commands.

  • Pretty prints tabular data.

Config

A config file is automatically created at ~/.config/pgcli/config at first launch. See the file itself for a description of all available options.

Contributions:

If you're interested in contributing to this project, first of all I would like to extend my heartfelt gratitude. I've written a small doc to describe how to get this running in a development setup.

https://github.com/dbcli/pgcli/blob/master/DEVELOP.rst

Please feel free to reach out to me if you need help. My email: [email protected], Twitter: @amjithr

Detailed Installation Instructions:

macOS:

The easiest way to install pgcli is using Homebrew.

$ brew install pgcli

Done!

Alternatively, you can install pgcli as a python package using a package manager called called pip. You will need postgres installed on your system for this to work.

In depth getting started guide for pip - https://pip.pypa.io/en/latest/installing.html.

$ which pip

If it is installed then you can do:

$ pip install pgcli

If that fails due to permission issues, you might need to run the command with sudo permissions.

$ sudo pip install pgcli

If pip is not installed check if easy_install is available on the system.

$ which easy_install

$ sudo easy_install pgcli

Linux:

In depth getting started guide for pip - https://pip.pypa.io/en/latest/installing.html.

Check if pip is already available in your system.

$ which pip

If it doesn't exist, use your linux package manager to install pip. This might look something like:

$ sudo apt-get install python-pip   # Debian, Ubuntu, Mint etc

or

$ sudo yum install python-pip  # RHEL, Centos, Fedora etc

pgcli requires python-dev, libpq-dev and libevent-dev packages. You can install these via your operating system package manager.

$ sudo apt-get install python-dev libpq-dev libevent-dev

or

$ sudo yum install python-devel postgresql-devel

Then you can install pgcli:

$ sudo pip install pgcli

Docker

Pgcli can be run from within Docker. This can be useful to try pgcli without installing it, or any dependencies, system-wide.

To build the image:

$ docker build -t pgcli .

To create a container from the image:

$ docker run --rm -ti pgcli pgcli <ARGS>

To access postgresql databases listening on localhost, make sure to run the docker in "host net mode". E.g. to access a database called "foo" on the postgresql server running on localhost:5432 (the standard port):

$ docker run --rm -ti --net host pgcli pgcli -h localhost foo

To connect to a locally running instance over a unix socket, bind the socket to the docker container:

$ docker run --rm -ti -v /var/run/postgres:/var/run/postgres pgcli pgcli foo

IPython

Pgcli can be run from within IPython console. When working on a query, it may be useful to drop into a pgcli session without leaving the IPython console, iterate on a query, then quit pgcli to find the query results in your IPython workspace.

Assuming you have IPython installed:

$ pip install ipython-sql

After that, run ipython and load the pgcli.magic extension:

$ ipython

In [1]: %load_ext pgcli.magic

Connect to a database and construct a query:

In [2]: %pgcli postgres://someone@localhost:5432/world
Connected: someone@world
someone@localhost:world> select * from city c where countrycode = 'USA' and population > 1000000;
+------+--------------+---------------+--------------+--------------+
| id   | name         | countrycode   | district     | population   |
|------+--------------+---------------+--------------+--------------|
| 3793 | New York     | USA           | New York     | 8008278      |
| 3794 | Los Angeles  | USA           | California   | 3694820      |
| 3795 | Chicago      | USA           | Illinois     | 2896016      |
| 3796 | Houston      | USA           | Texas        | 1953631      |
| 3797 | Philadelphia | USA           | Pennsylvania | 1517550      |
| 3798 | Phoenix      | USA           | Arizona      | 1321045      |
| 3799 | San Diego    | USA           | California   | 1223400      |
| 3800 | Dallas       | USA           | Texas        | 1188580      |
| 3801 | San Antonio  | USA           | Texas        | 1144646      |
+------+--------------+---------------+--------------+--------------+
SELECT 9
Time: 0.003s

Exit out of pgcli session with Ctrl + D and find the query results:

someone@localhost:world>
Goodbye!
9 rows affected.
Out[2]:
[(3793, u'New York', u'USA', u'New York', 8008278),
 (3794, u'Los Angeles', u'USA', u'California', 3694820),
 (3795, u'Chicago', u'USA', u'Illinois', 2896016),
 (3796, u'Houston', u'USA', u'Texas', 1953631),
 (3797, u'Philadelphia', u'USA', u'Pennsylvania', 1517550),
 (3798, u'Phoenix', u'USA', u'Arizona', 1321045),
 (3799, u'San Diego', u'USA', u'California', 1223400),
 (3800, u'Dallas', u'USA', u'Texas', 1188580),
 (3801, u'San Antonio', u'USA', u'Texas', 1144646)]

The results are available in special local variable _, and can be assigned to a variable of your choice:

In [3]: my_result = _

Pgcli only runs on Python3.6+ since 2.2.0, if you use an old version of Python, you should use install pgcli <= 2.2.0.

Thanks:

A special thanks to Jonathan Slenders for creating Python Prompt Toolkit, which is quite literally the backbone library, that made this app possible. Jonathan has also provided valuable feedback and support during the development of this app.

Click is used for command line option parsing and printing error messages.

Thanks to psycopg for providing a rock solid interface to Postgres database.

Thanks to all the beta testers and contributors for your time and patience. :)

Comments
  • Respect \pset pager on expected behavior

    Respect \pset pager on expected behavior

    \pset pager has three possible values: "always", "on", and "off". pgcli previously treated all non-"off" values as "always". This change implements the expected behavior, which is to use the pager when the output is larger than the terminal height (See \pset pager in https://www.postgresql.org/docs/9.2/static/app-psql.html). Pgcli adds to this and also uses the pager when the output is wider than the terminal width.

    Fixes #813

    Checklist

    • [x] I've added this contribution to the changelog.md.
    • [x] I've added my name to the AUTHORS file (or it's already there).
    opened by maxrothman 42
  • possible enhancing - using pspg for formatting

    possible enhancing - using pspg for formatting

    Description

    When I played with pgcli I had a opinion so formatting result is slow. Is possible to disable formatting in Python? pspg can read csv files now, and can format to tabular output.

    Your environment

    • [ ] Please provide your OS and version information.
    • [ ] Please provide your CLI version.
    • [ ] What is the output of pip freeze command.
    opened by okbob 32
  • Amjith/specials refactor

    Amjith/specials refactor

    Reviewer: @darikg & @j-bennet

    This is a complete rewrite of the specials package. A few things have changed.

    1. pgspecial is now a directory with all the relevant files to make it into a standalone package.
    2. \c, \connect and use are now handled by pgspecial instead of pgexecute.py (which was always felt icky). We now pass in the PGExecute as db_obj to the specials package. The idea is that db_obj should have a connect() call which will be invoked to change the database. That way whoever decides to use the pgspecial can provide their own db_obj and still achieve the \c functionality.
    3. There are @export decorators sprinkled around the pgspecial code. The export decorator is defined in the __init__.py file. When a function is decorated by this @export it will be exposed by that package and available to use when that package is imported. It is a technique I picked up from David Beazly's tutorial at this PyCon 2015. http://www.dabeaz.com/modulepackage/index.html. Check the slides 53 to 56 on this pdf: http://www.dabeaz.com/modulepackage/ModulePackage.pdf for more details. BTW, that video is worth watching. :)

    I'm tagging both of you on this PR since you two have been the most active in that part of the code. I've ran through most of the special commands by hand to make sure I didn't break anything.

    Give it a whirl.

    opened by amjith 32
  • Make autocomplete schema-aware

    Make autocomplete schema-aware

    This PR would fix #22, and also I think #39 as a side-effect

    Issues:

    • I used pandas DataFrames for working with database metadata -- I found them a lot more flexible than a pure standard lib dict/list based approach. But I'm not sure how you'd feel about adding pandas as a requirement.
    • We don't have any tests for "DoubleQuoted" identifiers -- I'm not confident I didn't break that.
    opened by darikg 32
  • [experimental] learn keyword preferences

    [experimental] learn keyword preferences

    Spinning this off from discussion in #377 since they're (mostly) orthogonal. I was originally a little skeptical of using any learning algorithm because

    1. It might be annoying to have different suggestions on different installations
    2. It might preclude improving the base suggestion engine. Right now sqlcomplete suggests only the very vague keyword category when it could take greater advantage of syntactical restrictions. Given SELECT * F, FREEZE is a pretty silly suggestion because it's just not valid sql.

    On the other hand, basing suggestions on syntax requires syntactically valid input, and a simple learning algorithm could be much more reliable in the middle of editing temporarily invalid queries. In the long run, the learning approach could move to estimating second- or third-order keyword transitions and be pretty powerful.

    So this PR offers a basic experiment of the learning approach. Measure zeroth-order keyword probabilities, and rank keywords thereby.

    Open questions:

    1. How should learning be shared between concurrent pgcli sessions? One global state or one state per pgcli instance?
    2. Should we (and if so, how do) we save keyword preferences between sessions?
    opened by darikg 30
  • Cherry-picked prompt-toolkit 2.0 changes.

    Cherry-picked prompt-toolkit 2.0 changes.

    Changes for prompt-toolkit 2.0, based on current master.

    Cherry-picked from https://github.com/dbcli/pgcli/pull/826.

    @jonathanslenders I cherry-picked because I have no permissions to rebase your branch, but we can merge either PR, provided it's in line with current pgcli master.

    Problems noticed so far:

    • ~styles are not applied~ fixed
    • cli takes longer to start. I had to increase "show help" integration test timeout.
    • ~cli won't run under 2.7~ fixed, it was unicode literals
    • ~iocommands integration test fail on editor command~ fixed
    • ~named_queries integration test fail~ fixed

    Named query integration test failed because of this warning printed under pexpect (which is not a real terminal):

    https://github.com/jonathanslenders/python-prompt-toolkit/blob/b8d09e971e5fbad76d3c5bda4b47063e4c0644b8/prompt_toolkit/application/application.py#L706

    @jonathanslenders Is there a way to suppress this kind of error output for tests? perhaps is you use warnings vs writing to stdout?

    opened by j-bennet 29
  • Perform auto-completion refresh in background.

    Perform auto-completion refresh in background.

    @dbcli/vcli-core @dbcli/pgcli-core

    This is my first attempt at making the auto-completion refresh in a background thread. I tried it on a database with 40,000 tables in them and I can see a noticeable different in the startup time. I could get more granular by spawning a thread for each type of completion (eg: tables, views, columns etc), but that'll require some granular locking on data-structures.

    Since we get a tremendous speed boost with just one thread, I'm not motivated to do multiple threads.

    Please pitch in and let me know if there are better ways of doing this.

    opened by amjith 29
  • Traceback on first run

    Traceback on first run

    Ubuntu 14.04, Python 2.7.3 (inside an activated virtualenv) pgcli==0.10.0 (installed inside activated virtualenv)

    I get the following...

    665 18:30:11 [~/code/ottoman] J:0 venv λ customcart ▼ pgcli ottoman
    Traceback (most recent call last):
      File "/home/vagrant/code/ottoman/venv/bin/pgcli", line 5, in <module>
        from pkg_resources import load_entry_point
      File "/home/vagrant/code/ottoman/venv/local/lib/python2.7/site-packages/setuptools-0.6c11-py2.7.egg/pkg_resources.py", line 2603, in <module>
      File "/home/vagrant/code/ottoman/venv/local/lib/python2.7/site-packages/setuptools-0.6c11-py2.7.egg/pkg_resources.py", line 666, in require
      File "/home/vagrant/code/ottoman/venv/local/lib/python2.7/site-packages/setuptools-0.6c11-py2.7.egg/pkg_resources.py", line 565, in resolve
    pkg_resources.DistributionNotFound: Click
    668 18:31:29 [~/code/ottoman] J:0 venv λ customcart ▼ psql ottoman
    psql (9.3.5)
    Type "help" for help.
    
    ottoman=# \q
    
    bug 
    opened by treystout 24
  • Cannot handle multibyte characters (ie. Japanese)

    Cannot handle multibyte characters (ie. Japanese)

    It seems, that multibyte characters are not supported at all.

    The following example uses Japanese characters:

    With PSQL:

    testdb=# SELECT '日本語' AS japanese;
     japanese 
    ----------
     日本語
    (1 row)
    

    With PGCLI:

    testdb> SELECT '日本語' AS japanese;
    'ascii' codec can't decode byte 0xe6 in position 0: ordinal not in range(128)
    

    You will receive the same error for every query, that has to deal with a table containing multibyte characters.

    opened by dkastl 23
  • UserWarning: The psycopg2 wheel package will be renamed from release 2.8

    UserWarning: The psycopg2 wheel package will be renamed from release 2.8

    Description

    The psycopg2 package display's a user warning when calling pgcli. They have a blog post describing why this warning is displayed: http://initd.org/psycopg/articles/2018/02/08/psycopg-274-released/.

    Warning Message

    UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use "pip install psycopg2-binary" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.
    

    Your environment

    • pgcli is installed in a virtual environment in my home .local folder.
    • Symlink pgcli in my .local/bin folder to the virtual instance of pgcli.

    System Information

    • [x] Please provide your OS and version information.
      Ubuntu 16.04.3 LTS (xenial, x86_64)

    • [x] Please provide your CLI version.
      pgcli Version: 1.8.2

    • [x] What is the output of pip freeze command.

    cli-helpers==1.0.1
    click==6.7
    configobj==5.0.6
    humanize==0.5.1
    pgcli==1.8.2
    pgspecial==1.9.0
    prompt-toolkit==1.0.15
    psycopg2==2.7.4
    Pygments==2.2.0
    setproctitle==1.1.10
    six==1.11.0
    sqlparse==0.2.4
    tabulate==0.8.2
    terminaltables==3.1.0
    wcwidth==0.1.7
    
    opened by ncFoCo 22
  • Iryna's Editor Command PR

    Iryna's Editor Command PR

    This PR is a cleanup of #213 to remove extraneous commits added in due to the repo move.

    I've cleaned up the commit history. I should have waited to move the repo after merging all pending PRs. That was my bad.

    @j-bennet Doe this look good?

    I'll review the changes and merge it in.

    opened by amjith 22
  • Foreign server does not work, works fine with psql

    Foreign server does not work, works fine with psql

    Description

    Summary: Attempting simple query on a foreign server, docs: https://www.postgresql.org/docs/current/catalog-pg-foreign-server.html

    Detail: I have this schema:

    my_db> \dn
    +----------+-------------------+
    | Name     | Owner             |
    |----------+-------------------|
    | partman  | my_usr      |
    | foo      | root              |
    | public   | admin          |
    +----------+-------------------+
    

    Note my postgres user has access to all these schemas. All queries that I'm about to describe work as expected in psql.

    Note that foo is actually a foreign server:

    select 
        srvname as name, 
        srvowner::regrole as owner, 
        fdwname as wrapper, 
        srvoptions as options
    from pg_foreign_server
    join pg_foreign_data_wrapper w on w.oid = srvfdw;
    

    returns:

       name   | owner |   wrapper    |                          options                           
    ----------+-------+--------------+------------------------------------------------------------
     bar | root  | postgres_fdw | {host=myvpn.com,dbname=my_db,port=5432}
    

    Queries work as expected with the partman schema, even autocomplete works fine:

    select * from partman.custom_time_partitions;
    

    Hitting Enter, this query succeeds.

    Interestingly, if I try the same thing for bar schema, autocomplete still works to auto-complete the table in that schema. Even \d bar.accounts works fine:

    \d bar.accounts
    

    Shows:

    +--------------------+--------------------------+-----------+-------------+
    | Column             | Type                     | Modifiers | FDW Options |
    |--------------------+--------------------------+-----------+-------------|
    | account_id         | text                     |  not null | 8           |
    | account_number     | text                     |  not null | 8           |
    ...
    

    However, when I try to run:

    select * from bar.accounts
    

    The repl hangs for a couple of minutes and then I get this:

    could not connect to server "bar"
    DETAIL:  could not connect to server: Connection timed out
    	Is the server running on host "myvpn.com" (10.10.21.182) and accepting
    	TCP/IP connections on port 5432?
    

    This works w/ psql

    Note also that starting pgcli with that db works:

    pgcli -h myvpn.com -U root -d bar
    

    All works as expected thereafter

    DEBUG LOGGING

    Turning on debug logging I see this:

    2022-12-31 02:17:29,065 (99983/MainThread) pgcli.pgexecute DEBUG - Trying a pgspecial command. sql: 'select * from bar.accounts'
    2022-12-31 02:17:29,065 (99983/MainThread) pgcli.pgexecute DEBUG - Regular sql statement. sql: 'select * from bar.accounts'
    2022-12-31 02:19:39,946 (99983/MainThread) pgcli.pgexecute ERROR - sql: 'select * from bar.accounts', error: SqlclientUnableToEstablishSqlconnection('could not connect to server "bar"\nDETAIL:  could not connect to server: Connection timed out\n\tIs the server running on host "myvpn.com" (10.10.23.182) and accepting\n\tTCP/IP connections on port 5432?')
    2022-12-31 02:19:39,957 (99983/MainThread) pgcli.pgexecute ERROR - traceback: 'Traceback (most recent call last):\n  File "/Users/.../pyenv/lib/python3.10/site-packages/pgcli/pgexecute.py", line 373, in run\n    yield self.execute_normal_sql(sql) + (sql, True, False)\n  File "/Users/.../pyenv/lib/python3.10/site-packages/pgcli/pgexecute.py", line 423, in execute_normal_sql\n    cur.execute(split_sql)\n  File "/Users/..../pyenv/lib/python3.10/site-packages/pgcli/pgexecute.py", line 69, in execute\n    super().execute(*args, **kwargs)\n  File "/Users/.../pyenv/lib/python3.10/site-packages/psycopg/cursor.py", line 728, in execute\n    raise ex.with_traceback(None)\npsycopg.errors.SqlclientUnableToEstablishSqlconnection: could not connect to server "bar"\nDETAIL:  could not connect to server: Connection timed out\n\tIs the server running on host "myvpn.com" (10.10.23.182) and accepting\n\tTCP/IP connections on port 5432?\n'
    2022-12-31 02:19:39,958 (99983/MainThread) pgcli.main DEBUG - headers: None
    2022-12-31 02:19:39,958 (99983/MainThread) pgcli.main DEBUG - rows: None
    2022-12-31 02:19:39,958 (99983/MainThread) pgcli.main DEBUG - status: '\x1b[31mcould not connect to server "bar"\nDETAIL:  could not connect to server: Connection timed out\n\tIs the server running on host "myvpn.com" (10.10.23.182) and accepting\n\tTCP/IP connections on port 5432?\x1b[0m'
    

    But, to emphasize, I can reach that domain. Even by just opening another terminal and trying w/ pgcli as mentioned above. I think it's something about proxying the connection. bar.accounts is behind a vpn. So connecting directly with

    pgcli -h myvpn.com -U root -d bar
    

    works fine but whatever connection is being tried internally, seems to try to connect with the IP address of the db in the private network.

    Your environment

    • [x] Please provide your OS and version information. Mac OS 12.1
    • [x] Please provide your CLI version. 3.5.0
    • [x] What is the output of pip freeze command.
    anyio==3.6.2
    appnope==0.1.3
    argon2-cffi==21.3.0
    argon2-cffi-bindings==21.2.0
    asttokens==2.0.8
    attrs==22.1.0
    backcall==0.2.0
    beautifulsoup4==4.11.1
    bleach==5.0.1
    certifi==2022.6.15
    cffi==1.15.1
    charset-normalizer==2.1.1
    cli-helpers==2.3.0
    click==8.1.3
    configobj==5.0.6
    debugpy==1.6.3
    decorator==5.1.1
    deepmerge==1.0.1
    defusedxml==0.7.1
    entrypoints==0.4
    executing==1.1.1
    fastjsonschema==2.16.2
    idna==3.3
    importlib-metadata==5.2.0
    ipykernel==6.16.1
    ipython==8.5.0
    ipython-genutils==0.2.0
    ipywidgets==8.0.2
    jaraco.classes==3.2.3
    jedi==0.18.1
    Jinja2==3.1.2
    jsonpath-ng==1.5.3
    jsonschema==4.15.0
    jupyter==1.0.0
    jupyter-console==6.4.4
    jupyter-server==1.21.0
    jupyter_client==7.4.3
    jupyter_core==4.11.2
    jupyterlab-pygments==0.2.2
    jupyterlab-widgets==3.0.3
    keyring==23.13.1
    MarkupSafe==2.1.1
    matplotlib-inline==0.1.6
    mistune==2.0.4
    more-itertools==9.0.0
    nbclassic==0.4.5
    nbclient==0.7.0
    nbconvert==7.2.2
    nbformat==5.7.0
    nest-asyncio==1.5.6
    notebook==6.5.1
    notebook_shim==0.2.0
    numpy==1.23.5
    oasapi==0.1.17
    packaging==21.3
    pandas==1.5.2
    pandocfilters==1.5.0
    parso==0.8.3
    pendulum==2.1.2
    pexpect==4.8.0
    pgcli==3.5.0
    pgspecial==2.0.1
    pickleshare==0.7.5
    ply==3.11
    prometheus-client==0.15.0
    prompt-toolkit==3.0.31
    psutil==5.9.1
    psycopg==3.1.7
    psycopg2==2.9.5
    ptyprocess==0.7.0
    pure-eval==0.2.2
    pycparser==2.21
    Pygments==2.13.0
    pyparsing==3.0.9
    pyrsistent==0.18.1
    python-dateutil==2.8.2
    pytz==2022.4
    pytzdata==2020.1
    PyYAML==6.0
    pyzmq==24.0.1
    qtconsole==5.3.2
    QtPy==2.2.1
    requests==2.28.1
    ruamel.yaml==0.17.21
    ruamel.yaml.clib==0.2.6
    Send2Trash==1.8.0
    setproctitle==1.3.2
    six==1.16.0
    sniffio==1.3.0
    soupsieve==2.3.2.post1
    sqlparse==0.4.3
    stack-data==0.5.1
    tabulate==0.9.0
    terminado==0.16.0
    tinycss2==1.2.1
    tornado==6.2
    traitlets==5.5.0
    typing_extensions==4.4.0
    urllib3==1.26.11
    wcwidth==0.2.5
    webencodings==0.5.1
    websocket-client==1.4.1
    widgetsnbextension==4.0.3
    zipp==3.11.0
    
    opened by marcoalban-hidalgo 0
  • convert to dual outputs: pager and stdout

    convert to dual outputs: pager and stdout

    regular output (mainly query results) goes to the pager. status output goes directly to stdout. both implement a consistent interface for easy mocking in tests.

    Description

    Picking up where #1383 left off.

    We agreed on the need to separate normal query result output from status output. The previous PR used stdout and stderr, but we decided instead to use the pager's stdin and pgcli's stdout as the two streams.

    Further, because there's a need to maintain order in the output lines even across the two streams, this PR inverts control and takes a DI approach, pushing "output emitters" down into the PGCli object. The previously pure "format_output" function is now "emit_output" which takes the injected emitters. For tests, we mock out the emitters with an implementation that collects the output lines in order for later assertions.

    Checklist

    • [X] I've added this contribution to the changelog.rst.
    • [X] I've added my name to the AUTHORS file (or it's already there).
    • [X] I installed pre-commit hooks (pip install pre-commit && pre-commit install), and ran black on my code.
    • [x] Please squash merge this pull request (uncheck if you'd like us to merge as multiple commits)
    opened by teepark 0
  • Add config option to always run with a single connection

    Add config option to always run with a single connection

    Description

    Add a config option for always running with a single connection. This is equivalent to always running with the --single-connection flag.

    This is a very minor change, but this flag seems like it's the type of thing that would be useful to have persist as a regular config setting; for people that use it, it can be annoying to always have to specify it.

    Leaving it defaulting to False to have the same behavior as before this change (not specifying the flag will not run in single-connection mode). When the config setting is False, users can still enable it in a non-persistent way by running with the flag in the same way as before.

    Checklist

    • [x] I've added this contribution to the changelog.rst.
    • [x] I've added my name to the AUTHORS file (or it's already there).
    • [x] I installed pre-commit hooks (pip install pre-commit && pre-commit install), and ran black on my code.
    • [x] Please squash merge this pull request (uncheck if you'd like us to merge as multiple commits)
    opened by andyscho 2
  • Echo notifications

    Echo notifications

    Description

    An initial implementation of #1359. If you run listen channelname and the channel receives a notification, the next time you run a statement, the notification will be shown (similar to psql).

    I'm not sure I'll have time to work on this further. If someone would like to take this and do what's necessary to get it merged (tests, confirm formatting, docs, ...) please do.

    Checklist

    • [ ] I've added this contribution to the changelog.rst.
    • [ ] I've added my name to the AUTHORS file (or it's already there).
    • [ ] I installed pre-commit hooks (pip install pre-commit && pre-commit install), and ran black on my code.
    • [x] Please squash merge this pull request (uncheck if you'd like us to merge as multiple commits)
    opened by amacfie 0
  • Allow defining a json file with preferred aliases

    Allow defining a json file with preferred aliases

    Description

    At $WORK we have a lot of tables with names like foo_noun_verb or foo_noun_related-noun_verb and so while the default aliasing is very helpful for shortening unwieldy names we do end up with lots of aliases like LEFT JOIN fnv on fnv2.id = fnv.fnv2_id

    This change will allow defining a json file of preferred aliases

    > cat ~/.config/pgcli/aliases.json
    {
        "foo_user": "user",
        "foo_user_group": "user_group"
    }
    

    so the alias suggestion for SELECT * FROM foo_user will be SELECT * FROM foo_user AS user instead of the default SELECT * FROM foo_user AS fu

    Checklist

    • [x] I've added this contribution to the changelog.rst.
    • [x] I've added my name to the AUTHORS file (or it's already there).
    • [x] I installed pre-commit hooks (pip install pre-commit && pre-commit install), and ran black on my code.
    • [x] Please squash merge this pull request (uncheck if you'd like us to merge as multiple commits)
    opened by rob-b 3
  • psql-like \crosstabview feature

    psql-like \crosstabview feature

    Description

    It'd be nice to be able to view a query result as a 2D table, such as can be done in psql with its \crosstabview command.

    For instance suppose you have a stock level table with the stock for each item per location. You can list all the stock levels like this:

    SELECT * FROM stock_level ORDER BY item_id, location_id;
     item_id | location_id | stock 
    ---------+-------------+-------
     IOC     | Harrogate   |     2
     IOC     | Skipton     |     3
     IOS     | Harrogate   |     1
     IOS     | Ilkley      |     6
     KMB     | Ilkley      |     3
     KMB     | Skipton     |     4
     TTR     | Harrogate   |     5
     TTR     | Ilkley      |     2
     TTR     | Skipton     |     1
    

    but it can be more useful to have just one row for each item, using columns for the locations. In psql that involves just running the same query with \crosstabview:

    SELECT * FROM stock_level ORDER BY item_id, location_id \crosstabview
     item_id | Harrogate | Skipton | Ilkley 
    ---------+-----------+---------+--------
     IOC     |         2 |       3 |       
     IOS     |         1 |         |      6
     KMB     |           |       4 |      3
     TTR     |         5 |       1 |      2
    

    Currently I switch from pgcli back to psql every time I want to do this.

    (Yes, Postgresql itself has an extension which provides a crosstab() function for construction queries that will do this. But that's much more involved to use, or involves writing out all the column headers (the locations in the above example), or both. psql's command is quick and useful enough to use on ad-hoc queries.)

    Your environment

    Ubuntu Linux 22.04 LTS pgcli Version: 3.4.1

    enhancement 
    opened by Smylers 0
Owner
dbcli
Better CLIs for Databases
dbcli
A Terminal Client for MySQL with AutoCompletion and Syntax Highlighting.

mycli A command line client for MySQL that can do auto-completion and syntax highlighting. HomePage: http://mycli.net Documentation: http://mycli.net/

dbcli 10.7k Jan 7, 2023
CLI for SQLite Databases with auto-completion and syntax highlighting

litecli Docs A command-line client for SQLite databases that has auto-completion and syntax highlighting. Installation If you already know how to inst

dbcli 1.8k Dec 31, 2022
AthenaCLI is a CLI tool for AWS Athena service that can do auto-completion and syntax highlighting.

Introduction AthenaCLI is a command line interface (CLI) for the Athena service that can do auto-completion and syntax highlighting, and is a proud me

dbcli 192 Jan 7, 2023
CLI utility for updating the EVE Online static data export in a postgres database

EVE SDE Postgres updater CLI utility for updating the EVE Online static data export postgres database. This has been tested with the Fuzzwork postgres

Markus Juopperi 1 Oct 29, 2021
Simple CLI for managing Postgres databases in Flask.

Overview Simple CLI that provides the following commands: flask psql create flask psql init flask psql drop flask psql setup: create → init flask psql

Daniel Reeves 21 Oct 3, 2022
🦎 A NeoVim plugin for highlighting visual selections like in a normal document editor!

?? HighStr.nvim A NeoVim plugin for highlighting visual selections like in a normal document editor! Demo TL;DR HighStr.nvim is a NeoVim plugin writte

Pocco81 222 Jan 3, 2023
Sink is a CLI tool that allows users to synchronize their local folders to their Google Drives. It is similar to the Git CLI and allows fast and reliable syncs with the drive.

Sink is a CLI synchronisation tool that enables a user to synchronise local system files and folders with their Google Drives. It follows a git C

Yash Thakre 16 May 29, 2022
A simple CLI based any Download Tool, that find files and let you stream or download thorugh WebTorrent CLI or Aria or any command tool

Privateer A simple CLI based any Download Tool, that find files and let you stream or download thorugh WebTorrent CLI or Aria or any command tool How

Shreyash Chavan 2 Apr 4, 2022
[WIP]An ani-cli like cli tool for movies and webseries

mov-cli A cli to browse and watch movies. Installation This project is a work in progress. However, you can try it out python git clone https://github

null 166 Dec 30, 2022
flora-dev-cli (fd-cli) is command line interface software to interact with flora blockchain.

Install git clone https://github.com/Flora-Network/fd-cli.git cd fd-cli python3 -m venv venv source venv/bin/activate pip install -e . --extra-index-u

null 14 Sep 11, 2022
AWS Interactive CLI - Allows you to execute a complex AWS commands by chaining one or more other AWS CLI dependency

AWS Interactive CLI - Allows you to execute a complex AWS commands by chaining one or more other AWS CLI dependency

Rafael Torres 2 Dec 10, 2021
Python-Stock-Info-CLI: Get stock info through CLI by passing stock ticker.

Python-Stock-Info-CLI Get stock info through CLI by passing stock ticker. Installation Use the following command to install the required modules at on

Ayush Soni 1 Nov 5, 2021
Yts-cli-streamer - A CLI movie streaming client which works on yts.mx API written in python

YTSP It is a CLI movie streaming client which works on yts.mx API written in pyt

null 1 Feb 5, 2022
A CLI tool for searching and watching videos on youtube with no spyware and MPV and yt-dlp

A CLI tool for searching and watching videos on youtube with no spyware and MPV and yt-dlp

TruncatedDinosour 3 Feb 22, 2022
Python wrapper and CLI utility to render LaTeX markup and equations as SVG using dvisvgm and svgo.

latex2svg Python wrapper and CLI utility to render LaTeX markup and equations as SVG using dvisvgm and svgo. Based on the original work by Tino Wagner

Matthias C. Hormann 4 Feb 18, 2022
A python script that enables a raspberry pi sd card through the CLI and automates the process of configuring network details and ssh.

This project is one script (wpa_helper.py) written in python that will allow for the user to automate the proccess of setting up a new boot disk and configuring ssh and network settings for the pi

Theo Kirby 6 Jun 24, 2021
pyNPS - A cli Linux and Windows Nopaystation client made with python 3 and wget

Currently, all the work is being done inside the refactoring branch. pyNPS - A cli Linux and Windows Nopaystation client made with python 3 and wget P

Everton Correia 45 Dec 11, 2022