NO MORE SQL FOR YOU!
Welcome to Ask My DB, an innovative project designed to interact with PostgreSQL and MySQL databases using natural language prompts. This project leverages the power of OpenAI's GPT-4 to generate SQL queries based on user input and seamlessly executes these queries on your database. The results are then formatted into a CSV file for easy consumption.
- Natural Language Processing: Generate SQL queries using GPT-4 based on user prompts.
- Database Integration: Connects to PostgreSQL and MySQL databases, retrieves schema information, and executes queries.
- CSV Export: Converts query results into CSV files with unique timestamps to avoid overwriting.
- Modular Design: Clean separation of concerns with dedicated classes for database interaction, GPT query generation, and core logic handling.
project_directory/
βββ .conf/
β βββ .env.template
βββ src/
β βββ __init__.py
β βββ gpt_asker.py
β βββ querier_db.py
β βββ queries_core.py
β βββ main.py
β βββ visual/
β β βββ ansi_colors.py
β β βββ ascii_table.py
β βββ queriers_db/
β βββ db_factory.py
β βββ querier_db.py
β βββ querier_db_mysql.py
β βββ querier_db_postgres.py
βββ tests/
β βββ __init__.py
β βββ conftest.py
β βββ test_gpt_asker.py
β βββ test_querier_db.py
β βββ test_queries_core.py
βββ .gitignore
βββ docker-compose.yml
βββ requirements.txt
βββ setup.py
βββ README.md
- Python 3.8 or higher
- PostgreSQL or MySQL database
- Docker (optional for running databases locally)
- OpenAI API key
-
Clone the Repository:
git clone https://github.com/yourusername/ask-my-db.git cd ask-my-db
-
Setup Environment:
cp .conf/.env.template .conf/.env
Edit the
.conf/.env
file to include your database and OpenAI credentials. -
Install Dependencies:
pip install -r requirements.txt
-
Run the Database: You can either run PostgreSQL or MySQL locally or use Docker:
docker-compose up -d
-
Initialize the Database: Ensure your database is set up and has the required tables. You can use the provided migration scripts or set it up manually.
-
Run the Application:
python src/main.py
-
Interact with the System: Enter your natural language query when prompted. The system will generate a SQL query, execute it, and save the results to a uniquely named CSV file. NOTE: the prompt is being hardcoded in main.py you must modify it here :)
-
Create a Prompt: Prompts are read from a JSON file located at
examples/prompts.json
or can be entered manually. The JSON file can contain one or more prompts. If there are no prompts in the file, you will be prompted to enter one manually. Here's an example of multiple prompts in the JSON file:{ "prompts": [ "Get all users with their names and companies they work for.", "Get all products with their prices and stock quantities." ] }
If you are entering the prompt manually, you only need to write the prompt directly when prompted by the application, without using JSON format. For example:
Qrite your prompt here: Get all users with their names and companies they work for.
-
Run the Main Script: When you run the
main.py
script, it will process the prompts, generate SQL queries using GPT-4, execute them, and display the results in an ASCII table format. Additionally, the results will be saved to a CSV file.python src/main.py
-
Review the Output: The output will be printed on the console as an ASCII table. The generated CSV files will be saved in the specified directory.
PROMPT: Get all users with their names and companies they work for. Processing... +---------+-------------+ | name | company | +---------+-------------+ | John | Company A | | Jane | Company B | +---------+-------------+
This example demonstrates how to use the system to generate and execute SQL queries based on natural language prompts and how to interpret the results.
To run the tests, ensure you have pytest
installed and run the following command:
pytest
The tests will verify the functionality of the database interactions, GPT prompt handling, and the CSV file generation.
We welcome contributions to enhance the functionality and features of this project. Please fork the repository, create a new branch, and submit a pull request with your changes.
This project is licensed under the MIT License.
For any inquiries or support, please reach out to [email protected].
Thank you for using Ask My DB! We hope this tool makes your database interactions more intuitive and efficient. Happy querying!
Author: Ignacio Rigoni
Repository: GitHub Repository URL