Extracting Reporting Data with Python

from __future__ import print_function
import os, sys
import urllib
import json
from datetime import datetime
import time
import argparse

try: #Try Python3
    from urllib.request import urlopen, Request
    from urllib.error import HTTPError
    from urllib.parse import urlencode, quote_plus
except ImportError: # Fallback to Python 2
    from urllib2 import urlopen, Request
    from urllib import urlencode
    from urllib2 import HTTPError


TENANT=''
CLIENT_ID=''
CLIENT_SECRET=''
TENANT_USERNAME=''
TENANT_PASSWORD=''
REFRESH_TOKEN='' # Set this only if you want to use a refresh_token flow instead of password flow


SQL_SERVER=''
SQL_DB=''
SQL_USERNAME=''
SQL_PASSWORD=''


reportList = [
    'contentProfileAssignments',
    'contentProfiles',
    'contentProperties',
    'contentPropertyAssignments',
    'contentUsageHistory',
    'contentViewHistory',
    'contents',
    'externalUsers',
    'generatedLivedocComponents',
    'generatedLivedocFields',
    'generatedLivedocOutputFormats',
    'generatedLivedocSlides',
    'generatedLivedocs',
    'groupMembers',
    'groups',
    'libraryContentVersions',
    'libraryContents',
    'livesendLinkContents',
    'livesendLinkMembers',
    'livesendLinks',
    'livesendPageViews',
    'livesendViewingSessions',
    'searchHistory',
    'searchWords',
    'teamsites',
    'userActivity',
    'userProperties',
    'userPropertyAssignments',
    'users',
    'workspaceContentVersions',
    'workspaceContents'
]



###################################################################################
############  MAIN FUNCTION TO AUTH, RUN API, SAVE DATA TO JSON + SQL  ############
###################################################################################
def main():
    args = parse_args()

    ##############  LOGIN USING DIRECT LOGON CREDENTIALS TO GET AN ACCESS TOKEN  ##############
    bearer = getBearerToken(TENANT)
    
    ##############  CONNECT TO SQL IF SPECIFIED  ##############
    if(args.sql):    
        try:
            import pymssql
        except:
            print("You do not have pymssql installed.  Please try to run the following command: pip install pymssql")
            quit()
        try:
            dbCon = pymssql.connect(server=SQL_SERVER, database=SQL_DB, user=SQL_USERNAME, password=SQL_PASSWORD,  tds_version='8.0', port='1433', charset="UTF-8")
            dbCur = dbCon.cursor()            
        except Exception as e:
            print('Error trying to connect to database.  SERVER:' + SQL_SERVER + ' DATABASE:' + SQL_DB + ' USER:' + SQL_USERNAME + ' PASSWORD:' + SQL_PASSWORD)
            print(str(e))
            quit()

    ##############  GET THE LIST OF REPORTS TO RUN  ##############
    try:
        if(args.all):
            reports = reportList
        else:
            reports = args.reports.split(',')
    except Exception as e:
        print('You must specify a comma separated list of reports using --report or specify --all to run all reports')

    
    ##############  RUN EACH OF THE REPORTS  ##############
    for report in reports:        
        url = 'https://api.seismic.com/reporting/v2/' + report + '?occuredAtStartTime=' + args.startdate + '&occuredAtEndTime=' + args.enddate
        print( "Getting data from url: " + url)
        if(args.csv):
            format = 'csv'
        else:
            format = 'json'
            
        data = getDataFromAPI(url, bearer, '', format)
        print('')
        if(args.json):
            print('Creating json file ' + args.prefix + report + '.json')
            dataFile = open(args.prefix + report + '.json', "w")
            json.dump(data,datafile, indent=4)
        if(args.csv):
            print('Creating CSV file ' + args.prefix + report + '.csv')
            dataFile = open(args.prefix + report + '.csv', "w")
            dataFile.write(data.encode('utf-8'))
        if(args.sql):
            ##############  GET THE LIST OF FIELDS IN THE DATA  ##############
            tableName = args.prefix + report
            print('Creating SQL table ' + tableName)
            fields = {}
            for record in data:
                for key in record:
                    if(type(record[key]) is not dict and key not in fields):
                        fields[key] = type(record[key]).__name__
                        if(fields[key] == 'unicode'):
                            ### Try to parse it as a date, if it passes, it is a datetime field.  There is a possibility this incorrectly classifies a field as a date, but it is quite low.
                            try:
                                date = datetime.strptime(record[key][0:10], '%Y-%m-%d')
                                fields[key] = 'datetime'
                            except Exception as e:
                                pass
                
                
            
            ##############  DROP & CREATE THE TABLE IN SQL  ##############
            sql = 'DROP TABLE IF EXISTS ' + tableName
            execSql(dbCur, dbCon, sql)
            
            sqlFieldListString = ','.join('[' + k + ']' for k in fields)
            sqlFieldDefinitionString = ','.join('[' + k + '] ' + pythonToSqlType(fields[k]) for k in fields)
            
            sql = 'CREATE TABLE ' + tableName + ' ('
            sql += sqlFieldDefinitionString
            sql += ')'
            execSql(dbCur, dbCon, sql)

            ##############  ADD THE DATA TO THE TABLE  ##############
            print('Adding data to SQL . . .'),
            i=0
            for record in data:
                fieldValueList = []
                for field in fields:
                    try:
                        fieldValueList.append("'" + cleanStr(record[field]) + "'")
                    except Exception as e:
                        # If the field is not found in the given record, put a null value in SQL
                        fieldValueList.append('null')
                        pass
                    
                fieldValueListString = ','.join(fieldValueList) 

                sql = 'INSERT INTO ' + tableName + ' ( ' + sqlFieldListString + ' ) '
                sql += ' VALUES (' + fieldValueListString + ')'
                i = i + 1
                if(i == 100):
                    i = 0
                    print (".", end="")      
                    sys.stdout.flush()
                execSql(dbCur, dbCon, sql)
            print("\n\n")
            
        
    


###################################################################################
################  FUNCTION TO GET DATA FROM API WITH CONTINUATION  ################
###################################################################################
def getDataFromAPI(url, authorization, continuation, format):
    trial = 10
    while(trial >= 0):
        trial = trial - 1
        if(trial <= 0):
            print('Unable to get data from ' + url)         
            raise
        else:
            try:
                req = Request(url)
                req.add_header('Python-Script-Version', '1.1')
                if(authorization != ''):
                    req.add_header('Authorization', authorization)
                if(continuation != ''):
                    req.add_header('Continuation', continuation)
                if(format == 'csv'):
                    req.add_header('Accept', 'text/csv')
                    
                response = urlopen(req)                
                if(response.getcode() == 200):
                
                    print (".", end="")      
                    sys.stdout.flush()
                    
                    if(format == 'json'):
                        data = json.loads(response.read().decode('utf-8'))
                    else:
                        data = (response.read().decode('utf-8'))
                    headers = response.info()
                    continuationHeader = ''
                    try:
                        # If there is a continuation header, there is more data, so call this again to get more data
                        continuationHeader = headers['Continuation']
                        data = data + getDataFromAPI(url, authorization, continuationHeader, format)
                    except Exception as e:
                        pass
                else:
                    data = None
                return data
            except HTTPError as e:
                print( 'Failure(' + str(e.code) + ') on attempt ' + str(10-trial) + ' to get data from ' + url)
                continue

       
###################################################################################
#########################  FUNCTION TO RUN A SQL COMMAND  #########################
###################################################################################    
def execSql(cursor, conn, sql):
    trial = 10
    while(trial >= 0):
        if(trial == 0):
            print('Major error trying to reconnect to database')
        
        trial = trial - 1
        try:
            cursor.execute(sql)
            conn.commit()
            return
        except pymssql.DatabaseError as e:
            try:
                print( 'Error with SQL: ' + sql)
                print(  str(e))
                print( 'Attempting to reconnect to database')
                time.sleep(3)
                dbCon = pymssql.connect(server=SQL_SERVER, user=SQL_USERNAME, password=SQL_PASSWORD, database=SQL_DB, tds_version='8.0', port='1433', charset="UTF-8")                
                print( 'connected')
                dbCur = dbCon.cursor()
                conn = dbCon
                cursor = dbCur    
                print( 'continuing')  
            except Exception as e:
                print( 'Failed to reconnect')
                print( str(e))
                pass
            pass
            
            

####################################################################################
####  FUNCTION TO EXCHANGE USERNAME+PASSWORD OR REFRESH TOKEN FOR BEARER TOKEN  ####
####################################################################################   
def getBearerToken(tenant):
    if(REFRESH_TOKEN != ''):
        try:
            url = 'https://auth.seismic.com/tenants/' + tenant + '/connect/token'

            data = {'client_id': CLIENT_ID,
            'client_secret' : CLIENT_SECRET,
            'grant_type' : 'refresh_token',
            'refresh_token' : REFRESH_TOKEN
            }
            response = urlopen(url, urlencode(data).encode("utf-8"))

            d = json.loads(response.read().decode('utf-8'))
            
            return 'Bearer ' + d['access_token']
        except HTTPError as e:
            print(str(e))
            print(e.read())
            quit()
        except Exception as e:
            print('Unable to get bearer token from tenant: [' + tenant + '] using refresh token flow')
            print(str(e))
            quit()
    else:
        try:
            url = 'https://auth.seismic.com/tenants/' + tenant + '/connect/token'

            data = {'client_id': CLIENT_ID,
            'client_secret' : CLIENT_SECRET,
            'grant_type' : 'password',
            'scope' : 'seismic.reporting',
            'username' : TENANT_USERNAME,
            'password' : TENANT_PASSWORD
            }
            response = urlopen(url, urlencode(data).encode("utf-8"))

            d = json.loads(response.read().decode('utf-8'))
            return 'Bearer ' + d['access_token']
        except HTTPError as e:
            print(str(e))
            print(e.read())
            quit()
        except Exception as e:
            print('Unable to get bearer token from tenant: [' + tenant + '] using password flow')
            print(str(e))
            quit()


        
            
            
def parse_args():

    parser = argparse.ArgumentParser(
        description='Get data from the Seismic reporting APIs into JSON, CSV and/or SQL.  An example to get all data into both SQL and JSON would be getSeismicData.py --all --sql --json',
    )

    parser.add_argument(
        '-r',
        '--reports',
        help='A comma separated list of reports such as ' + ','.join(reportList[0:2]) + '. Note, these are case sensitive',
        type=str
    )
    parser.add_argument(
        '-s',
        '--startdate',
        help='The start date for the report such as 2017-01-01',
        default='',
        type=str
    )
    parser.add_argument(
        '-e',
        '--enddate',
        help='The end date for the report such as 2019-01-01',
        default='',
        type=str
    )
    parser.add_argument(
        '--prefix',
        help='Optional prefix to add to JSON filename or SQL table names',
        default='',
        type=str
    )

    parser.add_argument(
        '--all',
        action='store_true',
        help='Runs all reports to individual JSON or SQL tables'
    )    
    
    parser.add_argument(
        '--sql',
        action='store_true',
        help='Output data to SQL'
    )
    parser.add_argument(
        '--json',
        action='store_true',
        help='Output data to JSON'
    )
    parser.add_argument(
        '--csv',
        action='store_true',
        help='Output data to JSON'
    )

    
    args = parser.parse_args()
    
    if(args.sql == False and args.json == False and args.csv == False):
        print( 'Please specify either --sql or --json or --csv as output formats')
        quit()
        
    if((args.sql == True or args.json == True) and args.csv == True):
        print('You cannot export to CSV at the same time as JSON or SQL')
        quit()
        
    return parser.parse_args()
   
    
def cleanStr(s):
    if s is None:
        return ''
    elif type(s) == bool:
        return str(s)   
    elif type(s) == list:
        if(len(s) > 0):
            return '|' + cleanStr('|'.join(s)) + '|'
        else:
            return 'null'
    return unicode(s).replace(u"\u2018", "'").replace(u"\u00e2\u20ac\u2122", "'").replace(u"\u2019", "'").replace("'", "''").replace("&", "\&").encode('ascii','ignore').strip()

 

def pythonToSqlType(t):
    return {
        'unicode' : 'nvarchar(max)',
        'int' : 'bigint',
        'list' : 'nvarchar(max)',
        'bool' : 'bit',
        'datetime' : 'datetime'
    }[t]

def module_exists(module_name):
    try:
        __import__(module_name)
    except ImportError:
        return False
    else:
        return True    

if __name__ == "__main__":
    main()