questionable read performance?
I am opening a xlsx file and reading a row, and building sql to insert it into a mysql database. I do this in batches of 100 rows.
I thought there might have been a problem. The time to read 100 rows starts out at 12.4149 seconds and then, after 20k rows, it is at 17.7845 seconds. There is some variability but this is the overall trend.
Then I thought, let us make this a read-only worksheet, since I am only reading. This started out at:
1.58375
4.39663
7.18701
Ok, so it is getting slower but it is low. But then, much later:
189.332
190.481
195.956
200.474
209.422
These are each for reading 100 rows. What the heck? :--)
There are a bunch of columns in the sheet. It goes from A to about EL. But still, 200 seconds excessive. And when I take the read-only off the load_worksheet() call, the times go right back down.
Is this as expected?
The file can be accessed here:
https://opencalaccess.org/misc/RIPA%20Stop%20Data%20_%20Santa%20Clara%202021.xlsx
And here is the relevant code:
from openpyxl import load_workbook
files = [
'/home/ray/Projects/ca_ripa/data/RIPA Stop Data 2019/RIPA Stop Data _ Santa Clara 2019.xlsx'
]
def data_in_row(ws, row_num) -> list:
values = list()
for cell in ws[row_num]:
values.append(cell.value)
return values
if __name__ == '__main__':
# wb = load_workbook(files[0], read_only=True)
wb = load_workbook(files[0])
sheet = wb['RIPA Stop Data _ SC 2019']
line_num = 0
done = False
while not done:
data = data_in_row(sheet, line_num)
if data[0] == '' or data[0] is None:
done = True
else:
values = list()
for datum in data:
value = f"'{datum}'"
values.append(value)
line_num += 1
and so on. But this is all of the code that is reading the xlsx file.