表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)
- 插入时间消耗不大,
- 更新随数量增加而增加