1. 首页
  2. 社区新闻
  3. 强人随笔

TBase应用接入指南(上篇)

原作者：腾讯大数据平台TBase研发组　创作时间：2019-12-09 14:58:13+08   采编：wangliyun

发布时间：2019-12-10 08:01:13

欢迎大家踊跃投稿，投稿信箱：press@postgres.cn

评论：5    浏览：6670

版本号－－V2.17

更新日期：2019-11-13

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");
     }
}

说明：

n 这里连接的节点为任意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"
int
main(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.c
gcc -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"
int
main(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.c
gcc -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"
int
main(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.c
gcc -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"   
int
main(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("%s\t",PQfname(res,j));
        }
        printf("\n");
        for(int i = 0;i< rownum; ++i){
            for(int j = 0;j< colnum; ++j){
                printf("%s\t",PQgetvalue(res,i,j));
            }
            printf("\n");
        }
    }
    PQclear(res);
    PQfinish(conn);
    return 0;
}

编译

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

运行

./select "dbname=postgres port=15432"
连接数据库成功！
查询数据成功！
id      nickname
1       tbase
2       pgxz

2.5、流数据COPY入表

#include <string.h>
#include <stdio.h>
#include <stdlib.h>
#include "libpq-fe.h"
int
main(int argc, char **argv){
const char *conninfo;
    PGconn     *conn;      
    PGresult   *res;
const char *buffer = "1,tbase\n2,pgxz\n3,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.c
gcc -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 1
fi

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/python
import psycopg2
try:
    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/python
import psycopg2
try:
    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/python
import psycopg2
try:
    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/python
import psycopg2
try:
    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 =  1
NICKNAME =  tbase 

ID =  2
NICKNAME =  pgxz 

ID =  3
NICKNAME =  pg

4.6、copy from 加载文件到表

#coding=utf-8
#!/usr/bin/python
import psycopg2
try:
    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 
连接数据库成功
导入数据成功

CENTER_PostgreSQL_Community

请在登录后发表评论，否则无法保存。
1楼 xcvxcvsdf
2024-11-26 22:51:39+08

https://tr.tiancebbs.cn/qths/460209.html https://www.tiancebbs.cn/gongsizhuxiao1/55850.html https://changshushi.tiancebbs.cn/hjzl/460066.html https://changshushi.tiancebbs.cn/hjzl/461491.html https://zulin.tiancebbs.cn/sh/2751.html https://www.tiancebbs.cn/ershoufang/471339.html https://sjz.tiancebbs.cn/b2bjzjc/57059.html https://ld.tiancebbs.cn/qths/470841.html https://zulin.tiancebbs.cn/sh/4205.html https://zulin.tiancebbs.cn/sh/1469.html https://su.tiancebbs.cn/hjzl/463471.html https://aihuishou.tiancebbs.cn/sh/3812.html https://su.tiancebbs.cn/hjzl/464918.html https://xt.tiancebbs.cn/qths/465614.html https://su.tiancebbs.cn/hjzl/468214.html https://taicang.tiancebbs.cn/hjzl/465137.html https://zulin.tiancebbs.cn/sh/2900.html

2楼 xcvxcvsdf
2024-11-20 23:26:40+08

http://fuyang.tjtcbmw.cn/baqz/ https://dyshi.tiancebbs.cn/ https://zzjsq.tiancebbs.cn/ http://cf.lstcxxw.cn/rikaze/ https://aihuishou.tiancebbs.cn/mtgbj/ http://wogao.ahtcbmw.cn/xiangfan/ http://ruanwen.xztcxxw.cn/cw/ http://ouyu.hftcbmw.cn/fengxiansh/ http://fuyang.tjtcbmw.cn/ahmas/ http://bjtcxxw.cn/panzhihua/ http://nalei.zjtcbmw.cn/sxwn/ https://yishui.tiancebbs.cn/ http://huilong.sctcbmw.cn/changningsh/ http://cf.lstcxxw.cn/hubei/ http://ouyu.hftcbmw.cn/gy/ http://xinguang.sctcbmw.cn/ankang/ http://taiying.njtcbmw.cn/shbaoshan/

3楼 xcvxcvsdf
2024-11-13 20:42:09+08

https://www.tiancebbs.cn/ershoufang/467328.html https://tacheng.tiancebbs.cn/qths/450462.html https://zulin.tiancebbs.cn/sh/546.html https://jj.tiancebbs.cn/qths/463123.html https://www.tiancebbs.cn/ershoufang/472763.html https://xz.tiancebbs.cn/qitashebeizhuanrang/235833.html https://su.tiancebbs.cn/hjzl/459482.html https://baise.tiancebbs.cn/qths/466424.html https://taicang.tiancebbs.cn/hjzl/466366.html https://zulin.tiancebbs.cn/sh/644.html https://changshushi.tiancebbs.cn/hjzl/458557.html https://zulin.tiancebbs.cn/sh/4761.html https://su.tiancebbs.cn/hjzl/466164.html https://mdj.tiancebbs.cn/qths/464734.html https://www.tiancebbs.cn/ershouwang/470765.html https://taicang.tiancebbs.cn/hjzl/462493.html https://www.tiancebbs.cn/ershoufang/472226.html

4楼 xcvxcvsdf
2024-10-25 12:02:04+08

https://shfxgxq.tiancebbs.cn/ https://qingzhen.tiancebbs.cn/ https://zhouzhuang.tiancebbs.cn/ http://taiying.njtcbmw.cn/twtd/ http://yz.cqtcxxw.cn/gczp/ http://fs.shtcxxw.cn/shluwan/ http://huilong.sctcbmw.cn/gliao/ http://gx.lztcxxw.cn/yingkou/ http://ty.cqtcxxw.cn/changde/ http://taiying.njtcbmw.cn/scgy/ http://ouyu.hftcbmw.cn/twty/ http://nalei.zjtcbmw.cn/dgqy/ https://xinbei.tiancebbs.cn/ https://shijiazhuangzhoubian.tiancebbs.cn/ https://fenlei.tiancebbs.cn/luzhou/ http://cf.lstcxxw.cn/jszp/ http://nalei.zjtcbmw.cn/shenzhen/

5楼 xiaowu
2024-09-03 19:36:02+08

长津湖之水门桥观后感:https://www.deipei.com/guanhougan/411.html 师德师风反思:https://www.deipei.com/shuzhibaogao/421.html 情况说明格式范文:https://www.deipei.com/fanwen/366.html 党员发言稿:https://www.deipei.com/yanjianggao/408.html 老师的故事:https://www.deipei.com/yuedu/214.html 观后感800字:https://www.deipei.com/guanhougan/254.html 入党申请范文:https://www.deipei.com/fanwen/352.html 写自己的作文:https://www.deipei.com/zuowen/423.html 三分钟演讲稿:https://www.deipei.com/yanjianggao/195.html 葬花吟赏析:https://www.deipei.com/yuedu/131.html 幼儿园实习报告:https://www.deipei.com/shixibaogao/128.html 员工自评:https://www.deipei.com/gongzuo/460.html 培训通知:https://www.deipei.com/gongzuo/462.html 学习总结:https://www.deipei.com/xuexi/471.html 党员发言:https://www.deipei.com/yanjianggao/444.html 银行培训心得体会:https://www.deipei.com/xindetihui/99.html 学生会自我介绍:https://www.deipei.com/xuexi/95.html 大班个人总结:https://www.deipei.com/zongjie/303.html 旷工检讨书:https://www.deipei.com/fanwen/196.html 乡镇民政工作总结:https://www.deipei.com/zongjie/280.html 情感散文:https://www.deipei.com/yuedu/132.html 续签合同申请书:https://www.deipei.com/fanwen/255.html 教育教学反思:https://www.deipei.com/gongzuo/322.html 宣传方案:https://www.deipei.com/fangan/89.html 招生方案:https://www.deipei.com/fangan/90.html 晒晒我们班的牛人:https://www.deipei.com/zuowen/431.html 消防演练方案:https://www.deipei.com/fangan/452.html 解除劳动合同协议书:https://www.deipei.com/hetong/467.html 厂房租赁合同:https://www.deipei.com/hetong/470.html 社会实践个人总结:https://www.deipei.com/zongjie/395.html

© 2010 PostgreSQL中文社区