Create tempfile that use in loop for insert into DB

[Firsttimepython]

Hi,

I have approx. 2 weeks Python experience but keen to learn it and I'm struggling on something, I also had no prior programming experience apart from a bit of SQL.

What I need to do was call a existing service that passes back customers nbrs based on Group and then insert it into a db, originally I was just to create the file then sftp it and I got that working :) but now they want me to insert it directly to the db from Python and I cant get it working :(. I know I can connect to the db from Python as the direct insert from my Python scripts works so connectivity is fine, I'm wondering if what I want to do is not possible with a temp file ?

#This is calling the service to get the customer nbr I pass in 'Group-1' and 'Group-2' as these are parameter different customer are in each group, when I write the file I have been adding the "," to make it comma separated I also cleaned up the group name to what I want sp 'Group-1' becomes 'Group1' etc

outfile = tempfileNamedTemporayFile('w', delete=False)
for s in service.getcustnbr('Group-1'):
    outfile.write(s + "," + "Group1" + "\n")
for s in service.getcustnbr('Group-2'):
    outfile.write(s + "," + "Group2" + "\n")

outfile.close()

#I know the above worked as when sftp-d the file and viewed it was ok and the data in the file was as follows, the file has no header not sure if that makes a difference ?

1, Group1
2, Group1
3, Group2
4, Group2

#Next I truncate the destination table this works so I know db connectivity is working.
sql.execute("truncate table SMALL_TABLE")

#I then try and do the insert but it fails, the table it inserts to is two columns customer nbr, group

read_file = open(outfile.name, 'r')
text = read_file.read()
read_file_close()
print(text)  # I had this so I could see values 

for row in text:
    sql.execute("INSERT INTO SMALL_TABLE (%s, %s)",
       [row[0], row[1]])
    sql.commit()

The error I get is

Error:
 [row[0], row[1]]) 
IndexError: string index out of range

What I'm I doing wrong, can I insert like this from a tempfile, do I need to change the way my temp file is setup ?
Spent most the day trying different things using a CVS reader etc but got no where.

thanks

[buran]

You read the whole file in text, so text is a string. When iterating over string you get one char at time, i.e. row is single char, thus row[1] cause index 1 to be out of range.

loop over file line by line, split at comma, pass values you want

with open(outfile.name, 'r') as read_file:
    for row in read_file:
        row = row.strip().split(',')
        sql.execute("INSERT INTO SMALL_TABLE (%s, %s)",
                    (row[0], row[1]))
        sql.commit()

alternatively you can use csv module to read each line as tuple.

[Firsttimepython]

Thanks you so much this worked, your explanation also helped me understand more clearly how Python handles the data.