python xls import mysql

import xlrd
import pymysql

# Open the workbook and define the worksheet
book = xlrd.open_workbook(r"C:/Users/유저이름/Desktop/통합과일.xls")
sheet = book.sheet_by_name("통합")
# sheet = book.sheet_by_index(0)
# Get the cursor, which is used to traverse the database, line by line
cursor = conn.cursor()
# DB Delete All
sql = 'delete from fruit'
cursor.execute(sql)

# AUTO_INCREMENT 값을 초기화
sql = 'alter table fruit auto_increment=1'
cursor.execute(sql)


# Create the INSERT INTO sql query
query = """INSERT INTO fruit (과일이름, 단가, 수량, 금액) VALUES (%s, %s, %s, %s)"""

# Create a For loop to iterate through each row in the XLS file, starting at row 2 to skip the headers
for r in range(1, sheet.nrows):
      과일이름      = sheet.cell(r,0).value
      단가    = sheet.cell(r,1).value
      수량    = sheet.cell(r,2).value
      금액    = sheet.cell(r,3).value

      # Assign values from each row
      values = (과일이름, 단가, 수량, 금액)

      # Execute sql Query
      cursor.execute(query, values)

# Close the cursor
cursor.close()

# Commit the transaction
conn.commit()

# Close the database connection
conn.close()

# Print results
print("")
print("All Done! Bye, for now.")
print("")
columns = str(sheet.ncols)
rows = str(sheet.nrows)
# print("I just imported " %2B columns %2B " columns and " %2B rows %2B " rows to MySQL!")
print("전체 행 : "+ rows)
print("전체 열 : "+ columns)