Skip to main content
Question

How to query a data model to create a data frame? (PyCelonis 2.4.0)

  • Sep 4, 2023 3:05 PM UTC
  • 3 replies
  • 238 views

Hi there,

I have a Data Model and a Data Pool containing two tables: "REQUESTS" and "ACTIVITIES".

I want to access "REQUESTS" in the Workbench via PyCelonis, select several columns and transfer them into a DataFrame.

 

The connection could be established successfully:

data_pool = celonis.data_integration.get_data_pools().find('Process-Block')

data_model = data_pool.get_data_models().find('Data Model')

 

If I now try to output the individual tables with

data_model.get_tables

(source: https://celonis.github.io/pycelonis/1.6.0/tutorials/api_tutorial/04_PQL_and_Python_2/#4-interaction-with-the-celonis-resource-object)

I get as output ID and name of the datamodel, but not the individual tables, as described in the documentation. Also the commands are outdated. Instead of .tables .get_tables must be used in the current version.

 

Since I can't display the tables, I also can't proceed with the following function according to the doc:

List all the columns of a Data Model Table

datamodel.tables.find('ACTIVITIES').columns

 

Questions:

How can I specifically extract data from a specific table / columns of the Data Model and transfer it to a dataframe (Inside the Wokbench / PyCelonis)?

Is there an actual doc for pushing data from / to the Data Model?

 

I am looking forward to your advice.

3 replies

  • Sep 4, 2023 3:39 PM UTC

You need to create a PQL() object with the query. With that you get a dataframe. See below.

 

Also, here you have some documentation - https://celonis.github.io/pycelonis/2.0.1/tutorials/executed/02_data_integration/03_data_pull/

 

You can NOT push data into the process model. You can update a table of the process model, then reload the process model.

 

HTH

 

----------------------------------

 

from pycelonis.pql import PQL, PQLColumn

 

query=PQL()

    for c in t.get_columns():

      query+=PQLColumn(name=c.name, query='\\"%s\\".\\"%s\\"' %(t.name, c.name))

    try:

      df=tempdm.export_data_frame(query)

    except:

      print("**** Error descargando contenido de las tabla %s desde datapool %s del entorno Origen" %(t.name,dplOrigen.name) ) 

      flog.write("**** Error descargando contenido de las tabla %s desde datapool %s del entorno Origen\\n" %(t.name,dplOrigen.name) )

      continue


  • Sep 5, 2023 3:45 PM UTC

Hello Guillermo,

Thank you for your answer and the reference to the tutorial. It is extremely helpful.

 


  • Sep 6, 2023 6:01 AM UTC

Hello Guillermo,

Thank you for your answer and the reference to the tutorial. It is extremely helpful.

 

My pleasure!