read_cursor = read_cnx.cursor() #read_cnx.cursor(buffered=True) write_cnx = mySQL.connect(user=args.user, passwd=password, db=args.database, host=args.host, port=args.port) write_cursor = write_cnx.cursor() query_string = "select AwardId, PDPIEmail, ProgramOfficerEmail from rg_award"\ " where PDPIEmail is not null or ProgramOfficerEmail is not null;" read_cursor.execute(query_string) num_rows_read = 0 print(datetime.datetime.now(), num_rows_read) # This is barfing after about 17000-21000 iterations. Maybe need to read everything in and then do the writes. for (AwardId, pdpiEmail, poEmail) in read_cursor: corrected_pdpi_email_address = email_corrector.email_corrector(pdpiEmail) corrected_po_email_address = email_corrector.email_corrector(poEmail) if (corrected_pdpi_email_address is not None) or (corrected_po_email_address is not None): query_string = "UPDATE rg_award SET UM_PDPIEmail_Corrected=%s, UM_ProgramOfficerEmail_Corrected=%s " \ "WHERE AwardId=%s;" write_cursor.execute(query_string, (corrected_pdpi_email_address, corrected_po_email_address, AwardId)) num_rows_read += 1 if divmod(num_rows_read, 10000)[1] == 0: print(datetime.datetime.now(), num_rows_read) write_cnx.commit() write_cnx.commit() write_cursor.close() write_cnx.close()
read_cnx = mySQL.connect(user=args.user, passwd=password, db=args.database, host=args.host, port=args.port) read_cursor = read_cnx.cursor() write_cnx = mySQL.connect(user=args.user, passwd=password, db=args.database, host=args.host, port=args.port) write_cursor = write_cnx.cursor() query_string = "select InvestigatorId, EmailAddress from NSF_Investigator"\ " where EmailAddress is not null;" read_cursor.execute(query_string) num_rows_read = 0 print(datetime.datetime.now(), num_rows_read) for (InvestigatorId, EmailAddress) in read_cursor: corrected_email_address = email_corrector.email_corrector(EmailAddress) if corrected_email_address is not None: query_string = "UPDATE NSF_Investigator SET UM_Corrected_EmailAddress=%s WHERE InvestigatorId=%s;" write_cursor.execute(query_string, (corrected_email_address, InvestigatorId)) num_rows_read += 1 if divmod(num_rows_read, 10000)[1] == 0: print(datetime.datetime.now(), num_rows_read) write_cnx.commit() write_cnx.commit() write_cursor.close() write_cnx.close() read_cursor.close() read_cnx.close()