MyCLI is a very easy-to-use command line (CLI) command line interface for the popular relational database management systems MySQL, MariaDB, and Percona which supports auto-completion and syntax highlighting. It is currently the only RDMS CLI interface that supports such features.
The auto-completion feature is immensely useful when you write queries. It allows you to write queries faster and with ease. The REPL (Read, Eval, Print, Loop) will pop up a suggestion menu as soon as you start typing.
MyCLI was developed by Amjith Ramanujam after a successful Kickstarter campaign. MyCLI is built using the prompt_toolkit and requires Python 2.7, 3.3, 3.4, 3.5, and 3.6. It supports secure connections over SSL to the MySQL server.
MyCLI uses BPython which is a Python REPL that has auto-completion, syntax highlighting and displays the doc string of functions as you type. This article covers installation and usages of MyCLI in any Linux flavours.
The suggestions that MyCLI is context sensitive based on the position of the cursor. eg: Only tables are suggested after the FROM keyword, only column names are suggested after the WHERE clause.
Installating MyCLI
Use python package installer (PIP) to install My-CLI. You need to have Python PIP installed in your system already. Install PIP using following command in the terminal.
//For Ubuntu or it's variants use the command below: # apt-get install python-pip // For CentOS use the following # yum -y install python-pip (CentOS)
Now, we are going to check the version of pip and then we can install MyCLI.
# pip -V # pip install -U mycli
Alternatively, for people using Ubuntu or any of the Debian variants, you can easily use apt-get to easily install it.
# sudo apt-get install mycli
At this point, we want to verify that we have successfully installed MyCLI by just typing the command to display the version and some help text. Type the command below to check:
# mycli --version # mycli --help
How to Use MyCLI
The primary feature is auto-completion as you type for SQL keywords as well as tables, views and columns in the database apart from syntax highlighting that uses Pygments.
Smart-completion which is enabled by default will suggest context-sensitive completion. It supports multi-line queries and SSL connections. You can save your frequently used query using \fs alias query and execute it with \f alias whenever you need and time sql statements and render a table.
Three configuration files are generated in the home directory. They are ~/.mycli-history, ~/.mycli.log and ~/.myclirc. The history file contains all the SQL statements and the timestamp that you have executed.
The log files contain the logs of My-CLI. Default log level is INFO. The other possible values: “CRITICAL”, “ERROR”, “WARNING”, and “DEBUG”. “NONE” disables logging. The configuration file is ~/.myclirc. These files get created when you launch mycli for the first time. Another cool feature is its ability to log every query and its results to a file.
This is disabled by default. You can enable this by uncommenting the following line in ~/.myclirc.
The default format for table output is ‘psql’. Change the format that suits you. The other options are psql, plain, simple, grid, fancy_grid, pipe, orgtbl, rst, mediawiki, html, latex, latex_booktabs, tsv.
MyCLI can be used n much the same way as the typicla mysql, to connect to a database you use the command below:
# mycli -h localhost -u root employees
In this guide, we showed how to install and use mycli with simple commands in Linux., in conclusion, we want to list all the features of MyCLI
- Supports auto-completion while typing SQL keywords as well as tables, views and columns in the database.
- Also, supports smart-completion which is enabled by default and will offer suggestions for context-sensitive completion.
- Supports syntax highlighting using Pygments.
- Support for SSL connections.
- Offers support for multiline queries.
- It optionally logs every query and its output to a file (note that this is disabled by default).
- Allows you to save favorite queries (save a query using \fs alias and run it with \f alias).
- Supports timing of SQL statements and table rendering.
- Prints tabular data in an appealing way.
