用python将excel中的内容存入mysql

灰太狼 2022-12-26 00:50 128阅读 0赞

将excel中的内容存入mysql，代码如下：

# -*- encoding: utf-8 -*-
    import time
    import xlrd
    import hashlib
    
    from sqlalchemy.orm import sessionmaker
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy import create_engine, Column, Integer, TIMESTAMP, CHAR, VARCHAR
    
    # mysql数据库
    ENGINE = create_engine('mysql+pymysql://wxx:654321@***/weixx?charset=utf8')
    # ENGINE = create_engine('mysql+pymysql://cuh:123456@***/zk?charset=utf8')
    DBSession = sessionmaker(bind=ENGINE)
    session = DBSession()
    Base = declarative_base()
    
    
    class YonghuiGoodsInfoItem(Base):
        """永辉超市部分商品表"""
        __tablename__ = 'yonghui_goods_info'
        id = Column(Integer, primary_key=True, autoincrement=True)
        sha = Column(CHAR(40), unique=True)
        crawled_time = Column(TIMESTAMP, doc={ 'zh': '数据存入的时间'})
        shop_name = Column(VARCHAR(200), doc={ 'zh': '超市名称'})
        goods_id = Column(VARCHAR(200), doc={ 'zh': '商品id'})
        goods_name = Column(VARCHAR(200), doc={ 'zh': '商品名称'})
        goods_category = Column(VARCHAR(200), doc={ 'zh': '商品分类'})
        goods_price = Column(VARCHAR(200), doc={ 'zh': '商品价格'})
        goods_stock = Column(VARCHAR(200), doc={ 'zh': '商品库存'})
        goods_unit = Column(VARCHAR(200), doc={ 'zh': '商品单位'})
        goods_image = Column(VARCHAR(200), doc={ 'zh': '商品图片url'})
        goods_desc = Column(VARCHAR(200), doc={ 'zh': '商品描述'})
    
        @classmethod
        def creat_table(cls):
            """创建数据表"""
            cls.__table__.create(bind=ENGINE, checkfirst=True)
    
    
    def insert():
        """插入数据"""
        wb = xlrd.open_workbook(filename='yonghui.xls')  # 打开文件
        sheet1 = wb.sheet_by_index(0)  # 通过索引获取表格sheet1.nrows
        for i in range(1, sheet1.nrows):
            rows = sheet1.row_values(i)  # 获取行内容
            shop_name = rows[1]
            goods_id = rows[4]
            goods_name = rows[5]
            goods_category = rows[6]
            goods_price = rows[7]
            goods_stock = rows[9]
            goods_unit = rows[10]
            goods_image = rows[11]
            goods_desc = rows[12]
            time_now = time.strftime("%Y-%m-%d %H:%M:%S")
            sha = get_hash(goods_id+goods_name)
            print(goods_id, goods_name)
            try:
                result = session.query(YonghuiGoodsInfoItem).filter(YonghuiGoodsInfoItem.sha == sha).first()
                if not result:
                    item = YonghuiGoodsInfoItem(sha=sha,
                                                crawled_time=time_now,
                                                shop_name=shop_name,
                                                goods_id=goods_id,
                                                goods_name=goods_name,
                                                goods_category=goods_category,
                                                goods_price=goods_price,
                                                goods_stock=goods_stock,
                                                goods_unit=goods_unit,
                                                goods_image=goods_image,
                                                goods_desc=goods_desc)
                    session.add(item)
                else:
                    result.crawled_time = time_now
                    result.sha = sha
                session.commit()
                print(f'时间:{time_now}, cookie:{goods_name} 已存入数据库！')
            except BaseException as e:
                session.rollback()
                session.close()
                raise BaseException(f"数据插入失败: {e}")
    
    
    def get_hash(hash_str):
        hash = hashlib.sha1()
        hash.update(str(hash_str).encode('utf-8'))
        url_sha = hash.hexdigest()
        return url_sha
    
    
    if __name__ == '__main__':
        table = YonghuiGoodsInfoItem()
        table.creat_table()
        insert()