python excel 파일을 mysql 올리기

 

myjamong_mysql.py

0.00MB

myjamong.tistory.com/53

위 공부해야 됨

import excelrd
import pymysql


# Open the workbook and define the worksheet
book = excelrd.open_workbook('client.xls',"rb")
sheet = book.sheet_by_name('Sheet1')

# Establish a MySQL connection
database = pymysql.connect(host="localhost",
user="root",
passwd="비밀번호",
db="my_bank")

# Get the cursor, which is used to traverse the database, line by line
cursor = database.cursor()

# Create the INSERT INTO sql query
query = """INSERT INTO client ( name, address, tel) VALUES ( %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):
# idclient = sheet.cell(r, ).value
name = sheet.cell(r, 1).value
address = sheet.cell(r, 2).value
tel = sheet.cell(r, 3).value

# Assign values from each row
values = (name, address, tel)

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

# Close the cursor
cursor.close()

# Commit the transaction
database.commit()

# Close the database connection
database.close()

# Print results
columns = str(sheet.ncols)
rows = str(sheet.nrows)
print(rows +
" 행 " + columns + " 열 " + "MySQL에 import 했습니다!")