The Artima Developer Community
Sponsored Link

Python Answers Forum
Reading from a text file

5 replies on 1 page. Most recent reply: Oct 18, 2004 1:48 PM by Maverick

Welcome Guest
  Sign In

Go back to the topic listing  Back to Topic List Click to reply to this topic  Reply to this Topic Click to search messages in this forum  Search Forum Click for a threaded view of the topic  Threaded View   
Previous Topic   Next Topic
Flat View: This topic has 5 replies on 1 page
Maverick

Posts: 11
Nickname: maverick43
Registered: Oct, 2004

Reading from a text file Posted: Oct 14, 2004 11:16 AM
Reply to this message Reply
Advertisement
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 ???????

=========================================
import cx_Oracle
import string,re
v_user=raw_input("Enter Username :")
v_pwd=raw_input("Enter Password :")
v_dsn=raw_input("Enter DNS :")
connection=cx_Oracle.connect(v_user,v_pwd,v_dsn)
cursor=connection.cursor()

inpfile=open('input_file.txt','r')
for i in range(7): # Skip first 7 lines
spam = inpfile.readline()
alllines=inpfile.readlines()
inpfile.close()
for eachline in alllines:
my_string=re.split('~',eachline)
#print my_string[0],my_string[1],my_string[2]
stmt="""insert into temp_wris_wages(ssn,earnings,employer_name,
employer_add,ein,yrqtr,state)
values (%s,%14f,%s,%s,%s,%d,%s)"""%("'"+str(my_string[0])+"'",
float(my_string[2]),
"'"+str(my_string[3])+"'",
"'"+str(my_string[4])+"'",
"'"+str(my_string[5])+"'",
int(my_string[7]),
"'"+str(my_string[8])+"'"
)
print stmt
cursor.execute(stmt)

connection.commit()
connection.close()
================== ===============================

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

Any help is greatly appreciated

Thanks,


Andy

Posts: 28
Nickname: adn
Registered: Jul, 2004

Re: Reading from a text file Posted: Oct 14, 2004 2:29 PM
Reply to this message Reply
You could put the quotes into the string you're putting the my_string parts into;


stmt = """
INSERT INTO temp_wris_wages (ssn, earnings, employer_name, employer_add, ein, yrqtr, state)
VALUES ('%s', %14f, '%s', '%s', '%s', %d, '%s')
""" % (my_string[0], float(my_string[2]), my_string[3], my_string[4],
my_string[5], int(my_string[7]), my_string[8])


Each element of your string will already be a string since you're reading it straight from a file so you just need to cast the float and int parts.

Andy

Posts: 28
Nickname: adn
Registered: Jul, 2004

Re: Reading from a text file Posted: Oct 14, 2004 2:38 PM
Reply to this message Reply
Oh, and also, you don't need the re module - string objects have a split method on them:

 my_string = eachline.split('~')

- has the same effect. And you prolly don't need the string module either.

Maverick

Posts: 11
Nickname: maverick43
Registered: Oct, 2004

Re: Reading from a text file Posted: Oct 15, 2004 7:13 AM
Reply to this message Reply
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.

Any insights is greatly appreicated..
Thanks

Andy

Posts: 28
Nickname: adn
Registered: Jul, 2004

Re: Reading from a text file Posted: Oct 15, 2004 8:46 AM
Reply to this message Reply
escapeQuotes = lambda my_string: my_string.replace("'", "''")

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:
>>> "(%s, %s)" % ("'" + 'test' + "'", "'" + 'test' + "'")
"('test', 'test')"
>>> "('%s', '%s')" % ('test', 'test')
"('test', 'test')"


The second form produces identical results and is easier to type.

But you're right on on the turning single quotes into double single quotes. Does the program work alright?

Maverick

Posts: 11
Nickname: maverick43
Registered: Oct, 2004

Re: Reading from a text file Posted: Oct 18, 2004 1:48 PM
Reply to this message Reply
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.

Thanks,

Flat View: This topic has 5 replies on 1 page
Topic: How to correct canvas coordinates when scroll bars are moved? Previous Topic   Next Topic Topic: Python doesnt seem to work on my PC >:/

Sponsored Links



Google
  Web Artima.com   

Copyright © 1996-2019 Artima, Inc. All Rights Reserved. - Privacy Policy - Terms of Use