Narrowcast Server service migration to Distribution Services - SQL & Python scripts

Scripts below will do the following tasks:
- get a list of NCS services from NCS database
- get a list of users, emails, and preferences (security restrictions like Region="North") from NCS database
- connect to Strategy with mstrio for Python
- list existing MSTR users with their default email addresses
- match MSTR users with NCS users based on email addresses
- create a simple email subscriptions for matched users
Note - I've added color formatting in the code below (http://hilite.me/). Code copied from here might not work due to indentation issues. In that case, use attached file with sample code.
Also this CRM is adding ';' near URLs (see below variable base_url)
Attached is the Jupyter Notebook file.
 

#!/usr/bin/env python
# coding: utf-8

# # Narrowcast Server service migration to Distribution Services

# ## 1. Getting data from NC

# ### 1.1 List of NC Services

# In[ ]:


# Run this SQL code against Narrowcast Server database
"""
select 
names1.MR_OBJECT_ID AS serviceID, 
names1.MR_OBJECT_NAME AS service_name, 
parent1.MR_OBJECT_NAME AS foldername, 
names2.MR_OBJECT_NAME AS publication_name, 
names3.MR_OBJECT_NAME AS document_name, 
info3.MR_OBJECT_SUBTYPE AS doc_type, 
names4.MR_OBJECT_ID AS info_obj_id,
names4.MR_OBJECT_NAME AS info_obj_name, 
info4.MR_OBJECT_SUBTYPE AS info_obj_subtype   
from 
MSTROBJNAMES names1, 
MSTROBJINFO info1, 
MSTROBJNAMES parent1, 
MSTROBJDEPN dpns,
MSTROBJNames names2, 
MSTROBJDEPN dpns2,
MSTROBJNames names3,
MSTROBJINFO info3, 
MSTROBJDEPN dpns3,
MSTROBJNames names4,
MSTROBJInfo info4  
where names1.MR_OBJECT_ID = dpns.MR_INDEP_OBJID
and names1.MR_OBJECT_ID = info1.MR_OBJECT_ID
and info1.MR_PARENT_ID = parent1.MR_OBJECT_ID
and dpns.MR_DEPN_OBJID = names2.MR_OBJECT_ID
and names2.MR_OBJECT_ID = dpns2.MR_INDEP_OBJID
and dpns2.MR_DEPN_OBJID = names3.MR_OBJECT_ID
and names3.MR_OBJECT_ID = dpns3.MR_INDEP_OBJID
and names3.MR_OBJECT_ID = info3.MR_OBJECT_ID
and dpns3.MR_DEPN_OBJID = names4.MR_OBJECT_ID
and dpns3.MR_DEPN_OBJID = info4.MR_OBJECT_ID
and names1.MR_Object_Type = 19
and names2.MR_Object_Type = 16
and names3.MR_Object_Type = 14
and names4.MR_Object_Type = 4
and info4.MR_OBJECT_SubType <> 1
"""


# <img src="Images/NC_services.png">

# ### 1.2 NC Service details

# In[ ]:


"""
select  
names1.MR_OBJECT_ID AS serviceID, --This is Service ID
names1.MR_OBJECT_NAME AS service_name, 
names2.MR_OBJECT_NAME AS subset_name, 
a11.MR_ADD_DISPLAY AS dispname, 
a11.MR_PHYSICAL_ADD AS email, 
a13.MR_USER_NAME,
sp.MR_INFOSOURCE_ID, 
sp.MR_QUES_OBJ_ID, 
po.mr_seq, 
sp.MR_USER_PREF,
po.MR_PREF_OBJ
from 
MSTROBJNames names1,
MSTROBJINFO info1, 
MSTROBJDEPN dpns,
MSTROBJNames names2,

MSTRSUBSCRIPTIONS a12,
MSTRADDRESSES a11,
MSTRUSERS a13,
MSTRSUBPREF sp,
MSTRPREFOBJS po

where names1.MR_Object_Type = 19
and names2.MR_Object_Type = 17
and info1.MR_STATUS =1

and names1.MR_OBJECT_ID = info1.MR_OBJECT_ID
and names1.MR_OBJECT_ID = dpns.MR_INDEP_OBJID
and dpns.MR_DEPN_OBJID = names2.MR_OBJECT_ID
and names2.MR_OBJECT_ID = a12.MR_SUB_SET_ID
and a11.MR_ADDRESS_ID = a12.MR_ADDRESS_ID
and a12.MR_SUB_GUID = sp.MR_SUB_GUID
and sp.MR_PREF_OBJ_ID = po.MR_PREF_OBJ_ID
and a12.MR_USER_ID = a13.MR_USER_ID
and names1.MR_OBJECT_ID = '047886F8A7474F4A929EC6DD135F0A98' --Filter for Service ID
"""


# <img src="Images/service_details.png">

# In[ ]:


import csv
import pandas as pd
with open('narrowcast_emails.csv', encoding="utf8", newline='') as f:
    email_list = [x.strip() for x in f]


# ## Automate tasks in Strategy

# In[ ]:


from mstrio.connection import Connection
from mstrio.admin.subscription.subscription import Subscription, EmailSubscription
from mstrio.admin.subscription.content import Content
from mstrio.admin.subscription.delivery import Delivery
from mstrio.admin.user import User, list_users
from datetime import datetime

#### Parameters ####
api_login, api_password = 'administrator', ''
base_url = 'http://warw7243-a.corp.Strategy.com:8080/MicroStrategyLibrary/'
project_id = 'B7CA92F04B9FAE8D941C3E9B7E0CD754'

conn = Connection(base_url,api_login,api_password)


# ### Get users' default addresses

# In[ ]:


conn.renew()


# In[ ]:


users = list_users(connection=conn)
default_addresses=[]

for u in users:
    if u.addresses:
        user_addresses = [[u.name, u.id, uad['value']] for uad in u.addresses if uad['isDefault']==True]
        default_addresses.extend(user_addresses)


# ### Create a list of recipients

# In[ ]:


# From MSTR Metadata
for d in default_addresses:
    print(d)


# In[ ]:


# From Narrowcast
for e in email_list:
    print(e)


# In[ ]:


# Match Metadata with Narrowcast
matched_emails = [d[1] for d in default_addresses if d[2] in email_list]
for m in matched_emails:
    print(m)


# ### Create a subscription

# In[ ]:


# create an email subscription
recipient_ids = matched_emails[:]
content_id = '77C0F2614D81B7A0C636628B2AC748CE'
schedule_id = 'FF7BB3B311D501F0C00051916B98494F'
subscription_name = 'REST_API_'+datetime.now().strftime("%Y-%m-%d__%H-%M")
subject_txt='Email Subject'
message_txt="CONTRA PRINCIPIA NEGANTEM NON EST DISPUTANDUM"
EmailSubscription.create(connection=conn,
                         name=subscription_name, 
                         application_id=project_id,
                         send_now = True,
                         contents=[Content(id=content_id, type='report', name='Report 1',
                                           personalization=Content.Properties(format_type='EXCEL'))],
                         schedules_ids=[schedule_id],
                         recipients=recipient_ids,
                         email_subject=subject_txt,
                         email_message=message_txt,
                         email_send_content_as="data")


# In[ ]: