Update Bulk CM managed object parent id

update_bulkcm_mo_parent_id.py

from sqlalchemy import create_engine, MetaData, Table
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql import text

engine = create_engine('postgresql://bodastage:password@database/bts')

vendor_pk=3
tech_pk=3
table_schema='zte_cm_4g'

Session = sessionmaker(bind=engine)
session = Session()
		
metadata = MetaData()

managedobjects = Table('managedobjects', metadata, autoload=True, autoload_with=engine, schema='public')
columns = Table('columns', metadata, autoload=True, autoload_with=engine, schema='information_schema')

mo_list = session.query(managedobjects).filter_by(vendor_pk=vendor_pk, tech_pk=tech_pk).all()
		
for mo in mo_list:
    
    mo_name=mo[1]
    
    id_columns = session.query(columns).filter_by(table_schema=table_schema,table_name=mo_name.lower()).filter(columns.columns.column_name.like('%\_id')).with_entities(columns.columns.column_name).all()
    print(id_columns)
    if len(id_columns) < 2 : continue
    
    second_last_id = id_columns[len(id_columns)-2][0]
    
    # Remove the id 
    parent_mo = second_last_id .replace("_id","")
    print("parent_mo: {}".format(parent_mo))
    
    # the parent mo id 
    parent_mo_details = session.query(managedobjects).filter_by(name=parent_mo,vendor_pk=vendor_pk,tech_pk=tech_pk).first()
    print("parent_mo_details: {}".format(parent_mo_details))
    
    parent_mo_id = parent_mo_details[0]
    print("parent_mo_id: {}".format(parent_mo_id))
    
    managedobject=session.query(managedobjects).filter_by(name=mo_name,vendor_pk=vendor_pk, tech_pk=tech_pk).first()
    
    conn = engine.connect()
    
    sql = """UPDATE managedobjects SET parent_pk = {} WHERE pk = {}""".format(parent_mo_id, managedobject.pk)
    engine.execute(sql)
    session.commit()