Bonjour La Communauté,
Le langage SQL reste le moyen le plus pratique pour récupérer de l'information stockée en base de données.
Le format JSON est très souvent utilisé dans les échanges de données.
Il est donc fréquent de chercher à obtenir des données au format JSON à partir de requêtes SQL.
Vous trouverez ci-dessous des exemples simples qui pourront vous aider à répondre à ce besoin à partir de code en ObjectScript et en Python.
ObjectScript : via le SQL dynamique avec %SQL.Statement + les structures JSON avec %DynamicObject et %DynamicArray
ClassMethod getJSON() As %String
{
// déclaration d'une requête SQL
set query = "SELECT top 3 name,age,to_char(dob,'Day DD Month YYYY') as dob FROM sample.person"
// instanciation + exécution d'une requête SQL dynamique
// avec le résultat dans un result set de type %SQL.StatementResult
set rs=##class(%SQL.Statement).%ExecDirect(,query)
// instanciation d'un %DynamicArray
set list = []
// boucle de parcours du result set
while rs.%Next() {
// instanciation d'un %DynamicObject 'row'
set row = {
"name":(rs.%Get("name"))
,"age":(rs.%Get("age"))
,"dob":(rs.%Get("dob"))
}
// ajout d'un élément dans le %DynamicArray
do list.%Push(row)
}
// instanciation d'un autre %DynamicObject 'result'
// composé à partir du contenu du %DynamicArray
set result = {
"results":(list.%Size())
,"items":(list)
}
// export des données du %DynamicObject
// au format chaîne JSON
return result.%ToJSON()
}
ObjectScriptObjectScript
Résultat :
write ##class(resultset).getJSON()
{"results":3,"items":[{"name":"Pantaleo,Mary Q.","age":36,"dob":"Monday 07 December 1987"},{"name":"Underman,Laura I.","age":71,"dob":"Friday 16 May 1952"},{"name":"Huff,Lydia J.","age":26,"dob":"Sunday 09 November 1997"}]}
ObjectScriptObjectScript
Embedded Python : via les modules iris et json
ClassMethod get() As %String [ Language = python ]
{
import iris
import json
# déclaration d'une requête SQL
query = "SELECT top 3 name,age,to_char(dob,'Day DD Month YYYY') FROM sample.person"
# instanciation + exécution d'une requête SQL dynamique
# avec résultat dans un result set de type %SYS.Python.SQLResultSet
rs=iris.sql.exec(query)
# instanciation d'une structure de type liste
list = []
# boucle de parcours du result set
for idx, x in enumerate(rs):
# instanciation d'une structure de type dictionnaire 'row'
row = {
"name":x[0]
,"age":x[1]
,"dob":x[2]
}
# ajout d'un élément dans la liste
list.append(row)
# instanciation d'une autre structure de type dictionnaire 'result'
# composée à partir du contenu du la struture de type liste
result = {
"results":len(list)
,"items":list
}
# export des données de type dictionnaire
# au format chaîne JSON
return json.dumps(result)
}
PythonPython
Résultat :
write ##class(resultset).get()
{"results": 3, "items": [{"name": "Pantaleo,Mary Q.", "age": 36, "dob": "Monday 07 December 1987"}, {"name": "Underman,Laura I.", "age": 71, "dob": "Friday 16 May 1952"}, {"name": "Huff,Lydia J.", "age": 26, "dob": "Sunday 09 November 1997"}]}
ObjectScriptObjectScript
Comme vous pouvez le constater, le code est très similaire entre ces 2 méthodes ; ObjectScript et Python offrant des moyens relativement proches pour manipuler des structures JSON.
Class User.resultset
{
/// Description
ClassMethod get() As %String [ Language = python ]
{
import iris
import json
query = "SELECT top 3 name,age,to_char(dob,'Day DD Month YYYY') FROM sample.person"
rs=iris.sql.exec(query)
list = []
for idx, x in enumerate(rs):
row = {"name":x[0],"age":x[1],"dob":x[2]}
list.append(row)
result = {"results":len(list),"items":list}
return json.dumps(result)
}
ClassMethod getJSON() As %String
{
set query = "SELECT top 3 name,age,to_char(dob,'Day DD Month YYYY') as dob FROM sample.person"
set rs=##class(%SQL.Statement).%ExecDirect(,query)
set list = []
while rs.%Next() {
set row = {"name":(rs.%Get("name")),"age":(rs.%Get("age")),"dob":(rs.%Get("dob"))}
do list.%Push(row)
}
set result = {"results":(list.%Size()),"items":(list)}
return result.%ToJSON()
}
}
ObjectScriptObjectScript
Vous pouvez aussi répondre à ce même besoin en utilisant les modules Python suivants :
resultsetJSON.py : avec les modules Python DB-API et json
import iris
import json
import getpass
import os
if 'IRISHOSTNAME' in os.environ:
hostname = os.environ['IRISHOSTNAME']
else:
hostname = input('hostname [localhost] :') or "localhost"
if 'IRISPORT' in os.environ:
port = os.environ['IRISPORT']
else:
port = input('port [1972] :') or "1972"
if 'IRISUSERNAME' in os.environ:
username = os.environ['IRISUSERNAME']
else:
username = input('login [_SYSTEM] :') or "_SYSTEM"
if 'IRISPASSWORD' in os.environ:
password = os.environ['IRISPASSWORD']
else:
password = getpass.getpass('password [SYS]:') or 'SYS'
if 'IRISNAMESPACE' in os.environ:
namespace = os.environ['IRISNAMESPACE']
else:
namespace = input('namespace [IRISAPP] :') or "IRISAPP"
connection_string = hostname + ":" + port + "/" + namespace
print("Connecting to",connection_string)
connectionIRIS = iris.connect(connection_string, username, password)
cursorIRIS = connectionIRIS.cursor()
print("Connected to",connection_string)
query = "SELECT top 3 name,age,to_char(dob,'Day DD Month YYYY') FROM sample.person"
cursorIRIS.execute(query)
resultSet = cursorIRIS.fetchall()
list = []
result = {"results":len(resultSet),"items":list}
for x in resultSet:
row = {"name":x[0],"age":x[1],"dob":x[2]}
list.append(row)
print(json.dumps(result))
connectionIRIS.close()
PythonPython
Résultat :
python resultsetJSON.py
hostname [localhost] :
port [1972] :51779
login [_SYSTEM] :
password [SYS]:
namespace [IRISAPP] :
Connecting to localhost:51779/IRISAPP
Connected to localhost:51779/IRISAPP
{"results": 3, "items": [{"name": "Pantaleo,Mary Q.", "age": 36, "dob": "Monday 07 December 1987"}, {"name": "Underman,Laura I.", "age": 71, "dob": "Friday 16 May 1952"}, {"name": "Huff,Lydia J.", "age": 26, "dob": "Sunday 09 November 1997"}]}
Shell SessionShell Session
resultsetJSONembedded.py : avec les modules iris et json
import os
import getpass
if not 'IRISUSERNAME' in os.environ:
os.environ['IRISUSERNAME'] = input('set IRISUSERNAME [_SYSTEM] :') or "_SYSTEM"
if not 'IRISPASSWORD' in os.environ:
os.environ['IRISPASSWORD'] = getpass.getpass('set IRISPASSWORD [SYS]:') or 'SYS'
if not 'IRISNAMESPACE' in os.environ:
os.environ['IRISNAMESPACE'] = input('set IRISNAMESPACE [IRISAPP] :') or "IRISAPP"
import iris
import json
query = "SELECT top 3 name,age,to_char(dob,'Day DD Month YYYY') FROM sample.person"
rs=iris.sql.exec(query)
list = []
for idx, x in enumerate(rs):
row = {"name":x[0],"age":x[1],"dob":x[2]}
list.append(row)
result = {"results":len(list),"items":list}
print(json.dumps(result))
PythonPython
Résultat :
export IRISUSERNAME=_SYSTEM
export IRISNAMESPACE=IRISAPP
irispython resultsetJSONembedded.py
set IRISPASSWORD [SYS]:
{"results": 3, "items": [{"name": "Ihringer,Nellie O.", "age": 32, "dob": "Friday 17 January 1992"}, {"name": "Koenig,Sally J.", "age": 34, "dob": "Monday 25 December 1989"}, {"name": "Uberoth,Belinda I.", "age": 47, "dob": "Thursday 11 March 1976"}]}
Shell SessionShell Session
Merci à @Vitaliy Serdtsev : il est aussi possible de récupérer directement un JSON à partir d'une requête SQL, avec les fonctions JSON_ARRAYAGG et JSON_OBJECT :
SELECT JSON_ARRAYAGG(json_obj) FROM (SELECT TOP 5 JSON_OBJECT( 'Name':name ,'Age':age ,'DOB':to_char(dob,'Day DD Month YYYY') ) json_obj FROM sample.person )
SELECT JSON_ARRAYAGG(json_obj) FROM (SELECT JSON_OBJECT( 'Name':name ,'Age':age ,'DOB':to_char(dob,'Day DD Month YYYY') ) json_obj FROM sample.person ) WHERE %VID BETWEEN 1 AND 5
Résultat :