mysqldb需要用pip install mysql来安装
import xlrd
import MySQLdb
import datetime
xlsfile=r'd:\1.xlsx'
book = xlrd.open_workbook(xlsfile)
#获取sheet的数量
count = len(book.sheets())
#设置连接数据库
database = MySQLdb.connect(host='127.0.0.1',user='root',passwd='123456',db='bdastat')
#设置字符集
database.set_character_set('utf8')
cursor = database.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-1):
sheet = book.sheet_by_index(i)
query = """INSERT INTO newtable ( name, age) VALUES ( %s, %s)"""
#循环每一行
for r in range(1, sheet.nrows):
#idseq = sheet.cell(r,0).value
name = sheet.cell(r,0).value
age = 20 #sheet.cell(r,2).value
#dq_datetime = sheet.cell(r,5).value
#读日期这里要处理一下,不然全变成数字了
#dq_datetime_num=xlrd.xldate_as_tuple(sheet.cell(r,5).value,0)
#dq_datetime = '%s/%s/%s' % (dq_datetime_num[0],dq_datetime_num[1],dq_datetime_num[2])
values = (name, age)
#print query,values
cursor.execute(query, values)
cursor.close()
database.commit()
database.close()
endtime=datetime.datetime.now()
print('结束时间:%s'%(endtime))
print('用时:%s 秒'%(endtime-starttime))
把mysql数据库中的内容导出到excel中
import pandas as pd,pymysql
import xlrd
import MySQLdb
import datetime
#连接数据库
sql="select * from newtable"
database = MySQLdb.connect(host='127.0.0.1',user='root',passwd='123456',db='bdastat')
sqldata=pd.read_sql(sql,database)
sqldata.to_excel(r"d:\newtable.xlsx",index=False)