#
# Loading dataInsert.csv data with cx_Oracle in "bulk" mode
#
# Sample data from emp.csv:
# 1,832072522,craftworks,Katmandu,Krista,committee,staking,increments,acarpous,instad67,facilites60,100
# 2,243042338,partialtone,Wuhan,Sukhendu,nuts,generalize,insulator,lag,trull134,klimsiak,190
# 3,866469228,throwerout,Pusan,Garnette,pick,treated,parameter,exculpate,gsolarmindmaker,chklasthe,50
# 4,110349392,manifestations,Bandung,Wojciech,fours,aiding,submerged,drowsiness,troynlisa,4bijkwamen,150
# 5,663083926,unfortunatedly,Wellington,Jozsef,chair,managed,accelerating,hospitable,ak1h1k058,mousefeathers,70
# 6,988145487,litmus,San Francisco,Antonina,mark,certifies,electromechanical,affinity,boering0,24oct1913,120
# 7,250162646,inflectionless,Katmandu,Marv,bean,twisting,insulator,macabre,gadeholt64,robertloubert,20
# 8,560164847,DecisionMaking,Santiago,Alexina,alloys,coated,accumulated,allegiance,burlile20,ciscoclair,80
# 9,625321324,cowncell,Lagos,Levent,efforts,recorded,installing,shard,n4v4rr078,6prediligently,120
# 10,418163575,kindlier,Hamburg,Jillie,reliabilities,originated,conductive,obsolete,g0rd35ch35,joeymcnew,90
# ...
# Position of fields in csv files equals position
# of columns in emp table.
import cx_Oracle
import csv
from timeit import default_timer as timer
 
connection = cx_Oracle.connect('learn/learn@RAC')
cursor = connection.cursor()
 
insert = """
 INSERT INTO hugedata (id, random, word, cities, names, nouns, verbs, tech_words, gre_words, custom_word, super_word, wordid)
   VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12)"""
 
# Initialize list that will serve as a container for bind values
L = []

startread = timer()
reader = csv.reader(open('F:\Projects\\bulkInsert\data\dataInsert.csv'), delimiter=',')
print("Took {0} seconds to read file".format(timer() - startread))

starttuple = timer()
for row in reader:
    L.append(tuple(row))
print("Took {0} seconds to create tuple".format(timer() - starttuple))
 
# prepare insert statement
cursor.prepare(insert)
print insert 
startins = timer()
# execute insert with executemany
cursor.executemany(None, L)
 
# report number of inserted rows
print 'Inserted: ' + str(cursor.rowcount) + ' rows.'
print("Took {0} seconds to insert into database".format(timer() - startins)) 
# commit
connection.commit()
 
# close cursor and connection
cursor.close()
connection.close()

'''
CREATE TABLE "LEARN"."HUGEDATA"
   (    "ID" NUMBER(*,0),
        "RANDOM" NUMBER,
        "WORD" VARCHAR2(150),
        "CITIES" VARCHAR2(30),
        "NAMES" VARCHAR2(50),
        "NOUNS" VARCHAR2(50),
        "VERBS" VARCHAR2(50),
        "TECH_WORDS" VARCHAR2(50),
        "GRE_WORDS" VARCHAR2(50),
        "CUSTOM_WORD" VARCHAR2(150),
        "SUPER_WORD" VARCHAR2(150),
        "WORDID" NUMBER(*,0),
         PRIMARY KEY ("ID"),
         CONSTRAINT "FK_WORDID_HUGEDATA" FOREIGN KEY ("WORDID") REFERENCES "LEARN"."WORD" ("WORDID"));
'''