æè¿çä¸ä¸ªæµè¯ä»»å¡éè¦æµè¯oracleåæ¥hiveæ°æ®åºçæ§è½ï¼é£å°±éè¦å¯¹oracleæ°æ®åºç注æµè¯æ°æ®ãæå°±åæå¼äºæçIDEï¼åå¤ææä¹åä¸ä¸å¯ä»¥ç50wæ°æ®å°MySQLç代ç ï¼æ¹ä¸æ¹ï¼ç´æ¥ç¨ãå 为æå¨ç½ä¸çå°ï¼è¯æ³ä¸ä¹å·®ä¸å¤ï¼åºæ¬ä¸æ²¡æåºå«ãäºæ¯ï¼æå æå¼äºwindowsçcmd模å¼ï¼è¾å ¥ï¼pip install cx_Oracle ä¸è½½å¥½è¿æ¥æ°æ®åºç第ä¸åå ãå½ç¶ä¹å¯ä»¥éè¿python第ä¸æ¹åºå®ç½ï¼https://pypi.org/ï¼ä¸è½½ç¸åºçæ¬çæ件åèªè¡å®è£ ã
æå®ç¬¬ä¸æ¹åºï¼åé¢å°±å¼å§å代ç äºï¼æç´æ¥æä¹åè¿æ¥MySQLç代ç æ¿è¿æ¥åä¿®æ¹ãé¦å æ¥çæ°æ®åºè¿æ¥é¨åã
è¿æ¥æ¹å¼ä¸åï¼ä½æ¯ä¹ä¸é¾ï¼è¿æ¥åå°±è¯è¯å§ï¼å æ¥çä¸ä¸çæ¬å·ï¼ç»æå°±åºéäº
æ¥éä¿¡æ¯ï¼
oracle_db = cx_Oracle.connect(âdspuserâ,âAa123456â,â10.26.214.119:1521/orclâ)
cx_Oracle.DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: âThe specified module could not be foundâ. See https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html for help
ä¸ç½æ¥å§ï¼åç°æ¯å 为pythonçå®è£ ç®å½ä¸ï¼æ²¡æoracleçdllæ件ï¼ä¹è®¸æ¯å 为第ä¸æ¹å éè¦ï¼é£å°±å®æãæä»ç½ä¸æ¾å°çå¸åä¸æä¾äºoracle11çdllæ件ï¼ä¸è½½åæ¥æ¾å°pythonå®è£ ç®å½åè¿«ä¸åå¾ çå次è¿è¡ä»£ç ï¼ç»æDPI-1047çé误åæ¯æ²¡æäºï¼æ¢æâORA-28040: No matching authentication protocolâäºãåªå¥½å次ä¸ç½æ¥è¯¢åºéåå ï¼åæ¥æ¯oracleççæ¬ä¸ä¸è´ï¼æç¨çæ¯oracle11çdllï¼èæè¿æ¥çoracleçæ¬æ¯å¨æ使ç¨select * from v$version è¯å¥æ¥è¯¢åï¼æçå°æè¿æ¥çoracleçæ¬
é®é¢è·çåæ¥äºï¼æå»åªæ¾12.2çæ¬çdllæ件å¢ï¼è¿æ¯ç»§ç»ç½ä¸æ¾ï¼å¤ç¯æç« é½è¯´è¦ä¸è½½ä¸ä¸ªinstantclientãé£å°±æå®è¯·åºæ¥å§ãæéè¿oracleå®ç½æ¾å°å¹¶ä¸è½½äºinstantclientï¼å°åå¦ä¸ï¼https://www.oracle.com/cn/database/technologies/instant-client/winx64-64-downloads.html
èä¸è½½ä¸è½½ä¸æ¥çinstantclientå ¶å®å°±æ¯ä¸ä¸ªzipå ï¼æå¼è¿ä¸ªå ï¼æéé¢ææçdllé½ä¸è¡èçå¤å¶å°pythonçæ ¹ç®å½ä¸ï¼é¾æ¥çé®é¢ç»äºè§£å³äºï¼
ä¸é¢å°±å¼å§å建表äºï¼ææ¯ç´æ¥æMySQLå建表çè¯å¥ç´æ¥æ¿äºè¿æ¥ï¼ç»æå°±ä¸åºæå¤çåºäºæå¤ãæ¥éä¿¡æ¯ï¼ORA-00907: missing right parenthesisï¼æ¥éä½ç½®å¨å建表è¯å¥ãå 为æ¯ç¨MySQLç建表è¯å¥ï¼æ以åªå¥½æ¾oracleç¸å ³å»ºè¡¨è¯å¥ï¼åç°åMySQLè¯å¥è¿æ¯æåºå«çãé¦å ï¼è¡¨ååå段åä¸çå°å¼å·è¦å»æï¼å ¶æ¬¡ï¼å段类åä¸åï¼å¾æ¹ï¼oracleä¸MySQL对åºè¯·ç½ä¸èªæ¥ï¼ï¼æåï¼oracleä¸æ¯æèªå¢ï¼ä¸»é®å¨å段å®ä¹ä¸å£°æã
MySQL建表è¯å¥ï¼
creat_table_sql = âââCREATE TABLE
testtable
(
id
bigint NOT NULL AUTO_INCREMENT,
name
varchar(255) NOT NULL COMMENT âåå ¸å称â,
remark
varchar(255) DEFAULT NULL COMMENT âæè¿°â,
testC01umn1
varchar(128) DEFAULT NULL,
testC01umn2
datetime DEFAULT NULL,
testC01umn3
int DEFAULT NULL,
testC01umn4
varchar(128) DEFAULT NULL,
testC01umn5
varchar(128) DEFAULT NULL,
testC01umn6
varchar(128) DEFAULT NULL,
testC01umn7
varchar(128) DEFAULT NULL,
testC01umn8
varchar(128) DEFAULT NULL,
testC01umn9
int DEFAULT NULL,
testC01umn10
datetime DEFAULT NULL,
testC01umn11
varchar(128) DEFAULT NULL,
testC01umn12
varchar(128) DEFAULT NULL,
testC01umn13
varchar(128) DEFAULT NULL,
testC01umn14
varchar(128) DEFAULT NULL,
testC01umn15
varchar(128) DEFAULT NULL,
PRIMARYKEY (
id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=Utf8mb4_0900_ai_ci COMMENT=âæµè¯è¡¨ââââ
Oracle建表è¯å¥ï¼
creat_table_sql = âââCREATE TABLE testtable (
id NUMBER(38,0) NOT NULL PRIMARY KEY,
name VARCHAR2(255) NOT NULL,
remark VARCHAR2(255) DEFAULT NULL,
testColumn1 VARCHAR2(128) DEFAULT NULL,
testColumn2 DATE DEFAULT NULL,
testColumn3 NUMBER(10) DEFAULT NULL,
testColumn4 VARCHAR2(128) DEFAULT NULL,
testColumn5 VARCHAR2(128) DEFAULT NULL,
testColumn6 VARCHAR2(128) DEFAULT NULL,
testColumn7 VARCHAR2(128) DEFAULT NULL,
testColumn8 VARCHAR2(128) DEFAULT NULL,
testColumn9 NUMBER(10) DEFAULT NULL,
testColumn10 DATE DEFAULT NULL,
testColumn11 VARCHAR2(128) DEFAULT NULL,
testColumn12 VARCHAR2(128) DEFAULT NULL,
testColumn13 VARCHAR2(128) DEFAULT NULL,
testColumn14 VARCHAR2(128) DEFAULT NULL,
testColumn15 VARCHAR2(128) DEFAULT NULL
âââ
建表è¯å¥çé®é¢è§£å³äºï¼åé¢å°±è¦æå ¥æ°æ®ï¼é®é¢æ´æ¯ä¸å¤§å ãæå åºç°çé误æ¯ï¼ORA-00928: missing SELECT keywordï¼è¿ä¸ªé误主è¦æ¯å 为æå ¥çæ°æ®å段åå«æå ³é®åï¼æè¯äºå¥½å ç§åæ³é½æ æ³é¿å ï¼ä¸»è¦æ¯æçåä»æ°å»ºè¡¨æä¿®æ¹å段åï¼ï¼æåæå ¥æ°æ®çè¯å¥å¹²èå°±ä¸å¡«åå段åï¼åªè¦ä½ç½®æ£ç¡®å°±å¯ä»¥äºï¼è¿æå°±æ¯æå ¥çæ°æ®ä¸è½ä½¿ç¨pythonçæ ¼å¼å符å·â%sâæ¥æ¿æ¢ï¼åªè½ä½¿ç¨å ä½ç¬¦ãæ以è¿æ¶çæå ¥è¯å¥å°±åæäºè¿æ ·ï¼
insert_sql = âââINSERT INTO TESTTABLE VALUES(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12, 13 ,:14,:15)âââ
ææå段åé½ä¸è¦äºï¼è¿åæ»æ²¡éäºå§ï¼ç»æè¿æ¯æ¥éï¼æ¥éä¿¡æ¯ï¼ORA-01861: literal does not match format stringãåä¸æ¬¡å ¨ç½æç´¢ï¼åç°æ¯å 为æææ¥ææ ¼å¼çå段ï¼å¨æå ¥æ°æ®æ¶éè¦è½¬æ¢ä¸ä¸æå¯ä»¥ï¼æ以æç»æçæå ¥è¯å¥åæäºè¿æ ·ï¼
insert_sql = âââINSERT INTO TESTTABLE VALUES(:1,:2,:3,:4,to_data(:5,âyyyy-MM-DD hh24:mi:ssâ),:6,:7,:8,:9,:10,:11,:12,to_data(:13,âyyyy-MM-DD hh24:mi:ssâ),:14,:15)âââ
å¨è½¬æ¢äºæ¥ææ ¼å¼åï¼é¡ºå©æå ¥æ°æ®æ²¡æåæ¥éãè¿æ¬¡æ¬æ¥è§å¾å¾ç®åçæµè¯æ°æ®æ³¨å ¥è´¹æ¶è´¹åï¼è¿å¥½ææ踩è¿çåé½å¸®åå¦ä»¬è¸©äºä¸éï¼ç¥åä½æµè¯é¡ºå©ï¼2022å£è¯å¿«ä¹ï¼