""" NAME fix_foreignkey_is_null SYNOPSIS fix_foreignkey_is_null DESCRIPTION It happens that foreign field 'publications.id_collaborations' and 'publications.id_teams' have the value NULL instead of 1. This script fix this issues. OPTIONS EXAMPLE > cd ...limbra/scripts > run script myapp fix_foreignkey_is_null.py AUTHOR R. Le Gac -- Jan 2021 """ import logging from plugin_dbui import UNDEF_ID def cli(): # start logger logger = logging.getLogger() logger.setLevel(logging.INFO) formatter = logging.Formatter( "%(asctime)s %(levelname)s %(message)s", datefmt="%H:%M:%S") logger.handlers[0].setFormatter(formatter) # list of foreign fields fields = ["id_collaborations", "id_publishers", "id_countries", "id_reports", "id_authors_roles", "id_teams", "id_projects", "id_categories", "id_status"] for field in fields: logger.info(f"scan foreign field '{field}'") query = db.publications[field] == None rows = db(query).iterselect(db.publications.id) for row in rows: print(f" fix {row.id}", type(row.id)) dct = dict() dct[field] = UNDEF_ID db(db.publications.id == row.id).update(**dct) rep = input("\n Commit change (y/N): ") if rep in ("y", "Y"): db.commit() logger.info("database modified") if __name__ == "__main__": cli()