python写数据到kingbase

 

操作kingbase与操作pg一样，需要psycopg2这个包

#!/usr/bin/env python
#coding=utf-8
import os, json, urllib, datetime, shutil
import psycopg2
import traceback
import time

##create table tb_test
##(
##id bigserial primary key not null,
##name1 varchar(32),
##name2 varchar(32),
##name3 varchar(32),
##name4 varchar(32),
##name5 varchar(32),
##name6 varchar(32),
##createtime timestamp default current_timestamp,
##modifytime timestamp default current_timestamp
##);

def insert_data():
    db =  psycopg2.connect(database="db_hxl", user="hxl", password="kingbase", host="192.168.1.101", port="54321")
    # 得到一个可以执行SQL语句的光标对象
    cursor = db.cursor()

    db.autocommit=False ## 关闭字自动提交

    for i in range(1, 10001):
        str_i = str(i)
        insert_sql = "insert into tb_test(name1,name2,name3,name4,name5,name6) values ('%s','%s','%s','%s','%s','%s')" %("nameA"+str_i, "nameB"+str_i, "nameC"+str_i,"nameD"+str_i,"nameE"+str_i,"nameF"+str_i)
        update_sql="update tb_test set name1='%s' where id=%s" %("update"+str_i,i)
        delete_sql="delete from tb_test where id=%s" %(i)
        try:
            # 执行sql语句
            cursor.execute(insert_sql)
            # 提交到数据库执行
            if (i % 10000) == 0:
                db.commit()
        except Exception as err:
            # Rollback in case there is any error
            print("sql语句执行错误", err)
            db.rollback()
    db.commit()
    cursor.close()
    db.close()
    return 0

if __name__ == '__main__':
    print("开始时间:"+time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())))
    l_flag = insert_data()
    print("结束时间:"+time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())))