Unlock Your Data: Simplifying SQL Queries building with Granite and Ollama
Introduction
In today’s data-driven world, the ability to extract insights from vast datasets is crucial for business success. However, accessing this information often requires a specialized skill: writing complex SQL queries. This creates a bottleneck, as many business users, while experts in their domain, lack the technical proficiency to directly interact with databases. This is where the power of Large Language Models (LLMs) like those from Ollama, especially when paired with models like Granite, comes into play, revolutionizing how non-technical professionals can query and understand their data.
Imagine effortlessly transforming your natural language questions — like “Show me the sales figures for last quarter” or “Which employees work in the marketing department?” — into precise SQL commands, all without writing a single line of code. LLMs are bridging this gap, acting as intelligent translators that convert human language into executable database queries. This not only democratizes data access but also empowers business users to independently retrieve the information they need, accelerating decision-making and fostering a more agile, data-literate environment.
Sample use-case
For the sake of simplicity and demonstration, we’ll explore this concept using two SQLite databases. One database will hold personal information for individuals, such as their first name, last name, and date of birth. The second database will contain employee-specific details, including salary, job title, and department. These two databases are linked by a common unique identifier, allowing us to combine information from both sources through a simple query.
Our first database, “personal_data” contains very simple personal information.
The second database on the other hand contains employees employment information. The two tables share a unique primary key.
Sample Code for Building our Databases
- First prepare your environment and all required packages.
python3 -m venv venv
source venv/bin/activate
pip install --upgrade pip
pip install requests
- Next, we’ll construct our two databases: one for the employees’ personal details and another for their employment information.
# create-db.py
import sqlite3
import os
def create_personal_data_db(db_name="personal_data.db"):
"""
Creates an SQLite database for personal employee data and populates it.
Args:
db_name (str): The name of the database file.
"""
conn = None
try:
# Connect to the database (creates it if it doesn't exist)
conn = sqlite3.connect(db_name)
cursor = conn.cursor()
print(f"Connected to database: {db_name}")
# Drop table if it exists (for fresh creation on each run)
cursor.execute("DROP TABLE IF EXISTS employees_personal")
# Create the employees_personal table
cursor.execute("""
CREATE TABLE employees_personal (
firstName TEXT NOT NULL,
lastName TEXT NOT NULL,
dateOfBirth TEXT NOT NULL,
uniqueID TEXT PRIMARY KEY
)
""")
print("Table 'employees_personal' created successfully.")
# Sample data
sample_data = [
("Alice", "Smith", "1990-05-15", "EMP001"),
("Bob", "Johnson", "1985-11-22", "EMP002"),
("Charlie", "Brown", "1992-03-01", "EMP003")
]
# Insert sample data
cursor.executemany("INSERT INTO employees_personal (firstName, lastName, dateOfBirth, uniqueID) VALUES (?, ?, ?, ?)", sample_data)
conn.commit()
print("Sample data inserted into 'employees_personal'.")
# Verify data
print("\n--- Data in employees_personal ---")
for row in cursor.execute("SELECT * FROM employees_personal"):
print(row)
except sqlite3.Error as e:
print(f"An SQLite error occurred: {e}")
finally:
if conn:
conn.close()
print(f"Connection to {db_name} closed.")
def create_employment_data_db(db_name="employment_data.db"):
"""
Creates an SQLite database for employment data and populates it.
Args:
db_name (str): The name of the database file.
"""
conn = None
try:
# Connect to the database (creates it if it doesn't exist)
conn = sqlite3.connect(db_name)
cursor = conn.cursor()
print(f"Connected to database: {db_name}")
# Drop table if it exists (for fresh creation on each run)
cursor.execute("DROP TABLE IF EXISTS employees_employment")
# Create the employees_employment table
cursor.execute("""
CREATE TABLE employees_employment (
uniqueID TEXT PRIMARY KEY,
salary REAL NOT NULL,
jobTitle TEXT NOT NULL,
departmentId INTEGER NOT NULL
)
""")
print("Table 'employees_employment' created successfully.")
# Sample data (uniqueIDs match those in personal_data.db)
sample_data = [
("EMP001", 60000.00, "Software Engineer", 101),
("EMP002", 75000.00, "Project Manager", 102),
("EMP003", 50000.00, "HR Specialist", 103)
]
# Insert sample data
cursor.executemany("INSERT INTO employees_employment (uniqueID, salary, jobTitle, departmentId) VALUES (?, ?, ?, ?)", sample_data)
conn.commit()
print("Sample data inserted into 'employees_employment'.")
# Verify data
print("\n--- Data in employees_employment ---")
for row in cursor.execute("SELECT * FROM employees_employment"):
print(row)
except sqlite3.Error as e:
print(f"An SQLite error occurred: {e}")
finally:
if conn:
conn.close()
print(f"Connection to {db_name} closed.")
def clean_up_dbs(db_names):
"""
Deletes the specified database files.
"""
print("\n--- Cleaning up database files ---")
for db_name in db_names:
if os.path.exists(db_name):
os.remove(db_name)
print(f"Deleted {db_name}")
else:
print(f"{db_name} not found, skipping deletion.")
if __name__ == "__main__":
db_files_to_clean = ["personal_data.db", "employment_data.db"]
# Optional: Clean up existing DB files before running
# clean_up_dbs(db_files_to_clean)
# Create the personal data database
create_personal_data_db()
print("\n" + "="*50 + "\n") # Separator for clarity
# Create the employment data database
create_employment_data_db()
print("\nApplication finished.")
# You can uncomment the line below to clean up the databases after creation
# clean_up_dbs(db_files_to_clean)
- Now let’s query our two databases.
# display-db.py
import sqlite3
import os
def read_sqlite_database(db_name):
"""
Connects to an SQLite database, lists all tables, and then retrieves
and prints all rows from each table.
Args:
db_name (str): The name of the SQLite database file.
"""
if not os.path.exists(db_name):
print(f"Error: Database file '{db_name}' not found.")
return
conn = None
try:
conn = sqlite3.connect(db_name)
cursor = conn.cursor()
print(f"\n--- Connected to database: {db_name} ---")
# Get a list of all tables in the database
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
tables = cursor.fetchall()
if not tables:
print("No tables found in this database.")
return
print("\n--- Tables found in the database ---")
for table_name_tuple in tables:
table_name = table_name_tuple[0]
print(f"- {table_name}")
# Select all data from the current table
print(f"\n--- Data from table: '{table_name}' ---")
cursor.execute(f"PRAGMA table_info({table_name});")
columns_info = cursor.fetchall()
column_names = [col[1] for col in columns_info]
print(f"Columns: {', '.join(column_names)}")
cursor.execute(f"SELECT * FROM {table_name}")
rows = cursor.fetchall()
if not rows:
print(f" No data in table '{table_name}'.")
else:
for row in rows:
print(f" {row}")
print("-" * 30) # Separator for data from each table
except sqlite3.Error as e:
print(f"An SQLite error occurred: {e}")
finally:
if conn:
conn.close()
print(f"\n--- Connection to {db_name} closed. ---")
if __name__ == "__main__":
db_file_input = input("Please enter the name of the SQLite database file (e.g., personal_data.db): ")
read_sqlite_database(db_file_input)
- The output ⬇️
python display-db.py
Please enter the name of the SQLite database file (e.g., personal_data.db): personal_data.db
--- Connected to database: personal_data.db ---
--- Tables found in the database ---
- employees_personal
--- Data from table: 'employees_personal' ---
Columns: firstName, lastName, dateOfBirth, uniqueID
('Alice', 'Smith', '1990-05-15', 'EMP001')
('Bob', 'Johnson', '1985-11-22', 'EMP002')
('Charlie', 'Brown', '1992-03-01', 'EMP003')
------------------------------
--- Connection to personal_data.db closed. ---
It seems to work fine. Let us move forward.
Using LLM to generate SQL Query
In this section, we will build a sample query which asks for a person (employee) uniqueID as input, and displays the employee’s employment information alongside with the SQL query generated. To do this, we use IBM Granite, which in this example is used locally using Ollama.
- The code to automate this task ⬇️
# text2sql.py
import sqlite3
import os
import requests
import json
# Configuration for Ollama
OLLAMA_API_URL = "http://localhost:11434/api/chat"
OLLAMA_MODEL = "granite3.3:latest" # Ensure this model is pulled in your Ollama instance
# Database file names
PERSONAL_DB = "personal_data.db"
EMPLOYMENT_DB = "employment_data.db"
def get_employee_details(unique_id):
"""
Connects to both personal and employment databases, performs a join,
and retrieves employee details for a given uniqueID.
Displays the SQL query being executed.
Args:
unique_id (str): The unique ID of the employee.
Returns:
tuple: A tuple containing (firstName, lastName, jobTitle) if found,
otherwise None.
"""
if not os.path.exists(PERSONAL_DB):
print(f"Error: Personal data database '{PERSONAL_DB}' not found.")
return None
if not os.path.exists(EMPLOYMENT_DB):
print(f"Error: Employment data database '{EMPLOYMENT_DB}' not found.")
return None
conn = None
try:
# Connect to the personal data database
conn = sqlite3.connect(PERSONAL_DB)
cursor = conn.cursor()
# Attach the employment data database
cursor.execute(f"ATTACH DATABASE '{EMPLOYMENT_DB}' AS employment_db;")
# Perform a JOIN query
query = f"""
SELECT
p.firstName,
p.lastName,
e.jobTitle
FROM
employees_personal AS p
JOIN
employment_db.employees_employment AS e
ON
p.uniqueID = e.uniqueID
WHERE
p.uniqueID = '{unique_id}'; -- Displaying the query with the ID for clarity
"""
print(f"\n--- Executing SQL Query ---")
print(query)
print(f"---------------------------\n")
cursor.execute(query) # Execute the query
result = cursor.fetchone() # Fetch one matching record
return result
except sqlite3.Error as e:
print(f"An SQLite error occurred during lookup: {e}")
return None
finally:
if conn:
# Detach the database before closing connection
try:
cursor.execute("DETACH DATABASE employment_db;")
except sqlite3.Error as e:
print(f"Warning: Could not detach employment_db: {e}")
conn.close()
def send_to_ollama(prompt):
"""
Sends a prompt to the local Ollama API and returns the LLM's response.
Args:
prompt (str): The text prompt to send to the LLM.
Returns:
str: The LLM's generated response, or an error message.
"""
messages = [
{"role": "system", "content": "You are a friendly assistant providing employee information. Respond concisely and helpfully."},
{"role": "user", "content": prompt}
]
payload = {
"model": OLLAMA_MODEL,
"messages": messages,
"stream": False # We want the full response at once
}
headers = {"Content-Type": "application/json"}
try:
response = requests.post(OLLAMA_API_URL, headers=headers, data=json.dumps(payload))
response.raise_for_status() # Raise an HTTPError for bad responses (4xx or 5xx)
response_data = response.json()
if 'message' in response_data and 'content' in response_data['message']:
return response_data['message']['content']
else:
return "Ollama: Unexpected response format."
except requests.exceptions.ConnectionError:
return "Ollama: Could not connect to the Ollama server. Please ensure it's running."
except requests.exceptions.RequestException as e:
return f"Ollama: An error occurred during the API call: {e}"
except json.JSONDecodeError:
return "Ollama: Failed to decode JSON response from Ollama."
def run_chat_app():
"""
Runs the main chat application loop.
"""
print(f"Welcome to the Employee Lookup Chatbot! (Using Ollama with {OLLAMA_MODEL})")
print("Type 'exit' or 'quit' to end the session.")
while True:
user_input = input("\nEnter employee Unique ID (e.g., EMP001): ").strip().upper()
if user_input in ["EXIT", "QUIT"]:
print("Goodbye!")
break
employee_data = get_employee_details(user_input)
if employee_data:
first_name, last_name, job_title = employee_data
llm_prompt = (
f"I found an employee with Unique ID {user_input}. "
f"Their first name is {first_name}, last name is {last_name}, "
f"and their job title is {job_title}. Please tell me this information "
f"in a friendly and concise way, confirming the ID."
)
else:
llm_prompt = (
f"I looked for an employee with Unique ID {user_input}, but I couldn't find them in the database. "
f"Please respond to the user that the employee was not found and suggest they try another ID."
)
llm_response = send_to_ollama(llm_prompt)
print(f"\nChatbot: {llm_response}")
if __name__ == "__main__":
run_chat_app()
- The code execution and output;
python text2sql.py
Welcome to the Employee Lookup Chatbot! (Using Ollama with granite3.3:latest)
Type 'exit' or 'quit' to end the session.
Enter employee Unique ID (e.g., EMP001): EMP001
Chatbot: Absolutely! You've found details about an employee with Unique ID EMP001. This person's name is Alice Smith and she works as a Software Engineer. Let me know if you need more information!
Enter employee Unique ID (e.g., EMP001): exit
Goodbye!
> python text2sql-2.py
Welcome to the Employee Lookup Chatbot! (Using Ollama with granite3.3:latest)
Type 'exit' or 'quit' to end the session.
Enter employee Unique ID (e.g., EMP001): EMP001
--- Executing SQL Query ---
SELECT
p.firstName,
p.lastName,
e.jobTitle
FROM
employees_personal AS p
JOIN
employment_db.employees_employment AS e
ON
p.uniqueID = e.uniqueID
WHERE
p.uniqueID = 'EMP001'; -- Displaying the query with the ID for clarity
---------------------------
Chatbot: You've found Alice Smith, who has the Unique ID EMP001 and works as a Software Engineer. Is there anything else you need to know?
Enter employee Unique ID (e.g., EMP001):
Et voilà ✌️
Conclusion
Though the steps provided here are very simple, nonetheless they show the power of using LLMs in order to build powerful applications to ease the usage of structured information in enterprise databases, bringing it to the fingertips of business users. Although the application is very simple, it effectively demonstrates the value brought to business users, enabling them to be more autonomous in their day-to-day tasks, without needing knowledge of using databases or sophisticated systems.
Top comments (2)
Am I missing something? I kind of expected the LLM to generate the SQL and execute that against the database but it seems the query is already specified in the code. So there is still a specialized skill needed.
The execution part is not implemented, I tried agains a SQLite db which is almost embarked on Mac and Linux machines (I don't know about windows ones). Normally an industrial level of these kind of applications requires connections and joints to far more dbs/tables and hence more complex applications. The code above is just an elementry piece.