Working With Database - minersoft/miner GitHub Wiki

Miner is able to fetch and push data from/to database. Currently sqlite and mysql are supported.

If to access mysql database you need to install mysql tool. To do it type:

UPDATE
INSTALL mysql

First connection to database should be established.

DB CONNECT <uri> as <connection-name>

is valid identifier which will represent connection

identifies database to connect to

For sqlite, uri is:

• Path to database file, should end with .db or .sqlite
• or should be file.sqlite:///c:/path/to/database/file, on linux just file.sqlite:///path/to/database/file

For mysql, uri is of following format mysql://[username[:password]@]hostname[:port]/[database] Parameters in [] are optional in URI although if they are not specified, user will be prompted to enter them explicitly.

After successful connection you can list all tables in the database

DB con TABLES

After that you are ready to fetch data

DB con FETCH "SELECT i, j FROM test LIMIT 100" | WRITE test.csv

Note, Use LIMIT functionality of database and not miner where possible since miner may not be able to abort result fetching from database.

Parameters can be used while running queries. In the case of sqlite parameters are specifie using '?'

DB con FETCH "SELECT i, j FROM test WHERE i BETWEEN ? AND ? LIMIT 100" WITH 0, 50 | ... 

In mysql you will specify parameters using '%s':

DB con FETCH "SELECT i, j FROM test WHERE i BETWEEN %s AND %s LIMIT 100" WITH 0, 50 | ... 

or using dictionary of parameters

DB con FETCH "SELECT i, j FROM test WHERE i BETWEEN %(from)s AND %(to)s" WITH {'from':0, 'to':50} | ... 

Actually the query itself and parameters are python expressions so you can do

SET queryStr = "SELECT i, j FROM test WHERE i BETWEEN %(from)s AND %(to)s"
SET paramsDict = {'from':0, 'to':50}
DB con FETCH queryStr WITH paramsDict | ... 

To insert parameters to database you would do something like that (in case of mysql):

ITERATE a in xrange(1000) | DB con PUSH 'insert into test(i,j) values(%s,%s)' WITH 2*a, 3*a

In this query 1000 entries will be inserted into the database, where first query parameter is defined to be current value of 2*a and second parameter is defined to be current value of 3*a.

Note, multiple insert query is usually optimized by underlying driver, thus it will be not 1000 insert requests.

When you're done with connection you close it, by executing

DB con CLOSE