How to go through my entire Excel sheet and get each cell value?-CodePudding

I am trying to get values from an xlsx Excel sheet, and input those values into a SQL Insert statement.

For example, I have this as the first 3 rows of my Excel sheet.

Id   Title          Make    Model       isCurrent
1    Red Ranger     Ford    Ranger XLT  1
2    White CRV      Honda   CRV Sport   0
3    Black Corolla  Toyota  Corolla LE  1

And I want to generate SQL Insert statements that look like this:

INSERT INTO CurrentOffers(Id, Title, Make, Model, isCurrent)
VALUES (1, 'Red Ranger', 'Ford', 'Ranger XLT', 1)

I got my Python script to write out the sheet using this:

import pandas as pd

df = pd.read_excel('July_Sales.xlsx')

print(df)

print(df.iloc[0, 0])

That gives me the entire sheet and then it prints the value of the first cell in the first row.

But I can't figure out how to loop through the whole sheet and generate my SQL Insert statements.

To get the value in each cell from each row, do I need to use another library?

Thanks!

CodePudding user response：

Instead of looping through everything, could you not just insert via DataFrame.to_sql?

If that isn't an option, you could also use DataFrame.itertuples like this:

print('INSERT INTO CurrentOffers(Id, Title, Make, Model, isCurrent) VALUES')
for row in df.itertuples(index=False):
    print(f'{tuple(row)}')