python 操作oracle

表DDL

-- "C##TAPDATA_TEST".ALAM3 definition

CREATE TABLE "C##TAPDATA_TEST"."ALAM3" 
   (	"UID1" VARCHAR2(100) NOT NULL ENABLE, 
	"UID2" VARCHAR2(100) NOT NULL ENABLE, 
	"COLUMN1" VARCHAR2(100), 
	"COLUMN2" VARCHAR2(100), 
	"COLUMN3" VARCHAR2(100), 
	"COLUMN4" VARCHAR2(100), 
	"COLUMN5" VARCHAR2(100), 
	"COLUMN6" VARCHAR2(100), 
	"COLUMN7" VARCHAR2(100), 
	"COLUMN8" VARCHAR2(100), 
	"COLUMN9" VARCHAR2(100), 
	"COLUMN10" VARCHAR2(100), 
	"CREATETIME" TIMESTAMP (6), 
	"UPDATETIME" TIMESTAMP (6)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

CREATE UNIQUE INDEX "C##TAPDATA_TEST"."ALAM3_UID1_IDX" ON "C##TAPDATA_TEST"."ALAM3" ("UID1", "UID2") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

　　

代码如下

import time

import cx_Oracle as oracledb
from random import randint

# import sample_env
source_db_username = 'C##TAPDATA_TEST'
source_db_password = 'ikasinfo123'
source_db_host = '192.168.10.155'
source_db_port = "1521"
source_db_sid = 'ORCLCDB'
connect = source_db_host + ':' + source_db_port + '/' + source_db_sid

oracledb.init_oracle_client(
    lib_dir=r'C:\Users\zheng.jianhang\AppData\Local\Programs\Python\Python310\instantclient_21_6')


class alam_oracle:
    def __init__(self):
        # connection = oracledb.connect(sample_env.get_main_connect_string())
        self.con = oracledb.connect(source_db_username,
                                    source_db_password,
                                    connect)

    def query(self, sql="select max(UID1+0) from ALAM3"):
        cursor = self.con.cursor()

        # print("Get all rows via iterator")
        cursor.execute(sql)
        result = cursor.fetchone()
        # print(result)
        result = result if isinstance(result[0], int) else (0,)
        return result

    def insert(self, result, num=10000):
        # print(result)
        cursor = self.con.cursor()

        rows = []
        # print(result[0])
        start = result[0] + 1
        # print(start)
        end = start + num
        for i in range(start, end):
            rows.append((i, i))
        # predefine maximum string size to avoid data scans and memory reallocations;
        # the None value indicates that the default processing can take place
        cursor.setinputsizes(None, 20)

        # cursor.executemany("insert into ALAM(COLUMN1, COLUMN2, COLUMN3) values (:1, :2, :3)", rows)
        cursor.executemany(
            "insert into ALAM3(UID1, UID2, "
            "COLUMN1, COLUMN2, COLUMN3, COLUMN4, COLUMN5, COLUMN6, COLUMN7,COLUMN8, COLUMN9, COLUMN10, "
            "CREATETIME) "
            "values(:1, :1, :1, :1, :1, :1, :1, :1, "
            "dbms_random.string('x', 20), dbms_random.string('x', 20), "
            "dbms_random.string('x', 20), dbms_random.string('x', 20), "
            "sysdate)", rows)
        self.con.commit()

    def update(self, max, count):
        cursor = self.con.cursor()
        uid_list = []
        for i in range(count):
            uid_list.append(randint(1, max))
        COLUMN_num = randint(1, 10)
        # print(tuple(uid_list))

        sql = "UPDATE ALAM3 SET " \
              "COLUMN" + str(COLUMN_num) + "=dbms_random.string('x', 20) ," \
                                           "UPDATETIME=sysdate " \
                                           "WHERE UID1 in" + str(tuple(uid_list)) + ""
        # print(sql)
        cursor.execute(sql)
        self.con.commit()


if __name__ == '__main__':
    pass
    a = alam_oracle()
    r = a.query()
    stime = time.time()
    # a.update(r[0], 100)
    a.insert(r, num=10000)
    print(time.time() - stime)

 

• 插入时间消耗不大，
• 更新随数量增加而增加