python把excel内容和mysql数据库的互导

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)