""" employee_db.py ============== Builds the small employee database used by Project_5.ipynb. HOW TO USE IT ------------- Put this file in the same folder as your notebook, then run: from employee_db import create_database connection, cursor = create_database() That one call does everything: * opens the database file project5_company.db * drops the three tables if they already exist * creates DEPARTMENT, JOBS and EMPLOYEES * inserts all the sample rows * prints a short report so you can see it worked It is always safe to run it again. The tables are dropped and rebuilt every time, so you can reset your data at any point by re running that one cell. You do not need pandas. You do not need to install anything. Python already includes sqlite3. THE THREE TABLES ---------------- DEPARTMENT 6 rows the departments in the company JOBS 6 rows the job titles, with their salary ranges EMPLOYEES 24 rows one row per person A few things about the data are deliberate, because the Project 5 problems depend on them: * Two departments, Marketing and Research, have no employees at all. They are the rows an inner join drops and an outer join keeps. * Three employees have no email address on file, so COUNT(*) and COUNT(email) give different answers. * Carolyn Reyes is the president and has no manager, so her manager_id is NULL and the recursive query returns 23 rows rather than 24. """ import sqlite3 DB_FILENAME = "project5_company.db" # --------------------------------------------------------------------------- # The data # --------------------------------------------------------------------------- # (dept_id, dept_name, location, budget) DEPARTMENT_ROWS = [ (1, "Accounting", "Turlock", 450000), (2, "Engineering", "Modesto", 1200000), (3, "Sales", "Fresno", 800000), (4, "Human Resources", "Turlock", 300000), (5, "Marketing", "Stockton", 550000), # no employees yet (6, "Research", "Merced", 900000), # no employees yet ] # (job_id, job_title, min_salary, max_salary) JOBS_ROWS = [ (1, "President", 90000, 150000), (2, "Manager", 70000, 110000), (3, "Software Engineer", 65000, 120000), (4, "Accountant", 50000, 85000), (5, "Sales Rep", 40000, 90000), (6, "HR Specialist", 45000, 75000), ] # (emp_id, first_name, last_name, email, hire_date, salary, dept_id, job_id, manager_id) EMPLOYEES_ROWS = [ (101, "Carolyn", "Reyes", "creyes@company.com", "2015-03-12", 145000, 1, 1, None), (102, "Marcus", "Delgado", "mdelgado@company.com", "2016-06-01", 102000, 2, 2, 101), (103, "Priya", "Raman", "praman@company.com", "2017-02-15", 98000, 3, 2, 101), (104, "Tomas", "Okafor", "tokafor@company.com", "2018-09-10", 88000, 1, 2, 101), (105, "Elena", "Castillo", "ecastillo@company.com", "2019-01-07", 76000, 4, 2, 101), (106, "David", "Nguyen", "dnguyen@company.com", "2019-04-22", 112000, 2, 3, 102), (107, "Aisha", "Bello", "abello@company.com", "2020-07-13", 95000, 2, 3, 102), (108, "Jorge", "Medina", "jmedina@company.com", "2020-11-02", 88000, 2, 3, 102), (109, "Hannah", "Kim", "hkim@company.com", "2021-05-17", 82000, 2, 3, 102), (110, "Samuel", "Ortiz", "sortiz@company.com", "2022-03-28", 79000, 2, 3, 102), (111, "Leila", "Haddad", "lhaddad@company.com", "2022-08-15", 74000, 2, 3, 102), (112, "Ryan", "Foster", "rfoster@company.com", "2023-01-09", 68000, 2, 3, 102), (113, "Mei", "Chen", None, "2024-06-03", 66000, 2, 3, 102), (114, "Andre", "Silva", "asilva@company.com", "2019-08-19", 72000, 3, 5, 103), (115, "Bianca", "Moreau", "bmoreau@company.com", "2020-02-24", 68000, 3, 5, 103), (116, "Kwame", "Asante", "kasante@company.com", "2021-09-06", 64000, 3, 5, 103), (117, "Daniela", "Rossi", "drossi@company.com", "2022-04-11", 58000, 3, 5, 103), (118, "Victor", "Pham", "vpham@company.com", "2023-07-24", 52000, 3, 5, 103), (119, "Grace", "Adeyemi", None, "2024-10-14", 47000, 3, 5, 103), (120, "Nathan", "Brooks", "nbrooks@company.com", "2018-05-21", 81000, 1, 4, 104), (121, "Sofia", "Marchetti", "smarchetti@company.com","2020-10-05", 73000, 1, 4, 104), (122, "Isaac", "Levine", "ilevine@company.com", "2022-11-28", 62000, 1, 4, 104), (123, "Camille", "Dubois", "cdubois@company.com", "2021-03-15", 61000, 4, 6, 105), (124, "Omar", "Farouk", None, "2023-02-06", 54000, 4, 6, 105), ] # --------------------------------------------------------------------------- # The builder # --------------------------------------------------------------------------- def create_database(filename=DB_FILENAME, quiet=False): """Create the database and return (connection, cursor). filename : where to store the database. The default creates a file in the same folder as your notebook. Pass ":memory:" if you would rather it disappear when you close the notebook. quiet : pass True to skip the summary printout. """ connection = sqlite3.connect(filename) cursor = connection.cursor() # Drop in child first, parent last, so nothing is left pointing at a # table that no longer exists. for table in ["EMPLOYEES", "JOBS", "DEPARTMENT"]: cursor.execute("DROP TABLE IF EXISTS " + table) cursor.execute(""" CREATE TABLE DEPARTMENT ( dept_id INTEGER PRIMARY KEY, dept_name TEXT NOT NULL, location TEXT, budget INTEGER ) """) cursor.execute(""" CREATE TABLE JOBS ( job_id INTEGER PRIMARY KEY, job_title TEXT NOT NULL, min_salary INTEGER, max_salary INTEGER ) """) cursor.execute(""" CREATE TABLE EMPLOYEES ( emp_id INTEGER PRIMARY KEY, first_name TEXT, last_name TEXT NOT NULL, email TEXT, hire_date TEXT, salary INTEGER, dept_id INTEGER, job_id INTEGER, manager_id INTEGER, FOREIGN KEY (dept_id) REFERENCES DEPARTMENT (dept_id), FOREIGN KEY (job_id) REFERENCES JOBS (job_id), FOREIGN KEY (manager_id) REFERENCES EMPLOYEES (emp_id) ) """) cursor.executemany( "INSERT INTO DEPARTMENT VALUES (?, ?, ?, ?)", DEPARTMENT_ROWS) cursor.executemany( "INSERT INTO JOBS VALUES (?, ?, ?, ?)", JOBS_ROWS) cursor.executemany( "INSERT INTO EMPLOYEES VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", EMPLOYEES_ROWS) connection.commit() if not quiet: print("Database ready:", filename) print() for table in ["DEPARTMENT", "JOBS", "EMPLOYEES"]: cursor.execute("SELECT COUNT(*) FROM " + table) print(" " + table.ljust(12), cursor.fetchone()[0], "rows") print() cursor.execute("SELECT COUNT(*) FROM EMPLOYEES WHERE email IS NULL") print(" Employees with no email: ", cursor.fetchone()[0]) cursor.execute("SELECT COUNT(*) FROM EMPLOYEES WHERE manager_id IS NULL") print(" Employees with no manager:", cursor.fetchone()[0]) cursor.execute(""" SELECT COUNT(*) FROM DEPARTMENT WHERE dept_id NOT IN (SELECT dept_id FROM EMPLOYEES) """) print(" Departments with no staff:", cursor.fetchone()[0]) print() print("You are ready to start Problem 1.") return connection, cursor def show_columns(cursor, table): """Print the column names of one table. Handy if you forget a name.""" cursor.execute("SELECT * FROM " + table + " LIMIT 1") names = [description[0] for description in cursor.description] print(table, "columns:") for name in names: print(" ", name) if __name__ == "__main__": # Running this file directly is a quick way to check it still works. connection, cursor = create_database(":memory:") print() show_columns(cursor, "EMPLOYEES") connection.close()