sqlalchemy简单使用

sqlalchemy简单使用

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class Filter(Base):
    __tablename__ = 'alcohol'   # 指定表名
    id = Column(Integer, primary_key=True, autoincrement=True)
    id_md = Column("id_md", String(40), index=True, unique=True)
    shop_id = Column("商品ID", String(30), nullable=False)
    room_title = Column("直播场次", String(256), nullable=False)
    begin_time = Column("开始时间", String(30), nullable=False)
    room_finish_time = Column("结束时间", String(30), nullable=False)
    time_long = Column("时长", String(60), nullable=False)
    total_user = Column("观看人次", String(15), nullable=False)
    user_peak = Column("人气峰值", String(15), nullable=False)
    user_value = Column("uv值", String(15), nullable=False)
    product_size = Column("商品数", String(10), nullable=False)
    volume = Column("销量", String(10), nullable=False)
    amount = Column("销售额", String(15), nullable=False)


class MysqlFilter:
    def __init__(self, host='localhost', port=3306, username='root', password='root', db='data'):
        self.username = username
        self.password = password
        self.host = host
        self.port = port
        self.db = db
        self.storage = self.get_storage()

    def get_storage(self):
        """
        返回一个链接对象
        :return:
        """
        try:
            # host = self.db_config.get('mysql').get('host', None)
            # port = self.db_config.get('mysql').get('port', None)
            # db = self.db_config.get('mysql').get('db', None)
            # password = self.db_config.get('mysql').get('password', None)
            # username = self.db_config.get('mysql').get('username', None)
            mysql_url = f'mysql+pymysql://{self.username}:{self.password}@{self.host}:{self.port}/{self.db}?charset=utf8'
            engine = create_engine(mysql_url)
            Base.metadata.create_all(engine)  # 创建表
            session = sessionmaker(bind=engine)
            return session()
        except:
            raise Exception("请确认mysql数据库账号密码和数据库是否配置正确")

    def save(self, item: dict):
        """
        :param hash_value:
        :return:
        """
        session = self.storage
        filter = Filter(
            id_md=item['id_md'],
            shop_id=item['shop_id'],
            room_title=item['room_title'],
            begin_time=item['begin_time'],
            room_finish_time=item['room_finish_time'],
            time_long=item['time_long'],
            total_user=item['total_user'],
            user_peak=item['user_peak'],
            user_value=item['user_value'],
            product_size=item['product_size'],
            volume=item['volume'],
            amount=item['amount']
        )
        session.add(filter)
        session.commit()
        session.close()

    def is_exists(self, item: dict):
        session = self.storage
        ret = session.query(Filter).filter_by(id_md=item['id_md']).first()
        session.close()
        if ret is None:
            return False
        return True