python 读取excel数据到mysql

#!/usr/bin/env python

# -*- coding: utf-8 -*-

import MySQLdb

import os

import sys

import re

reload(sys)

sys.setdefaultencoding( "utf-8" )

import time

import datetime

today=datetime.date.today()

oneday=datetime.timedelta(days=1)

to_yes=today-oneday

yesterday=to_yes.strftime('%Y%m%d')

currentDate=time.strftime('%Y%m%d',time.localtime())

import MySQLdb

import xlrd

from openpyxl import Workbook

from openpyxl.compat import range

xlsfile=r'C:\Users\cherry\Desktop\defriend_0\aaaaa.xlsx'

book=xlrd.open_workbook(xlsfile)

count=len(book.sheets())

print count

conn = MySQLdb.connect(host='192.168.10.70', user='dlan', passwd='root123', db='yy_access', charset="utf8")

conn.set_character_set('utf8')

cursor = conn.cursor()

cursor.execute('SET NAMES utf8;')

cursor.execute('SET CHARACTER SET utf8;')

cursor.execute('SET character_set_connection=utf8;')

starttime = datetime.datetime.now()

print '开始时间：%s' % (starttime)

#读取sheet数量

for i in range(0,count):

   print i

   sheet=book.sheet_by_index(i)

   print sheet

   query="""insert into yy_access.ca_user_phone_score(phone_number,score,notic)values(%s,%s,%s)"""

   ##循环每一行，不包含标题

   for r in range(1,sheet.nrows):

       phone_number = sheet.cell(r, 0).value

       score = sheet.cell(r, 1).value

       notic= sheet.cell(r, 2).value

       values=(phone_number,score,notic)

       print values,query

       cursor.execute(query,values)


cursor.close()

conn.commit()

conn.close()

endtime=datetime.datetime.now()

print '结束时间:%s' % (endtime)

print '用时：%s 秒' % (endtime-starttime)

本文转自 DBAspace 51CTO博客，原文链接:
http://blog.51cto.com/dbaspace/1914846