Python Module

Machbase provides Python modules. By installing Python modules, its classes are provided to use CLI method in order to communicate with the Machbase server. Thus, users can easily run various queries against the Machbase server for input and delete values, and create and drop tables.

Table of Contents

Sample Test

A few sample codes are provided in sample directory and you can check how it works.

File Description

File Name Description
Makefile compile file.
MakeData.py create a data file(data.txt) for Sample3Append.py sample.
Sample1Connect.py connect with Machbase.
Sample2Simple.py direct insert data and retrieve data from a table.
Sample3Append.py read a data file and insert with high speed input protocol.
_init_.py run python modules independently.

Go to python sample directory, compile sample files and run each sample.
For more information about this sample, please see the application development section.

# go to python sample directory.
[mach@localhost]$ cd $MACHBASE_HOME/sample/python

# compile sample files.
[mach@localhost]$ make

# run Sample1Connect.py
[mach@localhost]$ make run_sample1

# run Sample2Simple.py
[mach@localhost]$ make run_sample2

# run Sample3Append.py
[mach@localhost]$ make run_sample3

Environment Setting

In order to use Python, environment setup and library files are required. First, you need to check whether "$MACHBASE_HOME/lib" folder is registered in the "$LD_LIBRARY_PATH". It is required to have "libmachbasecli_dll.so" files in the library folder since you need to access the Machbase server by using CLI. Machbase provides Python 2.7 in the package and if users use this python, don't need to install python module. if users use another python, need to install python module. Unzip "machbaseAPI-1.0.tar.gz" file that can be found in the "$MACHBASE_HOME/3rd-party/python-module" folder. Then, install Python modules by running "python setup.py install" file. Recommended version is "Python 2.7".

Now, you are ready to use the Machbase python modules.

Creating a Class

To use "Machbase Python" modules, you need to declare the class. The name of the class is "machbase".

from machbaseAPI import machbase

Connecting and Disconnecting

machbaseDB.open(aHost, aUser, aPw, aPort)

This function allows a user to connect the Machbase server. If proper parameter values are entered, it returns whether the connection is successful or failed. If it success, it returns 1. If it fails, it returns 0.

machbaseDB.close()

It is used for disconnecting the Machbase server. If it returns 1, it successfully disconnected the server. If it fails, it returns 0.

machbaseDB.isConnected()

It checks whether the declared class is currently connected to the server. If it is connected, it returns 1. If not, it returns 0.

Extended Functions

machbaseDB.execute(aSql)

When accessing a server, it will send SQL query against the server. If it is successful, it returns 1. If it fails or an error occurred, it returns 0. A user can use all the commands except "UPDATE" that the Machbase doesn't support.

machbaseDB.append(aTableName, aTypes, aValues, aFormat)

It allows a user to use append protocol which is supported by the Machbase. Prerequisite actions are required for users to use append protocol. First, you need to specify the name of tables to enter data and a dictionary for a type of columns. And all the input values should be in JSON format and specify DateFormat as well. If it successes, it returns 1. If not, it returns 0.

Table 1. Data type values of Machbase

Name of Type Value
short 4
ushort 104
integer 8
uinteger 108
long 12
ulong 112
float 16
double 20
datetime 6
varchar 5
ipv4 32
ipv6 36
text 49
binary 97

machbaseDB.tables()

It returns the information regarding all the tables in the connected server. It returns 1 on success. If it fails, it returns 0.

machbaseDB.columns(aTableName)

It returns the information of columns within the table which is located in the connected server. It returns 1 on success. If it fails, it returns 0.

Check Result

All the results will be returned in JSON format in the Machbase Python modules. JSON format was selected as it returns manageable form that can be easily utilized in various environments.

machbaseDB.result()

All the functions described above are only returning success or fail rather than returning return values. To get return values, run machbaseDB.result() function.

Examples

Let's find out how to use Machbase Python modules through examples as shown below. You can use files in "$MACHBASE_HOME/sample/python". In this folder, you can find "Makefile" which provides simple and tests, and "MakData.py" file to create data. You need to specify the values of PYPATH of Makefile to Python where Machbase Python modules are installed. Then, it will work properly. The default value is set to Python installed in the Machbase. "init.py" file is required to execute Python modules independently. Thus, you need to check whether the file exists in the folder.

[mach@localhost]$ cd $MACHBASE_HOME/sample/python
[mach@localhost python]$ ls -l
total 20
-rw-rw-r-- 1 mach mach    0 Oct  7 14:37 __init__.py
-rw-rw-r-- 1 mach mach  764 Oct  7 14:37 MakeData.py
-rw-rw-r-- 1 mach mach  593 Oct  7 14:58 Makefile
-rw-rw-r-- 1 mach mach  664 Oct  7 14:37 Sample1Connect.py
-rw-rw-r-- 1 mach mach 2401 Oct  7 14:37 Sample2Simple.py
-rw-rw-r-- 1 mach mach 1997 Oct  7 14:37 Sample3Append.py
[mach@localhost python]$

Connect

The following shows how to return result values after executing queries against the server. Each function will return 0 when an error occurred. If it works successfully, it returns the number of m$tables tables. The name of the file is "Sample1Connect.py".

from machbaseAPI import machbase
def connect():
    db = machbase()
    if db.open('127.0.0.1','SYS','MANAGER',5656) is 0 :
        return db.result()
    if db.execute('select count(*) from m$tables') is 0 :
        return db.result()
    result = db.result()
    if db.close() is 0 :
        return db.result()
    return result
if __name__=="__main__":
    print connect()
[mach@localhost python]$ make run_sample1
/home/machbase/machbase_home/webadmin/flask/Python/bin/python Sample1Connect.py
{"count(*)":"13"}
[mach@localhost python]$

Simple

The following shows how to create a table, input values, and extract stored values by using Python against the Machbase. The name of the file is "Sample2Simple.py".

import re
import json
from machbaseAPI import machbase
def insert():
    db = machbase()
    if db.open('127.0.0.1','SYS','MANAGER',5656) is 0 :
        return db.result()
    db.execute('drop table sample_table')
    db.result()
    if db.execute('create table sample_table(d1 short, d2 integer, d3 long, f1 float, f2 double, name varchar(20), text text, bin binary, v4 ipv4, v6 ipv6, dt datetime)') is 0:
        return db.result()
    db.result()
    for i in range(1,10):
        sql = "INSERT INTO SAMPLE_TABLE VALUES ("
        sql += str((i - 5) * 6552) #short
        sql += ","+ str((i - 5) * 42949672) #integer
        sql += ","+ str((i - 5) * 92233720368547758L) #long
        sql += ","+ "1.234"+str((i-5)*7) #float
        sql += ","+ "1.234"+str((i-5)*61) #double
        sql += ",'id-"+str(i)+"'" #varchar
        sql += ",'name-"+str(i)+"'" #text
        sql += ",'aabbccddeeff'" #binary
        sql += ",'192.168.0."+str(i)+"'" #ipv4
        sql += ",'::192.168.0."+str(i)+"'" #ipv6
        sql += ",TO_DATE('2015-08-0"+str(i)+"','YYYY-MM-DD')" #date
        sql += ")";
        if db.execute(sql) is 0 :
            return db.result()
        else:
            print db.result()
        print str(i)+" record inserted."
    query = "SELECT d1, d2, d3, f1, f2, name, text, bin, to_hex(bin), v4, v6, to_char(dt,'YYYY-MM-DD') as dt from SAMPLE_TABLE";
    if db.execute(query) is 0 :
        return db.result()
    result = db.result()
    for item in re.findall('{[^}]+}',result):
        res = json.loads(item)
        print "d1 : "+res.get('d1')
        print "d2 : "+res.get('d2')
        print "d3 : "+res.get('d3')
        print "f1 : "+res.get('f1')
        print "f2 : "+res.get('f2')
        print "name : "+res.get('name')
        print "text : "+res.get('text')
        print "bin : "+res.get('bin')
        print "to_hex(bin) : "+res.get('to_hex(bin)')
        print "v4 : "+res.get('v4')
        print "v6 : "+res.get('v6')
        print "dt : "+res.get('dt')
    if db.close() is 0 :
        return db.result()
    return result
if __name__=="__main__":
    print insert()
[mach@loclahost python]$ make run_sample2
/home/machbase/machbase_home/webadmin/flask/Python/bin/python Sample2Simple.py
{"EXECUTE RESULT":"Execute Success"}
1 record inserted.
{"EXECUTE RESULT":"Execute Success"}
2 record inserted.
{"EXECUTE RESULT":"Execute Success"}
3 record inserted.
{"EXECUTE RESULT":"Execute Success"}
4 record inserted.
{"EXECUTE RESULT":"Execute Success"}
5 record inserted.
{"EXECUTE RESULT":"Execute Success"}
6 record inserted.
{"EXECUTE RESULT":"Execute Success"}
7 record inserted.
{"EXECUTE RESULT":"Execute Success"}
8 record inserted.
{"EXECUTE RESULT":"Execute Success"}
9 record inserted.
d1 : 26208
d2 : 171798688
d3 : 368934881474191032
f1 : 1.23428
f2 : 1.23424
name : id-9
text : name-9
bin : 616162626363646465656666
to_hex(bin) : 616162626363646465656666
v4 : 192.168.0.9
v6 : ::192.168.0.9
...

Append

In the Machbase, the append method which provides high-speed data input can be used on Python modules as well.
Two connecting classes "db" and "db2" were used as shown below. "db" is for column information and initialization, and "db2" is for utilizing each function. The name of the file is "Sample3Append.py".

import re
import json
from machbaseAPI import machbase
def append():
#init,columns start
    db = machbase()
    if db.open('127.0.0.1','SYS','MANAGER',5656) is 0 :
        return db.result()
    db.execute('drop table sample_table')
    db.result()
    if db.execute('create table sample_table(d1 short, d2 integer, d3 long, f1 float, f2 double, name varchar(20), text text, bin binary, v4 ipv4, v6 ipv6, dt datetime)') is 0:
        return db.result()
    db.result()
    tableName = 'sample_table'
    db.columns(tableName)
    result = db.result()
    if db.close() is 0 :
        return db.result()
#init, colums end
#append start
    db2 = machbase()
    if db2.open('127.0.0.1','SYS','MANAGER',5656) is 0 :
        return db2.result()
    types = []
    for item in re.findall('{[^}]+}',result):
        types.append(json.loads(item).get('type'))
    values = []
    with open('data.txt','r') as f:
        for line in f.readlines():
            v = []
            i = 0
            for l in line[:-1].split(','):
                t = int(types[i])
                if t == 4 or t == 8 or t == 12 or t == 104 or t == 108 or t == 112:
                    #short   integer    long       ushort      uinteger     ulong
                    v.append(int(l))
                elif t == 16 or t == 20:
                    #float      double
                    v.append(float(l))
                else:
                    v.append(l)
                i+=1
            values.append(v)
    db2.append(tableName, types, values, 'YYYY-MM-DD HH24:MI:SS')
    result = db2.result()
    if db2.close() is 0 :
        return db2.result()
#append end
    return result
if __name__=="__main__":
    print append()
[mach@localhost python]$ make run_sample3
/home/machbase/machbase_home/webadmin/flask/Python/bin/python Sample3Append.py
{"EXECUTE RESULT":"Append success"}

[mach@localhost python]$

results matching ""

    No results matching ""