def add_and_delete():
""""
show how we can add a record, and delete a record
"""
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)
database = SqliteDatabase('../data/personjob.db')
try:
database.connect()
database.execute_sql('PRAGMA foreign_keys = ON;')
logger.info('Add and display a Person called Fred; then delete him...')
logger.info('Add Fred in one step')
new_person = Person.create(person_name='Fred',
lives_in_town='Seattle',
nickname='Fearless')
new_person.save()
logger.info('Show Fred')
aperson = Person.get(Person.person_name == 'Fred')
logger.info(
f'We just created {aperson.person_name}, who lives in {aperson.lives_in_town}'
)
logger.info('but now we will delete him...')
# The record we last got is the one that's kind of like in focus
# So in this case Fred is in focus. He is in the aperson variable.
# so if we say aperson.delete_instance we will be deleting Fred
aperson.delete_instance()
logger.info(
'Reading and print all Person records (but not Fred; he has been deleted)...'
)
# We prove that Fred is gone by showing the logger.info() f string and seeing that
# Fred is not in the database.
for person in Person:
logger.info(
f"{person.person_name} lives in {person.lives_in_town} and likes to be known as {person.nickname}"
)
except Exception as e:
logger.info(e)
finally:
database.close()
def select_and_update():
""""
show how we can select a specific record, and then search and read through several records
"""
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)
database = SqliteDatabase('data/personjob.db')
try:
database.connect()
database.execute_sql('PRAGMA foreign_keys = ON;')
logger.info('Find and display by selecting a spcific Person name...')
aperson = Person.get(Person.person_name == 'Susan')
logger.info(f'{aperson.person_name} lives in {aperson.lives_in_town} ' + \
f' and likes to be known as {aperson.nickname}')
logger.info('Search and display all Person with missing nicknames')
logger.info(
'Our person class inherits select(). Specify search with .where()')
logger.info('Peter gets a nickname but noone else')
for person in Person.select().where(Person.nickname is None):
logger.info(
f'{person.person_name} does not have a nickname; see: {person.nickname}'
)
if person.person_name == 'Peter':
logger.info('Changing nickname for Peter')
logger.info('Update the database')
person.nickname = 'Painter'
person.save()
else:
logger.info(f'Not giving a nickname to {person.person_name}')
logger.info(
'And here is where we prove it by finding Peter and displaying')
aperson = Person.get(Person.person_name == 'Peter')
logger.info(
f'{aperson.person_name} now has a nickname of {aperson.nickname}')
except Exception as e:
logger.info(e)
finally:
database.close()
def pretty_print_departments():
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)
database = SqliteDatabase('../data/personjob.db')
logger.info('Print Job class with related departments')
try:
database.connect()
database.execute_sql('PRAGMA foreign_keys = ON;')
query = (Person.select(Person, Job).join(Job, JOIN.INNER))
logger.info('View matching records from both tables')
for person in query:
d1 = person.job.department.start_date
# print(d1) # 2001-09-22
d2 = person.job.department.end_date
dd1 = datetime.strptime(d1, "%Y-%m-%d")
dd2 = datetime.strptime(d2, "%Y-%m-%d")
delta = dd2 - dd1
# print('days: ', delta.days)
logger.info(
f'Person {person.person_name} had job {person.job.job_name} in {person.job.department.name} for {delta.days} days'
)
except Exception as e:
logger.info(f'Error creating = {job[JOB_NAME]}')
logger.info(e)
finally:
logger.info('database closes')
database.close()
def print_info():
"""
connects to database and queries/prints list of users, jobs, and departments.
:return: printed list of users, jobs, and departments
"""
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)
database = SqliteDatabase('../data/personjob.db')
try:
database.connect()
database.execute_sql('PRAGMA foreign_keys = ON;')
query = (Person.select(
Person.person_name, Job.job_name,
Department.department_name).join(Job).join(Department).tuples())
for record in query:
pprint.pprint(record)
except Exception as e:
logger.info(e)
finally:
logger.info('database closes')
database.close()
def show_integrity_del():
"""
demonstrate how database protects data inegrity : delete
"""
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)
database = SqliteDatabase('../data/personjob.db')
try:
database.connect()
database.execute_sql('PRAGMA foreign_keys = ON;')
logger.info('Try to Delete a person who has jobs...')
with database.transaction():
aperson = Person.get(Person.person_name == 'Andrew')
logger.info(
f'Trying to delete {aperson.person_name} who lives in {aperson.lives_in_town}'
)
aperson.delete_instance()
except Exception as e:
logger.info('Delete failed because Andrew has Jobs')
logger.info(f'Delete failed: {aperson.person_name}')
logger.info(e)
finally:
database.close()
def cant_change_pk():
"""
show that PKs cant be changed (and that there is no error!)
"""
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)
logger.info("Back to Person class: try to change Peter's name")
aperson = Person.get(Person.person_name == 'Peter')
logger.info(f'Current value is {aperson.person_name}')
logger.info('Update Peter to Peta, thereby trying to change the PK...')
database = SqliteDatabase('../data/personjob.db')
try:
try:
with database.transaction():
aperson = Person.get(Person.person_name == 'Peter')
aperson.person_name = 'Peta'
aperson.save()
logger.info(f'Tried to change Peter to {aperson.person_name}')
except Exception as e:
logger.info(f'Cant change a PK and caught you trying'
) # not caught; no error thrown by Peewee
logger.info(e)
aperson = Person.get(Person.person_name == 'Peter')
logger.info(f'Looked for Peter: found! -> {aperson.person_name}')
try:
aperson = Person.get(Person.person_name == 'Peta')
except Exception as e:
logger.info(
f'Looking for Peta results in zero records. PK changes are ignored and do not throw an '
f'error!!!!')
logger.info(f'Cant change a PK')
logger.info(
'PK "change" can only be achieved with a delete and new create'
)
finally:
database.close()
def add_and_delete():
""""
show how we can add a record, and delete a record
"""
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)
database = SqliteDatabase('../data/personjob.db')
try:
database.connect()
database.execute_sql('PRAGMA foreign_keys = ON;')
logger.info('Add and display a Person called Fred; then delete him...')
logger.info('Add Fred in one step')
new_person = Person.create(person_name='Fred',
lives_in_town='Seattle',
nickname='Fearless')
new_person.save()
logger.info('Show Fred')
aperson = Person.get(Person.person_name == 'Fred')
logger.info(
f'We just created {aperson.person_name}, who lives in {aperson.lives_in_town}'
)
logger.info('but now we will delete him...')
aperson.delete_instance()
logger.info(
'Reading and print all Person records (but not Fred; he has been deleted)...'
)
for person in Person:
logger.info(
f"{person.person_name} lives in {person.lives_in_town} and likes to be known as {person.nickname}"
)
except Exception as e:
logger.info(e)
finally:
database.close()
def join_classes():
"""
demonstrate how to join classes together : no matches too
"""
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)
database = SqliteDatabase('../data/personjob.db')
logger.info(
'View matching records and Persons without Jobs (note LEFT_OUTER)')
try:
database.connect()
database.execute_sql('PRAGMA foreign_keys = ON;')
query = (Person.select(Person, Job).join(Job, JOIN.LEFT_OUTER))
for person in query:
try:
logger.info(
f'Person {person.person_name} had job {person.job.job_name}'
)
except Exception as e:
logger.info(f'Person {person.person_name} had no job')
logger.info('Example of how to summarize data')
logger.info('Note select() creates a count and names it job_count')
logger.info('group_by and order_by control level and sorting')
query = (Person.select(Person,
fn.COUNT(Job.job_name).alias('job_count')).join(
Job,
JOIN.LEFT_OUTER).group_by(Person).order_by(
Person.person_name))
for person in query:
logger.info(f'{person.person_name} had {person.job_count} jobs')
except Exception as e:
logger.info(e)
finally:
database.close()
def populate_db():
"""
add person data to database
"""
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)
database = SqliteDatabase(os.path.abspath('data/personjob.db'))
logger.info('Working with Person 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')
person_name = 0
lives_in_town = 1
nickname = 2
people = [
('Andrew', 'Sumner', 'Andy'),
('Peter', 'Seattle', None),
('Susan', 'Boston', 'Beannie'),
('Pam', 'Coventry', 'PJ'),
('Steven', 'Colchester', None),
]
logger.info('Creating Person 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 person in people:
with database.transaction():
new_person = Person.create(person_name=person[person_name],
lives_in_town=person[lives_in_town],
nickname=person[nickname])
new_person.save()
logger.info('Database add successful')
logger.info('Print the Person records we saved...')
for saved_person in Person:
logger.info(f'{saved_person.person_name} lives in {saved_person.lives_in_town} ' + \
f'and likes to be known as {saved_person.nickname}')
except Exception as e:
logger.info(f'Error creating = {person[person_name]}')
logger.info(e)
logger.info('See how the database protects our data')
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 join_classes():
"""
demonstrate how to join classes together : matches
"""
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)
database = SqliteDatabase('../data/personjob.db')
logger.info('Working with Job class')
logger.info(
'Now resolve the join and print (INNER shows only jobs that match person)...'
)
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;')
query = (Person.select(Person, Job).join(Job, JOIN.INNER))
logger.info('View matching records from both tables')
for person in query:
logger.info(
f'Person {person.person_name} in department {person.department_id} had job {person.job.job_name}'
)
except Exception as e:
logger.info(f'Error creating = {job[JOB_NAME]}')
logger.info(e)
finally:
logger.info('database closes')
database.close()
def select_dept():
"""
select departments to show they are in database
"""
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)
database = SqliteDatabase('personjob.db')
try:
database.connect()
database.execute_sql('PRAGMA foreign_keys = ON;')
query = (Person.select(Person, Job,
Department).join(Job, JOIN.INNER).join(
Department, JOIN.INNER))
logger.info('View each department each person has worked in')
for person in query:
logger.info(
f'Person: {person.person_name}, Job: {person.job.job_name}, Department: {person.job.department.department_name}'
)
except Exception as e:
logger.info(e)
finally:
database.close()
def setUp(self):
database = SqliteDatabase('data/personjob.db')
database.connect()
database.execute_sql('PRAGMA foreign_keys = ON;')
self.test_person = Person.get(Person.person_name == "Andrew")
def add_with_without_bk():
"""
demonstrate impact of business keys
"""
person_name = 0
lives_in_town = 1
nickname = 2
people = [
('Andrew', 'Sumner', 'Andy'),
('Peter', 'Seattle', None),
('Susan', 'Boston', 'Beannie'),
('Pam', 'Coventry', 'PJ'),
('Steven', 'Colchester', None),
]
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)
database = SqliteDatabase('data/personjob.db')
logger.info('Try creating Person records again: it will fail')
try:
database.connect()
database.execute_sql('PRAGMA foreign_keys = ON;')
try:
with database.transaction():
for person in people:
new_person = Person.create(
person_name=person[person_name],
lives_in_town=person[lives_in_town],
nickname=person[nickname])
new_person.save()
logger.info('Database add successful')
except Exception as e:
logger.info(f'Error creating = {person[person_name]}')
logger.info(e)
logger.info(
'We make sure duplicates are not unintentionally created this way')
logger.info('BUT: how do we really identify a Person (uniquely)?')
logger.info(
'Creating Person records, but in a new table with generated PK...')
try:
with database.transaction():
for person in people:
new_person = PersonNumKey.create(
person_name=person[person_name],
lives_in_town=person[lives_in_town],
nickname=person[nickname])
new_person.save()
except Exception as e:
logger.info(f'Error creating = {person[0]}')
logger.info(e)
logger.info('Watch what happens when we do it again')
try:
with database.transaction():
for person in people:
new_person = PersonNumKey.create(
person_name=person[person_name],
lives_in_town=person[lives_in_town],
nickname=person[nickname])
new_person.save()
except Exception as e:
logger.info(f'Error creating = {person[0]}')
logger.info(e)
logger.info(
'Note no PK specified, no PK violation; "duplicates" created!')
for person in PersonNumKey.select():
logger.info(f'Name : {person.person_name} with id: {person.id}')
except Exception as e:
logger.info(e)
finally:
database.close()
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)
database = SqliteDatabase('./data/personjob.db')
logger.info('View matching records and Persons without Jobs (note LEFT_OUTER)')
# person name, department job, duration
try:
database.connect()
database.execute_sql('PRAGMA foreign_keys = ON;')
query = (Person.select(Person.person_name, Job.job_name, Job.start_date,
Job.end_date, Job_Department.dept_id).join(
Job,
JOIN.LEFT_OUTER).join(Job_Department,
JOIN.LEFT_OUTER))
for person in query:
try:
duration = (
datetime.strptime(person.job.end_date, '%Y-%m-%d') -
datetime.strptime(person.job.start_date, '%Y-%m-%d')).days
logger.info(
f'Person {person.person_name} had job {person.job.job_name} for {duration} days in dept: {person.job.job_department.dept_id.name}'
)
pprint(
f'{person.person_name} had job {person.job.job_name} for {duration} days in dept: {person.job.job_department.dept_id.name}'
)
