def add_customer(customer_id, name, lastname, home_address, phone_number,
email_address, status, credit_limit):
""" This function will add a new customer to the customers.db database """
logger.info('In add_customer().')
database = SqliteDatabase('customers.db')
try:
database.connect()
database.execute_sql('PRAGMA foreign_keys = ON;')
with database.transaction():
new_customer = Customer.create(customer_id=customer_id,
name=name,
lastname=lastname,
home_address=home_address,
phone_number=phone_number,
email_address=email_address,
status=status,
credit_limit=credit_limit)
new_customer.save()
logger.info('Add new customer to Customer database successfully.')
except Exception as e:
logger.info(f'Error creating {name} customer record.')
logger.info(e)
finally:
logger.info('database closes.')
database.close()
def loader():
database = SqliteDatabase(dbname)
names = 0
donations = 1
try:
database.connect()
database.execute_sql('PRAGMA foreign_keys = ON;')
for entry in raw_input:
with database.transaction():
new_donor = Donor.create(name=entry[names])
# new_donor.save()
new_donation = Donation.create(gift_id=uuid.uuid4(),
value=entry[donations],
donated_by=entry[names],
gift_num=1)
# new_donation.save()
logging.info('Database add successful')
# except Exception as e:
# logging.info(e)
finally:
logging.info('database closes')
database.close()
tag = ForeignKeyField(Tag)
class Meta:
database = db
if __name__ == '__main__':
import os, sys
os.system("rm question.db")
WrittenQuestion.create_table()
Tag.create_table()
QuestionTag.create_table()
client = MongoClient()
a = 0
total = client.test.writtenquestions.find().count()
with db.transaction():
for question in client.test.writtenquestions.find():
a += 1
sys.stdout.write("%s/%s\r" % (a, total))
sys.stdout.flush()
written_question = WrittenQuestion.create(
departement_fr=question["departement"]["fr"],
departement_nl=question["departement"]["nl"],
status_fr=question["status"]["fr"],
status_nl=question["status"]["nl"],
publication_question_pdf_url=question["publication_question_pdf_url"],
publication_reponse_pdf_url=question["publication_reponse_pdf_url"],
question_fr=question["question"]["fr"],
question_nl=question["question"]["nl"],
answer_fr=question["answer"]["fr"],
answer_nl=question["answer"]["nl"],
class LoadToDB:
def __init__(self, idbname):
logging.info("Link sqlite path to database")
self.database = SqliteDatabase(idbname)
def db_con(self):
logging.info("Establishing connection to database")
self.database.connect()
# execute SQL directly
self.database.execute_sql('PRAGMA foreign_keys = ON;')
def db_discon(self):
logging.info("Database closed")
self.database.close()
def check_unique(self, iname):
try:
Donor.get(Donor.name == iname)
logging.info(f"{iname} is on record")
return False
except Exception as e:
logging.info(f"{iname} is not on record")
logging.info(e)
return True
def add(self, iname, idonation=0.0):
# gift count to account for entry that is name only, or zero donation specified
if idonation == 0:
gift_count = 0
else:
gift_count = 1
self.db_con()
if self.check_unique(iname):
logging.info(
f"Adding new donor {iname}, with a donation of ${idonation}")
with self.database.transaction():
new_person = Donor.create(name=iname)
new_donation = Donation.create(gift_num=gift_count,
value=idonation,
donated_by=iname,
gift_id=uuid.uuid4())
# new_person.save()
# new_donation.save()
logging.info('Database add successful')
print(f"{iname} has been added to the Database")
else:
logging.info(
f"Donor {iname} already on record, adding ${idonation} to existing funds."
)
old_d = Donation.get(Donation.donated_by == iname).value
old_g = Donation.get(Donation.donated_by == iname).gift_num
# only try to add to Donation primary key if donation amount dont not equal zero
if gift_count > 0:
with self.database.transaction():
new_donation = Donation.create(gift_id=uuid.uuid4(),
gift_num=(old_g + 1),
value=(old_d + idonation),
donated_by=iname)
# new_donation.save()
logging.info('Database add successful')
print(
f"{iname} with a donation of ${idonation:.2f} has been added to the Database"
)
self.db_discon()
def remove(self, iname):
self.db_con()
if self.check_unique(iname):
print("Name is not on record")
else:
query = Donor.get(Donor.name == iname)
query.delete_instance()
query = Donation.get(Donation.donated_by == iname)
query.delete_instance()
print(f"{iname} removed from Database")
self.db_discon()
def remove_donation(self, iname, igift):
self.db_con()
try:
query = Donation.get(Donation.donated_by == iname,
Donation.gift_num == igift)
except Exception as e:
logging.info(
f"{iname} with {igift} is not on record of Donation DB")
logging.info(e)
else:
if int(igift) >= 1:
query.delete_instance()
print(f"{iname} gift number {igift} removed from Database")
self.db_discon()
class Migrations(object):
"""
Migrations
Handle all migrations during application start.
"""
logger = None
database = None
def __init__(self, config):
unmanic_logging = unlogger.UnmanicLogger.__call__()
self.logger = unmanic_logging.get_logger(__class__.__name__)
# Based on configuration, select database to connect to.
if config['TYPE'] == 'SQLITE':
# Create SQLite directory if not exists
db_file_directory = os.path.dirname(config['FILE'])
if not os.path.exists(db_file_directory):
os.makedirs(db_file_directory)
self.database = SqliteDatabase(config['FILE'])
self.router = Router(database=self.database,
migrate_table='migratehistory_{}'.format(
config.get('MIGRATIONS_HISTORY_VERSION')),
migrate_dir=config.get('MIGRATIONS_DIR'),
logger=self.logger)
def __log(self, message, level='info'):
if self.logger:
getattr(self.logger, level)(message)
else:
print(message)
def __run_all_migrations(self):
"""
Run all new migrations.
Migrations that have already been run will be ignored.
:return:
"""
self.router.run()
def update_schema(self):
"""
Updates the Unmanic database schema.
Newly added tables/models and columns/fields will be automatically generated by this function.
This way we do not need to create a migration script unless we:
- rename a column/field
- delete a column/field
- delete a table/model
:return:
"""
# Fetch all model classes
all_models = []
all_base_models = []
for model in list_all_models():
imported_model = getattr(
importlib.import_module("unmanic.libs.unmodels"), model)
if inspect.isclass(imported_model) and issubclass(
imported_model, BaseModel):
# Add this model to both the 'all_models' list and our list of base models
all_models.append(imported_model)
all_base_models.append(imported_model)
elif inspect.isclass(imported_model) and issubclass(
imported_model, Model):
# If the model is not one of the base models, it is an in-build model from peewee.
# For, this list of models we will not run a migration, but we will still ensure that the
# table is created in the DB
all_models.append(imported_model)
pass
# Start by creating all models
self.__log("Initialising database tables")
try:
with self.database.transaction():
for model in all_models:
self.router.migrator.create_table(model)
self.router.migrator.run()
except Exception:
self.database.rollback()
self.__log("Initialising tables failed", level='exception')
raise
# Migrations will only be used for removing obsolete columns
self.__run_all_migrations()
# Newly added fields can be auto added with this function... no need for a migration script
# Ensure all files are also present for each of the model classes
self.__log("Updating database fields")
for model in all_base_models:
# Fetch all peewee fields for the model class
# https://stackoverflow.com/questions/22573558/peewee-determining-meta-data-about-model-at-run-time
fields = model._meta.fields
# loop over the fields and ensure each on exists in the table
field_keys = [f for f in fields]
for fk in field_keys:
field = fields.get(fk)
if isinstance(field, Field):
if not any(f for f in self.database.get_columns(
model._meta.name) if f.name == field.name):
# Field does not exist in DB table
self.__log("Adding missing column")
try:
with self.database.transaction():
self.router.migrator.add_columns(
model, **{field.name: field})
self.router.migrator.run()
except Exception:
self.database.rollback()
self.__log("Update failed", level='exception')
raise
