Obtenir un résultat en JSON à partir d'une requête SQL

Article

Sylvain Guilbaud · Fév 1 6m de lecture

#Embedded Python #JSON #ObjectScript #Python #SQL #InterSystems IRIS #InterSystems IRIS for Health

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()
}

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"}]}

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)
}

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"}]}

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()
}

}

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()

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"}]}

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))

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"}]}