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