def populate_db():
"""
add job data to database
"""
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)
database = SqliteDatabase('../data/personjob.db')
logger.info('Working with Job class')
logger.info(
'Creating Job records: just like Person. We use the foreign key')
job_name = 0
start_date = 1
end_date = 2
salary = 3
person_employed = 4
department = 5
jobs = [('Analyst', '2001-09-22', '2003-01-30', 65500, 'Andrew', 'A101'),
('Senior analyst', '2003-02-01', '2006-10-22', 70000, 'Andrew',
'A201'),
('Senior business analyst', '2006-10-23', '2016-12-24', 80000,
'Andrew', 'A301'),
('Admin supervisor', '2012-10-01', '2014-11,10', 45900, 'Peter',
'401'),
('Admin manager', '2014-11-14', '2018-01,05', 45900, 'Peter',
'501')]
try:
database.connect()
database.execute_sql('PRAGMA foreign_keys = ON;')
for job in jobs:
with database.transaction():
new_job = Job.create(job_name=job[job_name],
start_date=job[start_date],
end_date=job[end_date],
salary=job[salary],
person_employed=job[person_employed],
department=job[department])
new_job.save()
logger.info(
'Reading and print all Job rows (note the value of person)...')
for job in Job:
logger.info(
f'{job.job_name} : {job.start_date} to {job.end_date} for {job.person_employed}'
)
except Exception as e:
logger.info(f'Error creating = {job[job_name]}')
logger.info(e)
finally:
logger.info('database closes')
database.close()
def show_integrity_add():
"""
demonstrate how database protects data inegrity : add
"""
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)
database = SqliteDatabase('../data/personjob.db')
job_name = 0
start_date = 1
end_date = 2
salary = 3
person_employed = 4
try:
database.connect()
database.execute_sql('PRAGMA foreign_keys = ON;')
with database.transaction():
logger.info('Try to add a new job where a person doesnt exist...')
addjob = ('Sales', '2010-04-01', '2018-02-08', 80400, 'Harry')
logger.info('Adding a sales job for Harry')
logger.info(addjob)
new_job = Job.create(job_name=addjob[job_name],
start_date=addjob[start_date],
end_date=addjob[end_date],
salary=addjob[salary],
person_employed=addjob[person_employed])
new_job.save()
except Exception as e:
logger.info('Add failed because Harry is not in Person')
logger.info(f'For Job create: {addjob[0]}')
logger.info(e)
def test_jobs(self):
query = Job.get(Job.person_employed == "Andrew")
self.assertEqual(query.job_name, "Analyst")
def populate_db():
"""
add job data to database
"""
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)
database = SqliteDatabase('../data/personjob.db')
logger.info("Working with Department class")
logger.info("Creating Department Records")
department_code = 0
department_name = 1
department_manager = 2
departments = [("A001", "Sales", "Susan"), ("A002", "Support", "Pam"),
("A003", "HR", "Steven")]
logger.info('Working with Job class')
logger.info(
'Creating Job records: just like Person. We use the foreign key')
logger.info("Added time_held and reference to department_employed here")
job_name = 0
start_date = 1
end_date = 2
time_held = 3
salary = 4
person_employed = 5
department_employed = 6
jobs = [
('Analyst', '2001-09-22', '2003-01-30',
dates_duration('2001-09-22', '2003-01-30'), 65500, 'Andrew', 'A001'),
('Senior analyst', '2003-02-01', '2006-10-22',
dates_duration('2003-02-01', '2006-10-22'), 70000, 'Andrew', 'A001'),
('Senior business analyst', '2006-10-23', '2016-12-24',
dates_duration('2006-10-23', '2016-12-24'), 80000, 'Andrew', 'A001'),
('Admin supervisor', '2012-10-01', '2014-11-10',
dates_duration('2012-10-01', '2014-11-10'), 45900, 'Peter', 'A002'),
('Admin manager', '2014-11-14', '2018-01-05',
dates_duration('2014-11-14', '2018-01-05'), 45900, 'Peter', 'A002')
]
try:
database.connect()
database.execute_sql('PRAGMA foreign_keys = ON;')
for department in departments:
with database.transaction():
new_department = Department.create(
department_code=department[department_code],
department_name=department[department_name],
department_manager=department[department_manager])
new_department.save()
for job in jobs:
with database.transaction():
new_job = Job.create(
job_name=job[job_name],
start_date=job[start_date],
end_date=job[end_date],
time_held=job[time_held],
salary=job[salary],
person_employed=job[person_employed],
department_employed=job[department_employed])
new_job.save()
logger.info(
'Reading and print all Job rows (note the value of person)...')
for job in Job:
logger.info(
f'{job.job_name} : {job.start_date} to {job.end_date} for {job.person_employed}'
)
except Exception as e:
logger.info(f'Error creating = {job[job_name]}')
logger.info(e)
finally:
logger.info('database closes')
database.close()