python:200526-pyDB_ver001.02, - insert 다건 , executemany()

from excel to oracle table 

 

dbcode 01

  CREATE TABLE "PYTEST001"."VITA5" 
   (	"GN_NM" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
	"GN_MEMO" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
	"GN_EVALUATION" VARCHAR2(4000 BYTE), 
	"GN_CONTENT" VARCHAR2(4000 BYTE), 
	"GN_DATE" DATE, 
	"GN_EVALUATIONCOUNT" NUMBER(5,0)
   ) 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 "SYSTEM" ;

 

dbcode 02

--select sysdate from dual;
--EXEC dbms_xdb.sethttpport(9090);
--SELECT dbms_xdb.gethttpport() FROM DUAL;
--alter table VITA5 modify(GN_EVALUATION varchar2(4000));
--alter table VITA5 modify(GN_CONTENT varchar2(4000));
--COMMIT;

--select a.sid, a.serial# from v$session a, v$lock b, dba_objects c
--where a.sid=b.sid and b.id1=c.object_id and b.type='TM' and c.object_name='VITA5';
--ALTER SYSTEM KILL SESSION '169,195'

--DESC VITA5;
select * from VITA5;  -- 전체

 

python code 

# pythonToOracle-003
import sqlite3
import pandas as pd 
import cx_Oracle
import time 
import base64

con = cx_Oracle.connect("pytest001/1234@192.168.182.1:1521/xe")
con01 = con.cursor()
df = pd.read_excel('./test-vita500.xlsx',sheet_name='general')
df1 = df[['작성자','일반댓글','상품평가','내용','등록일','댓글수']]
df1 = df1.rename({'작성자':'t1','일반댓글':'t2','상품평가':'t3','내용':'t4','등록일':'t5','댓글수':'t6'},axis='columns')
df1['t5']=df1['t5'].str.replace(".","/").str[2:]
df1['t6']=df1['t6'].str.replace(",","")

df1 = df1.applymap(str)  # all int to string of the DataFrame
# test2 = df1.loc[160][0]
# print(test2)
# print(type(test2))
# df1.dtypes
# print('--------------------')

rows = [tuple(x) for x in df1.to_records(index=False)]
print(type(rows))

# rows = [
# ('roh',
#   '일반 상품평',
#   '적극추천\n배송빠름',
#   '옵션없음\n적극추천 합니다. 배송이 빠릅니다.',
#   '2020.05.25',
#   '4906'),
#  ('ddi',
#   '일반 상품평',
#   '적극추천\n배송빠름',
#   '옵션없음\n적극추천 합니다. 배송이 빠릅니다.',
#   '2020.05.25',
#   '4906'),
#  ('kim',
#   '일반 상품평',
#   '적극추천\n배송빠름',
#   '옵션없음\n적극추천 합니다. 배송이 빠릅니다.',
#   '2020.05.25',
#   '4906')
# ]
# con01.executemany("insert into PYTEST001.VITA5 values (:1, :2, :3, :4, :5, :6)", rows)

df2 = df1
rows = [tuple(x) for x in df2.to_records(index=False)]
# con01.execute("select * from VITA5")
con01.execute("DELETE FROM VITA5 ")
con01.executemany("insert into PYTEST001.VITA5 values (:1, :2, :3, :4, :5, :6)", rows)

con.commit()

con01.close()
print('success01')