#Extension module SQLAlchemy

Introduction

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:

• File manipulation The user should be able to read, edit and create files or directories in the filesystem of the server (this is useful for configurations or metadata that don't need to be saved in the conversational process itself)
• Connectivity Conversational processes are usually fully interconnected with other services: the CPA user should be able to pass and retrieve information from APIs, services and other data sources.
• Data persistency Conversational data should be available for future uses, auditing purposes and to increase the intelligence of the processes, as described at the end of this chapter in the examples.

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:

• Requests module, designed to fetch and send information through APIs and any other service.
• ReadFile module, which allows the user to manipulate files into the server
• SQLAlchemy module, which guarantees the full control on the client's Virtual Private Database. It allows data to be available across different projects and conversations.

Which operations that can be performed

Object manipulation, reflected into the DB.

• SQL (Structured Query Language) statements In particular, SELECT, INSERT, UPDATE and DELETE operations are allowed.
• DDL (Data Definition Language) statements For example, CREATE TABLE and other commands are allowed.

Security

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:

• guarantee the full data segregation among different clients
• audit all the Database operations (available in the Audit Log extract)
• filter the DDL statements within the client's schema

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 dictionary SQL.tabs
• the method SQL.query(<SQLAlchemy table>)
• the function SQL.execute(<String>)

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

Operations

Data manipulations can be performed through

• plain text SQL queries
• ORM objects

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:
  print(el)

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

CxdUsage
CxdNode
CxdEdge
CxdChatmatrix
DmAggr_freq
DmAggr_stat
...

⚠️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

print(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:
    print(el)

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)

Examples

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
res = SQL.execute("SELECT COUNT(*) FROM CXD.NODE WHERE SCRIPT = 123 AND 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',
).count()

# 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',
).count()

# 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