def create_event(cursor, row):
_row = sqlite3.Row(cursor, row)
event = Event()
event.id = _row["event_id"]
event.user_id = _row["user_id"]
event.name = _row["name"]
event.date = _row["date"]
event.start_time = _row["start_time"]
event.end_time = _row["end_time"]
event.location = _row["location"]
event.duration = _row["duration"]
event.notes = _row["notes"]
event.songs = []
if _row["song_id"] is not None:
song = Song()
song.id = _row["song_id"]
song.title = _row["title"]
song.artist = _row["artist"]
song.song_length = _row["song_length"]
song.rating = _row["rating"]
song.event_song_id = _row["event_song_id"]
return (
event,
song,
)
else:
song = None
return (
event,
song,
)
def create_library(cursor, row):
_row = sqlite3.Row(cursor, row)
library = Library()
library.id = _row["id"]
library.title = _row["title"]
library.address = _row["address"]
# Note: You are adding a blank books list to the library object
# This list will be populated later (see below)
library.books = []
book = Book()
book.id = _row["book_id"]
book.title = _row["book_title"]
book.author = _row["author"]
book.isbn = _row["isbn"]
book.year_published = _row["year_published"]
# Return a tuple containing the library and the
# book built from the data in the current row of
# the data set
return (
library,
book,
)
def create_employee(cursor, row):
_row = sqlite3.Row(cursor, row)
employee = Employee()
employee.id = _row['employee_id']
employee.first_name = _row['first_name']
employee.last_name = _row['last_name']
employee.start_date = _row['start_date']
employee.is_supervisor = _row['is_supervisor']
employee.department_id = _row['department_id']
department = Department()
department.dept_name = _row['dept_name']
department.id = _row['department_id']
computer = Computer()
computer.make = _row['computer_make']
computer.id = _row['computer_id']
employee.employeecomputer_id = _row['employeecomputer_id']
employee.employee_computer_id = _row['employee_computer_id']
employee.training_programs = []
training_program = Training_program()
training_program.name = _row['training_program_name']
training_program.id = _row['training_program_id']
return (employee, training_program,)
def create_student(cursor, row):
_row = sqlite3.Row(cursor, row)
student = Student()
student.id = _row["student_id"]
student.first_name = _row["first_name"]
student.last_name = _row["last_name"]
student.slack_handle = _row["slack_handle"]
cohort = Cohort()
cohort.id = _row["cohort_id"]
cohort.name = _row["cohort_name"]
student.cohort = cohort
student.exercises = []
exercise = None
if _row["exercise_id"] is not None:
exercise = Exercise()
exercise.id = _row["exercise_id"]
exercise.name = _row["exercise_name"]
exercise.language = _row["language"]
return (student, exercise)
def create_exercise(cursor, row):
_row = sqlite3.Row(cursor, row)
exercise = Exercise()
exercise.id = _row["exercise_id"]
exercise.name = _row["exercise_name"]
exercise.language = _row["language"]
exercise.assignments = []
assignment = None
if _row["assignment_id"] is not None:
student = Student()
student.id = _row["student_id"]
student.first_name = _row["s_first"]
student.last_name = _row["s_last"]
student.slack_handle = _row["s_slack"]
student.cohort_id = _row["s_cohort"]
instructor = Instructor()
instructor.id = _row["instructor_id"]
instructor.first_name = _row["i_first"]
instructor.last_name = _row["i_last"]
instructor.slack_handle = _row["i_slack"]
instructor.specialty = _row["specialty"]
instructor.cohort_id = _row["i_cohort"]
assignment = Assignment()
assignment.id = _row["assignment_id"]
assignment.instructor = instructor
assignment.student = student
assignment.exercise = exercise
return (exercise, assignment)
def create_training_program(cursor, row):
_row = sqlite3.Row(cursor, row)
t = TrainingProgram()
t.title = _row["title"]
return t
def create_list_employees(cursor, row):
_row = sqlite3.Row(cursor, row)
department = Department()
department.id = _row["id"]
department.name = _row["name"]
department.budget = _row["budget"]
department.employees = []
employee = Employee()
employee.department_id = _row["department_id"]
employee.first_name = _row["first_name"]
employee.last_name = _row["last_name"]
department_employees = {}
for (department, employee) in department:
if department.id not in department_employees:
department_employees[department.id] = department
department_employees[department.id].employees.append(employee)
else:
department_employees[department.id].employees.append(employee)
return (
department,
employee,
)
def create_employee(cursor, row):
_row = sqlite3.Row(cursor, row)
employee = Employee()
employee.id = _row["employee_id"]
employee.first_name = _row["first_name"]
employee.last_name = _row["last_name"]
employee.start_date = _row["start_date"]
employee.department_id = _row["department_id"]
employee.is_supervisor = _row["is_supervisor"]
department = Department()
department.id = _row["department_id"]
department.dept_name = _row["dept_name"]
computer = Computer()
computer.manufacturer = _row["manufacturer"]
computer.make = _row["make"]
computer.id = _row["computer_id"]
# training_program = TrainingProgram()
# training_program.title = _row["title"]
employee.department = department
employee.computer = computer
# employee.training_program = training_program
return employee
def create_cohort(cursor, row):
_row = sqlite3.Row(cursor, row)
cohort = Cohort()
cohort.id = _row["cohort_id"]
cohort.name = _row["cohort_name"]
cohort.students = []
cohort.instructors = []
student = None
if _row["student_id"] is not None:
student = Student()
student.id = _row["student_id"]
student.first_name = _row["s_first"]
student.last_name = _row["s_last"]
student.slack_handle = _row["s_slack"]
student.cohort_id = _row["s_cohort"]
instructor = None
if _row["instructor_id"] is not None:
instructor = Instructor()
instructor.id = _row["instructor_id"]
instructor.first_name = _row["i_first"]
instructor.last_name = _row["i_last"]
instructor.slack_handle = _row["i_slack"]
instructor.specialty = _row["specialty"]
instructor.cohort_id = _row["i_cohort"]
return (cohort, student, instructor)
def create_employee_training_programs(cursor, row):
_row = sqlite3.Row(cursor, row)
training_program = TrainingProgram()
training_program.title = _row["title"]
return training_program
def open(self):
"""Opens the database connection, if not already open."""
if self.connection: return
conn = sqlite3.connect(self.path, detect_types=sqlite3.PARSE_DECLTYPES,
isolation_level=None, check_same_thread=False)
conn.row_factory = lambda cursor, row: dict(sqlite3.Row(cursor, row))
self.connection = conn
def create_department(cursor, row):
_row = sqlite3.Row(cursor, row)
department = Department()
department.id = _row["department_id"]
department.dept_name = _row["dept_name"]
department.budget = _row["budget"]
# Note: You are adding a blank employees list to the department object
# This list will be populated later (see below)
department.employees = []
employee = Employee()
employee.id = _row["employee_id"]
employee.first_name = _row["first_name"]
employee.Last_name = _row["last_name"]
employee.department_id = _row["department_id"]
# Return a tuple containing the department and the
# employee built from the data in the current row of
# the data set
return (
department,
employee,
)
def create_employee(cursor, row):
_row = sqlite3.Row(cursor, row)
employee = Employee()
employee.id = _row['id']
employee.first_name = _row['first_name']
employee.last_name = _row['last_name']
employee.dept_name = _row['dept_name']
if _row['model'] is not None:
employee.computer = _row['manufacturer'] + ' ' + _row['model']
else:
employee.computer = "Not Assigned"
employee.start_date = _row['start_date']
employee.is_supervisor = _row['is_supervisor']
if _row["training"] is not None:
employee.training = _row["training"]
else:
employee.training = "Not Assigned"
employee.trainings = []
return (employee, )
def create(cursor, row):
instance = Librarian()
smart_row = sqlite3.Row(cursor, row)
for col in smart_row.keys():
setattr(instance, col, smart_row[col])
return instance
def create(cursor, row):
#makes empty instance of Class(ex. Book)
instance = model_type()
smart_row = sqlite3.Row(cursor, row)
for col in smart_row.keys():
# Like saying book, title, whatever value is coming from database
setattr(instance, col, smart_row[col])
return instance
def create_tech_table(cursor, row):
row = sqlite3.Row(cursor, row)
tech_type = Tech_Type()
tech_type.tech_type_id = row[0]
tech_type.name = row[1]
return (tech_type)
def create_company(cursor, row):
row = sqlite3.Row(cursor, row)
company = Company()
company.id = row[0]
company.name = row[1]
return (company, )
def create_technology_table(cursor, row):
row = sqlite3.Row(cursor, row)
technology = Tech_Type()
technology.id = row[0]
technology.name = row[1]
return (technology)
def create_ratings(cursor, row):
row = sqlite3.Row(cursor, row)
rating = Rating()
rating.number = row["number"]
rating.total = row["total"]
rating.employee_profile_id = row["employee_profile_id"]
return rating
def loadDB(path):
for typename, converter in CONVERTERS.items():
sqlite3.register_converter(typename, converter)
conn = sqlite3.connect(path, detect_types=sqlite3.PARSE_DECLTYPES)
conn.row_factory = lambda cursor, row: dict(sqlite3.Row(cursor, row))
return {table: _loadTable(conn, table) for table in _getTables(conn)}
def create(cursor, row):
instance = model_type()
smart_row = sqlite3.Row(cursor, row)
# smart_row.keys allows you to access the row keys, equivalent to row["id"]
for col in smart_row.keys():
# col is only id
setattr(instance, col, smart_row[col])
return instance
def create_employee(cursor, row):
_row = sqlite3.Row(cursor, row)
employee.id = _row["employee_id"]
employee.first_name = _row["first_name"]
employee.last_name = _row["last_name"]
return employee
def collect_sql_quarry_result(self, sql_code, quarry_args=None, num_of_rows=None, filer_unique=True,
rows_dict=True, decode_rows=True):
"""
Asks for information from the database. It is returned in the form of a query.
If you have threads in your project that use this module to interact with the database, this function will
also make sure that every time the module collects a quarry, it not happen at the same time.
:param sql_code: a string of a sql code
:param num_of_rows: The number of top rows you want from a quarry result
(if the argument is null then it will just return all the rows it found)
:param quarry_args:a dictionary/list of values you want to safely escape values into the code.
:param filer_unique: if many rows are returned, but they're all one column,
it will return a list of column values instead of a list of tuples with one value in them.
:param rows_dict: will automatically configure a row to a dictionary of columns and the values
in them. It will do this as default, if you want the quarry result as it is, enter false.
:param decode_rows: Will decode complicated types you saved like dictioanries and lists, from bytes
to their types.
:return: a query as a list of rows. If it didn't find an rows, it will return None
"""
prev_row_factory = self.db_cursor.row_factory
l_func = self.__type_load
if rows_dict:
if decode_rows:
self.db_cursor.row_factory = \
lambda r_cur, row: {k: l_func(sqlite3.Row(r_cur, row)[k]) for k in sqlite3.Row(r_cur, row).keys()}
else:
self.db_cursor.row_factory = sqlite3.Row
else:
if decode_rows:
if filer_unique:
self.db_cursor.row_factory = lambda cursor, row: l_func(row[0]) if len(row) == 1 else \
[l_func(c) for c in row]
else:
self.db_cursor.row_factory = lambda cursor, row: [l_func(c) for c in row]
else:
if filer_unique:
self.db_cursor.row_factory = lambda cursor, row: row[0] if len(row) == 1 else row
with self.db_con:
if quarry_args is None:
quarry_args = []
try:
self.db_cursor.execute(sql_code, quarry_args)
if not num_of_rows:
query_result = self.db_cursor.fetchall()
elif num_of_rows > 0:
query_result = self.db_cursor.fetchmany(num_of_rows)
else:
raise WritingError("The number of rows needs to be positive")
self.db_cursor.row_factory = prev_row_factory
return query_result
except sqlite3.Error as e:
print('The sql code "{}" caused an error'.format(sql_code))
raise e
def get_db():
if 'db' not in g:
g.db = sqlite3.connect(
current_app.config['DATABASE'],
detect_types=sqlite3.PARSE_DECLTYPES,
)
g.db.row_factory = sqlite3.Row()
return g.db
def create_department(cursor, row):
_row = sqlite3.Row(cursor, row)
department = Department()
department.id = _row["id"]
department.dept_name = _row["dept_name"]
department.budget = _row["budget"]
return department
def create_employee_with_department(cursor, row):
_row = sqlite3.Row(cursor, row)
department = Department()
department.id = _row["dept_id"]
department.dept_name = _row["dept_name"]
employee.department = department
return department
def create_bread_ingredient(cursor, row):
_row = sqlite3.Row(cursor, row)
ingredient = Ingredient()
ingredient.bread_ingredient_id = _row["id"]
ingredient.name = _row["name"]
ingredient.amount = _row["amount"]
return ingredient
def create_computer(cursor, row):
_row = sqlite3.Row(cursor, row)
computer = Computer()
computer.id = _row["id"]
computer.make = _row["make"]
computer.model = _row["model"]
computer.purchase_date = _row["purchase_date"]
return computer
def create_employee(cursor, row):
_row = sqlite3.Row(cursor, row)
employee = Employee()
employee.id = _row['id']
employee.first_name = _row['first_name']
employee.last_name = _row['last_name']
return employee
def create_employee_computers(cursor, row):
_row = sqlite3.Row(cursor, row)
computer = Computer()
computer.manufacturer = _row["comp_manufacturer"]
computer.make = _row["comp_make"]
computer.id = _row["comp_id"]
employee.computer = computer
return computer
