Introduction

Overview

It is a procedure to call Python code from AWS serverless service Lambda using CData ODBC Driver and handle the data in the kintone application.

Diagram

Products and services used

・ AWS Lambda ・ "Kintone" (https://kintone.cybozu.kom/jp/) -CData kintone ODBC Driver for Linux

As of August 20, 2017, the beta version for kinton can be downloaded from here. ODBC drivers for other data sources can be downloaded from here ・ Pyodbc ・ RHEL5.7 (for pyodbc library extraction)

Sample code

https://github.com/kuwazzy/pycdatakintonedemo

reference

-CData kintone ODBC Driver Product Manual-Using from Python · Stack overflow-using pyODBC from Lambda

Preparing the Lambda function package

Preparing the CData kintone ODBC Driver

Go to the CData Software web page. The evaluation version and product version of CData kintone ODBC Driver can be downloaded from here. However, as of August 20, 2017, the Linux version is available as a beta version, so please download it from here. See here for the product manual.

Extracting libraries from CData kintone ODBC Driver for Linux builds

Unzip the build file setup.x86_64.deb. When you unzip it, data.tar will be created, so unzip it further. Then, a data directory with the following structure is created.

data/ ├ opt/ ｜　└ cdata/ ｜　　　└ cdata-odbc-driver-for-kintone/ ｜　　　　　├ bin/ ｜　　　　　├ db/ ｜　　　　　├ demos/ ｜　　　　　├ etc/ ｜　　　　　├ help/ ｜　　　　　└ lib/ ｜　　　　　　　├ cdata.odbc.kintone.ini ｜　　　　　　　├ CData.ODBCm.Kintone.DLL ｜　　　　　　　├ libcdatart.x64.so.4 ｜　　　　　　　└ libkintoneodbc.x64.so └ usr/ 　　└ doc/ 　　　　└ cdata-odbc-driver-for-kintone/

Extract 4 files under / data / opt / cdata / cdata-odbc-driver-for-kintone / lib.

Preparation of pyodbc and dependent libraries

Prepare the pyodbc.so library. In my environment, I copied it from /usr/lib64/python2.7/site-packages of RHEL7.3.

In addition, prepare the dependent libraries of pyodbc. In my environment, I copied it from / usr / lib64 under RHEL7.3.

List of added dependent libraries

libodbc.so libodbc.so.2 libodbccr.so libodbccr.so.2 libodbcdrvcfg1S.so libodbcdrvcfg1S.so.2 libodbcdrvcfg2S.so libodbcdrvcfg2S.so.2 libodbcinst.so libodbcinst.so.2 libodbcminiS.so libodbcminiS.so.2 libodbcmyS.so libodbcmyS.so.2 libodbcnnS.so libodbcnnS.so.2 libodbcpsqlS.so libodbcpsqlS.so.2 libodbctxtS.so libodbctxtS.so.2 libomapi.so.0 liboplodbcS.so liboplodbcS.so.2 liboraodbcS.so liboraodbcS.so.2

Python code preparation

Get it from GitHub here.

`pycdatakintonedemo.py`



# -*- coding: utf-8 -*-
import pyodbc
import sys

def Main(event, context):
    print('************************************************')
    print('\t\t Kintone Demo')
    print('This demo uses the CData ODBC for Kintone')
    print('************************************************')
    print('option:1,               - List all the tables in the database')
    print('option:2, table:name    - List all the columns for a specific table')
    print('option:3, table:name    - Select data from table')
    print('option:4, sql:statement - Custom SQL Query')
    print('------------------------------------------------')

    connStr =  'Driver={./cdata/libkintoneodbc.x64.so};' + event['conn_str']
    conn = pyodbc.connect(connStr)

    if event['option'] == '1':
        for table in conn.cursor().tables():
            print(table.table_name)
    elif event['option'] == '2':
        tableName = event['table']
        for column in conn.cursor().columns(tableName):
            print(column.column_name)
    elif event['option'] == '3':
        tableName = event['table']
        c = conn.cursor();
        c.execute('SELECT * FROM ' + tableName)
        for row in c.fetchall():
            print(row)
    elif event['option'] == '4':
        sql = event['sql']
        c = conn.cursor();
        c.execute(sql)
        for row in c.fetchall():
            print(row)
    else:
            print('Invalid option')

    conn.close();

    return {
        'status' : 'finish'
    }

The caveat is that the ODBC library is specified directly instead of the DSN. I referred to the here page.

 connStr =  'Driver={./cdata/libkintoneodbc.x64.so};' + event['conn_str']

Creating a function package

Prepare the files prepared in the above procedure with the following directory structure. . ├ pycdatakintonedemo.py (Python code called from Lambda) ├ pyodbc.so (pyodbc library) ├ cdata / (CData library) ｜　├ cdata.odbc.kintone.ini ｜　├ CData.ODBCm.Kintone.DLL ｜ ├ libcdatart.x64.so (renamed libcdatart.x64.so.4) ｜　└ libkintoneodbc.x64.so └ lib/ └ libodbc * .so etc (pyodbc dependent library group)

Zip this file group. * Please do not include the parent directory

Creating a Lambda function

Creating a function

Click the "Create Function" button on the AWS Lambda dashboard screen. Click the "Create from scratch" button on the blueprint selection screen in step 1. Since it is not set this time on the trigger setting screen in step 2, click the "Next" button. Enter the function name (optional) and description (optional) on the function setting screen in step 3, and select "Python 2.7" as the runtime. In the code for your Lambda function, select the code entry type Upload .ZIP File and upload the ZIP file created in the steps in Creating a Function Package. In the Lambda function handler and role, set the file name (sample: pycdatakintonedemo) excluding the extension of the Python code downloaded from GitHub and the handler name (sample: Main) connected by dots. In the advanced settings, set the timeout time to an appropriate value (example: 1 minute).

After completing the settings so far, click the "Next" button at the bottom, and if there are no problems on the confirmation screen, click the "Create Function" button. Confirm that the function has been created.

Execution of Lambda function

Creating a test event

From Actions, select Set Test Event.

The sample event template remains Hello World and sets the values for the four parameters.

`sample.json`


{
  "conn_str": "Url=https://***.cybozu.com;User=***;Password=***;",
  "option": "1",
  "table": "table_name",
  "sql": "SELECT * FROM table_name"
}

"Conn_str" is the connection string to kintone. At a minimum, you will need three URLs: Url, User, and Password. There are four "options" below. For a few, also specify "table". In case of 4, also specify "sql".

・ Option: 1, --List all the tables in the database' ・ Option: 2, table: name --List all the columns for a specific table' ・ Option: 3, table: name --Select data from table' · Option: 4, sql: statement --Custom SQL Query'

Test run

Now click the "Test" button to run it.

It is OK if "Success" is displayed in the execution result. Click "Details" and click "Click here" in the log output to see the log in CloudWatch. If an error occurs, check the contents of the log.

If you select the log stream and look at the message content, you can see that the list of apps in kintone is output.

Next, open a test event, set "3" (display of table data) in "option", set the application name (example: CDataJapanSKU) existing in kintone in "table", and "save" Click Test.

If you open the log in CloudWatch again, you can see that the data in the kintone app (example: product data in CDataJapanSKU) is displayed.

Summary

It was a procedure of kintone integration by Python from AWS Lambda using CData ODBC Driver. In addition to kintone, the CData ODBC Driver can connect to over 90 Saas, applications and databases. Please download and try various ODBC drivers from the here web page.

Manipulating kintone data with Python & C Data ODBC Driver from AWS Lambda