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

「爱情、让人受尽委屈。」 2022-11-17 13:46 223阅读 0赞

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhaWR1XzI2NjQ2MTI5_size_16_color_FFFFFF_t_70]

# coding: utf-8
    import pymysql, xlrd
    from sqlalchemy import create_engine, Column, Integer, SmallInteger, String
    from sqlalchemy.ext.declarative import declarative_base
    from 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.nrows
    ncols = sheet.ncols
    table_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;
    # 查看带注释的表信息

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhaWR1XzI2NjQ2MTI5_size_16_color_FFFFFF_t_70]: /images/20221022/48e12c0e416c4ca6a97e2d57268fa75b.png