#Extension module SQLAlchemy


This chapter is dedicated to the advanced module SQLAlchemy, a powerful framework that allows to manipulate data directly on the client's Virtual Private Database.

What is an ORM

An ORM (Object-Relational Mapping) is a wrapping technique that converts entities of a database (let's say a RDBMS, relational database) to an object-oriented paradigm. SQLAlchemy is widely recognised as the best ORM for Python applications and gives all the needed flexibility to manipulate data efficiently.

Why connecting to a database

It may be obvious for software.prod.lopers, but in general the maturity of a SaaS platform is measured by three quantity:

The Function Manager is the CPA module dedicated to the implementation of advanced logic and allows.prod.lopers to write their own Python source code. Furthermore, the three quantities previously mentioned are covered by the three extra modules expansions:

Which operations that can be performed

Object manipulation, reflected into the DB.


The manipulation of data at Database level requires an extensive understanding of security and performance concerns. The implementation of the two sophisticated frameworks, the Oracle Virtual Private Database and Fine-grained Hierarchical Access Control, allows the CPA to:

The Python ORM SQLAlchemy toolkit

When the SQLAlchemy extension is activated, the Function Manager will get an object SQL containing all the methods and procedures to manipulate data. The full Python ORM SQLAlchemy reference documentation is available here.

In particular, there are three objects that cover most of the data manipulation scenarios:

The SQL.tabs dictionary contains the list of tables where SQL operations are allowed.

    <TableObject>: {
        "schema": <String>,     <- RDBMS table schema
        "name": <String>,       <- RDBMS table name
        "permission": {         <- True when operations are allowed, False otherwise
            "select": <Boolean>,
            "insert": <Boolean>,
            "update": <Boolean>,
            "delete": <Boolean>
        "columns": {
            <ColumnName>: {
                "id": <Integer>,                <- Index of the column in the RDBMS
                "type": <Type of data>,         <- Type of data (NUMBER, VARCHAR2, CHAR, CLOB, ETC ...)
                "length": <Integer>,            <- Max possible length of data
                "nullable": <Boolean>,          <- True if it can be null
                "default_on_null": <Boolean>,   <- True if automatically assigned when NULL is inserted
                "default": <String>,            <- Default value
                "is_pk": <Boolean>              <- True if this column is part of the table primary key
            ...     <- Other columns
    ...     <- Other tables

⚠️Important note⚠️ SQLAlchemy extension module is available only for Advanced and Premium subscriptions. If the SQLAlchemy box in the Extension modules configuration table is not ticked, the system will show the following message in the Log Error console:

[x] Security error in importing a Python module: package "SqlAlchemy" is forbidden. Will be ignored


Data manipulations can be performed through

It's up to the CPA user to decide which approach to use. The full Python ORM SQLAlchemy reference documentation is available here.

Get all tables

The list of tables can be obtained from the list SQL.tabs.

for el in SQL.tabs:

The result will display the list of ORM objects that can be used in the statement SQL.query(<ORM object>):


⚠️Important note⚠️ The list SQL.tabs includes all the accessible tables and views of the client's Virtual Private Database. The name convention is SchemaTableName. Most of the schemas are available across all the organizations, like Cxd (Conversational configuration), Dm (Data Model), Db (Auditing and global settings), etc … but also private client's tables like <ClientName>.

ORM Objects manipulation

Data manipulation can be perform via plain SQL text or Python ORM objects. In this second case, the SQL.query(<ORM object>) will expose all the SQLAlchemy methods. Every ORM object contains a number of attributes corresponding to the mapped database table. For example, the object CxdAccount maps the table CXD.ACCOUNT that contains all the CPA users; below are listed some of the available attributes:

CxdAccount.geo          # Geographical location (UE, US, ...)
CxdAccount.env          # Environment (DEV, TEST or PROD)
CxdAccount.client_id    # Hashed Id of the current client
CxdAccount.username     # Email address of the user

The list of SQLAlchemy methods is quite big and allows to cover all the SQL instructions. Below are reported few examples of some of the most common methods:

.all()                              # Return dataset
.filter(condition, condition, ...)  # Filter result by conditions
.count()                            # Count number of rows
.first()                            # Fetch only the first row

In the following example the variable text will contain all the texts available in the project id 123 branch 1:

res = SQL.query(CxdNode).filter(CxdNode.script == 123, CxdNode.branch == 1)

text = ""
for el in res:
    text += el.text


Execute plain SQL statements

Plain SQL statements can be executed with the procedure

res = SQL.execute(<SQL plain string>)

The result res is an SQLAlchemy.engine.result object that can be iterated with:

for el in res:

The content el is a lazy tuple where each element corresponds to a column of the SQL query with the order specified.

For example, SQL.execute("SELECT 'A' AS C1, 'B' AS C2 FROM DUAL") will return the values A and B from the Oracle dummy table DUAL. Below the complete example script:

res = SQL.execute("SELECT 'A', 'B' FROM DUAL")
for el in res:
    print(el)   # <-- this will print "('A','B')"

The command res.keys() will give the columns' names of the table (in the previous example, ['C1', 'C2']).

Queries can be also bind with variables, which can be specified as a dictionary. Below an example of SQL query bind with two variables x and y:

statement = "SELECT * FROM MYTABLE WHERE X < :x AND Y = :y"
variables = {
    'x': 100,
    'y': 'hello'

res = SQL.execute(, variables)


Scan current conversation

In this example, the conversational process will count the messages of the process itself and report back to the user. This information could be used to inform the end user on their progress in the chat.

Using a plain SQL statement:

# Count the number of messages for the project 123 branch 1
# Save the result in a variable called "numOfMess" (1 row, 1 column result)
save("numOfMess", res.first().values()[0])

Using a Python ORM object:

# Count the number of messages for the project 123 branch 1
res = SQL.query(CxdNode).filter(CxdNode.script == 123, CxdNode.branch == 1).count()
# Save the result in a variable called "numOfMess"
save("numOfMess", res)

It's possible now creating a message in the conversational process like: This process contains {{numOfMess}} questions: would you like to proceed?

A simple AI algorithm: make decisions base on conversational history

Let's imagine a quote&buy journey: at the end of the process, a variable productPurchased with key values T or F will be collected. The process starts with a functional node splitAI that split the traffic in two paths: the first starting with message A, the second with message B. The splitAI function decides automatically to redirect the user to the message that gave a better selling performance in the last 5 days. The function will randomly choose between message A and message B if the performance difference is lower that 75%.

This AI algorithm can be obtained with a function similar to the following:

from datetime import datetime, timedelta
import random

# Get number of purchases with message A
res_A = SQL.query(DmAgg_freq).filter(
    DmAgg_freq.ref_date >= datetime.today() - timedelta(days = 5),
    DmAgg_freq.productPurchased == 'T',
    DmAgg_freq.firstMessage == 'A',

# Get number of purchases with message B
res = SQL.query(DmAgg_freq).filter(
    DmAgg_freq.ref_date >= datetime.today() - timedelta(days = 5),
    DmAgg_freq.productPurchased == 'T',
    DmAgg_freq.firstMessage == 'B',

# Message A performed better than 75%
if res_A / (res_A + res_B) > 0.75:
    exit(1) # Mode to message A

# Message B performed better than 75%
if res_B / (res_A + res_B) > 0.75:
    exit(2) # Move to message B

# Neither message A and B exceeded the 75%: choose randomly one of them
exit(random.randint(0, 1))  # Move to A or B