SQLAlchemy related usage
The biggest benefits of SQLAlchemy are the clarity of structure and the fact that migrating the database does not create too much redundancy. But the disadvantage is not as functional as pure SQL, nor as convenient as pure SQL. But the biggest benefit of SQLAlchmey for developers is that it is more intuitive to read the code.
This article mainly focuses on the pits I have encountered in using SQLAlchemy over the years, and how to do it, as well as the mistakes that are easy to encounter at the beginning of sprout. I hope this article can give me some personal accumulation.
How do I build a link
In the case of MySQL, notice that the password does not include an @ sign
from flask import Flask from flask_sqlalchemy import SQLAlchemy app = Flask(__name__) db_url = 'mysql+pymysql://username:password@localhost:port/datebase' app.config['SQLALCHEMY_DATABASE_URI'] = db_url App. config[" SQLAlchemy_COMMIT_ON_TEARDOWN "] = True # Automate commit app.config[' SQLAlchemy_echo '] = False # If True when printing out SQL log db = SQLAlchemy(app)
How to create a table that supports inherited refactoring
class BaseRoom(db.Model): Id = db.Column(db.String(50)), primary_key=True, Third_id = db.Column(db.String(50), nullable=False, comment=' site id') title = db.Column(db.String(500), Nullable =False, comment=' title ') price = db.Column(db.Float(asDecimal =True), nullable=False, Class DAXIANGROOM (BaseRoom): __table__ =' DAXIANG_DAXIANGROOM '= {" useExisting ": True} landlord_id = db.Column(db.ForeignKey('daxiang_daxianglandlord.id'), index=True) landlord = db.relationship('DaxiangLandlord', primaryjoin='DaxiangRoom.landlord_id == DaxiangLandlord.id', backref='daxiang_daxiangrooms')
Conduct a session commit, search, and update
Class CtripRoomSqlAlchemyPipeline (object) : # in each database update or write to def process_item (self, item, spiders) : model = CtripRoom(hotel_id=item['hotel_id'], hotel_name=item['hotel_name'], city=item['city'], location=item['location'], type=item['type'], score=item['score'] ) try: db.session.add(model) db.session.commit() except Exception as e: # print(e) db.session.rollback() pass return item class lukeRoomUpdateSqlAlchemyPipeline(object): SQL def process_item(self, item, spider) landlord_id = item['landlord_id'] model = LukeRoom.query.filter_by(id=item['house_id']).first() model.landlord_id = landlord_id model.is_finished = 1 db.session.add(model) db.session.commit()
Use SQLAlchemy to implement ON DUPLICATE KEY UPDATE
from dbs.database import db from sqlalchemy.dialects.mysql import insert token = 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") # insert COMMENT_MODEL = insert(User).values( id=_id, platform=platform, city=city, token=token, user_id=user_id, create_time=_time, 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()