def get_employees_whose_not_lastname(last_name):
query = """SELECT * FROM Employees
WHERE lastName != ?"""
params = [last_name]
with DatabaseContextManager(DATABASE_FILE) as cursor:
cursor.execute(query, params)
for i in cursor:
print(i)
def update_date_of_birth(employee, new_date):
query = """UPDATE Employees
SET dateOfBirth = ?
WHERE firstName = ? AND lastName = 1980"""
params = [new_date, employee.first_name, employee.last_name]
with DatabaseContextManager(DATABASE_FILE) as cursor:
cursor.execute(query, params)
employee.date_of_birth = new_date
def get_employee_by_name_employees_table(name):
query = """SELECT * FROM Employees
WHERE lastName = ?"""
params = [name]
with DatabaseContextManager(DATABASE_FILE) as cursor:
cursor.execute(query, params)
for i in cursor:
print(i)
def get_employees_after_year_table(year):
query = """SELECT * FROM Employees
WHERE dateOfBirth >= ?"""
year = year
params = [year]
with DatabaseContextManager(DATABASE_FILE) as cursor:
cursor.execute(query, params)
for i in cursor:
print(i)
def create_employee(employee):
query = '''INSERT INTO Employees(firstName, lastName, dateOfBirth, phoneNumber, email, salary)
VALUES (?,?,?,?,?,?)'''
params = [
employee.first_name, employee.last_name, employee.date_of_birth,
employee.phone_number, employee.email, employee.salary
]
with DatabaseContextManager(DATABASE_FILE) as cursor:
cursor.execute(query, params)
def get_employees_before_year_or_firstname_table(year, first_name):
query = """SELECT * FROM Employees
WHERE dateOfBirth >= ? OR firstName = ?"""
converted_year_to_fit_db_structure = year + "-01-01"
params = [converted_year_to_fit_db_structure, first_name]
with DatabaseContextManager(DATABASE_FILE) as cursor:
cursor.execute(query, params)
for i in cursor:
print(i)
def part5_task5():
query = """CREATE TABLE IF NOT EXISTS EmployeesProjects(
employeeProjectId INTEGER PRIMARY KEY AUTOINCREMENT,
employeeId INTEGER NOT NULL,
projectId INTEGER NOT NULL,
FOREIGN KEY (employeeId) REFERENCES Employees(employeeId),
FOREIGN KEY (projectId) REFERENCES Projects(projectId))"""
with DatabaseContextManager(DATABASE_FILE) as cursor:
cursor.execute(query)
def get_employee_if_characters_contained_in_table(characters):
query = """SELECT * FROM Employees
WHERE lastName LIKE ?"""
characters = "%" + characters + "%"
params = [characters]
with DatabaseContextManager(DATABASE_FILE) as cursor:
cursor.execute(query, params)
for i in cursor:
print(i)
def create_employees():
query = """CREATE TABLE IF NOT EXISTS Employees(
employeeId INTEGER PRIMARY KEY AUTOINCREMENT,
firstName VARCHAR ,
lastName VARCHAR ,
dateOfBirth DATE,
phoneNumber TEXT,
email TEXT,
salary INTEGER,
departmentId INTEGER,
managerId INTEGER,
FOREIGN KEY (managerId) REFERENCES Employees(employeeId),
FOREIGN KEY (departmentId) REFERENCES Departments(departmentId))"""
with DatabaseContextManager(DATABASE_FILE) as cursor:
cursor.execute(query)
def part4_task6(name):
query = """DELETE FROM Departments
WHERE name = ?"""
params = (name, )
with DatabaseContextManager(DATABASE_FILE) as cursor:
cursor.execute(query, params)
# Part 4 Task 7
# SQLITE can't do this. The solution is in Part1 Task1 you have to initially create foreign key on table creation
# Part 4 Task 8
# Still works does the same as Part 4 Task 6 But SQLITE doesn't have the right functionality to support the restrictions of deletion.
# Part 4 Task 9
# Still works because of SQLITE implementation
# Part 4 Task 10
# In SQLITE you can delete anything you want as it doesn't prohibit deletion even if foreign keys are set.
def get_from_employees_table():
query = """SELECT * FROM Employees"""
with DatabaseContextManager(DATABASE_FILE) as cursor:
cursor.execute(query)
for i in cursor:
print(i)
def get_employees_avg_salary():
query = """SELECT AVG(salary) FROM Employees"""
with DatabaseContextManager(DATABASE_FILE) as cursor:
cursor.execute(query)
for i in cursor:
print(i)
def part4_task2(employee_list):
query = """INSERT INTO EMPLOYEES(firstName, lastName,dateOfBirth,phoneNumber,email,salary) VALUES (?,?,?,?,?,?)"""
normalized_data = function_parse_employee_objects_into_lists(employee_list)
with DatabaseContextManager(DATABASE_FILE) as cursor:
cursor.executemany(query, normalized_data)
def delete_employee_address_table():
query = """DROP Table EmployeesAddresses
"""
with DatabaseContextManager(DATABASE_FILE) as cursor:
cursor.execute(query)
def part4_task3():
query = """CREATE TABLE IF NOT EXISTS Departments(
departmentId INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR NOT NULL)"""
with DatabaseContextManager(DATABASE_FILE) as cursor:
cursor.execute(query)
def part5_task6(project_employee_connections):
query = """INSERT INTO EmployeesProjects(employeeId, projectId) VALUES (?,?)"""
with DatabaseContextManager(DATABASE_FILE) as cursor:
cursor.executemany(query, project_employee_connections)
def part5_task5(project_list):
query = """INSERT INTO Projects(description) VALUES (?)"""
with DatabaseContextManager(DATABASE_FILE) as cursor:
cursor.executemany(query, project_list)
def part5_task4():
query = """CREATE TABLE IF NOT EXISTS Projects(
projectId INTEGER PRIMARY KEY AUTOINCREMENT,
description varchar(255) DEFAULT NULL)"""
with DatabaseContextManager(DATABASE_FILE) as cursor:
cursor.execute(query)
def part5_task3(list):
query = """UPDATE Employees
SET managerId = ?
WHERE firstName = ?"""
with DatabaseContextManager(DATABASE_FILE) as cursor:
cursor.executemany(query, list)
def get_first_last_name_employees_table():
query = """SELECT firstName, lastName FROM Employees"""
with DatabaseContextManager(DATABASE_FILE) as cursor:
cursor.execute(query)
for i in cursor:
print(i)
def delete_everything_from_employees_table():
query = """DELETE FROM Employees"""
with DatabaseContextManager(DATABASE_FILE) as cursor:
cursor.execute(query)
def alter_employees():
query = """ALTER TABLE Employees
ADD salary INTEGER"""
with DatabaseContextManager(DATABASE_FILE) as cursor:
cursor.execute(query)
def create_employee_address_table():
query = """CREATE TABLE IF NOT EXISTS EmployeesAddresses(
country VARCHAR )
"""
with DatabaseContextManager(DATABASE_FILE) as cursor:
cursor.execute(query)
def part4_task4(depart_name):
query = """INSERT INTO Departments(name) VALUES (?)"""
params = (depart_name, )
with DatabaseContextManager(DATABASE_FILE) as cursor:
cursor.execute(query, params)