!mkdir -p .data # Create the data directory, if it doesn't exist
DB_PATH = ".data/course_catalog.db"Text-to-SQL
Create the SQLite db, if it doesn’t already exist
import sqlite3
import os
def create_and_populate_db(db_path=DB_PATH):
db_exists = os.path.exists(db_path)
conn = sqlite3.connect(db_path)
conn.row_factory = sqlite3.Row
cursor = conn.cursor()
if db_exists:
print(f"Found existing database at '{db_path}'. Skipping creation.")
return conn
print(f"Creating new database at '{db_path}'...")
# --- Schema ---
cursor.executescript("""
CREATE TABLE instructors (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT,
department TEXT,
bio TEXT
);
CREATE TABLE courses (
id INTEGER PRIMARY KEY,
code TEXT NOT NULL UNIQUE,
title TEXT NOT NULL,
description TEXT,
credits INTEGER,
level TEXT CHECK(level IN ('intro', 'intermediate', 'advanced')),
instructor_id INTEGER REFERENCES instructors(id),
max_enrollment INTEGER
);
CREATE TABLE schedules (
id INTEGER PRIMARY KEY,
course_id INTEGER REFERENCES courses(id),
days TEXT,
time_start TEXT,
time_end TEXT,
room TEXT,
semester TEXT
);
""")
# --- Instructors ---
cursor.executemany(
"INSERT INTO instructors (name, email, department, bio) VALUES (?, ?, ?, ?)",
[
("Dr. Sarah Chen", "s.chen@university.edu", "Computer Science",
"Specializes in machine learning and NLP. Author of 'Practical Deep Learning'."),
("Prof. Marcus Webb", "m.webb@university.edu", "Computer Science",
"Focuses on systems programming and computer architecture."),
("Dr. Priya Nair", "p.nair@university.edu", "Data Science",
"Expert in statistical learning, data visualization, and reproducible research."),
("Prof. James Okafor", "j.okafor@university.edu", "Computer Science",
"Teaches software engineering and has 10 years of industry experience at major tech firms."),
("Dr. Elena Russo", "e.russo@university.edu", "Mathematics",
"Research interests include linear algebra, optimization, and mathematical foundations of AI."),
]
)
# --- Courses ---
cursor.executemany(
"""INSERT INTO courses (code, title, description, credits, level, instructor_id, max_enrollment)
VALUES (?, ?, ?, ?, ?, ?, ?)""",
[
("CS-101", "Introduction to Programming",
"Fundamentals of programming using Python. Covers variables, control flow, functions, and basic data structures.",
3, "intro", 2, 40),
("CS-201", "Data Structures and Algorithms",
"Core data structures including linked lists, trees, graphs, and hash tables. Algorithm design and complexity analysis.",
3, "intermediate", 2, 35),
("CS-301", "Machine Learning Fundamentals",
"Supervised and unsupervised learning, model evaluation, feature engineering, and scikit-learn. Final project required.",
4, "intermediate", 1, 30),
("CS-394", "How Generative AI Works",
"Transformer architectures, large language models, prompt engineering, fine-tuning, and deployment. Hands-on with open-source models.",
3, "advanced", 1, 25),
("CS-310", "Database Systems",
"Relational database design, SQL, transactions, indexing, and an introduction to NoSQL systems.",
3, "intermediate", 2, 20)
]
)
# --- Schedules ---
cursor.executemany(
"""INSERT INTO schedules (course_id, days, time_start, time_end, room, semester)
VALUES (?, ?, ?, ?, ?, ?)""",
[
(1, "Mon/Wed/Fri", "09:00", "09:50", "Room 101", "Spring 2026"),
(2, "Tue/Thu", "10:00", "11:20", "Room 204", "Spring 2026"),
(3, "Mon/Wed", "13:00", "14:20", "Lab 12", "Spring 2026"),
(4, "Tue/Thu", "14:00", "15:20", "Lab 12", "Spring 2026"),
(5, "Mon/Wed/Fri", "11:00", "11:50", "Room 305", "Spring 2026"),
]
)
conn.commit()
print("Database created and populated successfully.")
print(f" - {cursor.execute('SELECT COUNT(*) FROM instructors').fetchone()[0]} instructors")
print(f" - {cursor.execute('SELECT COUNT(*) FROM courses').fetchone()[0]} courses")
print(f" - {cursor.execute('SELECT COUNT(*) FROM schedules').fetchone()[0]} schedules")
return conn
create_and_populate_db()Creating new database at '.data/course_catalog.db'...
Database created and populated successfully.
- 5 instructors
- 5 courses
- 5 schedules<sqlite3.Connection at 0x1102f7790>Retrieval functions
import sqlite3
import re
def get_course_by_code(conn, code):
"""Return full details for a single course by its code (e.g. 'CS-394')."""
return conn.execute("""
SELECT
c.code,
c.title,
c.credits,
c.level,
c.description,
c.max_enrollment,
i.name AS instructor,
i.email AS instructor_email,
i.bio AS instructor_bio,
s.days,
s.time_start,
s.time_end,
s.room,
s.semester
FROM courses c
JOIN instructors i ON c.instructor_id = i.id
JOIN schedules s ON s.course_id = c.id
WHERE UPPER(c.code) = UPPER(?)
""", (code,)).fetchone()
def rows_to_text(rows):
"""Convert sqlite3.Row results into a readable string block."""
if not rows:
return "No results found."
if isinstance(rows, sqlite3.Row):
rows = [rows]
return "\n".join(
" " + ", ".join(f"{k}: {row[k]}" for k in row.keys())
for row in rows
)
def build_context(conn, user_query):
"""
Extract a course code from the user query and retrieve its details.
Returns a formatted string ready to inject into the system prompt.
"""
code_match = re.search(r'\b([A-Z]{2,4}-\d{3})\b', user_query.upper())
if not code_match:
return "No course code found in query. Please include a course code (e.g. CS-394)."
code = code_match.group(1)
course = get_course_by_code(conn, code)
if not course:
return f"No course found with code {code}."
return (
f"Course details for {code}:\n{rows_to_text(course)}\n\n"
)Get the OpenRouter API key
import sys
import os
from dotenv import load_dotenv
if 'google.colab' in sys.modules:
from google.colab import userdata # type:ignore
OPENROUTER_API_KEY = userdata.get('OPENROUTER_API_KEY')
os.environ["OPENROUTER_API_KEY"] = OPENROUTER_API_KEY
print("Loaded key from Colab")
else:
load_dotenv()
print("Loaded key locally")Loaded key locallySetup OpenRouter Client
MODEL = "nvidia/nemotron-nano-9b-v2:free"
import openai
client = openai.OpenAI(
base_url='https://openrouter.ai/api/v1',
api_key=os.environ["OPENROUTER_API_KEY"],
)User query
USER_PROMPT = "Who teaches CS-394?"Call without injecting course information
response = client.chat.completions.create(
model=MODEL,
messages=[
{"role": "system", "content": "You help students lookup course information."},
{"role": "user", "content": USER_PROMPT},
],
)
response.choices[0].message.content"I don't have access to specific course details like instructors for CS-394, as that information is typically managed by educational institutions. To find out who teaches CS-394, you could:\n\n1. Check your university/department's official course schedule or syllabus. \n2. Contact the course instructor or department directly via email or phone. \n3. Use your school's learning management system (e.g., Moodle, Canvas) if the course is listed there. \n\nLet me know if you'd help drafting a message to inquire!\n"Call with injecting course information
# Connect to the sqlite database
conn = create_and_populate_db()
# Query the database based on the user prompt
context = build_context(conn, USER_PROMPT)
# Create system prompt
SYSTEM_PROMPT = f"""
You help students lookup course information. Here are the course details:
---COURSE INFORMATION---
{context}
---END COURSE INFORMATION---
"""
# Query the model
response = client.chat.completions.create(
model=MODEL,
messages=[
{"role": "system", "content": SYSTEM_PROMPT},
{"role": "user", "content": USER_PROMPT},
],
)
response.choices[0].message.contentFound existing database at '.data/course_catalog.db'. Skipping creation.'The instructor for CS-394 "How Generative AI Works" is **Dr. Sarah Chen**.\n'