python读取Excel到Mysql

最近在做python相关的一些操作，在这里记录一下，以备忘记：

#coding=utf-8import xlrdimport MySQLdbbook = xlrd.open_workbook("d://pythonTest/pytest.xlsx")sheet = book.sheet_by_index(0)#建立mysql连接database = MySQLdb.connect(host='10.23.163.45',user='root',passwd='passwd',db='schedule_ad_v2',port=3306,use_unicode=True,charset="utf8")# 获得游标对象, 用于逐行遍历数据库数据cursor = database.cursor()# 创建插入SQL语句query = """insert into operation_to_log_info (id,uri,operation_type,operation_name,manager) values(%s,%s,%s,%s,%s)"""# 转换字符串def change(var):if var == u'记录具体参数':return 'detail'if var == u'忽略':return 'ignore'else:return 'visit'def excel_insert_data():i = 0# 创建一个for循环迭代读取xls文件每行数据的, 从第二行开始是要跳过标题for rx in range(1,sheet.nrows):id = iuri = sheet.cell(rx,1).valueoperation_type = change(sheet.cell(rx,2).value)operation_name = sheet.cell(rx,3).valuemanager = sheet.cell(rx,4).valuevalues = (id,uri,operation_type,operation_name,manager)cursor.execute(query,values)i = i+1def excel_close():# 关闭游标cursor.close()# 提交database.commit()# 关闭数据库连接database.close()def main():excel_insert_data()excel_close()print u"Done!恭喜"if __name__ == "__main__":main()