def populate_db():
"""
add departments 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: just like Person. We use the foreign key'
)
# index used to clearly read code when index on a list of tuple data below
dep_num = 0
dep_name = 1
dep_head = 2
dep_job = 3
dep_data = [
("0001", "Shipping", "D. Vader", "Analyst"),
("0002", "Shipping", "O. Kenobi", "Senior analyst"),
("0003", "Shipping", "O. Kenobi", "Senior business analyst"),
("0052", "Product Development", "O. Kenobi", "Admin supervisor"),
("0053", "Product Development", "O. Kenobi", "Admin manager"),
]
try:
# open connect to database, db defined above as sqlite + path
database.connect()
# not sure what this does
database.execute_sql('PRAGMA foreign_keys = ON;')
# load raw data to database
for dep in dep_data:
# database.transaction() opens writing (similar to sys file write)
with database.transaction():
# create new entry in Department class
# the Department class inherited from peewee Meta, and has .create for new entry
new_dep = Department.create(dep_num=dep[dep_num],
dep_name=dep[dep_name],
dep_head=dep[dep_head],
dep_job=dep[dep_job])
new_dep.save()
logger.info('Reading and print all Department rows')
for dep in Department:
logger.info(
f'Job Title: {dep.dep_job} In Department: {dep.dep_name} [{dep.dep_num}], Head: {dep.dep_head}'
)
except Exception as e:
logger.info(f'Error creating = {dep[dep_name]}')
logger.info(e)
finally:
logger.info('database closes')
database.close()
def populate_db():
"""
add department data to database
"""
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)
database = SqliteDatabase(os.path.abspath('data/personjob.db'))
logger.info('Working with Department class')
logger.info(
'Note how I use constants and a list of tuples as a simple schema')
logger.info(
'Normally you probably will have prompted for this from a user')
dept_number = 0
dept_name = 1
dept_manager = 2
departments = [
('A110', 'Executive', 'Michael Gregor'),
('B210', 'Accounting', 'Johnny Goodfella'),
('C310', 'Human Resources', 'Kat Robinson'),
('D410', 'Engineering', 'Anita Smith'),
('E510', 'Facilities', 'Duke Rodgers'),
]
logger.info(
'Creating Department records: iterate through the list of tuples')
logger.info('Prepare to explain any errors with exceptions')
logger.info('and the transaction tells the database to fail on error')
try:
database.connect()
database.execute_sql('PRAGMA foreign_keys = ON;')
for dept in departments:
with database.transaction():
new_dept = Department.create(dept_number=dept[dept_number],
dept_name=dept[dept_name],
dept_manager=dept[dept_manager])
new_dept.save()
logger.info('Database add successful')
logger.info('Print the Department records we saved...')
for saved_dept in Department:
logger.info(f'{saved_dept.dept_name} ' + \
f'is managed by {saved_dept.dept_manager}. ' + \
f'The department number is {saved_dept.dept_number}')
except Exception as e:
logger.info(f'Error creating = {dept[dept_number]}')
logger.info(e)
logger.info('See how the database protects our data')
finally:
logger.info('database closes')
database.close()
def populate_dept():
"""
add person data to database
"""
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)
database = SqliteDatabase('personjob.db')
logger.info('Working with Department class')
logger.info(
'Note how I use constants and a list of tuples as a simple schema')
logger.info(
'Normally you probably will have prompted for this from a user')
dept_num = 0
dept_name = 1
dept_manager = 2
department = [
('B100', 'Business', 'Smith'),
('A200', 'Administration', 'Jones'),
('C300', 'Computers', 'Howard'),
]
logger.info(
'Creating Department records: iterate through the list of tuples')
logger.info('Prepare to explain any errors with exceptions')
logger.info('and the transaction tells the database to fail on error')
try:
database.connect()
database.execute_sql('PRAGMA foreign_keys = ON;')
for department in department:
with database.transaction():
new_dept = Department.create(
dept_num=department[dept_num],
dept_name=department[dept_name],
dept_manager=department[dept_manager])
new_dept.save()
logger.info('Database add successful')
logger.info('Print the Department records we saved...')
for saved_dept in Department:
logger.info(f'{saved_dept.dept_num} department code is for {saved_dept.dept_name} ' + \
f'and the manager is: {saved_dept.dept_manager}')
except Exception as e:
logger.info(f'Error creating = {department[dept_num]}')
logger.info(e)
logger.info('See how the database protects our data')
finally:
logger.info('database closes')
database.close()
def populate_departments():
"""
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')
dept_number = 0
dept_name = 1
start_date = 2
end_date = 3
departments = [('A001', 'Mailroom', '2001-09-22', '2003-01-30'),
('B001', 'Sales', '2003-02-01', '2006-10-22')]
try:
database.connect()
database.execute_sql('PRAGMA foreign_keys = ON;')
# delete the departments since they are added each time
# this script is run.
# d1 = Department.get(Department.name == 'Mailroom')
# d1.delete_instance()
# d2 = Department.get(Department.name == 'Sales')
# d2.delete_instance()
for department in departments:
with database.transaction():
logger = logging.getLogger(f'creating {department[dept_name]}')
new_dept = Department.create(number=department[dept_number],
name=department[dept_name],
start_date=department[start_date],
end_date=department[end_date])
new_dept.save()
logger.info('Reading and print all Department rows...')
for dept in Department:
logger.info(
f'{dept.number} {dept.name}: {dept.start_date} to {dept.end_date}'
)
except Exception as e:
logger.info(f'Error creating = {department[dept_name]}')
logger.info(e)
finally:
logger.info('database closes')
database.close()
def join_classes():
"show all departments a person worked"
"""
demonstrate how to join classes together : matches
"""
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)
database = SqliteDatabase('data/personjob.db')
logger.info('Now resolve the join and print (INNER shows only jobs that match person and dep that match job)')
logger.info('Notice how we use a query variable in this case')
logger.info('We select the classes we need, and we join Person to Job')
logger.info('Inner join (which is the default) shows only records that match')
try:
database.connect()
database.execute_sql('PRAGMA foreign_keys = ON;')
query1 = Person.select(Person, Job).join(Job, JOIN.INNER)
logger.info('View matching records from tables')
print("{:30s}|{:30s}|{:15}|{:30}|{:4}".format("Name", "Job", "Duration [days]", "Department", "ID"))
for person in query1:
job_start = person.job.start_date
job_end = person.job.end_date
y1 = int(job_start.split("-")[0])
y2 = int(job_end.split("-")[0])
m1 = int(job_start.split("-")[1])
m2 = int(job_end.split("-")[1])
d1 = int(job_start.split("-")[2])
d2 = int(job_end.split("-")[2])
job_dur = date(y2, m2, d2) - date(y1, m1, d1)
dep = Department.get(Department.dep_job == person.job.job_name)
print("{:30s}|{:30s}|{:15}|{:30}|{:4}".format(person.person_name,
person.job.job_name,
job_dur.days,
dep.dep_name,
dep.dep_num))
except Exception as e:
logger.info(e)
finally:
logger.info('database closes')
database.close()
def populate_db():
"""
add department data to database
"""
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)
database = SqliteDatabase('../data/personjob.db')
department_number = 0
department_name = 1
department_manager = 2
departments = [
('A101', 'US', 'Donald'),
('A201', 'China', 'Xi'),
('A301', 'Russia', 'Putin'),
('A401', 'Germany', 'Merkel'),
('A501', 'Canada', 'Trudeau'),
]
try:
database.connect()
database.execute_sql('PRAGMA foreign_keys = ON;')
for department in departments:
with database.transaction():
new_department = Department.create(
department_number = department[department_number],
department_name = department[department_name],
department_manager = department[department_manager])
new_department.save()
logger.info('Database add successful')
logger.info('Print the Department records we saved...')
for saved_department in Department:
logger.info(f'Dept_num:{saved_department.department_number} Name:{saved_department.department_name} ' + \
f'Manager {saved_department.department_manager}')
except Exception as e:
logger.info(f'Error creating = {department[department_name]}')
logger.info(e)
logger.info('See how the database protects our data')
finally:
logger.info('database closes')
database.close()
def populate_department():
"""
add department data to database
"""
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)
database = SqliteDatabase('personjob.db')
logger.info('Working with Department class')
job_name = 0
department_number = 1
department_name = 2
department_manager = 3
depts = [('Analyst', 'A123', 'Analytics', 'Navdeep'),
('Senior analyst', 'A123', 'Analytics', 'Navdeep'),
('Senior business analyst', 'A123', 'Analytics', 'Navdeep'),
('Admin manager', 'B123', 'Administrative', 'Kaleb'),
('Admin supervisor', 'B123', 'Administrative', 'Kaleb')]
try:
database.connect()
database.execute_sql('PRAGMA foreign_keys = ON;')
for dept in depts:
with database.transaction():
new_dept = Department.create(
job_title=dept[job_name],
department_number=dept[department_number],
department_name=dept[department_name],
department_manager=dept[department_manager])
new_dept.save()
for saved_dept in Department:
logger.info(
f'{saved_dept.department_number}, {saved_dept.department_name}, {saved_dept.department_manager}'
)
except Exception as e:
logger.info(f'Error creating = {dept[department_number]}')
logger.info(e)
finally:
database.close()
def test_department(self):
query = Department.get(Department.dep_job == "Analyst")
self.assertEqual(query.dep_name, "Shipping")
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()