DbRheo-CLI: Command-line tool for manipulating databases and analyzing data using natural language

DbRheo-CLI is a command line agent tool for database operations and data analysis. It allows users to give commands in natural language, which the tool converts into safe SQL queries and executes. The tool also automatically explores and analyzes the structure of the database and has a built-in risk assessment system that detects dangerous operations in advance and warns about them. In addition to direct database manipulation, DbRheo-CLI has an integrated Python environment that supports writing and executing Python code directly from the command line for data analysis, data visualization and automated scripting. The results of queries and analysis can be easily exported to various formats such as CSV, JSON and Excel. It currently supports many mainstream databases such as PostgreSQL, MySQL and SQLite.

Function List

• natural language query: Operate databases directly in the language of everyday speech.
• Intelligent SQL Generation:: Automatically convert user's natural language commands into optimized and secure SQL query statements.
• Exploring Database Architecture: You can dynamically analyze and display information about the table structure of a database.
• Risk assessment systems:: The system performs pre-testing and alerts users before performing risky database operations (e.g., deleting data).
• Executing Python code: Support for running Python code inside the tool for analyzing data, making charts, or performing automated tasks.
• Data export: Support saving query results as CSV, JSON and Excel files.
• Multi-database support: Compatible with PostgreSQL, MySQL, SQLite and many other databases.
• asynchronous processing: Asynchronous execution of tasks for improved runtime performance.
• streaming output: Real-time display of the process and results of command execution.

Using Help

The following is a detailed description of how to install and use DbRheo-CLI, a command line tool.

Environmental requirements

Before starting the installation, make sure your computer meets the following conditions:

• Python: Version 3.9 or higher is required.
• API key: You need to have a Google or OpenAI API key for driving natural language processing functions.

Installation steps

1. Clone Code Repository
   First, open your computer's terminal (command line interface) and use thegitcommand clones the project's code locally from GitHub.

   git clone https://github.com/Din829/DbRheo-CLI.git
   

   Then go to the project directory.

   cd DbRheo-CLI
   

2. Installation of dependent libraries
   There are a number of third-party Python libraries required to run the project, which are documented in therequirements.txtfile. Use thepipcommand to install them.

   pip install -r requirements.txt
   

3. Configuring Environment Variables
   The project contains a file named.env.examplethe example environment variable file. You need to copy this file and rename it.env, and then fill in your own configuration information.

   cp .env.example .env
   

   Next, use a text editor to open the.envfile, find and set the following:

   • GOOGLE_API_KEY maybe OPENAI_API_KEY: Fill in at least one of these two fields with your API key.
   • Database Connection Information: Depending on the type of database you want to connect to (e.g. PostgreSQL, MySQL, or SQLite), fill in the host, port, username, password, and database name information.
4. Launching the command line tool
   After all configurations are complete, go to thepackages/clidirectory, runcli.pyscript to start the program.

   cd packages/cli
   python cli.py
   

   After a successful launch, you will seeDbRheo>prompt, indicating that you can now begin entering commands.

How to use

The primary mode of operation of the DbRheo-CLI is to enter natural language instructions or specific commands on its interactive command line.

• View Help:
  If you're not sure what commands are available, you can enter the/helpto get help information.
• Select Language Model:
  If you have multiple API keys configured at the same time, you can use the/modelcommand to select the bigram model to be used for the current dialog.

Basic Usage Examples

1. Lookup Table Structure
   You can use natural language to ask about the structure of a table in a database.

   DbRheo> 告诉我users表的结构是什么
   ```    工具会返回该表的字段、数据类型等详细信息。
   

2. Query Data
   You can use natural language to make the tool query the data, such as querying the latest 10 users.

   DbRheo> 帮我查询最新的10个用户
   

   The tool will automatically generate the corresponding SQL statement SELECT * FROM users ORDER BY created_at DESC LIMIT 10; and execute it, and then show the results.

Example of data analysis

You can call Python directly from the command line for data analysis and visualization.

Suppose you want to analyze sales data and generate a chart, you can enter it like this:

DbRheo> 使用Python分析并可视化销售数据

tool generates and executes a piece of Python code that uses thepandasThe library reads the sales data from the database and then uses thematplotlibThe library generates a bar chart showing monthly sales trends and saves the chart as an image filesales_analysis.pngThe

Advanced SQL Queries

For complex query requirements, you can also describe them directly in natural language.

For example, you need to count the total amount of orders by month:

DbRheo> 创建一个销售数据的月度汇总

The tool generates an SQL statement similar to the one below to complete the aggregation query.

SELECT
DATE_TRUNC('month', order_date) as month,
SUM(amount) as total_sales
FROM orders
GROUP BY month
ORDER BY month;

application scenario

1. Quick data query
   For team members who are not familiar with SQL (e.g., product managers, operations staff), they can use natural language to get the data they need directly from the database, such as "querying the number of users who signed up last week", without having to wait for developer support.
2. Data analysis and reporting
   Data analysts can perform real-time analysis of data in the database, generate statistics and visualization charts, and export the results to Excel or CSV files for report production, using natural language commands combined with Python scripts directly from the command line tool.
3. Exploring Database Architecture
   Developers approaching a new database can quickly understand the database design and table relationships by using natural language commands such as "tell me the structure of a table" or "list all tables" without the need for heavyweight database management tools.
4. Secure Database Operation
   When database administrators perform some high-risk operations (such as updating or deleting data), they can use the tool's risk assessment function to understand the possible impact of the operation in advance and prevent data loss due to misuse.

QA

1. What types of databases does this tool support?
   Currently it mainly supports PostgreSQL, MySQL/MariaDB and SQLite databases. At the same time , it uses plug-in architecture , you can write new adapters to extend support for other types of databases .
2. Do I have to pay to use this tool?
   The tool itself is open source and free to use. However, it needs to call big language modeling APIs provided by companies like Google or OpenAI, and these API service providers charge you based on how much you use them.
3. Are my database connection information and API keys secure?
   Your database connection information and API key are stored locally in the.envfile, it is not uploaded to any server and is therefore safe. You need to make sure to keep your local environment safe.
4. What if the SQL statement generated by the tool does not meet my expectations?
   The tool displays the generated statement before executing the SQL. You can check if the statement is correct. If the result is not as expected, you can try a different, clearer and more specific natural language instruction to guide the tool to generate the correct SQL.