【Python】读取Excel表格动态生成MySQL数据表并插入数据

概述#coding:utf-8importpymysql,xlrdfromsqlalchemyimportcreate_engine,Column,Integer,SmallInteger,Stringfromsqlalchemy.ext.declarativeimportdeclarative_basefromsqlalchemy.ormimportsessionmaker"""读取Excel动态生成数据表/字段，并插入

# Coding: utf-8import pyMysqL, xlrdfrom sqlalchemy import create_engine, Column, Integer, Smallinteger, Stringfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy.orm import sessionmaker"""    读取Excel动态生成数据表/字段，并插入记录"""def make_model(Base, _table_name, table_COMMENT):    class table_model(Base):        __tablename__ = _table_name        ID=Column(Integer,primary_key=True)        # __table_args__ = (Index('index(zone,status)', 'resource_zone', 'resource_status'), \        # {'comment': '压测资源表'})  # 添加索引和表注释        __table_args__ = ({'comment': table_COMMENT})    return table_model"""    连接数据库"""engine = create_engine("MysqL+pyMysqL://root:123456@127.0.0.1/flasktest", enCoding='utf8', echo=True)"""    读取excel"""workbook = xlrd.open_workbook("./table.xls")sheet = workbook.sheet_by_index(0)nrows = sheet.nrowsncols = sheet.ncolstable_name = sheet.cell_value(0,0)table_comment = sheet.cell_value(0,1)"""    创建数据表"""Base = declarative_base()table = make_model(Base, table_name, table_comment)for i in range(0, ncols):    fIEld_name = sheet.cell_value(1, i)    fIEld_type = sheet.cell_value(2, i)    fIEld_comment = sheet.cell_value(3, i).strip().replace('\n', '').replace('\r', '')    print(fIEld_name, fIEld_type, fIEld_comment)    if fIEld_type=="Integer":        setattr(table, fIEld_name, (Column(fIEld_name, Integer, comment=fIEld_comment)))Base.Metadata.create_all(engine)"""    连接数据表，插入数据"""DBSession = sessionmaker(bind=engine)session = DBSession()datas = []for i in range(5, nrows):    data = table()    for j in range(0, ncols):        fIEld_name = sheet.cell_value(1, j)        fIEld_value = sheet.cell_value(i, j)        setattr(data, fIEld_name, fIEld_value)    datas.append(data)session.add_all(datas)session.commit()# show create table xxx;# 查看带注释的表信息

总结

以上是内存溢出为你收集整理的【Python】读取Excel表格动态生成MySQL数据表并插入数据全部内容，希望文章能够帮你解决【Python】读取Excel表格动态生成MySQL数据表并插入数据所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。