Bottom Page

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
Quote
#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) 
Quote
#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.
Quote
#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.
Quote

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  Evaluating multiple lists in a tuple cdogo 2 260 Apr-14-2019, 09:15 AM
Last Post: Yoriz
  Question about for loop not creating an infinite loop. FWendeburg 1 376 Feb-03-2019, 08:45 PM
Last Post: ichabod801
  Weird scoping error Stef 3 344 Jan-20-2019, 04:36 PM
Last Post: Stef
  Understanding Scoping in Python yksingh1097 5 674 Aug-06-2018, 07:42 PM
Last Post: nilamo
  Loop Condition Question malonn 6 690 Aug-01-2018, 01:56 PM
Last Post: malonn
  Beginner Loop question BigDisAok 5 815 Jul-24-2018, 02:04 PM
Last Post: BigDisAok
  Question with while loop placement Tunechi 2 674 May-16-2018, 02:54 AM
Last Post: Tunechi
  For loop question(skipping) jure98 1 602 Mar-31-2018, 01:54 PM
Last Post: Larz60+
  For loop question peejj 1 645 Mar-18-2018, 10:36 PM
Last Post: stranac
  Question about an infinite loop Robo_Pi 6 945 Mar-05-2018, 05:46 AM
Last Post: wavic

Forum Jump:


Users browsing this thread: 1 Guest(s)