This example shows how to create a very simple rule engine that executes SQL queries on an in-memory SQL database

In [1]:
import pandas as pd
import sqlite3
In [2]:
class RuleEngine(object):
    """Handles creation and execution of a SQL based rule engine.
    Input are a pandas dataframe with market data and a list of 
    SQL queries to be executed. Generates a database with
    all data, old and newly generated
    """
    def __init__(self, pd_marketdata, output_fields):
        """
        Takes a pandas dataframe with market data and  a list of 
        output fields, i.e. fields that should be newly generated  and
        returns an SQLite in-memory database as self.db with one table temp_tbl
        containing all market data
        """
        self.db = sqlite3.connect(':memory:')
        marketdata_fields = pd_marketdata.columns
        # attach output fields to the dataframe
        for field in output_fields:
            pd_marketdata[field] = ""
        # write dataframe to database
        pd_marketdata.to_sql("temp_tbl", self.db)
        self.db.commit()

    def execute_rules(self, sql_list):
        """
        Takes a list of sql queries of the form 
        UPDATE temp_tbl set SPPI_ASSET_CLASS = 'FAIL' WHERE MARKET_SECTOR_DES = 'Equity'
        and executes all queries on the in-memory database self.db
        """
        cursor = self.db.cursor()
        for sql in sql_list:
            cursor.execute(sql)
            
    def to_excel(self, path_to_file):
        """Writes the rule engine database to Excel"""
        df = pd.read_sql_query("SELECT * FROM temp_tbl", self.db)
        writer = pd.ExcelWriter(path_to_file, engine='xlsxwriter')
        df.to_excel(writer, sheet_name='Sheet1')
        writer.save()
In [3]:
path_csv_marketdata = 'market_data.csv'
pd_marketdata = pd.read_csv(path_csv_marketdata)
pd_marketdata
Out[3]:
Identifier INDUSTRY_SUBGROUP LAST_CPN_PERIOD_TYP MARKET_SECTOR_DES NAME
0 AT0000386115 Sovereign Normal Govt REPUBLIC OF AUSTRIA
1 AT0000A001X2 Sovereign Normal Govt REPUBLIC OF AUSTRIA
2 AT0000A0N9A0 Sovereign Normal Govt REPUBLIC OF AUSTRIA
3 DE0001134922 Sovereign Normal Govt BUNDESREPUB. DEUTSCHLAND
4 IE00B5M4WH52 N.A. Equity ISHARES EM LOCAL GOVT
5 LU0826191198 N.A. Equity RESPONSABILITY MIKROFIN-IEUR
In [4]:
new_fields = ["SPPI_ASSET_CLASS", "SPPI_RESULT"]
In [5]:
sql_list = [
    "UPDATE temp_tbl set SPPI_ASSET_CLASS = 'PASS' WHERE MARKET_SECTOR_DES = 'Govt'",
    "UPDATE temp_tbl set SPPI_ASSET_CLASS = 'FAIL' WHERE MARKET_SECTOR_DES = 'Equity'",
    "UPDATE temp_tbl set SPPI_RESULT = 'PASS'",
    "UPDATE temp_tbl set SPPI_RESULT = 'FAIL' WHERE SPPI_ASSET_CLASS = 'FAIL'"
]
In [6]:
rule_engine = RuleEngine(pd_marketdata, new_fields)
In [7]:
pd.read_sql_query("SELECT * FROM temp_tbl", rule_engine.db)
Out[7]:
index Identifier INDUSTRY_SUBGROUP LAST_CPN_PERIOD_TYP MARKET_SECTOR_DES NAME SPPI_ASSET_CLASS SPPI_RESULT
0 0 AT0000386115 Sovereign Normal Govt REPUBLIC OF AUSTRIA
1 1 AT0000A001X2 Sovereign Normal Govt REPUBLIC OF AUSTRIA
2 2 AT0000A0N9A0 Sovereign Normal Govt REPUBLIC OF AUSTRIA
3 3 DE0001134922 Sovereign Normal Govt BUNDESREPUB. DEUTSCHLAND
4 4 IE00B5M4WH52 N.A. Equity ISHARES EM LOCAL GOVT
5 5 LU0826191198 N.A. Equity RESPONSABILITY MIKROFIN-IEUR
In [8]:
rule_engine.execute_rules(sql_list)
In [9]:
pd.read_sql_query("SELECT * FROM temp_tbl", rule_engine.db)
Out[9]:
index Identifier INDUSTRY_SUBGROUP LAST_CPN_PERIOD_TYP MARKET_SECTOR_DES NAME SPPI_ASSET_CLASS SPPI_RESULT
0 0 AT0000386115 Sovereign Normal Govt REPUBLIC OF AUSTRIA PASS PASS
1 1 AT0000A001X2 Sovereign Normal Govt REPUBLIC OF AUSTRIA PASS PASS
2 2 AT0000A0N9A0 Sovereign Normal Govt REPUBLIC OF AUSTRIA PASS PASS
3 3 DE0001134922 Sovereign Normal Govt BUNDESREPUB. DEUTSCHLAND PASS PASS
4 4 IE00B5M4WH52 N.A. Equity ISHARES EM LOCAL GOVT FAIL FAIL
5 5 LU0826191198 N.A. Equity RESPONSABILITY MIKROFIN-IEUR FAIL FAIL
In [ ]:
export_path = "C:\\Users\\jakob\\GoogleDrive\\market_data.xlsx"
rule_engine.to_excel(export_path)
In [ ]:
In [ ]: