python 操作oracle

表DDL

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

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

　　

代码如下

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85

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)

 

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