Tutorial 1: Database

This sections shows several examples how to use database client.

API

Module hydratk.lib.network.dbi.client.

Method DBClient is factory method which requires attribute engine to create proper DBClient object instance. Additional attributes are passed as args, kwargs.

Supported engines:

  • SQLite - module sqlite_client
  • Oracle - module oracle_client
  • MySQL - module mysql_client
  • PostgreSQL - module postgresql_client
  • JDBC - module jdbc_client
  • MSSQL - module mssql_client
  • Redis - module nosql.redis_client
  • MongoDB - module nosql.mongodb_client
  • Cassandra - module nosql.cassandra_client

Methods for relational db:

  • connect: connect to database file (SQLite) or database server (Oracle, MySQL, PostgreSQL, JDBC)

  • disconnect: disconnect from database

  • exec_query: execute query, prepared statements with variable bindings are supported (use ? character)

  • call_proc: call procedure (has no return param) or function (has return param), supported for Oracle, MySQL, PostgreSQL/MSSQL (procedure only)

  • commit: commit transaction

  • rollback: rollback transaction

  • close: stop JVM, supported for JDBC

    Note

    API uses HydraTK core functionalities so it must be running.

SQLite

# import library
from hydratk.lib.network.dbi.client import DBClient

# initialize client
client = DBClient('sqlite')

# connect to database
# returns bool
client.connect(db_file='/home/lynus/hydratk/testenv.db3')

# select records from table
# returns bool, list of rows
res, rows = client.exec_query('SELECT * FROM LOV_STATUS')

for row in rows:
    print row

# insert record to table
client.exec_query('INSERT INTO LOV_STATUS (id, title) VALUES (4, \'pokus\')')

# delete record from table
client.exec_query('DELETE FROM LOV_STATUS WHERE id = 4;')

# insert record using prepared statement with variable binding
client.exec_query('INSERT INTO LOV_STATUS (id, title) VALUES (?, ?)', [4, 'pokus 2'])

# delete record using prepared statement with variable binding
client.exec_query('DELETE FROM LOV_STATUS WHERE id = ?', [4])
res, rows = client.exec_query('SELECT title FROM LOV_STATUS')

# disconnect from database
# returns bool
client.disconnect()

Oracle

Oracle client is not bundled with HydraTK and must be installed individually.

# import library
import hydratk.lib.network.dbi.client as db

# initialize client
client = db.DBClient('oracle')

# connect to database
client.connect(host='localhost', port=49161, sid='XE', user='crm', passw='crm')

# select records from table
# returns bool, list of rows
res, rows = client.exec_query('SELECT * FROM CUSTOMER')

for row in rows:
    print row

# call function
param_names = ['id', 'name', 'status', 'segment', 'birth_no', 'reg_no', 'tax_no', 'err']
input_values = {'name': 'Charlie Bowman', 'status': 'active', 'segment': 2,
                'birth_no': '840809/0009', 'reg_no': '12345', 'tax_no': 'CZ12345'}
output_types = {'id': 'int', 'err': 'string'}
result_type = 'int'

# returns result, output param values dictionary
res, params = client.call_proc('crm.customer_pck.f_create', param_names, input_values, output_types, 'func', result_type)

# call procedure
param_names = ['id', 'name', 'status', 'segment', 'birth_no', 'reg_no', 'tax_no', 'err']
input_values = {'id': id}
output_types = {'name': 'string', 'status': 'string', 'segment': 'int',
                'birth_no': 'string', 'reg_no': 'string', 'tax_no': 'string', 'err': 'string'}

# returns output param values dictionary
params = client.call_proc('crm.customer_pck.p_read', param_names, input_values, output_types, 'proc')

# disconnect from database
# returns bool
client.disconnect()

JDBC

Part of JDBC client library is implemented in Java as a wrapper application which uses Java JDBC API. Python client library uses Java bridge to create Java object instance. Specific Java libraries are needed to access database via JDBC, they are not bundled with hydratk.

After installation do following actions: 1. Check that directory /var/local/hydratk/java was created and contains files: DBClient.java, DBClient.class. 2. Store specific client jar file to same directory (i.e. ojdbc6.jar).

Note

JDBC is not supported for PyPy due to module JPype1.

# import library
import hydratk.lib.network.dbi.client as db

# initialize client
client = db.DBClient('jdbc', True)

# connect to database
client.connect(driver='oracle.jdbc.driver.OracleDriver', conn_str='jdbc:oracle:thin:@localhost:49161/XE', user='crm', passw='crm')

# select records from table
# returns bool, list of rows
res, rows = client.exec_query('SELECT * FROM LOV_STATUS')

for row in rows:
    print row

# insert record to table
client.exec_query('INSERT INTO LOV_STATUS (id, title) VALUES (4, \'pokus\')')

# disconnect from database
# returns bool
client.disconnect()

# stop JVM
client.stop()

MySQL

# import library
import hydratk.lib.network.dbi.client as db

# initialize client
client = db.DBClient('mysql')

# connect to database
client.connect(host='localhost', port=3306, sid='mysql', user='root', passw='root')

# select records from table
# returns bool, list of rows
res, rows = client.exec_query('SELECT * FROM CUSTOMER')

for row in rows:
    print row

# call procedure
param_names = ['id', 'name', 'status', 'segment', 'birth_no', 'reg_no', 'tax_no', 'err']
input_values = {'id': id}
output_types = {'name': 'string', 'status': 'string', 'segment': 'int',
                'birth_no': 'string', 'reg_no': 'string', 'tax_no': 'string', 'err': 'string'}

# returns output param values dictionary
params = client.call_proc('read_customer', param_names, input_values, output_types, 'proc')

# disconnect from database
# returns bool
client.disconnect()

PostgreSQL

# import library
import hydratk.lib.network.dbi.client as db

# initialize client
client = db.DBClient('postgresql')

# connect to database
client.connect(host='localhost', port=5432, sid='postgre', user='root', passw='root')

# select records from table
# returns bool, list of rows
res, rows = client.exec_query('SELECT * FROM CUSTOMER')

for row in rows:
    print row

# call procedure
param_names = ['id', 'name', 'status', 'segment', 'birth_no', 'reg_no', 'tax_no', 'err']
input_values = {'id': id}
output_types = {'name': 'string', 'status': 'string', 'segment': 'int',
                'birth_no': 'string', 'reg_no': 'string', 'tax_no': 'string', 'err': 'string'}

# returns output param values dictionary
params = client.call_proc('read_customer', param_names, input_values, output_types)

# disconnect from database
# returns bool
client.disconnect()

MSSQL

# import library
import hydratk.lib.network.dbi.client as db

# initialize client
client = db.DBClient('mssql')

# connect to database
client.connect(host='10.0.0.1', port=1433, sid='test', user='root', passw='root')

# select records from table
# returns bool, list of rows
res, rows = client.exec_query('SELECT * FROM CUSTOMER')

for row in rows:
    print row

# call procedure
param_names = ['id', 'name', 'status', 'segment', 'birth_no', 'reg_no', 'tax_no', 'err']
input_values = {'id': id}
output_types = {'name': 'string', 'status': 'string', 'segment': 'int',
                'birth_no': 'string', 'reg_no': 'string', 'tax_no': 'string', 'err': 'string'}

# returns output param values dictionary
params = client.call_proc('read_customer', param_names, input_values, output_types)

# disconnect from database
# returns bool
client.disconnect()

Redis

# import library
import hydratk.lib.network.dbi.client as db

# initialize client
client = db.DBClient('redis')

# connect to database
client.connect(host='127.0.0.1', port=6379, db=0)

# set key, returns bool
res = client.set(key, value)

# get key, returns str
res = client.get(key, value)

# check if key exists, returns bool
res = client.exists(key)

# delete key, returns bool
res = client.delete(key)

# execute command
# returns bool, output
res, output = client.exec_command('INCR key')

MongoDB

# import library
import hydratk.lib.network.dbi.client as db

# initialize client
client = db.DBClient('mongodb')

# connect to database
client.connect(host='127.0.0.1', port=27017, db='test')

# insert record to database
# returns bool, id
doc = {"customer": {"name": "Charlie Bowman", "status": "active", "segment": 2,
                    "payer": {"name": "Charlie Bowman", "status": "active"},
                    "services": [{"id": 615, "status": "active"}, {"id": 619, "status": "suspend"}]}}
res, id = client.exec_command('insert', collection='test', document=doc)

# find record
# returns bool, rows
res, rows = client.exec_command('find', collection='test', filter={'_id': id})

# find records using complex filter
filter = {"$or": [{"customer.name": "Charlie Bowman"}, {"customer.name": "Vince Neil"}]}
res, rows = client.exec_command('find', collection='test', filter=filter)

# aggregate records
# returns bool, rows
filter = [{"$match": {"customer.payer.status": "active"}},
          {"$group": {"_id": "$customer.name", "count": {"$sum": 1}}}]
res, rows = c.exec_command('aggregate', 'test', filter=filter)

# update multiple records
# returns bool, modified count
doc, filter = {"$set": {"customer.name": "Vince Neil 2"}}, {"customer.name": "Vince Neil"}
res, count = client.exec_command('update', 'test', document=doc, filter=filter, single=False)

# replace record
# returns bool, modified count
doc, filter = {"customer": {"name": "Vince Neil"}}, {"customer.name": "Vince Neil 2"}
res, count = client.exec_command('replace', 'test', doc, filter)

# delete record
# returns bool, deleted count
res, count = client.exec_command('delete', 'test', filter={"customer.name": "Vince Neil"})

# drop collection
# returns bool, None
res, out = client.exec_command('drop', collection='test')

# disconnect from database
# returns bool
client.disconnect()

Cassandra

# import library
from hydratk.lib.network.dbi.client import DBClient

# initialize client
client = DBClient('cassandra')

# connect to database
# returns bool
client.connect(host='127.0.0.1', port=9042, key_space='test')

# select records from table
# returns bool, list of rows
res, rows = client.exec_query('SELECT * FROM LOV_STATUS')

for row in rows:
    print row

# insert record to table
client.exec_query('INSERT INTO LOV_STATUS (id, title) VALUES (4, \'pokus\')')

# delete record from table
client.exec_query('DELETE FROM LOV_STATUS WHERE id = 4;')

# insert record using prepared statement with variable binding
client.exec_query('INSERT INTO LOV_STATUS (id, title) VALUES (?, ?)', [4, 'pokus 2'])

# delete record using prepared statement with variable binding
client.exec_query('DELETE FROM LOV_STATUS WHERE id = ?', [4])
res, rows = client.exec_query('SELECT title FROM LOV_STATUS')

# disconnect from database
# returns bool
client.disconnect()