TBase 應用接入指南

上一篇:TBase Quick Start (請點擊文章底部“閱讀原文”查看)

介紹了TBase的架構,源碼的編譯安裝,集群運行狀態、啟動停止等。本篇將介紹應用程序如何連接TBase數據庫進行建庫、建表、數據導入、查詢等操作。

TBase兼容所有支持Postgres協議的客戶端連接,這里將詳細介紹JAVA、C語言、shell語言、Python、PHP、Golang 這6種最常用的開發語言連接TBase的操作方法。

1、JAVA開發

1.1、創建數據表

import java.sql.Connection;import java.sql.DriverManager;import java.sql.Statement;  public class createtable {   public static void main( String args[] )     {       Connection c = null;       Statement stmt = null;       try {         Class.forName("org.postgresql.Driver");         c = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:15432/postgres?currentSchema=public&binaryTransfer=false","tbase", "tbase");         System.out.println("Opened database successfully");         stmt = c.createStatement();         String sql = "create table tbase(id int,nickname text) distribute by shard(id) to group  default_group" ;         stmt.executeUpdate(sql);         stmt.close();         c.close();       } catch ( Exception e ) {         System.err.println( e.getClass().getName()+": "+ e.getMessage() );         System.exit(0);       }       System.out.println("Table created successfully");     }}

說明:

• 這里連接的節點為任意CN主節點,后面所有操作,沒特別說明,都是連接到CN主節點進行操作。

1.2、使用普通協議插入數據

import java.sql.Connection;import java.sql.DriverManager;import java.sql.Statement; public class insert {   public static void main(String args[]) {      Connection c = null;      Statement stmt = null;      try {         Class.forName("org.postgresql.Driver");         c = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:15432/postgres?currentSchema=public&binaryTransfer=false","tbase", "tbase");         c.setAutoCommit(false);         System.out.println("Opened database successfully");          stmt = c.createStatement();         String sql = "insert INTO tbase (id,nickname) "               + "VALUES (1,'tbase');";         stmt.executeUpdate(sql);          sql = "insert INTO tbase (id,nickname) "               + "VALUES (2, 'pgxz' ),(3,'pgxc');";         stmt.executeUpdate(sql);         stmt.close();         c.commit();         c.close();      } catch (Exception e) {         System.err.println( e.getClass().getName()+": "+ e.getMessage() );         System.exit(0);      }      System.out.println("Records created successfully");   }}

1.3、使用擴展協議插入數據

import java.sql.Connection;import java.sql.DriverManager;import java.sql.*;import java.util.Random; public class insert_prepared {   public static void main(String args[]) {      Connection c = null;      PreparedStatement stmt;      try {         Class.forName("org.postgresql.Driver");         c = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:15432/postgres?currentSchema=public&binaryTransfer=false","tbase", "tbase");         c.setAutoCommit(false);         System.out.println("Opened database successfully");         //插入數據         String sql = "insert INTO tbase (id,nickname) VALUES (?,?)";         stmt = c.prepareStatement(sql);         stmt.setInt(1, 9999);         stmt.setString(2, "tbase_prepared");         stmt.executeUpdate();                  //插入更新         sql = "insert INTO tbase (id,nickname) VALUES (?,?) ON CONFLICT(id) DO update SET nickname=?";         stmt = c.prepareStatement(sql);         stmt.setInt(1, 9999);         stmt.setString(2, "tbase_prepared");         stmt.setString(3, "tbase_prepared_update");         stmt.executeUpdate();                 stmt.close();         c.commit();         c.close();      } catch (Exception e) {         System.err.println( e.getClass().getName()+": "+ e.getMessage() );         System.exit(0);      }      System.out.println("Records created successfully");   }}

1.4、copy from 加載文件到表

import java.sql.Connection;import java.sql.DriverManager;import java.sql.Statement;import org.postgresql.copy.CopyManager;import org.postgresql.core.BaseConnection;import java.io.*; public class copyfrom {   public static void main( String args[] )     {       Connection c = null;       Statement stmt = null;       FileInputStream fs = null;       try {         Class.forName("org.postgresql.Driver");         c = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:15432/postgres?currentSchema=public&binaryTransfer=false","tbase", "tbase");         System.out.println("Opened database successfully");         CopyManager cm = new CopyManager((BaseConnection) c);         fs = new FileInputStream("/data/tbase/tbase.csv");         String sql = "COPY tbase FROM STDIN delimiter AS ','";         cm.copyIn(sql, fs);         c.close();         fs.close();       } catch ( Exception e ) {         System.err.println( e.getClass().getName()+": "+ e.getMessage() );         System.exit(0);       }       System.out.println("Copy data successfully");     }}

1.5、copy to 導出數據到文件

import java.sql.Connection;import java.sql.DriverManager;import java.sql.Statement;import org.postgresql.copy.CopyManager;import org.postgresql.core.BaseConnection;import java.io.*; public class copyto {   public static void main( String args[] )     {       Connection c = null;       Statement stmt = null;       FileOutputStream fs = null;       try {         Class.forName("org.postgresql.Driver");         c = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:15432/postgres?currentSchema=public&binaryTransfer=false","tbase", "tbase");         System.out.println("Opened database successfully");         CopyManager cm = new CopyManager((BaseConnection) c);         fs = new FileOutputStream("/data/tbase/tbase.csv");         String sql = "COPY tbase TO STDOUT delimiter AS ','";         cm.copyOut(sql, fs);         c.close();         fs.close();       } catch ( Exception e ) {         System.err.println( e.getClass().getName()+": "+ e.getMessage() );         System.exit(0);       }       System.out.println("Copy data successfully");     }}

1.6、jdbc包下載地址

https://jdbc.postgresql.org/download.html

2、C程序開發

2.1、連接數據庫

#include <stdio.h>#include <stdlib.h>#include "libpq-fe.h"intmain(int argc, char **argv){    const char *conninfo;    PGconn     *conn;    if (argc > 1){        conninfo = argv[1];    }else{        conninfo = "dbname = postgres";    }    conn = PQconnectdb(conninfo);    if (PQstatus(conn) != CONNECTION_OK){        fprintf(stderr, "連接數據庫失敗: %s",PQerrorMessage(conn));    }else{        printf("連接數據庫成功!n");    }    PQfinish(conn);    return 0;}

編譯

gcc -c -I /usr/local/install/tbase_pgxz/include/ conn.cgcc -o conn conn.o -L /usr/local/install/tbase_pgxz/lib/ -lpq

運行

./conn "host=172.16.0.3 dbname=postgres port=11000"連接數據庫成功!

./conn "host=172.16.0.3 dbname=postgres port=15432 user=tbase"連接數據庫成功!

2.2、建立數據表

#include <stdio.h>#include <stdlib.h>#include "libpq-fe.h"intmain(int argc, char **argv){    const char *conninfo;    PGconn     *conn;    PGresult   *res;    const char *sql = "create table tbase(id int,nickname text) distribute by shard(id) to group  default_group";    if (argc > 1){        conninfo = argv[1];    }else{        conninfo = "dbname = postgres";    }    conn = PQconnectdb(conninfo);    if (PQstatus(conn) != CONNECTION_OK){        fprintf(stderr, "連接數據庫失敗: %s",PQerrorMessage(conn));    }else{        printf("連接數據庫成功!n");    }    res = PQexec(conn,sql);    if(PQresultStatus(res) != PGRES_COMMAND_OK){        fprintf(stderr, "建立數據表失敗: %s",PQresultErrorMessage(res));    }else{        printf("建立數據表成功!n");    }    PQclear(res);    PQfinish(conn);    return 0;}

編譯

gcc -c -I /usr/local/install/tbase_pgxz/include/ createtable.cgcc -o createtable createtable.o -L /usr/local/install/tbase_pgxz/lib/ -lpq

運行

./createtable "port=11000 dbname=postgres"連接數據庫成功!建立數據表成功!

2.3、插入數據

#include <stdio.h>#include <stdlib.h>#include "libpq-fe.h"intmain(int argc, char **argv){    const char *conninfo;    PGconn     *conn;    PGresult   *res;    const char *sql = "insert INTO tbase (id,nickname) values(1,'tbase'),(2,'pgxz')";    if (argc > 1){        conninfo = argv[1];    }else{        conninfo = "dbname = postgres";    }    conn = PQconnectdb(conninfo);    if (PQstatus(conn) != CONNECTION_OK){        fprintf(stderr, "連接數據庫失敗: %s",PQerrorMessage(conn));    }else{        printf("連接數據庫成功!n");    }    res = PQexec(conn,sql);    if(PQresultStatus(res) != PGRES_COMMAND_OK){        fprintf(stderr, "插入數據失敗: %s",PQresultErrorMessage(res));    }else{        printf("插入數據成功!n");    }    PQclear(res);    PQfinish(conn);    return 0;}

編譯

gcc -c -I /usr/local/install/tbase_pgxz/include/ insert.cgcc -o insert insert.o -L /usr/local/install/tbase_pgxz/lib/ -lpq

運行

./insert "dbname=postgres port=15432"

2.4、查詢數據

#include <stdio.h>#include <stdlib.h>#include "libpq-fe.h"intmain(int argc, char **argv){    const char *conninfo;    PGconn     *conn;    PGresult   *res;    const char *sql = "select * from tbase";    if (argc > 1){        conninfo = argv[1];    }else{        conninfo = "dbname = postgres";    }    conn = PQconnectdb(conninfo);    if (PQstatus(conn) != CONNECTION_OK){        fprintf(stderr, "連接數據庫失敗: %s",PQerrorMessage(conn));    }else{        printf("連接數據庫成功!n");    }    res = PQexec(conn,sql);    if(PQresultStatus(res) != PGRES_TUPLES_OK){        fprintf(stderr, "插入數據失敗: %s",PQresultErrorMessage(res));    }else{        printf("查詢數據成功!n");        int rownum = PQntuples(res) ;        int colnum = PQnfields(res);        for(int j = 0;j< colnum; ++j){            printf("%st",PQfname(res,j));        }        printf("n");        for(int i = 0;i< rownum; ++i){            for(int j = 0;j< colnum; ++j){                printf("%st",PQgetvalue(res,i,j));            }            printf("n");        }    }    PQclear(res);    PQfinish(conn);    return 0;}

編譯

gcc -std=c99 -c -I /usr/local/install/tbase_pgxz/include/ select.cgcc -o select select.o -L /usr/local/install/tbase_pgxz/lib/ -lpq

運行

./select "dbname=postgres port=15432"連接數據庫成功!查詢數據成功!id      nickname1       tbase2       pgxz

2.5、流數據COPY入表

#include <string.h>#include <stdio.h>#include <stdlib.h>#include "libpq-fe.h"intmain(int argc, char **argv){    const char *conninfo;    PGconn     *conn;    PGresult   *res;    const char *buffer = "1,tbasen2,pgxzn3,Tbase牛";    if (argc > 1){        conninfo = argv[1];    }else{        conninfo = "dbname = postgres";    }    conn = PQconnectdb(conninfo);    if (PQstatus(conn) != CONNECTION_OK){        fprintf(stderr, "連接數據庫失敗: %s",PQerrorMessage(conn));    }else{        printf("連接數據庫成功!n");    }    res=PQexec(conn,"COPY tbase FROM STDIN delimiter ',';");    if(PQresultStatus(res) != PGRES_COPY_IN){        fprintf(stderr, "copy數據出錯1: %s",PQresultErrorMessage(res));    }else{        int len = strlen(buffer);        if(PQputCopyData(conn,buffer,len) == 1){             if(PQputCopyEnd(conn,NULL) == 1){                res = PQgetResult(conn);                if(PQresultStatus(res) == PGRES_COMMAND_OK){                    printf("copy數據成功!n");                }else{                    fprintf(stderr, "copy數據出錯2: %s",PQerrorMessage(conn));                }             }else{                fprintf(stderr, "copy數據出錯3: %s",PQerrorMessage(conn));             }        }else{            fprintf(stderr, "copy數據出錯4: %s",PQerrorMessage(conn));        }    }    PQclear(res);    PQfinish(conn);    return 0;}

編譯

gcc -c -I /usr/local/install/tbase_pgxz/include/ copy.cgcc -o copy copy.o -L /usr/local/install/tbase_pgxz/lib/ -lpq

執行

./copy "dbname=postgres port=15432"連接數據庫成功!copy數據成功!

3、shell腳本開發

#!/bin/sh if [ $# -ne 0 ]then    echo "usage: $0 exec_sql"    exit 1fi exec_sql=$1 masters=`psql -h 172.16.0.29 -d postgres -p 15432 -t -c "select string_agg(node_host, ' ') from (select * from pgxc_node where node_type = 'D' order by node_name) t"`port_list=`psql -h 172.16.0.29 -d postgres -p 15432 -t -c "select string_agg(node_port::text, ' ') from (select * from pgxc_node where node_type = 'D' order by node_name) t"`node_cnt=`psql -h 172.16.0.29 -d postgres -p 15432 -t -c "select count(*) from pgxc_node where node_type = 'D'"`masters=($masters)ports=($port_list) echo $node_cnt flag=0 for((i=0;i<$node_cnt;i++));do    seq=$(($i+1))    master=${masters[$i]}    port=${ports[$i]}    echo $master    echo $port     psql -h $master -p $port  postgres -c "$exec_sql"done

4、python程序開發

4.1、安裝psycopg2模塊

[root@VM_0_29_centos ~]# yum install python-psycopg2

4.2、連接數據庫

#coding=utf-8#!/usr/bin/pythonimport psycopg2try:    conn = psycopg2.connect(database="postgres", user="tbase", password="", host="172.16.0.29", port="15432")    print "連接數據庫成功"    conn.close()except psycopg2.Error,msg:    print "連接數據庫出錯,錯誤詳細信息:%s" %(msg.args[0])

運行

[tbase@VM_0_29_centos python]$ python conn.py連接數據庫成功

4.3、創建數據表

#coding=utf-8#!/usr/bin/pythonimport psycopg2try:    conn = psycopg2.connect(database="postgres", user="tbase", password="", host="172.16.0.29", port="15432")    print "連接數據庫成功"    cur = conn.cursor()    sql = """          create table tbase          (              id int,              nickname varchar(100)          )distribute by shard(id) to group default_group          """    cur.execute(sql)    conn.commit()    print "建立數據表成功"    conn.close()except psycopg2.Error,msg:    print "TBase Error %s" %(msg.args[0])

運行

[tbase@VM_0_29_centos python]$ python createtable.py連接數據庫成功建立數據表成功

4.4、插入數據

#coding=utf-8#!/usr/bin/pythonimport psycopg2try:    conn = psycopg2.connect(database="postgres", user="tbase", password="", host="172.16.0.29", port="15432")    print "連接數據庫成功"    cur = conn.cursor()    sql = "insert into tbase values(1,'tbase'),(2,'tbase');"    cur.execute(sql)    sql = "insert into tbase values(%s,%s)"    cur.execute(sql,(3,'pg'))    conn.commit()    print "插入數據成功"    conn.close()except psycopg2.Error,msg:    print "操作數據庫出庫 %s" %(msg.args[0])

運行

[tbase@VM_0_29_centos python]$ python insert.py連接數據庫成功插入數據成功

4.5、查詢數據

#coding=utf-8#!/usr/bin/pythonimport psycopg2try:    conn = psycopg2.connect(database="postgres", user="tbase", password="", host="172.16.0.29", port="15432")    print "連接數據庫成功"    cur = conn.cursor()    sql = "select * from tbase"    cur.execute(sql)    rows = cur.fetchall()    for row in rows:        print "ID = ", row[0]        print "NICKNAME = ", row[1],"n"    conn.close()except psycopg2.Error,msg:    print "操作數據庫出庫 %s" %(msg.args[0])

運行

[tbase@VM_0_29_centos python]$ python select.py連接數據庫成功ID =  1NICKNAME =  tbase ID =  2NICKNAME =  pgxz ID =  3NICKNAME =  pg

4.6、copy from 加載文件到表

#coding=utf-8#!/usr/bin/pythonimport psycopg2try:    conn = psycopg2.connect(database="postgres", user="tbase", password="", host="172.16.0.29", port="15432")    print "連接數據庫成功"    cur = conn.cursor()    filename = "/data/tbase/tbase.txt"    cols = ('id','nickname')    tablename="public.tbase"    cur.copy_from(file=open(filename),table=tablename,columns=cols,sep=',')    conn.commit()    print "導入數據成功"    conn.close()except psycopg2.Error,msg:    print "操作數據庫出庫 %s" %(msg.args[0])

執行

[tbase@VM_0_29_centos python]$ python copy_from.py連接數據庫成功導入數據成功

5、PHP程序開發

5.1、連接數據庫

<?php$host="172.16.0.29";$port="15432";$dbname="postgres";$user="tbase" ;$password=""; //連接數據庫$conn=@pg_connect("host=$host port=$port dbname=$dbname user=$user password=$password");if (!$conn){    $error_msg=@pg_errormessage($conn);    echo "連接數據庫出錯,詳情:".$error_msg."n<BR>"; ;    exit;}else{    echo "連接數據庫成功"."n<BR>";}//關閉連接pg_close($conn);?>

執行

[root@VM_0_47_centos test]# curl http://127.0.0.1:8080/dbsta/test/conn.php連接數據庫成功

5.2、創建數據表

<?php$host="172.16.0.29";$port="15432";$dbname="postgres";$user="tbase" ;$password=""; //連接數據庫$conn=@pg_connect("host=$host port=$port dbname=$dbname user=$user password=$password");if (!$conn){    $error_msg=@pg_errormessage($conn);    echo "連接數據庫出錯,詳情:".$error_msg."n"; ;    exit;}else{    echo "連接數據庫成功"."n";} //建立數據表$sql="create table public.tbase(id integer,nickname varchar(100)) distribute by shard(id) to group default_group;";$result = @pg_exec($conn,$sql) ;if (!$result){    $error_msg=@pg_errormessage($conn);    echo "創建數據表出錯,詳情:".$error_msg."n"; ;    exit;}else{    echo "創建數據表成功"."n";}//關閉連接pg_close($conn);?>

執行

[root@VM_0_47_centos test]# curl http://127.0.0.1:8080/dbsta/test/createtable.php連接數據庫成功創建數據表成功

5.3、插入數據

<?php$host="172.16.0.29";$port="15432";$dbname="postgres";$user="tbase" ;$password=""; //連接數據庫$conn=@pg_connect("host=$host port=$port dbname=$dbname user=$user password=$password");if (!$conn){    $error_msg=@pg_errormessage($conn);    echo "連接數據庫出錯,詳情:".$error_msg."n"; ;    exit;}else{    echo "連接數據庫成功"."n";} //插入數據$sql="insert into public.tbase values(1,'tbase'),(2,'pgxz');";$result = @pg_exec($conn,$sql) ;if (!$result){    $error_msg=@pg_errormessage($conn);    echo "插入數據出錯,詳情:".$error_msg."n";    exit;}else{    echo "插入數據成功"."n";} //關閉連接pg_close($conn); ?>

執行

[tbase@VM_0_47_centos test]$ curl http://127.0.0.1:8080/dbsta/test/insert.php連接數據庫成功插入數據成功

5.4、查詢數據

<?php$host="172.16.0.29";$port="15432";$dbname="postgres";$user="tbase" ;$password=""; //連接數據庫$conn=@pg_connect("host=$host port=$port dbname=$dbname user=$user password=$password");if (!$conn){    $error_msg=@pg_errormessage($conn);    echo "連接數據庫出錯,詳情:".$error_msg."n"; ;    exit;}else{    echo "連接數據庫成功"."n";} //查詢數據$sql="select id,nickname from public.tbase";$result = @pg_exec($conn,$sql) ;if (!$result){    $error_msg=@pg_errormessage($conn);    echo "查詢數據出錯,詳情:".$error_msg."n";    exit;}else{    echo "插入數據成功"."n";}$record_num = pg_numrows($result);echo "返回記錄數".$record_num."n";$rec=pg_fetch_all($result);for($i=0;$i<$record_num;$i++){    echo "記錄數#".strval($i+1)."n";    echo "id:".$rec[$i]["id"]."n";    echo "nickname:".$rec[$i]["nickname"]."nn";}//關閉連接pg_close($conn);?>

調用方法

[root@VM_0_47_centos ~]# curl http://127.0.0.1:8080/dbsta/test/select.php連接數據庫成功插入數據成功返回記錄數2記錄數#1id:1nickname:tbase 記錄數#2id:2nickname:pgxz

5.5、流數據copy 入表

<?php $host="172.16.0.29";$port="15432";$dbname="postgres";$user="tbase" ;$password=""; //連接數據庫$conn=@pg_connect("host=$host port=$port dbname=$dbname user=$user password=$password");if (!$conn){    $error_msg=@pg_errormessage($conn);    echo "連接數據庫出錯,詳情:".$error_msg."n"; ;    exit;}else{    echo "連接數據庫成功"."n";}$row=ARRAY("1,TBase","2,pgxz");$flag=pg_copy_from($conn,"public.tbase",$row,","); if (!$flag){    $error_msg=@pg_errormessage($conn);    echo "copy出錯,詳情:".$error_msg."n";}else{    echo "copy成功"."n";} //關閉連接pg_close($conn);        ?>

調用方法

curl http://127.0.0.1/dbsta/cron/php_copy_from.php連接數據庫成功copy成功

5.6、copy to導出數據到一個數組中

<?php $host="172.16.0.29";$port="15432";$dbname="postgres";$user="tbase" ;$password=""; //連接數據庫$conn=@pg_connect("host=$host port=$port dbname=$dbname user=$user password=$password");if (!$conn){    $error_msg=@pg_errormessage($conn);    echo "連接數據庫出錯,詳情:".$error_msg."n"; ;    exit;}else{    echo "連接數據庫成功"."n";} $row=pg_copy_to($conn,"public.tbase",",");if (!$row){    $error_msg=@pg_errormessage($conn);    echo "copy出錯,詳情:".$error_msg."n";}else{    print_r($row);}//關閉連接pg_close($conn);?>

調用方法

curl http://127.0.0.1/dbsta/cron/php_copy_to.php連接數據庫成功Array(    [0] => 1,TBase     [1] => 2,pgxz )

6、golang程序開發

6.1、連接數據庫

package main import (    "fmt"    "time"     "github.com/jackc/pgx") func main() {    var error_msg string     //連接數據庫    conn, err := db_connect()    if err != nil {        error_msg = "連接數據庫失敗,詳情:" + err.Error()        write_log("Error", error_msg)        return    }    //程序運行結束時關閉連接    defer conn.Close()    write_log("Log", "連接數據庫成功") } /*功能描述:寫入日志處理 參數說明:log_level -- 日志級別,只能是是Error或Logerror_msg -- 日志內容 返回值說明:無*/ func write_log(log_level string, error_msg string) {    //打印錯誤信息    fmt.Println("訪問時間:", time.Now().Format("2006-01-02 15:04:05"))    fmt.Println("日志級別:", log_level)    fmt.Println("詳細信息:", error_msg)} /*功能描述:連接數據庫 參數說明:無 返回值說明:conn *pgx.Conn -- 連接信息err error --錯誤信息 */ func db_connect() (conn *pgx.Conn, err error) {    var config pgx.ConnConfig    config.Host = "127.0.0.1"    //數據庫主機host或ip    config.User = "tbase"         //連接用戶    config.Password = "pgsql"    //用戶密碼    config.Database = "postgres" //連接數據庫名    config.Port = 15432          //端口號    conn, err = pgx.Connect(config)    return conn, err}

[root@VM_0_29_centos tbase]# go run conn.go訪問時間:2018-04-03 20:40:28日志級別:Log詳細信息:連接數據庫成功

編譯后運行

[root@VM_0_29_centos tbase]# go build conn.go[root@VM_0_29_centos tbase]# ./conn訪問時間:2018-04-03 20:40:48日志級別:Log詳細信息:連接數據庫成功

6.2、創建數據表

package main import (    "fmt"    "time"     "github.com/jackc/pgx") func main() {    var error_msg string    var sql string     //連接數據庫    conn, err := db_connect()    if err != nil {        error_msg = "連接數據庫失敗,詳情:" + err.Error()        write_log("Error", error_msg)        return    }    //程序運行結束時關閉連接    defer conn.Close()    write_log("Log", "連接數據庫成功")     //建立數據表    sql = "create table public.tbase(id varchar(20),nickname varchar(100)) distribute by shard(id) to group  default_group;"    _, err = conn.exec(sql)    if err != nil {        error_msg = "創建數據表失敗,詳情:" + err.Error()        write_log("Error", error_msg)        return    } else {        write_log("Log", "創建數據表成功")    }} /*功能描述:寫入日志處理 參數說明:log_level -- 日志級別,只能是是Error或Logerror_msg -- 日志內容 返回值說明:無*/ func write_log(log_level string, error_msg string) {    //打印錯誤信息    fmt.Println("訪問時間:", time.Now().Format("2006-01-02 15:04:05"))    fmt.Println("日志級別:", log_level)    fmt.Println("詳細信息:", error_msg)} /*功能描述:連接數據庫 參數說明:無 返回值說明:conn *pgx.Conn -- 連接信息err error --錯誤信息 */ func db_connect() (conn *pgx.Conn, err error) {    var config pgx.ConnConfig    config.Host = "127.0.0.1"    //數據庫主機host或ip    config.User = "tbase"         //連接用戶    config.Password = "pgsql"    //用戶密碼    config.Database = "postgres" //連接數據庫名    config.Port = 15432          //端口號    conn, err = pgx.Connect(config)    return conn, err}

[root@VM_0_29_centos tbase]# go run createtable.go訪問時間:2018-04-03 20:50:24日志級別:Log詳細信息:連接數據庫成功訪問時間:2018-04-03 20:50:24日志級別:Log詳細信息:創建數據表成功

6.3、插入數據

package main import (    "fmt"    "strings"    "time"     "github.com/jackc/pgx") func main() {    var error_msg string    var sql string    var nickname string     //連接數據庫    conn, err := db_connect()    if err != nil {        error_msg = "連接數據庫失敗,詳情:" + err.Error()        write_log("Error", error_msg)        return    }    //程序運行結束時關閉連接    defer conn.Close()    write_log("Log", "連接數據庫成功")     //插入數據    sql = "insert into public.tbase values('1','tbase'),('2','pgxz');"    _, err = conn.exec(sql)    if err != nil {        error_msg = "插入數據失敗,詳情:" + err.Error()        write_log("Error", error_msg)        return    } else {        write_log("Log", "插入數據成功")    }     //綁定變量插入數據,不需要做防注入處理    sql = "insert into public.tbase values($1,$2),($1,$3);"    _, err = conn.exec(sql, "3", "postgresql", "postgres")    if err != nil {        error_msg = "插入數據失敗,詳情:" + err.Error()        write_log("Error", error_msg)        return    } else {        write_log("Log", "插入數據成功")    }     //拼接sql語句插入數據,需要做防注入處理    nickname = "TBase is ' good!"    sql = "insert into public.tbase values('1','" + sql_data_encode(nickname) + "')"    _, err = conn.exec(sql)    if err != nil {        error_msg = "插入數據失敗,詳情:" + err.Error()        write_log("Error", error_msg)        return    } else {        write_log("Log", "插入數據成功")    }} /*功能描述:sql查詢拼接字符串編碼 參數說明:str -- 要編碼的字符串 返回值說明:返回編碼過的字符串 */ func sql_data_encode(str string) string {    return strings.replace(str, "'", "''", -1)} /*功能描述:寫入日志處理 參數說明:log_level -- 日志級別,只能是是Error或Logerror_msg -- 日志內容 返回值說明:無*/ func write_log(log_level string, error_msg string) {    //打印錯誤信息    fmt.Println("訪問時間:", time.Now().Format("2006-01-02 15:04:05"))    fmt.Println("日志級別:", log_level)    fmt.Println("詳細信息:", error_msg)} /*功能描述:連接數據庫 參數說明:無 返回值說明:conn *pgx.Conn -- 連接信息err error --錯誤信息 */ func db_connect() (conn *pgx.Conn, err error) {    var config pgx.ConnConfig    config.Host = "127.0.0.1"    //數據庫主機host或ip    config.User = "tbase"         //連接用戶    config.Password = "pgsql"    //用戶密碼    config.Database = "postgres" //連接數據庫名    config.Port = 15432          //端口號    conn, err = pgx.Connect(config)    return conn, err}

[root@VM_0_29_centos tbase]# go run insert.go訪問時間:2018-04-03 21:05:51日志級別:Log詳細信息:連接數據庫成功訪問時間:2018-04-03 21:05:51日志級別:Log詳細信息:插入數據成功訪問時間:2018-04-03 21:05:51日志級別:Log詳細信息:插入數據成功訪問時間:2018-04-03 21:05:51日志級別:Log詳細信息:插入數據成功

6.4、查詢數據

package main import (    "fmt"    "strings"    "time"     "github.com/jackc/pgx") func main() {    var error_msg string    var sql string     //連接數據庫    conn, err := db_connect()    if err != nil {        error_msg = "連接數據庫失敗,詳情:" + err.Error()        write_log("Error", error_msg)        return    }    //程序運行結束時關閉連接    defer conn.Close()    write_log("Log", "連接數據庫成功")     sql = "select id,nickname FROM public.tbase LIMIT 2"    rows, err := conn.Query(sql)    if err != nil {        error_msg = "查詢數據失敗,詳情:" + err.Error()        write_log("Error", error_msg)        return    } else {        write_log("Log", "查詢數據成功")    }     var nickname string    var id string     for rows.Next() {        err = rows.Scan(&id, &nickname)        if err != nil {            error_msg = "執行查詢失敗,詳情:" + err.Error()            write_log("Error", error_msg)            return        }        error_msg = fmt.Sprintf("id:%s nickname:%s", id, nickname)        write_log("Log", error_msg)    }    rows.Close()     nickname = "tbase"     sql = "select id,nickname FROM public.tbase WHERE nickname ='" + sql_data_encode(nickname) + "' "    rows, err = conn.Query(sql)    if err != nil {        error_msg = "查詢數據失敗,詳情:" + err.Error()        write_log("Error", error_msg)        return    } else {        write_log("Log", "查詢數據成功")    }    defer rows.Close()     for rows.Next() {        err = rows.Scan(&id, &nickname)        if err != nil {            error_msg = "執行查詢失敗,詳情:" + err.Error()            write_log("Error", error_msg)            return        }        error_msg = fmt.Sprintf("id:%s nickname:%s", id, nickname)        write_log("Log", error_msg)    }} /*功能描述:sql查詢拼接字符串編碼 參數說明:str -- 要編碼的字符串 返回值說明:返回編碼過的字符串 */ func sql_data_encode(str string) string {    return strings.replace(str, "'", "''", -1)} /*功能描述:寫入日志處理 參數說明:log_level -- 日志級別,只能是是Error或Logerror_msg -- 日志內容 返回值說明:無*/ func write_log(log_level string, error_msg string) {    //打印錯誤信息    fmt.Println("訪問時間:", time.Now().Format("2006-01-02 15:04:05"))    fmt.Println("日志級別:", log_level)    fmt.Println("詳細信息:", error_msg)} /*功能描述:連接數據庫 參數說明:無 返回值說明:conn *pgx.Conn -- 連接信息err error --錯誤信息 */ func db_connect() (conn *pgx.Conn, err error) {    var config pgx.ConnConfig    config.Host = "127.0.0.1"    //數據庫主機host或ip    config.User = "tbase"         //連接用戶    config.Password = "pgsql"    //用戶密碼    config.Database = "postgres" //連接數據庫名    config.Port = 15432          //端口號    conn, err = pgx.Connect(config)    return conn, err}

[root@VM_0_29_centos tbase]# go run select.go訪問時間:2018-04-09 10:35:50日志級別:Log詳細信息:連接數據庫成功訪問時間:2018-04-09 10:35:50日志級別:Log詳細信息:查詢數據成功訪問時間:2018-04-09 10:35:50日志級別:Log詳細信息:id:2 nickname:tbase訪問時間:2018-04-09 10:35:50日志級別:Log詳細信息:id:3 nickname:postgresql訪問時間:2018-04-09 10:35:50日志級別:Log詳細信息:查詢數據成功訪問時間:2018-04-09 10:35:50日志級別:Log詳細信息:id:1 nickname:tbase

6.5、流數據copy from入表

package main import (    "fmt"    "math/rand"    "time"     "github.com/jackc/pgx") func main() {    var error_msg string     //連接數據庫    conn, err := db_connect()    if err != nil {        error_msg = "連接數據庫失敗,詳情:" + err.Error()        write_log("Error", error_msg)        return    }    //程序運行結束時關閉連接    defer conn.Close()    write_log("Log", "連接數據庫成功")     //構造5000行數據    inputRows := [][]interface{}{}    var id string    var nickname string    for i := 0; i < 5000; i++ {        id = fmt.Sprintf("%d", rand.Intn(10000))        nickname = fmt.Sprintf("%d", rand.Intn(10000))        inputRows = append(inputRows, []interface{}{id, nickname})    }    copyCount, err := conn.CopyFrom(pgx.Identifier{"tbase"}, []string{"id", "nickname"}, pgx.CopyFromRows(inputRows))    if err != nil {        error_msg = "執行copyFrom失敗,詳情:" + err.Error()        write_log("Error", error_msg)        return    }    if copyCount != len(inputRows) {        error_msg = fmt.Sprintf("執行copyFrom失敗,copy行數:%d 返回行數為:%d", len(inputRows), copyCount)        write_log("Error", error_msg)        return    } else {        error_msg = "Copy 記錄成功"        write_log("Log", error_msg)    } } /*功能描述:寫入日志處理 參數說明:log_level -- 日志級別,只能是是Error或Logerror_msg -- 日志內容 返回值說明:無*/ func write_log(log_level string, error_msg string) {    //打印錯誤信息    fmt.Println("訪問時間:", time.Now().Format("2006-01-02 15:04:05"))    fmt.Println("日志級別:", log_level)    fmt.Println("詳細信息:", error_msg)} /*功能描述:連接數據庫 參數說明:無 返回值說明:conn *pgx.Conn -- 連接信息err error --錯誤信息 */ func db_connect() (conn *pgx.Conn, err error) {    var config pgx.ConnConfig    config.Host = "127.0.0.1"    //數據庫主機host或ip    config.User = "tbase"         //連接用戶    config.Password = "pgsql"    //用戶密碼    config.Database = "postgres" //連接數據庫名    config.Port = 15432          //端口號    conn, err = pgx.Connect(config)    return conn, err}

[root@VM_0_29_centos tbase]# go run copy_from.go訪問時間:2018-04-09 10:36:40日志級別:Log詳細信息:連接數據庫成功訪問時間:2018-04-09 10:36:40日志級別:Log詳細信息:Copy 記錄成功

6.6、golang相關資源包

需要git的資源包:https://github.com/jackc/pgxhttps://github.com/pkg/errors