import os
import sqlite3
from camel.logger import get_logger
logger = get_logger(__name__)
db_path = "sample.db" # Database will be created in the current working directory
# Remove existing database if any, to ensure a clean start
if os.path.exists(db_path):
os.remove(db_path)
logger.info(f"Removed existing database: {db_path}")
conn = sqlite3.connect(db_path)
cursor = conn.cursor()
# Create employees table
cursor.execute("""
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
department TEXT,
salary REAL,
hire_date TEXT
)
""")
logger.info("Created 'employees' table.")
# Create departments table
cursor.execute("""
CREATE TABLE departments (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
budget REAL,
location TEXT
)
""")
logger.info("Created 'departments' table.")
# Insert sample employee data
employees_data = [
(1, 'John Doe', 'Engineering', 85000.00, '2020-01-15'),
(2, 'Jane Smith', 'Marketing', 75000.00, '2019-05-20'),
(3, 'Bob Johnson', 'Engineering', 95000.00, '2018-11-10'),
(4, 'Alice Brown', 'HR', 65000.00, '2021-03-05'),
(5, 'Charlie Davis', 'Engineering', 90000.00, '2020-08-12')
]
cursor.executemany("INSERT INTO employees VALUES (?, ?, ?, ?, ?)", employees_data)
logger.info(f"Inserted {len(employees_data)} records into 'employees' table.")
# Insert sample department data
departments_data = [
(1, 'Engineering', 1000000.00, 'Building A'),
(2, 'Marketing', 500000.00, 'Building B'),
(3, 'HR', 300000.00, 'Building A'),
(4, 'Finance', 600000.00, 'Building C')
]
cursor.executemany("INSERT INTO departments VALUES (?, ?, ?, ?)", departments_data)
logger.info(f"Inserted {len(departments_data)} records into 'departments' table.")
conn.commit()
conn.close()
logger.info(f"Sample database '{db_path}' created and populated successfully.")