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:
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:
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:
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 <table>
</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 <database> SCHEMA <schema> TABLE <table>
</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 <table>(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 <table> 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 <table> 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 <table> [ 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 <table> WHERE column=value")
return("",sqlColumn,sqlValue)
if tokens[1] != "FROM":
error("DELETE syntax requires DELETE FROM <table>")
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 <table> SET COLUMN=VALUE WHERE KEY=VALUE.")
return("", sqlKey, sqlKeyValue, sqlColumn, sqlValue)
sqlTable = tokens[1]
if tokens[2] != "SET":
error("UPDATE syntax requires UPDATE <table> 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>