python 读取excel数据到mysql-DBAspace
#!/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)
页:
[1]