python SqlAlchemy+xlrd 读取excel入库小记

user.py:
from sqlalchemy import create_engine
from sqlalchemy import Table,MetaData

engine = create_engine('mysql+mysqldb://root:password@localhost:3306/test?charset=utf8')
metaData=MetaData(bind=engine)
class BUser:
    userTable=None
    @classmethod
    def getUserTable(cls):
        cls.userTable=Table('user',metaData,autoload=True)#拿到表
        return cls.userTable
    @classmethod
    def addUser(cls,list):#入库操作
        i=cls.userTable.insert()
        print i
        conn=engine.connect()
        conn.execute(i,list)

test.py:

from xlrd import open_workbook
from user import BUser
data=open_workbook(r'E:\testE.xlsx',encoding_override='utf-8')
table =data.sheets()[0]#获取第一签页
rows=table.nrows#行数
cols=table.ncols#列数
colnames=table.row_values(0)#获取第一行一般是类别名字
mUser=BUser()
mUser.getUserTable()
list=[]#在当前代码里仅作为查看数据方便使用
for num in range(1,rows):#第二行一般是源数据 开始读取
    row=table.row_values(num)
    app ={}
    for i in range(len(colnames)):
        app[colnames[i]] =row[i]
    list.append(app)
    mUser.addUser(app)
print list

 

 

转载于:https://my.oschina.net/FatWhite/blog/727064