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 
连接数据库成功
导入数据成功