Python Forum
Create tempfile that use in loop for insert into DB
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Create tempfile that use in loop for insert into DB
#1
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
Reply
#2
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.
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

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


Possibly Related Threads…
Thread Author Replies Views Last Post
  How to create a variable only for use inside the scope of a while loop? Radical 10 1,522 Nov-07-2023, 09:49 AM
Last Post: buran
  Create Dynamic For Loop quest 3 4,290 Apr-26-2021, 02:03 PM
Last Post: ibreeden
  Error on open of file created with tempfile.TemporaryDirectory() Brian177 4 6,142 Apr-05-2021, 07:12 PM
Last Post: Brian177
  Python Matplotlib: How do I save (in pdf) all the graphs I create in a loop? JaneTan 1 8,778 Feb-28-2021, 06:20 PM
Last Post: Larz60+
  how to create pythonic codes including for loop and if statement? aupres 1 1,884 Jan-02-2021, 06:10 AM
Last Post: Gribouillis
  create loop of subplot plotly dash without hardcode tonycat 0 3,873 Sep-23-2020, 08:40 AM
Last Post: tonycat
  Create, assign and print variables in loop steven_tr 10 4,239 May-28-2020, 04:26 PM
Last Post: ndc85430
  VideoWriter unreadable output using tempfile fourtino2322 1 2,201 Mar-27-2020, 03:15 PM
Last Post: fourtino2322
  how to create subplots in for loop? python_newbie09 1 5,865 Sep-25-2019, 02:29 PM
Last Post: stullis
  Create application that will take information from database and insert into PDF sorrelsj 1 2,042 Aug-19-2019, 10:08 PM
Last Post: Gribouillis

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020