Pull data from a [Db2 or SQLAlchemy] database and inject into IBM Watson Discovery collection

README.md

From database into Watson Discovery

The code shown is a simple Python example of pulling data from a SQL database supported by SQLAlchemy, and then injecting the data as new JSON document into a collection of Watson Discovery Service on IBM Cloud.

config.json

{
    "WDS": {
        "credentials": {
            "version": "2018-08-01",
            "url": "https://gateway.watsonplatform.net/discovery/api",
            "username": "xxxxxxxx-xxxx-xxxx-xxxx-7a77bc2102b6",
            "password": "123456784Kdc",
            "environmentID": "yyyyyyyy-yyyy-yyyy-yyyy-8ced7d002a10",
            "collectionID": "zzzzzzzz-zzzz-zzzz-zzzz-f42672bc8a86"
        }
    },
    "DB": {
        "credentials": {
            "dburi": "db2+ibm_db://user:password@host:port/database"
        }
    }
}

db2discovery.py

import json
from watson_developer_cloud import DiscoveryV1
from sqlalchemy import create_engine

discoveryService=None

# Load configuration and initialize service
def loadAndInit(confFile='config.json'):
    # Credentials are read from a file
    global discoveryService
    global configWDS
    global configDB
    with open(confFile) as confFile:
        config = json.load(confFile)
        configWDS = config['WDS']['credentials']
        configDB = config['DB']['credentials']

    # Initialize the Watson Assistant client, use API V2
    if 'username' in configWDS:
        discoveryService = DiscoveryV1(
            username=configWDS['username'],
            password=configWDS['password'],
            version=configWDS['version'],
            url=configWDS['url'])
    elif 'apikey' in configWDS:
        assistantService = DiscoveryV1(
            iam_apikey=configWDS['apikey'],
            version=configWDS['versionV2'],
            url=configWDS['url'])
    else:
        print('Expected either username / password or apikey in credentials.')
        exit

# upload a JSON document to Watson Discovery Service (WDS)
def uploadDocs(snippet='foo'):
    print (">>> processing docs...\n")

    # compose string to upload
    s='{"comment": "Here is some silly comment I made up. And I have the table name '+snippet+' coming out of Db2"}'
    # upload
    res = discoveryService.add_document(environment_id=configWDS['environmentID'],
                                  collection_id=configWDS['collectionID'],
                                  file_content_type='application/json',
                                  file=s,
                                  metadata='{"author": "Henrik", "uri":"https://blog.4loeser.net"}',
                                  filename=snippet).get_result()
    # print response
    print(res)
  


#
# Main program, for now just detect what function to call and invoke it
#
if __name__ == '__main__':
    loadAndInit()

    # Open database connection, pull 10 strings and pass each string on as a snippet
    # dburi must be a valid SQLAlchemy database URI
    e = create_engine(configDB['dburi'])
    conn = e.connect()
    result = conn.execute('select tabname from syscat.tables fetch first 10 rows only')
    for row in result:
        uploadDocs(snippet=row['tabname'])
    conn.close()