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
1, Group1
2, Group1
3, Group2
4, Group2
#Next I truncate the destination table this works so I know db connectivity is working.
#I then try and do the insert but it fails, the table it inserts to is two columns customer nbr, group
Spent most the day trying different things using a CVS reader etc but got no where.
thanks
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