File size: 1,525 Bytes
e773157 7226f4e e773157 | 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 | import sqlite3
conn = sqlite3.connect('hello_earth_data_2.db')
cursor = conn.cursor()
cursor.execute('''
CREATE TABLE IF NOT EXISTS Project (
project_id TEXT PRIMARY KEY,
title TEXT,
status TEXT,
detail_information TEXT,
start_date DATETIME,
end_date DATETIME
)
''')
cursor.execute('''
CREATE TABLE IF NOT EXISTS Deliverable (
deliverable_id TEXT PRIMARY KEY,
project_id TEXT,
title TEXT,
status TEXT CHECK(status IN ('done', 'ongoing')),
risk_level TEXT CHECK(risk_level IN ('green', 'yellow', 'red')),
risk_level_rationale TEXT,
start_date DATETIME,
end_date DATETIME,
FOREIGN KEY (project_id) REFERENCES Project(project_id)
)
''')
cursor.execute('''
CREATE TABLE IF NOT EXISTS Expense (
expense_id TEXT PRIMARY KEY,
associated_deliverable_id TEXT,
seller_name TEXT,
seller_address TEXT,
seller_phone_number TEXT,
buyer_name TEXT,
buyer_address TEXT,
transaction_date DATETIME,
total_payment_amount REAL,
expense_description TEXT,
status TEXT,
FOREIGN KEY (associated_deliverable_id) REFERENCES Deliverable(deliverable_id)
)
''')
cursor.execute('''
CREATE TABLE IF NOT EXISTS Deliverable_Budget (
id TEXT PRIMARY KEY,
deliverable_id TEXT,
wage REAL,
materials REAL,
tools_equipment REAL,
services REAL,
misc REAL,
FOREIGN KEY (deliverable_id) REFERENCES Deliverable(deliverable_id)
)
''')
conn.commit()
conn.close()
print("SQLite database created with the specified schema.")
|