Python Forum
Scoping Question: If else in for loop not evaluating i as expected
Thread Rating:
  • 1 Vote(s) - 4 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Scoping Question: If else in for loop not evaluating i as expected
#1
#!python3
I am using psycopg2 to read rows from a spreadsheet into a array using xlrd. I want to compare the 3 element in each row (a sales order number) with records in a postgres database.If the sales order number already exists in the database I can skip the row (for now). If the sales order number doesn't exist in the database, then I want to insert a new record into the DB.

What works:
1. xlrd reads the spreadsheet rows into a array of lists named "frecords"
2. I can variablize the 3rd element in each list using a for p in frecords; opnumber = p[3]
3. I can query the DB with a select statement using opnumber
4. I can print a statement indicating opnumber is or is not already a record in the DB

What doesn't work:

I want to evaluate each list in the frecords array with an if else statement

1.if the output of my query (query_opnum) is None add the spreadsheet row into the DB via an INSERT statement and then evaluate the next list in the array
2.elif print "opnumber is already a record in the database"

What is happening is the for loop is running through the entire array of lists each time instead of evaluate each list once and going to the next list one time only.

curr_row = 0
while curr_row < num_rows:
    curr_row += 1
    row = worksheet.row(curr_row)
    print('Row:', curr_row)
    curr_cell = - 1
    while curr_cell < num_cells:
        curr_cell += 1
        cell_value = worksheet.cell_value(curr_row, curr_cell)
        if type(cell_value) == type(str()):
            cell_value = cell_value.rstrip()
            frecord.append(cell_value)
        elif type(cell_value) == type(float()):
            cell_value=int(cell_value)
            frecord.append(cell_value)
        else:
            frecord.append(cell_value)
            #print(frecord)
    frecords.append(list(frecord))
    frecord=[]

    for p in frecords:
        opnumber = p[3]
       
        conn = psycopg2.connect("dbname='forecast' user='datasundae' password='P3ns3UR'")

        cur = conn.cursor()
        cur.execute("SELECT opnum FROM forecast WHERE opnum = '%s'" % opnumber)
        query_opnum = cur.fetchone()

        if query_opnum is None:
            print(opnumber + ' is not a record in the forecast database')
            conn2 = psycopg2.connect("dbname='forecast' user='datasundae' password='P3ns3UR'")

            cur2 = conn2.cursor()
            
            query = "INSERT INTO forecast (papartner,theater,smotion,opnum,account,opname,status,stage,cdate,category,nsteps,famount) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
            
            cur2.executemany(query, frecords)
            conn2.commit()
            conn2.close()
        

        elif query_opnum is not None:
            print(opnumber + ' is already a record in the forecast database')

        conn.commit()
        conn.close()
    frecord=[]
Sample Output:
Output:
Row: 1 15256385 is not a record in the forecast database Row: 2 15256385 is already a record in the forecast database 15295234 is not a record in the forecast database Row: 3 15256385 is already a record in the forecast database 15295234 is already a record in the forecast database 15714619 is not a record in the forecast database Row: 4 15256385 is already a record in the forecast database 15295234 is already a record in the forecast database 15714619 is already a record in the forecast database 15812373 is not a record in the forecast database Row: 5 15256385 is already a record in the forecast database 15295234 is already a record in the forecast database 15714619 is already a record in the forecast database 15812373 is already a record in the forecast database OP-0873560 is already a record in the forecast database Row: 6 15256385 is already a record in the forecast database 15295234 is already a record in the forecast database 15714619 is already a record in the forecast database 15812373 is already a record in the forecast database OP-0873560 is already a record in the forecast database OP-2410408 is not a record in the forecast database Row: 7 15256385 is already a record in the forecast database 15295234 is already a record in the forecast database 15714619 is already a record in the forecast database 15812373 is already a record in the forecast database OP-0873560 is already a record in the forecast database OP-2410408 is already a record in the forecast database OP-2410377 is not a record in the forecast database Row: 8
I am sure this is a simple scoping error. Any advice would be much appreciated.

Best,

Hagen
Fort Collins, CO
Reply
#2
frecords is not necessary as you continually append to frecord. This means that row one is appended to frecords on the first pass. Then row 1 and row 2 are appended to frecords on the second pass, etc., because frecord still has row 1 in it And I assume that this is not all of your code as neither frecord or frecords is declared before the while. Note that you can simplify your code a little with
        if type(cell_value) == type(str()):
            cell_value = cell_value.rstrip()
##            frecord.append(cell_value)
        elif type(cell_value) == type(float()):
            cell_value=int(cell_value)
##            frecord.append(cell_value)
##        else:
        frecord.append(cell_value) 
Reply
#3
(May-11-2018, 04:24 PM)datasundae Wrote: .....
curr_row = 0
while curr_row < num_rows:
    curr_row += 1
    row = worksheet.row(curr_row)
    print('Row:', curr_row)
    curr_cell = - 1
    while curr_cell < num_cells:
        curr_cell += 1
        cell_value = worksheet.cell_value(curr_row, curr_cell)
        if type(cell_value) == type(str()):
            cell_value = cell_value.rstrip()
            frecord.append(cell_value)
        elif type(cell_value) == type(float()):
            cell_value=int(cell_value)
            frecord.append(cell_value)
        else:
            frecord.append(cell_value)

Not an answer to your question - but couple of code structure advices
  1. Why while and not
    for curr_row in range(1, num_rows + 1):
  2. I am not sure which Excel process library do you use - xlrd or openpyxl - but are you sure there is not more efficient way to access rows and cells?
  3. Excel library probably has a function to check a cell type - but if you insist to do it your way, checking variable type by the following construct is considered more Pythonic
    isinstance(value, float)
Test everything in a Python shell (iPython, Azure Notebook, etc.)
  • Someone gave you an advice you liked? Test it - maybe the advice was actually bad.
  • Someone gave you an advice you think is bad? Test it before arguing - maybe it was good.
  • You posted a claim that something you did not test works? Be prepared to eat your hat.
Reply
#4
Thanks to the forum for your replies. The primary issue with my code and the resulting output was the executemany command:
cur2.executemany(query, frecords)
When I replaced that with just cur2.execute the code quit repeatedly iterating over the same arrays.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Evaluating arithmetic expression with recursion. muddybucket 3 2,760 Dec-17-2021, 08:31 AM
Last Post: deanhystad
  A question about 'Event loop is closed' fc5igm 2 2,163 Oct-05-2021, 02:00 AM
Last Post: fc5igm
Exclamation question about input, while loop, then print jamie_01 5 2,615 Sep-30-2021, 12:46 PM
Last Post: Underscore
  for loop question KEYS 1 1,697 Oct-27-2020, 11:42 PM
Last Post: jefsummers
  Netmiko Loop question sc00ter 2 3,261 Oct-24-2020, 10:54 PM
Last Post: sc00ter
  Please help my while loop does not work as expected KingKhan248 6 2,561 Sep-28-2020, 09:12 PM
Last Post: deanhystad
  while loop question KEYS 2 1,976 Sep-26-2020, 11:02 PM
Last Post: KEYS
  New to programming, loop question tomyan 1 1,588 Sep-25-2020, 04:32 PM
Last Post: Larz60+
  while loop question spalisetty06 2 1,812 Aug-13-2020, 04:18 PM
Last Post: buran
  question about for loop Than999 5 2,427 Jun-09-2020, 02:16 PM
Last Post: Emekadavid

Forum Jump:

User Panel Messages

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