Db2 ODATA Magic Formating Functions

This program implements a Python magic command (%odata) that simplifies the testing and creation of RESTful calls to Db2. The program supports the following commands that translate Db2 SQL commands into an equivalent OData call.

The %odata command supports a command and any special flags on a single line, while the %%odata command will consider the entire cell to be the command. The %%odata format is useful for larger INSERT and SELECT statements that would not fit easily onto one line.

The commands that are supported by the program includes:

  • SET - Set connection and other program parameters
  • RESET - Drop any existing connection information that may be stored
  • REGISTER - Prompt the user for connection information
  • INSERT, SELECT, UPDATE, DELETE - Modify or retrieve data from the OData source
  • DESCRIBE - Describe the data source by listing all of the columns and attributes
  • SETTINGS - Display current settings

Before using any OData SQL commands, a connection must be established to a backend Db2 database. This requires the use of the PROMPT command or the SET command. The information that is required for a connection includes the following:

  • DATABASE name - The name of the DB2 database you want to connect to
  • SCHEMA userid - The SCHEMA that any request will use when accessing a table
  • HOST ipaddress - The IP address (or localhost) where the DB2 instance can be found
  • PORT portno - The PORT number that DB2 is listening to (usually 50000)
  • USER userid - The user that will be issuing the OData requests to the table
  • PASSWORD pwd - The password for the USER (use a "?" to prompt for the value)
  • ADMIN - The user that has administrative privileges for creating the service (perhaps the same as the user)
  • PASSWORD pwd - The password for the administrative user (use a ? to prompt for the value)

Using the PROMPT command will ask for each one of these fields to be populated. You can bypass the prompts by using the SET field value ... format.

When any SQL command is issued, the %odata program will first check to see if we have created a service or if we need to create a new one. Once we have the service information, an OData command is generated from the SQL. The SQL can include:

  • INSERT a new row
  • UPDATE a field in an existing row
  • DELETE a row
  • SELECT rows from a table based on some logic

By default the OData command will execute the command (SQL) that is requested. Output from a SELECT request will be displayed as a table. If you want to see the raw results returned from the OData service, use the -r flag. Displaying the results as JSON records requires the use of the -j flag. To display the URL service address and the OData command that was generated by the SQL, use the -e flag.

Prototyping OData with Db2 can be done without Db2 drivers on the client system. However, it may be more convenient to use the Db2 magic command to access the database directly to create objects and test the results from the OData calls.

The %odata command allows python variables to be included as part of the syntax. To include a variable in the command, place the {} brackets around the variables name: {empno}. The variable will be substituted when the command is run. Note that you may still need to place punctuation around the variable depending on what the %odata syntax requires.

More details on all of the options found above can be viewed by issuing the command with no arguments. For instance "%odata select" will describe the options available to you when issuing a SELECT statement using OData formatting rules.


In [ ]:
#
# Set up Jupyter MAGIC commands "odata". 
# %odata will return results from a Db2 insert/update/delete/select statement via a RESTful API call
#
# IBM 2017: George Baklarz
# 2017-11-15
#

import pandas
import json
import getpass
import os
import pickle
import time
import sys
import requests
import re
import datetime

from IPython.display import HTML as pHTML, Image as pImage, display as pDisplay
from __future__ import print_function
from IPython.core.magic import (Magics, magics_class, line_magic,
                                cell_magic, line_cell_magic)

# Python Hack for Input between 2 and 3

try: 
    input = raw_input 
except NameError: 
    pass 

# settings parameters

odata_settings = {
     "database" : "",
     "schema"   : "",
     "host"     : "localhost",
     "port"     : "50000",
     "hostodata": "localhost",
     "portodata": "9080",
     "userid"   : "",
     "u_pwd"    : "",
     "admin"    : "",
     "a_pwd"    : "",
     "echo"     : False,
     "format"   : "table",
     "maxrows"  : 10
}

def load_settings():

    # This routine will load the settings from the previous session if they exist
    
    global settings
    
    fname = "odata.pickle"

    try:
        with open(fname,'rb') as f: 
            odata_settings = pickle.load(f) 
        
        if ('database'  not in settings): odata_settings["database"] = ""
        if ('schema'    not in settings): odata_settings["schema"]   = ""
        if ('host'      not in settings): odata_settings["host"]     = "localhost"
        if ('port'      not in settings): odata_settings["port"]     = "50000"
        if ('hostodata' not in settings): odata_settings["hostodata"]= "localhost"
        if ('portodata' not in settings): odata_settings["portodata"]= "9080"
        if ('userid'    not in settings): odata_settings["userid"]   = ""
        if ('u_pwd'     not in settings): odata_settings["u_pwd"]    = ""
        if ('admin'     not in settings): odata_settings["admin"]    = ""
        if ('a_pwd'     not in settings): odata_settings["a_pwd"]    = ""
        if ('echo'      not in settings): odata_settings["echo"]     = False
        if ('format'    not in settings): odata_settings["format"]   = "table"
        if ('maxrows'   not in settings): odata_settings["maxrows"]  = 10
        
    except: 
        pass    
    
    return

def save_settings():

    # This routine will save the current settings if they exist
    
    global odata_settings
    
    fname = "odata.pickle"
    
    try:
        with open(fname,'wb') as f:
            pickle.dump(odata_settings,f)
            
    except:
        pass    
    
    return    

# Help description for settings information needed for OData

def odata_describe_help():
    
    help = """
       <h3>DESCRIBE Command Syntax</h3>
       <br>The DESCRIBE command will display all of the columns and their data types for the requested table.
       <p><b><pre>
DESCRIBE &lt;table&gt;
       </pre></b>
    """
    pDisplay(pHTML(help))    
    
def odata_drop_help():
 
    help = """
       <h3>RESET Command Syntax</h3>
       <br>The %odata command keeps track of tables that have been accessed previously. The service URL is
       reused every time you access the table for any of the SQL commands. In the event you want to rebuild
       the service, you must delete the connection information from disk. This command will remove any previous 
       connection information for a table associated with a schema in a database. If any of the parameters
       are missing, the current settings are used (i.e. Database, Schema).       
       <p><b><pre>
RESET DATABASE &lt;database&gt; SCHEMA &lt;schema&gt; TABLE &lt;table&gt;
       </pre></b>
    """
    pDisplay(pHTML(help))    

def odata_insert_help():
    
    help = """
       <h3>INSERT Command Syntax</h3>
       <br>The INSERT command will insert a new record into the table. Any columns that are missing from the 
       list will have a NULL value assigned to it.
       <p><b><pre>
INSERT INTO &lt;table&gt;(cols,....) VALUES (values,...)
       </pre></b>
    """
    pDisplay(pHTML(help))
    
    
def odata_delete_help():
    
    help = """
       <h3>DELETE Command Syntax</h3>
       <br>The DELETE command will delete one record from the table (it does not do a searched delete).
           The  keycolumn in the WHERE clause must have a primary key/index associated with it. If a primary
          index does not match this column, or the index does not exist, the DELETE will not work.
       <p><b><pre>
DELETE FROM &lt;table&gt; WHERE keycolumn=value
       </pre></b> 

    """  
    pDisplay(pHTML(help))
    
def odata_update_help():
    
    help = """
       <h3>UPDATE Command Syntax</h3>
       <br>The UPDATE command will update one column in a table. The  keycolumn in the WHERE clause must have a primary key/index associated with it. If a primary
          index does not match this column, or the index does not exist, the DELETE will not work. Only 
          one column value can be changed at a time with this syntax.
       <p><b><pre>
UPDATE &lt;table&gt; SET column=value WHERE keycolumn=value
       </pre></b>  
    """  
    pDisplay(pHTML(help))   
    
def odata_select_help():
    
    help = """
       <h3>SELECT Command Syntax</h3>
       <br>The SELECT command will return data from one table. There is no ability to join tables with this
       system. If you do want to join tables, you may want to create a VIEW on the Db2 system and then
       use that as the TABLE. This will allow for SELECT, but no INSERT/DELETE/UPDATE.
       <p>You do not need to use the primary key in the WHERE clause to use this statement. By default,
       any results will be displayed in a table. If you want to retrieve the results as JSON records,
       use the -j option on the %odata command. 
       <p><b><pre>
SELECT [col1, col2, ... | count(*)] FROM &lt;table&gt; [ WHERE logic] [ LIMIT rows ]
       </pre></b>  
       The column list can contain as many values as you want, or just COUNT(*). COUNT(*) will return the
       count of rows found. If you use the -r or -j flags to display everything in JSON format, you will 
       also get the entire answer set along with the row count. This is the behavior of using count in OData.
       <p>
       The FROM clause must contain the name of the table you want to access. 
       <p>
       The WHERE clause is optional, as is the LIMIT clause. The WHERE clause can contain comparisons between
       columns and constants (EMPNO='000010'), logic (AND, OR) as well as LIKE clauses (COLUMN LIKE 'xxx'). 
       The current version cannot use arithmetic operators (+, -, *, /) or the NOT operator.
       <p>
       The LIMIT clause will restrict the results to "x" number of rows. So even if there are 500 rows that
       meet the answer set, only "x" rows will be returned to the client.
    """  
    pDisplay(pHTML(help))       
    
def odata_commands_help():
    
    help = """
       <h3>OData Command Syntax</h3>
       <br>The OData magic command (%odata) can be used as a command on a single line, or across a series of
          lines by using the form %%odata. The %odata command accepts three possible flags:
       <ul>
       <li>-e Echo the generated OData commands
       <li>-r Display the results with all data returned from the RESTful call
       <li>-j Display the results as JSON records
       </ul>
       <p>The default display of any results returned from an OData call will be in a table.
       <p>The commands that can be used as part of the %odata command are found below. Issuing the command 
       without any pararmeters will display help on that particular command (i.e. %odata select)
       <ul>
       <li>SET - Set connection and other program parameters
       <li>RESET - Reset any existing connection information that may be stored 
       <li>REGISTER - Prompt the user for connection information
       <li>INSERT, SELECT, UPDATE, DELETE - Modify or retrieve data from the OData source
       <li>DESCRIBE - Describe the data source by listing all of the columns and attributes
       <li>SETTINGS - Display current settings
       </ul>
    """  
    pDisplay(pHTML(help))     

def odata_set_help():
        
    help = """
       <h3>SET Command Syntax</h3> 
       <p>The set command is used to tell the program how to access a data source as well as give it
       credentials nessary to connect. The keywords that are allowed in the command include the following.
       Note, you do not need to specify all of these values, only the ones that you want to change. To get
       the current values (except passwords), use the SETTINGS command.
       <ul>
       <li>DATABASE name - The name of the Db2 database you want to connect to
       <li>SCHEMA userid - The SCHEMA that any request will use when accessing a table
       <li>HOST ipaddress:port - The IP address (or localhost) and port (50000) where the Db2 instance can be found
       <li>ODATA ipaddress:port - The IP address (or localhost) and port (9080) where the OData server is
       <li>USER userid - The user that will be issuing the OData requests to the table
       <li>PASSWORD pwd - The password for the USER (use a "?" to prompt for the value)
       <li>ADMIN - The user that has administrative privileges for creating the service (perhaps the same as the user)
       <li>PASSWORD pwd - The password for the administrative user (use a ? to prompt for the value)
       <li>MAXROWS amount - By default 10 rows are displayed. A value of -1 will show all rows, while any other 
           value will allow a maximum of that many rows to be displayed
       </ul>
       
       <p>When entering HOST and ODATA information, you must use the following syntax:
       <ul>
       <li>ip_address:port
       <li>ip_address
       <li>#x
       <li>#x:port
       </ul>
       <p>The full ip address can be specified with or without a port number. For Db2, the default port assigned will be 50000, and for 
       OData it is 9080. If you are running in a docker container, the assumption is that the container has the default range of ip addresses for
       Docker. The ip addresses usually start at 172.17.0.1 and go up from there. Use the #x format to specify the last digits of the ip address, so 172.17.0.1
       would be entered as #1. If you do need to specify a different port (you shouldn't have to since port mapping is only for the host machine), use the format
       #x:port.
       <p>If you don't know the ip address of the Db2 server or OData, use the docker CLI to issue the command:
       <pre>
       docker inspect -f "{{ .NetworkSettings.IPAddress }}" db2server
       </pre>
       """
        
    pDisplay(pHTML(help))
        
# Prompt for Db2 settings Settings
    
def split_ipport(in_port):

    # Split input into an IP address and Port number

    checkports = in_port.split(':')
    ip = checkports[0]
    if (len(checkports) > 1):
        port = checkports[1]
    else:
        port = None

    if (ip[:1] == '#'):
        ip = "172.17.0." + ip[1:]

    return ip, port
            
def odata_set_prompt():
    
    #global db2_database, db2_host, db2_port, db2_uid, db2_pwd, db2_admin, db2_admin_pwd, db2_schema, trace
    global odata_settings
    
    print("Enter connection information - hit ENTER without any input to cancel changes.")
    
    prompt = "Enter the DATABASE name: "
    t_database = input(prompt).upper();
    if (t_database == ''): return   
   
    prompt = "Enter the HOST IP address and PORT in the form ip:port or #x:port."
    t_host = input(prompt);
    if (t_host == ''): return
    
    ip, port = split_ipport(t_host)
    if (port == None): port = "50000"
    
    t_host = ip
    t_port = port

    prompt = "Enter the OData IP address and PORT in the form ip:port or #x:port."
    t_hostodata = input(prompt);
    if (t_hostodata == ''): return
    
    ip, port = split_ipport(t_hostodata)
    if (port == None): port = "9080"
    
    t_hostodata = ip
    t_portodata = port    
    
    prompt = "Enter the Userid on the Db2 system: "
    t_userid = input(prompt).upper();
    if (t_userid == ''): return
    
    t_u_pwd = getpass.getpass("Userid Password: ")
    if (t_u_pwd == ''): return
    
    prompt = "Enter the Admin user on the Db2 system: "
    t_admin = input(prompt).upper();
    if (t_admin == ''): return

    t_a_pwd = getpass.getpass("Admin Password: ")
    if (t_a_pwd == ''): return
    
    prompt = "Enter the SCHEMA that the table belongs to: "
    t_schema = input(prompt).upper();
    if (t_schema == ''): return
    
    odata_settings['database'] = t_database
    odata_settings['host'] = t_host
    odata_settings['port'] = t_port
    odata_settings['hostodata'] = t_hostodata
    odata_settings['portodata'] = t_portodata
    odata_settings['userid'] = t_userid
    odata_settings['u_pwd'] = t_u_pwd
    odata_settings['admin'] = t_admin
    odata_settings['a_pwd'] = t_a_pwd
    odata_settings['schema'] = t_schema
        
    save_settings()
    return

# Connect to OData Service or create one

def print_json(json_in):
    
    formatted = json.dumps(json_in, indent=4, separators=(',', ': '))
    print(formatted)    
    return

def odata_getservice(db2_table):
    
    global odata_settings

    odata_url = ""
    odata_metadata = ""
    
    parmname = ""
    for key, value in odata_settings.items():
        if value == "":
            if key == "database":
                parmname = "Database name"
            elif key == "schema":
                parmname = "Schema name"
            elif key == "userid":
                parmname = "Userid"
            elif key == "admin":
                parmname = "Admin Userid"
            elif key == "host":
                parmname = "Host IP address"
            elif parmname == "port":
                parmname = "Db2 port number"
            elif parmname == "hostodata":
                parmname = "OData IP address"
            elif parmname == "portodata":
                parmname = "OData port number"                
            elif parmname == "a_pwd":
                parmname = "Admin password"
            elif parmname == "u_pwd":
                parmname = "User password"
            else:
                pass
            
    if parmname != "" or db2_table == "":
        if db2_table == "": parmname = "Table name"
        error(parmname + " not set. Set the value with the SET command or use PROMPT for guidance on values.")
        return(odata_url, odata_metadata)
 
    # See if we have written out a pickle file that contains the settings URL for the table we want
    # Database pickle = DATABASE@SCHEMA@TABLE

    fname = "{0}@{1}@{2}.pickle".format(db2_table, odata_settings['schema'], odata_settings['database'])

    try:
        with open(fname,'rb') as f: 
            odata_url, odata_metadata = pickle.load(f) 
            return(odata_url, odata_metadata)
        
    except: 
        pass
                
    # We are making the assumption the Db2 Server and the OData gateway are on the same box
       
    set_service_URL = "http://{0}:{1}/ODataOne/createService".format(odata_settings['hostodata'],odata_settings['portodata'])
    header = {"Content-Type":"application/json", "Accept":"application/json"} 
    
    parameters = {
        "database": odata_settings['database'],
        "host": odata_settings['host'],
        "port": odata_settings['port'],
        "db2AdminUser": odata_settings['admin'],
        "db2AdminPassword": odata_settings['a_pwd'],
        "db2ServiceUser": odata_settings['userid'],
        "db2ServicePassword": odata_settings['u_pwd'],
        "ssl": False,
        "schema": odata_settings['schema'],
        "tablenames": [db2_table]
        }
       
    if odata_settings['echo'] == True:
        print("Creating Service Request for OData Connection")
        print("RESTful Call Type: POST(set_service_url, header, parameters)")
        print("Service URL: " + set_service_URL)
        print("Request Header")
        print_json(header)
        print("Connection Parameters") 
        temp_parameters = dict(parameters)
        temp_parameters["db2AdminPassword"] = "********"
        temp_parameters["db2ServicePassword"] = "********"
        print_json(temp_parameters)
        
    try:          
        r = requests.post(set_service_URL,headers=header,json=parameters) 
            
        if r.ok == True:
            response = (r.text).split('\n')
            if len(response) == 3:
                odata_url = response[1]
                odata_url = odata_url[odata_url.find('http'):]
                odata_metadata = response[2]
                odata_metadata = odata_metadata[odata_metadata.find('http'):]
            else:
                error("Improper response from OData.")
                return("", odata_metadata)
            
            try:
                with open(fname,'wb') as f:
                    pickle.dump([odata_url, odata_metadata],f)
            
            except:
                error("Failed trying to write OData settings to disk.")
                       
            return(odata_url, odata_metadata)    
                    
        else:
            print(errorOData)
    
    except:
        error("Error on RESTFul call. Check the connection parameters.")
        
    return("", odata_metadata)
        
    
# Parse the CONNECT statement and execute if possible 

def set_odata_settings(inSQL):
    
    global odata_settings
    
    cParms = inSQL.split()
    cnt = 0
    which_user = 'NONE'
    
    if (len(cParms) == 1):
        odata_set_help()
        return
    
    while cnt < len(cParms):
        
        if cParms[cnt].upper() == 'DATABASE':
            if cnt+1 < len(cParms):
                odata_settings['database']  = cParms[cnt+1].upper()
                cnt = cnt + 1
            else:
                error("No database specified in the TO clause")
                return
        elif cParms[cnt].upper() == 'USER':
            if cnt+1 < len(cParms):
                odata_settings['userid'] = cParms[cnt+1].upper()   
                cnt = cnt + 1
                which_user = 'USER'
            else:
                error("No userid specified in the USER clause.")
                return
        elif cParms[cnt].upper() == 'PASSWORD' or cParms[cnt].upper() == 'USING':
            if cnt+1 < len(cParms):
                db2_p = cParms[cnt+1]   
                if (db2_p == '?'):
                    db2_p = getpass.getpass("Password [password]: ") or "password"
                cnt = cnt + 1
                
                if which_user == 'USER':
                    odata_settings['u_pwd'] = db2_p
                elif which_user == 'ADMIN':
                    odata_settings['a_pwd'] = db2_p
                else:
                    error("No USER/ADMIN found for PASSWORD clause.")
                    return
            else:
                error("No password specified in the PASSWORD clause.")
                return
        elif cParms[cnt].upper() == 'ADMIN':
            if cnt+1 < len(cParms):
                odata_settings['admin'] = cParms[cnt+1].upper()
                cnt = cnt + 1
                which_user = 'ADMIN'                
            else:
                error("No admin userid specified in the ADMIN clause.")
                return
        elif cParms[cnt].upper() == 'HOST':
            if cnt+1 < len(cParms):
                odata_settings['host'] = cParms[cnt+1]
                cnt = cnt + 1
            else:
                error("No hostname specified in the HOST clause.")
                return
        elif cParms[cnt].upper() == 'PORT':                           
            if cnt+1 < len(cParms):
                odata_settings['port'] = cParms[cnt+1]
                cnt = cnt + 1
            else:
                error("No port specified in the PORT clause.")
                return
        elif cParms[cnt].upper() == 'HOSTODATA':
            if cnt+1 < len(cParms):
                odata_settings['hostodata'] = cParms[cnt+1]
                cnt = cnt + 1
            else:
                error("No hostname specified in the HOSTODATA clause.")
                return
        elif cParms[cnt].upper() == 'PORTODATA':                           
            if cnt+1 < len(cParms):
                odata_settings['portodata'] = cParms[cnt+1]
                cnt = cnt + 1
            else:
                error("No port specified in the PORTODATA clause.")
                return           
        elif cParms[cnt].upper() == 'SCHEMA':                           
            if cnt+1 < len(cParms):
                odata_settings['schema'] = cParms[cnt+1].upper()
                cnt = cnt + 1
            else:
                error("No schema specified in the SCHEMA clause.")
                return  
        elif cParms[cnt].upper() == 'MAXROWS':                           
            if cnt+1 < len(cParms):
                try:
                    odata_settings['maxrows'] = int(cParms[cnt+1])
                    if odata_settings['maxrows'] < -1: odata_settings['maxrows'] = -1
                except:
                    error("Invalid maximum number of rows specified.")
                cnt = cnt + 1
            else:
                error("No value specified in the MAXROWS clause.")
                return              
        else:
            cnt = cnt + 1
            
    if odata_settings['admin'] == '': odata_settings['admin'] = odata_settings['userid']
    if odata_settings['a_pwd'] == '':  odata_settings['a_pwd'] = odata_settings['u_pwd']
    if odata_settings['schema'] == '': odata_settings['schema'] = odata_settings['userid']
        
    save_settings()
            
    return

def tokenizer(inSQL):

    # Common routine used to take the input SQL string and parse out the tokens in it
    # The return value is a list of tokens that the routine can scan looking for values
    
    tokens = []
    strings = []
    stringCount = -1
      
    # Take out any quoted string that we will use later - makes parsing easier
    
    while "'" in inSQL:
        startQuote = inSQL.find("'")
        endQuote = inSQL.find("'", startQuote + 1)
        if endQuote == -1:
            error("Syntax Error: Quotes not properly matched.")
            return(tokens)
        
        stringCount = stringCount + 1
        strings.append(inSQL[startQuote:endQuote+1])
        inSQL = inSQL[:startQuote] + " &" + str(stringCount) + " " + inSQL[endQuote+1:]    
        
    # Remove any whitespace characters including CR/LF/TAB etc
    
    inSQL = " ".join(inSQL.split())
    inSQL = inSQL.upper()
    
    # Convert typical SQL syntax to ODATA syntax for < > etc...
    
    findpat = ["<="  ,">="  ,"!="  ,"<>"  ,"<"   ,">"   ,"="   ,",", "("  ,")"  , "-"    ,"*"     ,"/"     ,"+"     ]
    replpat = [" le "," ge "," ne "," ne "," lt "," gt "," eq "," " ," ( "," ) ", " sub ", " mul ", " div ", " add "]
    
    patNo = -1
    for findp in findpat:
        patNo = patNo + 1
        if findp in inSQL: inSQL = inSQL.replace(findp,replpat[patNo])
            
    inSQL = " ".join(inSQL.split())
                  
    # Split the entire string by blanks and replace &* values with the strings
    
    tokens = inSQL.split()
    
    i = 0
    while i < len(tokens):
        if "&" in tokens[i]:
            tokenstr = tokens[i]
            ch = tokenstr.find("&")
            index = int(tokenstr[ch+1:ch+2])
            tokens[i] = strings[index]
        i = i + 1 
        
    # Scan the list looking for a LIKE clause to rearrange the tokens into a function
    
    i = 0
    while i < len(tokens):
        if "LIKE" in tokens[i]:
            if i >= 0 and i+1 < len(tokens):
                column = tokens[i-1]
                pattern = tokens[i+1].replace("%","")
                tokens[i-1] = "contains({0},{1})".format(column,pattern)
                tokens[i] = ""
                tokens[i+1] = ""
        i = i + 1  
        
    # Return the list of tokens
        
    return(tokens)
        

def odata_buildinsert(inSQL):
    
    # Build an INSERT string for OData

    global odata_settings 
    
    sqlColumns = {}
    sqlTable   = ""
    
    tokens = tokenizer(inSQL)
    if len(tokens) == 1: 
        odata_insert_help()
        return("", sqlColumns)
    
    # Analyze the syntax. INSERT INTO TABLE(...) VALUES ...
    
    if len(tokens) < 6:
        error("INSERT syntax: INSERT INTO <table>(columns...) VALUES (val1, val2, ...)")
        return("", odata_request)    
        
    if tokens[1] != "INTO":
        error("INSERT syntax requires INSERT INTO <table>.")
        return("", sqlColumns)
    
    sqlTable = tokens[2]
    
    if "VALUES" not in tokens:
        error("INSERT requires a set of values to insert into a row.")
        return("", sqlColumns)
    
    column_start = 3
    values_start = tokens.index("VALUES") + 1
    
    if values_start >= len(tokens):
        error("No values suppled after the VALUES keyword.")
        return("", sqlColumns)
    
    while column_start < values_start:
        if tokens[column_start] == ")" or tokens[values_start] == ")": break
            
        if tokens[column_start] == "(":
            column_start = column_start + 1
            continue
            
        if tokens[values_start] == "(":
            values_start = values_start + 1
            continue
            
        column = tokens[column_start].upper()
        value = tokens[values_start]
        
        if "'" in value:
            value = value.strip("'")
        else:
            try:
                value = int(value)
            except:
                try:
                    value = float(value)
                except:
                    pass
 
        sqlColumns[column] = value
        column_start = column_start + 1
        values_start = values_start + 1
       
    return(sqlTable, sqlColumns) 

def findtoken(start, value, tokens):
    
    # Check to see if the value is found in the token position. If so, return the next position or -1 if not found
    
    while start < len(tokens):
        if tokens[start] == value:
            return(start)
        start = start + 1
        
    return(-1)
    
      
def odata_buildselect(inSQL):
    
    # Take some SQL and convert it into OData Format
    
    global odata_settings 
    
    odata_request = ""
    
    sqlColumns = ""
    sqlTable   = ""
    sqlWhere   = ""
    sqlLimit   = ""
    sqlCount   = ""
 
    tokens = tokenizer(inSQL)
    
    if len(tokens) == 1: 
        odata_select_help()
        return("",odata_request)
    
    # Start by looking for COUNT(*) or COUNT(COLUMN) in the SELECT list
    
    sqlpos = findtoken(1,"COUNT",tokens)
    
    # COUNT(*) or COUNT(column)
    
    if sqlpos == 1:
        syntax = False
        sqlpos = findtoken(2,"(",tokens)
        if sqlpos == 2:
            sqlpos = findtoken(4,")",tokens)
            if sqlpos == 4:
                sqlCount = tokens[3]
                syntax = True
                sqlpos = findtoken(5,"FROM",tokens)
                frompos = sqlpos
        if syntax == False:
            error("Syntax Error: COUNT(*) or COUNT(column) expected.")
            return("", odata_request)
    else:
        sqlpos = 1
        frompos = findtoken(1,"FROM",tokens)
        
    if frompos == -1:
        error("Syntax Error: No FROM clause found.")
        return("", odata_request)            
    
    # Gather all of the columns before the FROM clause (or ignore them for COUNT(*))
    
    while sqlpos < frompos:
        if tokens[sqlpos] != "mul":
            if sqlColumns == "":
                sqlColumns = tokens[sqlpos]
            else:
                sqlColumns = sqlColumns + "," + tokens[sqlpos]
        sqlpos = sqlpos + 1
 
    sqlpos = sqlpos + 1
    if sqlpos == len(tokens):
        error("Syntax Error: No table name following the FROM clause.")
        return("", odata_request)
                  
    sqlTable = tokens[sqlpos]
    
    # Now we need to check if we have a WHERE clause, ORDER BY clause, or LIMIT clause
    sqlpos = sqlpos + 1
    if sqlpos < len(tokens):
        sqlWhere = findValue("WHERE", tokens)
        sqlLimit = findValue("LIMIT", tokens)
        
    odata_request = odata_select_url(sqlTable, sqlColumns, sqlWhere, sqlLimit, sqlCount)
      
    return(sqlTable, odata_request)

def odata_builddelete(inSQL):
    
    # Build an DELETE string for OData

    global odata_settings 
    
    sqlTable = ""
    sqlColumn = ""
    sqlValue = ""
    
    tokens = tokenizer(inSQL)
    
    if len(tokens) == 1: 
        odata_delete_help()
        return(sqlTable,sqlColumn,sqlValue)
    
    sqlTable   = ""
    
    # Analyze the syntax. DELETE FROM TABLE WHERE ...
    
    if len(tokens) != 7:
        error("DELETE syntax: DELETE FROM &lt;table&gt; WHERE column=value")
        return("",sqlColumn,sqlValue)    
        
    if tokens[1] != "FROM":
        error("DELETE syntax requires DELETE FROM &lt;table&gt;")
        return("",sqlColumn,sqlValue)
    
    sqlTable = tokens[2]
    
    if tokens[3] != "WHERE":
        error("DELETE requires a WHERE clause to delete an individual row.")
        return("",sqlColumn,sqlValue)
              
    sqlColumn = tokens[4]
              
    if tokens[5] != "eq":
        error("DELETE can only have equality conditions: COLUMN=VALUE")
        return("",sqlColumn,sqlValue)
              
    sqlValue = tokens[6]
    
    return(sqlTable,sqlColumn,sqlValue)

def odata_buildupdate(inSQL):
    
    # Build an DELETE string for OData

    global odata_settings 
    
    sqlTable = ""
    sqlKey = ""
    sqlKeyValue = ""
    sqlColumn = ""
    sqlValue = ""
    
    tokens = tokenizer(inSQL)
    if len(tokens) == 1:
        odata_update_help()    
        return("", sqlKey, sqlKeyValue, sqlColumn, sqlValue)
    
    # Analyze the syntax. UPDATE TABLE SET col=val WHERE col=val
    
    if len(tokens) != 10:
        error("UPDATE syntax: UPDATE &lt;table&gt; SET COLUMN=VALUE WHERE KEY=VALUE.")
        return("", sqlKey, sqlKeyValue, sqlColumn, sqlValue)   
        
    sqlTable = tokens[1]
    
    if tokens[2] != "SET":
        error("UPDATE syntax requires UPDATE &lt;table&gt; SET col=value WHERE col=value.")
        return("", sqlKey, sqlKeyValue, sqlColumn, sqlValue)
    
    sqlColumn = tokens[3]
    
    if tokens[4] != "eq":
        return("", sqlKey, sqlKeyValue, sqlColumn, sqlValue)
    
    sqlValue = tokens[5]

    if "'" in sqlValue:
        sqlValue = sqlValue.strip("'")
    else:
        try:
            sqlValue = int(sqlValue)
        except:
            try:
                sqlValue = float(sqlValue)
            except:
                pass          
                    
    if tokens[6] != "WHERE":
        error("UPDATE statement requires a WHERE clause that includes the key column.")
        return("", sqlKey, sqlKeyValue, sqlColumn, sqlValue)
              
    sqlKey = tokens[7]
              
    if tokens[8] != "eq":
        error("UPDATE requires WHERE key=value to find the row to be updated.")
        return("", sqlKey, sqlKeyValue, sqlColumn, sqlValue)
    
    sqlKeyValue = tokens[9]
              
    return(sqlTable, sqlKey, sqlKeyValue, sqlColumn, sqlValue)

def odata_select_url(sqlTable, sqlColumns, sqlWhere, sqlLimit, sqlCount):
    
    # Step 1: Build table reference by appending an "S" at the back of the name. 
    # The ?& aren't required for the base URL, but added there if there are args

    if sqlTable == '': return("")
    
    header = "/" + sqlTable + "S"
    
    logic = ""
    prefix = "?"
    
    if sqlCount != "":
        if sqlCount != "mul" and sqlCount != "*": 
            logic = prefix + "$select={0}".format(sqlCount)
            prefix = "&"
    elif len(sqlColumns) > 0:
        logic = prefix + "$select={0}".format(sqlColumns)
        prefix = "&"
        
    if len(sqlWhere) > 0: 
        logic = logic + prefix + "$filter={0}".format(sqlWhere)
        prefix = "&"
            
    if len(sqlLimit) > 0:
        logic = logic + prefix + "$top={0}".format(sqlLimit)
        prefix = "&"
        
    if sqlCount != "":
        logic = logic + prefix + "$count=true"
        prefix = "&"
        
    logic = logic + prefix + "$format=json"
        
    logic = logic.replace("( ","(")
    logic = logic.replace(" )",")")

    return(header+logic)

def findValue(keyword, tokens):
    
    i = 0
    capture = False
    returnString = ""
    while i < len(tokens):
        if capture == False:
            if tokens[i] == keyword: capture = True
        else:
            if tokens[i] == "ORDERBY" or tokens[i] == "LIMIT": 
                return(returnString)
            else:
                if (tokens[i] == "AND" or tokens[i] == "OR" or tokens[i] == 'NOT'): tokens[i] = tokens[i].lower()
                if returnString == "":
                    returnString = tokens[i]
                else:
                    if tokens[i] != "": returnString = returnString + " " + tokens[i]
        i = i + 1
    
    return(returnString)
    
def success(message):
    
    html = '<p style="border:2px; border-style:solid; border-color:#008000; background-color:#e6ffe6; padding: 1em;">'
            
    if message != "":
        pDisplay(pHTML(html + message + "</p>"))
        
    return    
    
def errorOData(r):
    
    html = '<p style="border:2px; border-style:solid; border-color:#FF0000; background-color:#ffe6e6; padding: 1em;">'    
    
    if r == None: return
    
    errmsg = r.text
    
    try:
        results = r.json()
        sqlerror = results.get('error',None)
        if sqlerror != None:
            msg = sqlerror.get('message',None)
            if msg != None: errmsg = msg
    except:
        pass
    
    if errmsg != "":
        pDisplay(pHTML(html + errmsg +"</p>"))
        
    return

def error(message):
    
    # Given a message, display it in a box. If message is None, then look at the HTTP result for details
    
    html = '<p style="border:2px; border-style:solid; border-color:#FF0000; background-color:#ffe6e6; padding: 1em;">'    
    
    if message != "":
        pDisplay(pHTML(html + message + "</p>"))
 
    return

def odata_drop(inSQL):
    
    global odata_settings
    
    cParms = inSQL.split()
    cnt = 0
    
    if (len(cParms) == 1):
        odata_drop_help()
        return    

    db2_database = odata_settings['database']
    db2_schema = odata_settings['schema']
    db2_table = ""
    
    while cnt < len(cParms):
        
        if cParms[cnt].upper() == 'DATABASE':
            if cnt+1 < len(cParms):
                db2_database  = cParms[cnt+1].upper()
                cnt = cnt + 1
            else:
                error("No database specified in the UNREGISTER command")
                return
        elif cParms[cnt].upper() == 'SCHEMA':
            if cnt+1 < len(cParms):
                db2_schema = cParms[cnt+1].upper()   
                cnt = cnt + 1
            else:
                error("No schema specified in the SCHEMA clause.")
                return
        elif cParms[cnt].upper() == 'TABLE':
            if cnt+1 < len(cParms):
                db2_table = cParms[cnt+1].upper()
                cnt = cnt + 1
            else:
                error("No schema specified in the TABLE clause.")
                return          
        else:
            cnt = cnt + 1
    
    if db2_table == "" or db2_schema == "" or db2_database == "":
        return
    
    try:
        os.remove("{0}@{1}@{2}.pickle".format(db2_table,db2_schema,db2_database))
    except:
        pass
    
    success("OData connection removed for {0}.{1} in Database {2}".format(db2_schema,db2_table,db2_database))
            
    return


def odata_describe(sql):
    
    # Describe the contents of the table and build a column list
    global odata_settings 
    
    odata_request = ""
 
    tokens = tokenizer(sql)
    
    if (len(tokens) == 1):
        odata_describe_help()
        return
    
    if len(tokens) < 2: return
    
    sqlTable = tokens[1]
    
    odataurl, odatameta = odata_getservice(sqlTable)
    if odataurl == '' or odatameta == '': return
    
    header = {"Content-Type":"application/json"}        
        
    if (odata_settings['echo'] == True): 
        print("OData Meta Data Request")
        print("RESTful Call Type: GET(service_url + $metadata, header,)")
        print("URL: " + odatameta) 

    r = requests.get(odatameta,headers=header)  
    
    if r.ok == False:
        errorOData(r)
        return
    
    pattern = '''.*?<Property.*?Name="(?P<name>.*?)".*?Type="Edm.(?P<type>.*?)".*?'''
    
    columns = re.findall(pattern, r.text)
    
    select_list = ""
    for i in columns:
        if select_list == "":
            select_list = "(" + i[0]
        else:
            select_list = select_list + "," + i[0]
    
    select_list = select_list + ")"
    print(select_list)
    
    pd = pandas.DataFrame(columns)
    pd.columns = ['COLUMN','TYPE']
    pDisplay(pd)
    
    return  
  
def odata_delete(sql):
              
    # Build the DELETE string required by OData
    
    db2_table, db2_column, db2_value = odata_builddelete(sql)
    if db2_table == "": return
    
    # See if our table has the proper key available
    
    if odata_findkey(db2_table,db2_column) == False: return
    
    # Set up parameters and execute 
    
    odataurl, odatameta = odata_getservice(db2_table)  
    if odataurl == "": return
    
    header = {"Content-Type":"application/json", "Accept":"application/json"}        
        
    request = "/{0}S({1})".format(db2_table,db2_value)          
    
    if (odata_settings['echo'] == True): 
        print("OData Delete")
        print("RESTful Call Type: DELETE(service_url + OData request, header)")
        print(request)

    r = requests.delete(odataurl+request,headers=header)
    
    if r.ok == True:
        print(r.text)
        success("Record Deleted.")
    
    else:
        errorOData(r)

    return

def odata_update(sql):
    
    # Build the UPDATE string required by OData
   
    db2_table, db2_key, db2_keyvalue, db2_column, db2_value = odata_buildupdate(sql)
    if db2_table == "": return
    
    # See if our table has the proper key available
    
    if odata_findkey(db2_table,db2_key) == False: return
    
    # Set up parameters and execute 
    
    odataurl, odatameta = odata_getservice(db2_table)  
    if odataurl == "": return
    
    header = {"Content-Type":"application/json", "Accept":"application/json"}        
        
    request = "/{0}S({1})".format(db2_table,db2_keyvalue)     
    data = { db2_column : db2_value }  

    if (odata_settings['echo'] == True): 
        print("UPDATE Command")
        print("RESTful Call Type: PATCH(service_url + OData request, header, parameters)")        
        print("URL  : {0}".format(odataurl))
        print("OData: {0}".format(request))
        print("Parameters")
        print_json(data)  

    r = requests.patch(odataurl+request,json=data,headers=header)
    
    if r.ok == True:
        success("Record Updated.")
    else:
        errorOData(r)
        
    return
    

def odata_findkey(db2_table, db2_column):

    # Build the DELETE string required by OData
    
    if db2_table == "": return(False)
    
    # Get the URL for the request and the metadata
    
    odataurl, odatameta = odata_getservice(db2_table)  
    if odataurl == "": return(False)
    
    # Need to see if there is a primary key on this table for deletion
    
    header = {"Content-Type":"application/json"}        

    r = requests.get(odatameta,headers=header)  
    
    if r.ok == False:
        error("Unable to get metadata information required for a SQL statement.")
        return(False)
 
    pattern = '''.*?<Key><Property.*?Name="(?P<name>.*?)"/></Key>.*?'''
    
    columns = re.findall(pattern, r.text)
    
    if len(columns) == 0:
        error("The {0} table does not have a key that we can use to find a row.".format(db2_table))
        return(False)
    
    if columns[0] != db2_column:
        error("The requested column {0} in the SQL statement does not match the key {1} on the {2} table.".format(db2_column,columns[0],db2_table))
        return(False)
    
    return(True)
    
def odata_insert(sql):
    
    # Built the INSERT string required by OData
    
    db2_table, odatainsert = odata_buildinsert(sql)
    if db2_table == "": return
    
    odataurl, odatameta = odata_getservice(db2_table)  
    if odataurl == "": return
    
    # Set up parameters and execute request
    
    header = {"Content-Type":"application/json", "Accept":"application/json"}    
    
    if (odata_settings['echo'] == True): 
        print("INSERT Command")
        print("RESTful Call Type: POST(service_url + OData request, header, parameters)")        
        print("URL  : {0}".format(odataurl))
        print("OData: {0}".format(odatainsert))
        print("Parameters")
        print_json(odatainsert)        

    r = requests.post("{0}/{1}S".format(odataurl,db2_table),headers=header,json=odatainsert)
    
    if r.ok == True:
        success("Record inserted.")
    
    else:
        errorOData(r)

    return    
    
    
def odata_select(sql):
         
    db2_table, odatasql = odata_buildselect(sql)
       
    if db2_table == "" or odatasql == "": return

    odataurl, odatameta = odata_getservice(db2_table) 
       
    if odataurl == "": return

    # Now try to execute the OData request we built
        
    header = {"Content-Type":"application/json", "Accept":"application/json"}        
        
    if (odata_settings['echo'] == True): 
        print("SELECT Command")
        print("RESTful Call Type: GET(service_url + OData request, header)")        
        print("URL  : {0}".format(odataurl))
        print("OData: {0}".format(odatasql))

    r = requests.get(odataurl+odatasql,headers=header)
                 
    if r.ok == True:
        results = r.json()
        if results.get('@odata.count', None) != None and odata_settings['format'] != 'raw':
            count = int(results.get('@odata.count'))
            if count > 1 or count == 0:
                print("{0} rows found.".format(count))
            else:
                print("1 row found.")
            return
                
        if odata_settings['format'] == "table":
            s = json.dumps(results['value'])
            if odata_settings['maxrows'] == -1:
                pandas.reset_option('max_rows')
            else:
                pandas.options.display.max_rows = odata_settings['maxrows']
            
            pd = pandas.read_json(s,orient='records')
            pDisplay(pd)
            
        elif odata_settings['format'] == "raw":
            print_json(json.loads(r.text))
            
        else:
            s = json.dumps(results['value'])
            print_json(json.loads(s))
            
        if odata_settings['echo'] == True:
            usql = odatasql.replace(' ','%20')
            return(odataurl+usql)
        else:
            return
 
    else:
        errorOData(r)    
               
    return
    
   
@magics_class
class ODataDB2(Magics):
      
    @line_cell_magic
    def odata(self, line, cell=None):
            
        global odata_settings
        
        # If you use %odata (line) we just run the one line. If you use %%odata the entire cell is run.

        if cell == None:
            sql = line.strip()
        else:
            sql = line.strip() + " " + cell.strip()
            
        sql = " ".join(sql.split())    
        
        if len(sql.strip()) == 0: 
            odata_commands_help()
            return
         
        # See if you have any flags defined in the script
        # -j = results are returned as a JSON string with only the values 
        # -r = results are returned with all metadata included
        # -e = show the raw OData calls used by the code
        
        if "-j" in sql: 
            odata_settings['format'] = 'json'
        else:
            if "-r" in sql:
                odata_settings['format'] = 'raw'
            else:
                odata_settings['format'] = 'table'
            
        if "-e" in sql: 
            odata_settings['echo'] = True
        else:
            odata_settings['echo'] = False
            
        sql = sql.replace("-r","")
        sql = sql.replace("-e","")
        sql = sql.replace("-j","")
        
       
        tokens = sql.split()
        if len(tokens) > 0:
            cmd = tokens[0].upper()

            if cmd == "REGISTER": 
                odata_set_help()
                odata_set_prompt() 
                return
            elif cmd == 'SETTINGS':
                print("Database   : {0}".format(odata_settings['database']))
                print("Schema     : {0}".format(odata_settings['schema']))
                print("User       : {0}".format(odata_settings['userid']))
                print("Admin User : {0}".format(odata_settings['admin']))
                print("Host       : {0}".format(odata_settings['host']))
                print("Port       : {0}".format(odata_settings['port'])) 
                print("OData Host : {0}".format(odata_settings['hostodata']))
                print("OData Port : {0}".format(odata_settings['portodata']))
                print("Format     : {0}".format(odata_settings['format'])) 
                return
            elif cmd == "SET": set_odata_settings(sql)   
            elif cmd == "SELECT": return(odata_select(sql));
            elif cmd == "INSERT": odata_insert(sql)
            elif cmd == "RESET": odata_drop(sql)
            elif cmd == "UPDATE": odata_update(sql)
            elif cmd == "DELETE": odata_delete(sql)
            elif cmd == "DESCRIBE": odata_describe(sql)
            else:
                error("Unknown command: " + cmd)
        else:
            error("Empty %odata command.")
            
        return
            
# Register the Magic extension in Jupyter    
ip = get_ipython()          
ip.register_magics(ODataDB2)
load_settings()
print ("Db2 OData Extensions Loaded.")

Set the table formatting to left align a table in a cell. By default, tables are centered in a cell. Remove this cell if you don't want to change Jupyter notebook formatting for tables.


In [ ]:
%%html
<style>
  table {margin-left: 0 !important;}
</style>

Credits: IBM 2017, George Baklarz [baklarz@ca.ibm.com]