sqlalchemy相关用法sqlalchemy最大的优点就是结构清晰，数据库的迁移不会造成过多的冗余。但缺点是没有纯sql那么多的功能，也没有纯sql那么方便。但是对于开发者来说，sqlalchmey最大的优势就是阅读代码更加直观。这篇文章主要讲述我这些年在使用sqlalchemy中遇到的一些陷阱，如何去做，以及刚入门时容易遇到的错误。希望这篇文章能给我一些个人的积累。如何建立链接以MYSQL为例，注意不要包含@fromflaskimportFlaskfromflask_sqlalchemyimportSQLAlchemyapp=Flask(__name__)db_url='mysql+pymysql://username:password@localhost:port/datebase'app.config['SQLALCHEMY_DATABASE_URI']=db_urlapp.config["SQLALCHEMY_COMMIT_ON_TEARDOWN"]=True#自动提交app.config['SQLALCHEMY_ECHO']=False#如果为True则打印出sql日志db=SQLAlchemy(app)howtocreatea数据表并支持继承重构classBaseRoom(db.Model):__abstract__=True#生命跳过生成此类tableid=db.Column(db.String(50),primary_key=True,comment='user'sid')third_id=db.Column(db.String(50),nullable=False,comment='网站的id')title=db.Column(db.String(500),nullable=False,comment='title')价格=分贝。Column(db.Float(asdecimal=True),nullable=False,comment='price')classDaxiangRoom(BaseRoom):__tablename__='daxiang_daxiangroom'#表名__table_args__={"useexisting":True}landlord_id=db.Column(db.ForeignKey('daxiang_daxianglandlord.id'),index=True)楼主=db.relationship('DaxiangLandlord',primaryjoin='DaxiangRoom.landlord_id==DaxiangLandlord.id',backref='daxiang_daxiangrooms')提交会话，查找并更新类CtripRoomSqlAlchemyPipeline(object):#更新或写入每一行到数据库defprocess_item(self,item,spider):model=CtripRoom(hotel_id=item['hotel_id'],hotel_name=item['hotel_name'],city=item['city'],location=item['位置'],type=item['type'],score=item['score'])try:db.session.add(model)db.session.commit()exceptExceptionase:#print(e)db.session.rollback()通过returnitemclasslukeRoomUpdateSqlAlchemyPipeline(object):#更新sqldefprocess_item(self,item,spider):landlord_id=item['landlord_id']model=LukeRoom.query.filter_by(id=item['house_id']).first()model.landlord_id=landlord_idmodel.is_finished=1db.session.add(model)db.session。commit()使用sqlalchemy实现重复密钥更新fromdbs.databaseimportdbfromsqlalchemy.dialects.mysqlimportinserttoken=content['token']city=content['city']account=content['account']platform='airbnb'user_id=content['user_id']_id=platform+'_'+content['user_id']_time=datetime.now(timezone('Asia/Shanghai')).strftime("%Y-%m-%d%H:%M:%S")#插入的数据comment_model=insert(User).values(id=_id,platform=platform,city=city,token=token,user_id=user_id,create_time=_time,account=account)#出现冲突后需要更新的数据do_update_comment_model=comment_model.on_duplicate_key_update(id=_id,platform=platform,city=city,token=token,user_id=user_id,create_time=_time,account=account,user_status='')db.session.execute(do_update_comment_model)db.session.commit()
