server_name = '10.1.2.254'
username = 'rocketchat'
password = 'uMdB9WAtVauyMFc'
database = 'rocketchat'

import pymongo
import pyodbc
import pandas as pd
import urllib.parse
from sqlalchemy import create_engine

#query message table from mongo
from pymongo import MongoClient
client = MongoClient('mongodb://10.0.2.254:27017/')
db = client.rocketchat
collection = db.rocketchat_message
cursor = collection.aggregate([{ "$project": {"ts":1, "rid":1, "msg":1, 'username':'$u.username', "_id":0}}])
message = pd.DataFrame(list(cursor))

#insert into mssql
connection_string = 'DRIVER={{SQL Server}};SERVER={};DATABASE={};UID={};PWD={}'.format(server_name, database, username, password)
connection_string = urllib.parse.quote(connection_string, safe='')
connection_string = "mssql+pyodbc:///?odbc_connect={}".format(connection_string)
engine = create_engine(connection_string)
#print(message)

#query room table from mongo and insert into mssql
collection = db.rocketchat_room
cursor = collection.find({}, {"_id":1, "t":1, "name":1, "usernames":1})
room = pd.DataFrame(list(cursor))
#variable != variable is a test for NaN
room.loc[room['name'] != room['name'], 'name'] = room.usernames.apply(' x '.join)
room.drop('usernames', axis=1, inplace=True)
#print(room)

#join the message and room tables
message = message.join(room.set_index('_id'), on='rid')
message = message.rename(columns={'name': 'channel_name'})
#print(message)
message.to_sql("message", engine, if_exists="replace")