Python Forum

#!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

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) 

(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)

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.