Question
· Nov 16, 2023

Vue SQL vers fichier CSV

Bonjour,

Je dois récupérer quotidiennement la totalité des données d'une vue SQL externe pour générer un fichier csv unique. J'étais partie sur l'utilisation d'un service de type EnsLib.SQL.Service.GenericService mais cela ne me convient pas puisque chaque ligne de résultat de la requête SQL génère un message et une trace différents. Dois-je passer par une table de lien ? Créer un service de toute pièce en implémentant le OnProcessInput() ? Autre ?

Merci par avance pour votre aide.

Bien cordialement,

Version du produit: IRIS 2023.1
$ZV: IRIS for UNIX (Ubuntu Server 20.04 LTS for x86-64) 2023.1.2 (Build 450U) Mon Oct 16 2023 10:47:42 EDT
Discussion (2)2
Connectez-vous ou inscrivez-vous pour continuer

Bonjour,

Effectivement, le service EnsLib.SQL.Service.GenericService est un service de type "Business Service" qui va générer un message pour chaque ligne de résultat de la requête SQL. Ce service est donc adapté pour des requêtes qui retournent un nombre limité de lignes.

Je pense que tu vas devoir passer par du code custom.

Voici un exemple en Python qui est relativement simple à mettre en oeuvre.

Le bs :

from grongier.pex import BusinessService
import pandas as pd
from sqlalchemy import create_engine


from .msg import SQLMessage

class SQLService(BusinessService):

    def __init__(self, **kwargs):
        self.sql = None
        self.conn = None
        self.target = None

    def on_init(self):
        if not hasattr(self, 'sql'):
            raise Exception('Missing sql attribute')
        if not hasattr(self, 'conn'):
            raise Exception('Missing conn attribute')
        if not hasattr(self, 'target'):
            raise Exception('Missing target attribute')

        self.engine = create_engine(self.conn)

        # raise an error if cannot connect to the database
        self.engine.connect()

    def get_adapter_type():
        """
        Name of the registred Adapter
        """
        return "Ens.InboundAdapter"

    def on_process_input(self, message_input):
        # create a dataframe from the sql query
        df = pd.read_sql(self.sql, self.engine)
        # create a message
        message = SQLMessage(dataframe=df)
        # send the message to the target
        self.send_request_sync(self.target, message)
        return

Ici on utilise la force de pandas pour créer un dataframe à partir du résultat de la requête SQL. On crée ensuite un message qui contient le dataframe et on l'envoie au target.

La configuration est faite à partir de propriétés suivantes :

  • sql : la requête SQL
  • conn : la connexion à la base de données
  • target : le target

Le message :

from grongier.pex import Message
from dataclasses import dataclass
from pandas.core.frame import DataFrame

@dataclass
class SQLMessage(Message):
    dataframe: DataFrame = None

Le message qui contient le dataframe.

La target qui permet de créer le fichier csv :

from grongier.pex import BusinessOperation
from .msg import SQLMessage

import pandas as pd

class CSVOperation(BusinessOperation):

    def __init__(self, **kwargs):
        self.filename = None

    def on_init(self):
        if not hasattr(self, 'filename'):
            raise Exception('Missing filename attribute')

    def on_sql_message(self, message_input: SQLMessage):
        # get the dataframe from the message
        df = message_input.dataframe
        # create a csv file
        df.to_csv(self.filename, index=False)
        return

La configuration est faite à partir du fichier settings.py :

from sqltocsv import bo,bs
import os

CLASSES = {
    'Python.Bs.SQLService': bs.SQLService,
    'Python.Bo.CSVOperation': bo.CSVOperation
}

db_user = os.environ.get('POSTGRES_USER', 'DemoData')
db_password = os.environ.get('POSTGRES_PASSWORD', 'DemoData')
db_host = os.environ.get('POSTGRES_HOST', 'db')
db_port = os.environ.get('POSTGRES_PORT', '5432')
db_name = os.environ.get('POSTGRES_DB', 'DemoData')

PRODUCTIONS = [{
    "Python.Production": {
        "@Name": "Python.Production",
        "@LogGeneralTraceEvents": "false",
        "Description": "",
        "ActorPoolSize": "2",
        "Item": [
            {
                "@Name": "Python.Bs.SQLService",
                "@Category": "",
                "@ClassName": "Python.Bs.SQLService",
                "@PoolSize": "1",
                "@Enabled": "true",
                "@Foreground": "false",
                "@Comment": "",
                "@LogTraceEvents": "false",
                "@Schedule": "",
                "Setting": {
                    "@Target": "Host",
                    "@Name": "%settings",
                    "#text": "sql=select * from formation\nconn=postgresql://"+db_user+":"+db_password+"@"+db_host+":"+db_port+"/"+db_name+"\ntarget=Python.Bo.CSVOperation"
                }
            },
            {
                "@Name": "Python.Bo.CSVOperation",
                "@Category": "",
                "@ClassName": "Python.Bo.CSVOperation",
                "@PoolSize": "1",
                "@Enabled": "true",
                "@Foreground": "false",
                "@Comment": "",
                "@LogTraceEvents": "false",
                "@Schedule": "",
                "Setting": {
                    "@Target": "Host",
                    "@Name": "%settings",
                    "#text": "filename=/tmp/export.csv"
                }
            }
        ]
    }
}]

On utilise les variables d'environnement pour la connexion à la base de données.

Tu as l'exemple complet ici :

https://github.com/grongierisc/formation-template-python/tree/demo_sqltodb

Je laisse un autre membre de la communauté répondre avec une solution en ObjectScript.

Bonjour Guillaume,

mes excuses pour ce retour tardif. Merci beaucoup pour ton retour, Laurent avait posté pour moi à un moment où j'étai bloquée.

Je suis partie sur une solution hybride avec une table de lien + un service custom en objectscript qui appelle une opération avec une méthode en embedded python + panda. Très efficace, merci encore.

Bien cordialement,

Cécile