Spaces:
Paused
Paused
File size: 5,551 Bytes
ff679e0 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 | import sqlite3
import os
DB_PATH = "company.db"
def init_database():
"""Initialize the SQLite database with sample tables and data."""
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
# Create tables
cursor.executescript("""
CREATE TABLE IF NOT EXISTS employees (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
department TEXT NOT NULL,
salary REAL NOT NULL,
hire_date TEXT NOT NULL,
manager_id INTEGER,
email TEXT UNIQUE
);
CREATE TABLE IF NOT EXISTS departments (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE,
budget REAL NOT NULL,
location TEXT NOT NULL,
head_count INTEGER DEFAULT 0
);
CREATE TABLE IF NOT EXISTS projects (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
department TEXT NOT NULL,
start_date TEXT NOT NULL,
end_date TEXT,
status TEXT DEFAULT 'active',
budget REAL NOT NULL
);
CREATE TABLE IF NOT EXISTS sales (
id INTEGER PRIMARY KEY AUTOINCREMENT,
employee_id INTEGER NOT NULL,
product TEXT NOT NULL,
amount REAL NOT NULL,
sale_date TEXT NOT NULL,
region TEXT NOT NULL,
FOREIGN KEY (employee_id) REFERENCES employees(id)
);
""")
# Seed data only if empty
cursor.execute("SELECT COUNT(*) FROM employees")
if cursor.fetchone()[0] == 0:
cursor.executescript("""
INSERT INTO departments (name, budget, location, head_count) VALUES
('Engineering', 2500000, 'San Francisco', 12),
('Sales', 1800000, 'New York', 15),
('Marketing', 900000, 'Chicago', 8),
('HR', 600000, 'Austin', 5),
('Finance', 750000, 'Boston', 6);
INSERT INTO employees (name, department, salary, hire_date, email) VALUES
('Alice Johnson', 'Engineering', 120000, '2020-03-15', 'alice@company.com'),
('Bob Smith', 'Engineering', 115000, '2019-07-22', 'bob@company.com'),
('Carol White', 'Sales', 85000, '2021-01-10', 'carol@company.com'),
('David Brown', 'Sales', 92000, '2018-11-05', 'david@company.com'),
('Eve Davis', 'Marketing', 78000, '2022-04-18', 'eve@company.com'),
('Frank Miller', 'HR', 72000, '2020-09-30', 'frank@company.com'),
('Grace Wilson', 'Engineering', 130000, '2017-06-01', 'grace@company.com'),
('Henry Moore', 'Finance', 95000, '2019-02-14', 'henry@company.com'),
('Iris Taylor', 'Marketing', 81000, '2021-08-25', 'iris@company.com'),
('Jack Anderson', 'Sales', 88000, '2020-12-07', 'jack@company.com'),
('Karen Thomas', 'Engineering', 125000, '2018-05-20', 'karen@company.com'),
('Leo Jackson', 'Finance', 98000, '2016-10-11', 'leo@company.com');
INSERT INTO projects (title, department, start_date, end_date, status, budget) VALUES
('AI Platform v2', 'Engineering', '2024-01-01', '2024-12-31', 'active', 500000),
('Customer Portal', 'Engineering', '2023-06-01', '2024-03-31', 'completed', 200000),
('Q4 Campaign', 'Marketing', '2024-10-01', '2024-12-31', 'active', 150000),
('Sales CRM Migration', 'Sales', '2024-03-01', NULL, 'active', 80000),
('Annual Audit', 'Finance', '2024-11-01', '2024-11-30', 'completed', 30000),
('Talent Pipeline', 'HR', '2024-07-01', NULL, 'active', 50000);
INSERT INTO sales (employee_id, product, amount, sale_date, region) VALUES
(3, 'Enterprise License', 45000, '2024-01-15', 'East'),
(4, 'SaaS Subscription', 12000, '2024-02-20', 'West'),
(10, 'Consulting Package', 28000, '2024-03-10', 'Central'),
(3, 'Enterprise License', 52000, '2024-04-05', 'East'),
(4, 'Support Plan', 8500, '2024-05-18', 'West'),
(10, 'Enterprise License', 61000, '2024-06-22', 'East'),
(3, 'SaaS Subscription', 15000, '2024-07-30', 'Central'),
(4, 'Consulting Package', 33000, '2024-08-14', 'West'),
(10, 'Support Plan', 9000, '2024-09-01', 'East'),
(3, 'Enterprise License', 47000, '2024-10-17', 'Central');
""")
conn.commit()
conn.close()
print("✅ Database initialized successfully.")
def get_schema() -> str:
"""Return a CREATE TABLE schema string for the prompt."""
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
cursor.execute("SELECT sql FROM sqlite_master WHERE type='table' AND sql IS NOT NULL")
tables = cursor.fetchall()
conn.close()
return "\n\n".join(t[0] for t in tables)
def execute_query(sql: str):
"""Execute a SQL query and return (columns, rows) or raise on error."""
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
try:
cursor.execute(sql)
columns = [desc[0] for desc in cursor.description] if cursor.description else []
rows = cursor.fetchall()
conn.commit()
return columns, rows
finally:
conn.close()
if __name__ == "__main__":
init_database()
schema = get_schema()
print("\n--- SCHEMA ---")
print(schema) |