Code Example

The previous article introduced how to create a table, insert and query data. All operations are performed using psql in the command line terminal. In the actual development process, developers mostly use programming languages to connect databases and perform DML and DDL operations. This section will introduce how to connect to databases and query using Java, Python, Golang, and C.

MatrixDB is compatible with PostgreSQL in communication protocol, so you can connect in the same way as PostgreSQL.

1. JAVA

Connecting to MatrixDB with Java, you must first download the JDBC package, https://jdbc.postgresql.org/download.html.

The sample code is as follows:

package main;
import java.sql.*;

public class Main {
    public static void main(String []args) {
        Connection connection=null;
        Statement statement =null;
        try{
            String url="jdbc:postgresql://127.0.0.1:5432/mxadmin";
            String user="mxadmin";
            String password = "abcd";
            Class.forName("org.postgresql.Driver");
            connection= DriverManager.getConnection(url, user, password);

            // Create a table
            String sql="CREATE TABLE data ("
                    + "time timestamp,"
                    + "tag_id int,"
                    + "metrics1 float8,"
                    + "metrics2 float8,"
                    + "metrics3 float8"
                    + ")Distributed by(tag_id)";
            statement=connection.createStatement();
            statement.execute(sql);

            // Insert data
            sql = "INSERT INTO data VALUES(now(), 1, 1.1, 1.2, 1.3)";
            statement.execute(sql);

            // Query results
            sql = "SELECT * FROM data";
            ResultSet resultSet=statement.executeQuery(sql);
            while(resultSet.next()){
                String ts=resultSet.getString(1);
                int tag_id = resultSet.getInt(2);
                double metrics1 = resultSet.getDouble(3);
                double metrics2 = resultSet.getDouble(4);
                double metrics3 = resultSet.getDouble(5);
                System.out.printf("%s %d %f %f %f\n", ts, tag_id, metrics1, metrics2, metrics3);
            }
        }catch(Exception e){
            throw new RuntimeException(e);
        }finally{
            try{
                statement.close();
            }
            catch(SQLException e){
                e.printStackTrace();
                throw new RuntimeException(e);
            }finally{
                try{
                    connection.close();
                }
                catch(SQLException e){
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
        }
    }

}

2. Python

Use python to connect to MatrixDB, first install psycopg2:

pip3 install psycopg2

The sample code is as follows:

# -*- coding: utf-8 -*-
import psycopg2

class MatrixDB(object):
    def __init__(self):
        self.host = "127.0.0.1"
        self.user = "mxadmin"
        self.database = "mxadmin"
        self.port = "5432"
        self.password = "abcd"

    def get_conn(self):
        conn = psycopg2.connect(database=self.database,
                                user=self.user,
                                password=self.password,
                                host=self.host,
                                port=self.port)
        return conn

    def create_table(self):
        conn = self.get_conn()
        cursor = conn.cursor()
        sql = "CREATE TABLE data(" \
                "time timestamp," \
                "tag_id int," \
                "metrics1 float8," \
                "metrics2 float8," \
                "metrics3 float8" \
                ")Distributed by(tag_id)"
        cursor.execute(sql)
        conn.commit()
        conn.close()

    def insert(self):
        conn = self.get_conn()
        cursor = conn.cursor()
        sql = "INSERT INTO data VALUES(now(), 1, 1.1, 1.2, 1.3)"
        cursor.execute(sql)
        conn.commit()
        conn.close()

    def select(self):
        conn = self.get_conn()
        cursor = conn.cursor()
        sql = "SELECT * FROM data"
        cursor.execute(sql)
        data = cursor.fetchone()
        conn.commit()
        conn.close()
        return data

if __name__ == '__main__':
    mxdb = MatrixDB()
    mxdb.create_table()
    mxdb.insert()
    print (mxdb.select())

3. Golang

Golang connection MatrixDB dependency github.com/lib/pq

The sample code is as follows:

package main

import (
    "database/sql"
    "fmt"
    "time"

    _ "github.com/lib/pq"
)

type Data struct {
    Time time.Time
    TagId int32
    Metrics1 float64
    Metrics2 float64
    Metrics3 float64
}

func getConn() (*sql.DB, error) {
    psqlInfo := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", "127.0.0.1", 5432, "mxadmin", "", "mxadmin")

    db, err := sql.Open("postgres", psqlInfo)
    if err != nil {
        return nil, err
    }

    return db, nil
}

func createTable() error {
    db, _ := getConn()
    defer db.Close()

    _, err := db.Exec(`
CREATE TABLE data(
    time timestamp,
    tag_id int,
    metrics1 float8,
    metrics2 float8,
    metrics3 float8
)Distributed by(tag_id)
`)
    return err
}

func insertData() error {
    db, _ := getConn()
    defer db.Close()

    _, err := db.Exec(`INSERT INTO data VALUES(now(), 1, 1.1, 1.2, 1.3)`)
    return err
}

func selectData() ([]Data, error) {
    db, _ := getConn()
    defer db.Close()

    datas := make([]Data, 0)
    rows, err := db.Query(`SELECT * FROM data`)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    for rows.Next() {
        data := Data{}
        err = rows.Scan(&data.Time, &data.TagId, &data.Metrics1, &data.Metrics2, &data.Metrics3)
        if err != nil {
            return nil, err
        }
        datas = append(datas, data)
    }

    return datas, err
}

func main() {
    createTable()
    insertData()
    datas, _ := selectData()
    fmt.Println(datas)
}

4. C

Connecting MatrixDB in C language requires relying on the libpq library, which is installed with MatrixDB and is in the $GPHOME directory. When compiling, specify the header file path and library path:

gcc -I $GPHOME/include -L $GPHOME/lib -lpq main.c -o main

The sample code is as follows:

#include <stdio.h>
#include "libpq-fe.h"

void PQresultPrint(PGresult *res)
{
    int nFields = PQnfields(res);
    int nTuples = PQntuples(res);
    int i, j;
    for (i=0; i<nTuples; i++)
    {
        for (j=0; j<nFields; j++)
        {
            printf("%s ", PQgetvalue(res, i, j));
        }
        printf("\n");
    }
}

PGconn *GetConn()
{
    PGconn *conn;
    char str[128];

    sprintf(str, "host=%s port=%d dbname=%s user=%s password=%s",
            "127.0.0.1",
            5432,
            "mxadmin",
            "mxadmin",
            "abcd"
            );

    // Establish a connection
    conn = PQconnectdb(str);
    if(PQstatus(conn) == CONNECTION_BAD)
    {
        fprintf(stderr,"数据库连接失败!");
        fprintf(stderr,"%s",PQerrorMessage(conn));
        return NULL;
    }
    return conn;
}

PGresult *ExecuteQuery(const char *query)
{

    PGconn *conn;
    PGresult *res;

    conn = GetConn();
    if (conn == NULL)
        return NULL;

    // Execute SQL
    res = PQexec(conn, query);
    if (PQresultStatus(res) == PGRES_FATAL_ERROR)
    {
        fprintf(stderr, "%s", PQerrorMessage(conn));
        return NULL;
    }

    PQfinish(conn);
    return res;
}

PGresult *CreateTable()
{
    const char *sql =
"CREATE TABLE data \
( \
    time timestamp, \
    tag_id int, \
    metrics1 float8, \
    metrics2 float8, \
    metrics3 float8 \
)Distributed by(tag_id)";
    return ExecuteQuery(sql);
}

PGresult *InsertData()
{
    const char *sql = "INSERT INTO data VALUES(now(), 1, 1.1, 1.2, 1.3)";
    return ExecuteQuery(sql);
}

PGresult *SelectData()
{
    const char *sql = "SELECT * FROM data";
    return ExecuteQuery(sql);
}

int main()
{
    PGresult *res;
    res = CreateTable();
    if (res != NULL)
        PQclear(res);
    res = InsertData();
    if (res != NULL)
        PQclear(res);
    res = SelectData();
    if (res != NULL) {
        PQresultPrint(res);
        PQclear(res);
    }
    return 0;
}