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 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 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 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()