PostgreSQL Database Chatbot

A Python script for querying an Azure PostgreSQL database using LLaMA 3.2

Back to Home

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

Requirements

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:

Sample Files

Download the schema SQL file to set up the database:

Download

Get the script and start building your PostgreSQL chatbot:

Download postgres_chatbot.py

Example 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!