Python Forum

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

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.

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