Hi all, I am trying to Read a txt file and insert it's contents into an Oracle Table. This values in the file are '~' (TILDA) seperated. And while inserting, Oracle expects quotes around each value for a varchar field. All the data i'm reading from file are strings. But when i try to insert into table, python is not passing quotes around the value (after converting them to strings using str()) So , i am concatenating quotes for each value and passing it Qtn:- is this the right way to do it ???????
So far so good. I could read and insert the data in the table. But another problem here is , if the column Employee_name has a quote in the value eg: "sam O' Connor", how do i pass it to oracle. if i concatenate strings around each value it inserts fine except for names like these. If i do not have to concatenate quotes and there is a way to insert , please let me know
Andy, Thanks for your replies. I think I cannot put strings into the values my_string[0],my_string[1] are refering to as i'm reading them directly from a Text file. So with the Insert query, it's not sending quotes around each string to Oracle database. So i had to append quotes around each my_string value. This works properly, untill i get the string with a quote in it already..like simon O'Connor
so , I have tried yesterday and put a simple logic in it ..I am not sure whether it's correct way to do it.
for eachline in alllines: my_string=re.split('~',eachline) list=[] for eachcol in my_string: eachcol=string.replace(eachcol,"'","''") list.append(eachcol) stmt="""insert into temp_wris_wages(ssn,earnings,employer_name, employer_add,ein,yrqtr,state,timestmp) values (%s,%14f,%s,%s,%s,%d,%s,%s)"""%("'"+list[0]+"'", float(list[2]), "'"+list[3]+"'", "'"+list[4]+"'", "'"+list[5]+"'", int(list[7]), "'"+list[8][:2]+"'", "'"+today_dt+"'" )
This way im replacing all single qotes into two quotes(not double quotes), so when it hits database, oracle understands the Quote and inserts them correctly.
for eachline in alllines: items = eachline.split('~') items = map(escapeQuotes, items) stmt = """ INSERT INTO temp_wris_wages (ssn, earnings, employer_name, employer_add, ein, yrqtr, state, timestmp) values ('%s',%14f,'%s','%s','%s',%d,'%s','%s') """ % \ (items[0], float(items[2]), items[3], items[4], items[5], int(list[7]), list[8][:2], today_dt)
The mapping is applying the lambda (simple sql escaping function) to each element in the list of items. It doesn't matter that we're doing it to the fields we're int'ing and float'ing since they shouldn't have quotes in anyway.
A few points;
- Python always reads the contents of a file out as a string. - I'm curious as to why you need to do the stuff with "'"+list[3]+"'", each time. If you bring up python and try:
Andy, Thanks for your reply. It works fine like i expected. and the reason why i was using "'"+List[3]+"'" form is , after reading from text file python is passing it as strings but , without quotes around each column. Eg: my file has the following contents 001~xyz company~xy street xy city zip:23405~Simon O' Connor~2345.56~42004
when i fetch them into columns 2nd,3rd and 4th columns are passed for inserting into table as it is. But Oracle expects quotes around them because , if you look at 3rd column, it has spaces in between which oracle doesn't understand with out quotes. Hence the reason for additional quotes around each string.
I do not entirely understand "Lamda" concept YET , but hopefully i'll get some ideas from your code.