mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]

pip install flask_sqlalchemy

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
# 設(shè)置數(shù)據(jù)庫(kù)鏈接地址
app.config['SQLALCHEMY_DATABASE_URI']='mysql+pymysql://root:123456@127.0.0.1:3306/test'
# 設(shè)置顯示底層執(zhí)行的sql語(yǔ)句
app.config['SQLALCHEMY_ECHO'] = True
# 初始化組件對(duì)象，關(guān)聯(lián)flask應(yīng)用
db = SQLAlchemy(app)

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import datetime
app = Flask(__name__)
# 設(shè)置數(shù)據(jù)庫(kù)鏈接地址
app.config['SQLALCHEMY_DATABASE_URI']='mysql+pymysql://root:123456@127.0.0.1:3306/test'
# 設(shè)置顯示底層執(zhí)行的sql語(yǔ)句
app.config['SQLALCHEMY_ECHO'] = True
# 初始化組件對(duì)象，關(guān)聯(lián)flask應(yīng)用
db = SQLAlchemy(app)
# 構(gòu)建模型類(lèi)
class User(db.Model):
    # 設(shè)置表名，表名默認(rèn)為類(lèi)名小寫(xiě)
    __tablename__ = 'user'
    id = db.Column(db.Integer,primary_key=True)   # 設(shè)置主鍵，默認(rèn)自增
    name = db.Column(db.String(20))
    age = db.Column(db.Integer,default=20)  #設(shè)置默認(rèn)值
    createtime=db.Column(db.DateTime())
    def __repr__(self):  # 自定義 交互模式 & print() 的對(duì)象打印
      return "(%s, %s, %s, %s)" % (self.id, self.name, self.age,self.createtime)
if __name__ == '__main__':
    app.run(debug=True)

@app.route("/initDB")
def init_db():
    # 刪除所有繼承睡在db.Model的表
    db.drop_all()
    # 創(chuàng)建所有繼承自 db.Model的表
    db.create_all()
    return "initdb"

@app.route("/add")
def add():
    # 添加數(shù)據(jù)
    user1 = User(name='zhao', age=33, createtime=datetime.datetime.now())
    # 將模型添加到會(huì)話中
    db.session.add(user1)
    # 添加多條記錄
    # db.session.add_all([user1,user2,user3])
    # 提交會(huì)話， sqlalchemy會(huì)自動(dòng)創(chuàng)建隱式事務(wù)，失敗會(huì)自動(dòng)回滾
    db.session.commit()
    return "add" 

@app.route("/query")
def query():
    # 查詢(xún)所有用戶(hù)數(shù)據(jù), 返回User列表,
    data = User.query.all()
    for item in data:
        print(item.name,item.age)
    # 查詢(xún)有多少個(gè)用戶(hù)，返回結(jié)果數(shù)
    data = User.query.count()
    print(data)
    # 查詢(xún)第1個(gè)用戶(hù) ， 返回User對(duì)象
    data = User.query.first()
    print(data)
    # 查詢(xún)id為4的用戶(hù)， 返回User對(duì)象
    data = User.query.get(4)
    print(data)
    return "query"

@app.route("/query2")
def query2():
    # 查詢(xún) name 等于 zhao 的記錄集中第一條記錄
    data = User.query.filter(User.name =='zhao').first()
    # 查詢(xún) name 不等于 zhao 的所有記錄
    data = User.query.filter(User.name != 'zhao').all()
    # 前模糊查詢(xún)
    data = User.query.filter(User.name.like("%zhao")).all()
    print(data)
    # 查詢(xún)名字以 zhao 開(kāi)頭的用戶(hù)
    data = User.query.filter(User.name.startswith('zhao')).all()
    # 查詢(xún)名字是 zhao，并且年齡是22歲的用戶(hù)
    from sqlalchemy import and_
    data = User.query.filter(and_(User.name=='zhao', User.age==22)).all()
    # 查詢(xún)名字是 zhao或者h(yuǎn)ong的用戶(hù)
    from sqlalchemy import or_
    data = User.query.filter(or_(User.name=='zhao', User.name=='hong')).all()
    # 查詢(xún)id為[1, 2, 3]的用戶(hù)
    data = User.query.filter(User.id.in_([1, 2, 3])).all()
    # 分頁(yè)查詢(xún)
    # 所有用戶(hù)先按年齡從小到大, 再按id從大到小排序, 取前5個(gè)
    data = User.query.order_by(User.age, User.id.desc()).limit(5).all()
    # 分頁(yè)查詢(xún), 每頁(yè)1個(gè), 查詢(xún)第2頁(yè)的數(shù)據(jù)  paginate(頁(yè)碼, 每頁(yè)條數(shù))
    pn = User.query.paginate(page=2, per_page=1)
    print("總頁(yè)數(shù):"+str(pn.pages)+" 當(dāng)前頁(yè)碼:"+str(pn.page)+"當(dāng)前頁(yè)的數(shù)據(jù)："+ str(pn.items)+" 總條數(shù):"+str(pn.total))
    # 分組查詢(xún)
    # 查詢(xún)每個(gè)年齡的人數(shù)    select age, count(name) from t_user group by age  分組聚合
    from sqlalchemy import func
    data = db.session.query(User.age, func.count(User.id).label("count")).group_by(User.age).all()
    return "query2"

@app.route("/update")
def update():
    # 更新用戶(hù)年齡為44，條件是：用戶(hù)名=zhao
    # 相當(dāng)于：update user set age = 44 where name = 'zhao';
    User.query.filter(User.name == 'zhao').update({'age': 44})
    # 提交會(huì)話
    db.session.commit()
    return "update"

@app.route("/del")
def delete():
    # 刪除id=1的用戶(hù)
    # 相當(dāng)于：delete from user where id=1
    User.query.filter(User.id == 1).delete()
    # 提交會(huì)話
    db.session.commit()
    return "del"

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import datetime
app = Flask(__name__)
# 設(shè)置數(shù)據(jù)庫(kù)鏈接地址
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:123456@127.0.0.1:3306/test'
# 設(shè)置顯示底層執(zhí)行的sql語(yǔ)句
app.config['SQLALCHEMY_ECHO'] = True
# 初始化組件對(duì)象，關(guān)聯(lián)flask應(yīng)用
db = SQLAlchemy(app)
# 構(gòu)建模型類(lèi)
class User(db.Model):
    # 設(shè)置表名，表名默認(rèn)為類(lèi)名小寫(xiě)
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)  # 設(shè)置主鍵，默認(rèn)自增
    name = db.Column(db.String(20))
    age = db.Column(db.Integer, default=20)  # 設(shè)置默認(rèn)值
    createtime = db.Column(db.DateTime())
    def __repr__(self):  # 自定義 交互模式 & print() 的對(duì)象打印
        return "(%s, %s, %s, %s)" % (self.id, self.name, self.age, self.createtime)
class Address(db.Model):
    __tablename__ = 'address'
    id = db.Column(db.Integer, primary_key=True)
    detail = db.Column(db.String(20))
    user_id = db.Column(db.Integer)  # 定義外鍵
if __name__ == '__main__':
    app.run(debug=True)
@app.route("/")
def index():
    return "index"
@app.route("/query")
def query():
    """ 查詢(xún)多表數(shù)據(jù)  需求: 查詢(xún)姓名為"hong" 的用戶(hù)id和地址信息"""
    # sqlalchemy的join查詢(xún)
    # 相當(dāng)于：SELECT user.id AS user_id, address.detail AS address_detail
    #           FROM user INNER JOIN address ON user.id = address.user_id
    data = db.session.query(User.id, Address.detail).join(Address, User.id == Address.user_id).filter(
        User.name == 'hong').all()
    for item in data:
        print(item.detail, item.id)
    return "query"