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