May-11-2018, 04:24 PM
#!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.
Best,
Hagen
Fort Collins, CO
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