Overview
The postgres_chatbot.py
script creates a chatbot that answers questions about a library database hosted on an Azure PostgreSQL Flexible Server. It uses a locally installed LLaMA 3.2 model via Ollama to convert natural language queries into PostgreSQL queries, executes them, and displays results in a command-line chat interface. The database schema includes Authors
, Books
, and Borrowers
tables.
Features
- Queries an Azure PostgreSQL database using natural language.
- Uses LLaMA 3.2 for SQL generation.
- Checks if the Ollama server is running and provides setup instructions.
- Handles invalid queries gracefully.
- User-friendly command-line interface.
Requirements
- Python 3.8 or higher
- Libraries:
psycopg2-binary
,ollama
- Ollama (latest version, e.g., 0.3.12+)
- LLaMA 3.2 model (installed via
ollama pull llama3.2
) - Azure PostgreSQL Flexible Server instance
Installation
# Install Python 3.8+ (if not already installed) # Linux sudo apt-get install python3 # macOS brew install python3 # Install required libraries pip install psycopg2-binary ollama # Install Ollama # Linux/macOS curl -fsSL https://ollama.com/install.sh | sh # Windows: Download from https://ollama.com/download # Pull LLaMA 3.2 model ollama pull llama3.2 # Start Ollama server (run in a separate terminal) ollama serve
Setup
Create an Azure PostgreSQL Flexible Server in the Azure portal. Configure public access and add your client IP to the firewall rules. Create the database schema using the provided SQL file.
# Connect to your Azure PostgreSQL database psql "host=your_host port=5432 dbname=postgres user=your_username password=your_password sslmode=require" # Run the schema SQL file \i library_schema.sql
Usage
Create a postgres_connection.txt
file with your database credentials. Run the script to start the chatbot.
# Example postgres_connection.txt host="your_host.postgres.database.azure.com" port="5432" database="postgres" username="your_username" password="your_password" # Run the script python3 postgres_chatbot.py
Type your question in the chat interface, or 'exit' to quit. Example questions:
- List all books by George Orwell.
- Who borrowed books in May 2025?
- How many books are in the Fantasy genre?
Sample Files
Download the schema SQL file to set up the database:
- library_schema.sql: SQL script to create
Authors
,Books
, andBorrowers
tables with sample data.
Download
Get the script and start building your PostgreSQL chatbot:
Download postgres_chatbot.pyExample Output
=== PostgreSQL Database Chatbot === Hello! I'm ready to answer questions about the library database. Ask about books, authors, or borrowers. Type 'exit' to quit. ================================ You: List all books by George Orwell Generated SQL: SELECT b.title FROM Books b JOIN Authors a ON b.author_id = a.author_id WHERE a.last_name = 'Orwell' Bot: Results: ['TITLE'] ('1984',) ('Animal Farm',) You: exit Goodbye!