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}' )