File size: 3,783 Bytes
0276b0d | 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 | import sqlite3
def initialize_database(db_name='chat_assistant.db'):
conn = None
try:
conn = sqlite3.connect(db_name)
cursor = conn.cursor()
# Create Employees table
cursor.execute('''CREATE TABLE IF NOT EXISTS employees (
ID INTEGER PRIMARY KEY,
Name TEXT,
Department TEXT,
Salary REAL,
Hire_Date TEXT
)''')
# Create Departments table
cursor.execute('''CREATE TABLE IF NOT EXISTS departments (
ID INTEGER PRIMARY KEY,
Name TEXT,
Manager TEXT
)''')
cursor.execute('''CREATE TABLE IF NOT EXISTS table_metadata (
table_name TEXT PRIMARY KEY,
description TEXT
)''')
cursor.execute('''CREATE TABLE IF NOT EXISTS column_metadata (
table_name TEXT,
column_name TEXT,
data_type TEXT,
description TEXT,
PRIMARY KEY (table_name, column_name),
FOREIGN KEY (table_name) references table_metadata (table_name)
)''')
# Insert sample data into Employees table
employees_data = [
(1, 'Alice', 'Sales', 50000, '2021-01-15'),
(2, 'Bob', 'Engineering', 70000, '2020-06-10'),
(3, 'Charlie', 'Marketing', 60000, '2022-03-20')
]
cursor.executemany('INSERT INTO Employees VALUES (?, ?, ?, ?, ?)', employees_data)
# Insert sample data into Departments table
departments_data = [
(1, 'Sales', 'Alice'),
(2, 'Engineering', 'Bob'),
(3, 'Marketing', 'Charlie')
]
cursor.executemany('INSERT INTO Departments VALUES (?, ?, ?)', departments_data)
cursor.execute("INSERT INTO table_metadata (table_name, description) VALUES ('Employees','Details of Employees in Department.')")
cursor.execute("INSERT INTO table_metadata (table_name, description) VALUES ('Department','Details of Manager of the Department.')")
cursor.execute("INSERT INTO column_metadata (table_name, column_name, data_type,description) VALUES ('Employees','id','INTEGER','Identification number of the Employee.')")
cursor.execute("INSERT INTO column_metadata (table_name, column_name, data_type,description) VALUES ('Employees','name','TEXT','Name of the Employee.')")
cursor.execute("INSERT INTO column_metadata (table_name, column_name, data_type,description) VALUES ('Employees','department','TEXT','Department of the Employee.')")
cursor.execute("INSERT INTO column_metadata (table_name, column_name, data_type,description) VALUES ('Employees','Salary','INTEGER','Salary of the Employee.')")
cursor.execute("INSERT INTO column_metadata (table_name, column_name, data_type,description) VALUES ('Employees','Hire_Date','DATE','Date in which the Employee was hired.')")
cursor.execute("INSERT INTO column_metadata (table_name, column_name, data_type,description) VALUES ('Departments','id','INTEGER','Identification number of the Employee.')")
cursor.execute("INSERT INTO column_metadata (table_name, column_name, data_type,description) VALUES ('Departments','name','TEXT','Name of the Department.')")
cursor.execute("INSERT INTO column_metadata (table_name, column_name, data_type,description) VALUES ('Departments','manager','TEXT','Manager of the Department.')")
conn.commit()
print("Database initialized successfully.")
except sqlite3.Error as e:
print(f"Database initialization error: {e}")
finally:
if conn:
conn.close()
if __name__ == "__main__":
initialize_database()
|