Skip to content

Instantly share code, notes, and snippets.

@lyleaf
Last active May 10, 2022
Embed
What would you like to do?
Insert pandas dataframe to Oracle database using cx_Oracle
"""
ATTENTION:
When using executemany with a list of tuples, the numbers representing the rows has to be strictly from 1 to the last. Or else it won't work.
I really don't understand why.
"""
import cx_Oracle
from parserFWF import getConfigDF
HOTEL_CONFIG = getConfigDF() #dataframe
create_table = '''CREATE TABLE HOTEL_DIMENSION (
OID NUMBER(38,0) NOT NULL,
PROPERTY_ID VARCHAR2(8),
HOTEL_NAME NVARCHAR2(64),
CITY_CODE VARCHAR2(4),
CITY_NAME VARCHAR2(64),
COUNTRY_NAME VARCHAR2(64),
CHAIN_CODE VARCHAR2(4),
CHAIN_NAME VARCHAR2(64),
"LOCK" VARCHAR2(4),
ADDR NVARCHAR2(255),
MADE_ON TIMESTAMP
)'''
con = cx_Oracle.connect('USERNAME','PASSWORD',cx_Oracle.makedsn('HOSTNAME',PORTNUMBER,'SERVICENAME'))
cur = con.cursor()
cur.execute('DROP TABLE HOTEL_DIMENSION')
cur.execute(create_table)
rows = [tuple(x) for x in HOTEL_CONFIG.values]
cur.executemany('''INSERT INTO HOTEL_DIMENSION (PROPERTY_ID,OID,
HOTEL_NAME,CITY_CODE,CITY_NAME,
COUNTRY_NAME,CHAIN_CODE, CHAIN_NAME,"LOCK",
ADDR,MADE_ON)
VALUES (:2,:1,:3,:4,:5,:6,:7,:8,:9,:10,:11)''',rows)
con.commit()
cur2 = con.cursor()
cur2.execute("SELECT * FROM HOTEL_DIMENSION")
print (cur2.fetchall())
cur.close()
cur2.close()
con.close()
@Svrap
Copy link

Svrap commented Feb 18, 2020

from parserFWF import getConfigDF

can you explain me thi?please

@Juanpaaab
Copy link

Juanpaaab commented May 10, 2022

from parserFWF import getConfigDF

can you explain me thi?please

It's where he's getting his dataframe

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment