diff options
| -rw-r--r-- | scripts/update-case-attribute-tables-20230818 | 29 |
1 files changed, 11 insertions, 18 deletions
diff --git a/scripts/update-case-attribute-tables-20230818 b/scripts/update-case-attribute-tables-20230818 index 151a375..5f21e9c 100644 --- a/scripts/update-case-attribute-tables-20230818 +++ b/scripts/update-case-attribute-tables-20230818 @@ -21,7 +21,7 @@ from MySQLdb.cursors import DictCursor def convert_to_innodb(cursor, table): """Convert `table` to InnoDB Engine.""" - cursor.execute(f"SHOW CREATE TABLE {table};"); print("fetched...") + cursor.execute(f"SHOW CREATE TABLE {table};") res = cursor.fetchone() tblname, tblcreate = res["Table"], res["Create Table"] engine_charset = { @@ -100,7 +100,6 @@ def create_temp_case_attributes_xref_table(cursor): def fetch_case_attribute_data(cursor, limit: int = 1000): """Fetch case attribute data.""" - print("fetching...") offset = 0 while True: cursor.execute( @@ -130,7 +129,6 @@ def fetch_case_attribute_data(cursor, limit: int = 1000): def copy_data(cursor): """Copy data from existing tables into new temp tables.""" if table_exists(cursor, "CaseAttributeTemp") and table_exists(cursor, "CaseAttributeXRefNewTemp"): - print("okay, we are in here...") for bunch_of_data in fetch_case_attribute_data(cursor): ca_data = tuple({key: val for key, val in item} for item in set( (("InbredSetId", item["InbredSetId"]), @@ -141,7 +139,6 @@ def copy_data(cursor): or item["CaseAttributeName"])), ("Value", item["Value"])) for item in bunch_of_data)) - print(f"ca_data: {ca_data}") cursor.executemany( "INSERT INTO " "CaseAttributeTemp(" @@ -151,7 +148,6 @@ def copy_data(cursor): "%(Description)s) " "ON DUPLICATE KEY UPDATE Name=VALUES(Name)", ca_data) - print("exec01") cursor.executemany( "INSERT INTO " "CaseAttributeXRefNewTemp(" @@ -160,9 +156,7 @@ def copy_data(cursor): "%(InbredSetId)s, %(StrainId)s, %(CaseAttributeId)s, %(Value)s) " "ON DUPLICATE KEY UPDATE `Value`=VALUES(`Value`)", bunch_of_data) - print("exec02") time.sleep(random.randint(5, 20)) - print("leaving ...") def rename_table(cursor, table, newtable): """Rename `table` to `newtable`.""" @@ -188,19 +182,18 @@ def main(sql_uri: str) -> None: db=db_name, user=user, passwd=passwd or '', host=host, port=port or 3306) try: cursor = conn.cursor(cursorclass=DictCursor) - print("beginning ...") - convert_to_innodb(cursor, "InbredSet"); print("2") - convert_to_innodb(cursor, "Strain"); print("3") - cleanup_inbred_set_schema(cursor); print("4") - create_temp_case_attributes_table(cursor); print("5") - create_temp_case_attributes_xref_table(cursor); print("6") - copy_data(cursor); print("7") + convert_to_innodb(cursor, "InbredSet") + convert_to_innodb(cursor, "Strain") + cleanup_inbred_set_schema(cursor) + create_temp_case_attributes_table(cursor) + create_temp_case_attributes_xref_table(cursor) + copy_data(cursor) timestr = datetime.now().isoformat().replace( "-", "").replace(":", "_").replace(".", "__") - rename_table(cursor, "CaseAttribute", f"BACKUP_CaseAttribute_{timestr}"); print("8") - rename_table(cursor, "CaseAttributeXRefNew", f"BACKUP_CaseAttributeXRefNew_{timestr}"); print("9") - rename_table(cursor, "CaseAttributeTemp", "CaseAttribute"); print("10") - rename_table(cursor, "CaseAttributeXRefNewTemp", "CaseAttributeXRefNew"); print("11") + rename_table(cursor, "CaseAttribute", f"BACKUP_CaseAttribute_{timestr}") + rename_table(cursor, "CaseAttributeXRefNew", f"BACKUP_CaseAttributeXRefNew_{timestr}") + rename_table(cursor, "CaseAttributeTemp", "CaseAttribute") + rename_table(cursor, "CaseAttributeXRefNewTemp", "CaseAttributeXRefNew") conn.commit() except Exception as _exc: print(traceback.format_exc(), file=sys.stderr) |