sqlAlchemy操作
表模型定义
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, FLOAT
from sqlalchemy.orm import sessionmaker
# 数据库连接配置
host = mysql_config.get("HOST", 'localhost')
port = mysql_config.get("PORT", 3306)
user = mysql_config.get("USER", "root")
pass_wd = mysql_config.get("PASSWD", "123456")
db_name = mysql_config.get("DBNAME", "pdd")
engine = create_engine(f"mysql+pymysql://{user}:{pass_wd}@{host}:{port}/{db_name}?charset=utf8")
Base = declarative_base()
class TradingSituationModel(Base):
# 表模型 用来定义字段
# __tablename__ 定义要创建的表名 comment 字段注释
__tablename__ = "spt_trading_situation"
id = Column(Integer, primary_key=True)
company_name = Column("company_name", String(54), comment="公司名")
shop_keeper = Column("shop_keeper", String(54), comment="店长")
shop_url = Column("shop_url", String(54), comment="店铺url")
crawl_time = Column("crawl_time", String(64),comment="采集时间")
created_at = Column("created_at", Integer,comment="创建时间")
updated_at = Column("updated_at", Integer,comment="更新时间")
is_deleted = Column("is_deleted", Integer,comment="软删除 1 是 0 否")
建表 数据查找 以及数据更新
class TradingSituationManager():
def __init__(self):
Session = sessionmaker(bind=engine) # 创建会话对象 用于数据表的操作
self.model = TradingSituationModel
self.session = Session()
Base.metadata.create_all(engine)
def handle_result(self, item):
try:
filter = self.session.query(self.model).filter_by(
shop_id=item["shop_id"], # 这里填写过滤条件
)
filter_res = filter.first()
if filter_res:
# 如果存在索引字段查询的数据 就更新数据 不需要更新的字段这里直接pop掉即可
for key in item:
setattr(filter_res, key, item[key])
else:
# 数据不存在索引字段插叙你的数据,新增一条数据
jobs = self.model(**item)
self.session.add(jobs)
# 可以设置是否自动提交
self.session.commit()
except:
traceback.print_exc()
self.session.rollback()