https:\/\/flask-sqlalchemy.palletsprojects.com\/en\/2.x\/quickstart\/<\/a><\/p>\n\n\n\n\u5b89\u88c5 flask-sqlalchemy\u3010\u6e05\u534e\u6e90\u3011<\/p>\n\n\n\n
pip install flask-sqlalchemy -i https:\/\/pypi.tuna.tsinghua.edu.cn\/simple<\/pre>\n\n\n\n\u5982\u679csqlalchemy\u8fde\u63a5\u7684\u662f mysql \/MariaDB\u6570\u636e\u5e93\uff0c\u9700\u8981\u5b89\u88c5 mysqldb \u6216pymysql\u9a71\u52a8<\/strong><\/p>\n\n\n\nconda install flask-mysqldb -c conda-forge<\/pre>\n\n\n\n\u5b89\u88c5flask-mysqldb\u65f6\uff0c\u6ce8\u610f<\/p>\n\n\n\n
\u4f7f\u7528pip install \u5b89\u88c5 flask-mysqldb\u7684\u65f6\u5019\uff0cpython\u5e95\u5c42\u4f9d\u8d56\u4e8e\u4e00\u4e2a\u5e95\u5c42\u7684\u6a21\u5757 mysqlclient \u6a21\u5757
\u5982\u679c\u6ca1\u6709\u8fd9\u4e2a\u6a21\u5757\uff0c\u5219\u4f1a\u62a5\u9519\u5982\u4e0b\uff1a
\u200b
Command \"python setup.py egg_info\" failed with error code 1 in \/tmp\/pip-install-21hysnd4\/mysqlclient\/<\/pre>\n\n\n\n\u89e3\u51b3\u65b9\u6848\uff1a<\/p>\n\n\n\n
sudo apt-get install -y libmysqlclient-dev python3-dev
\u200b
# \u8fd0\u884c\u4e0a\u9762\u7684\u5b89\u88c5\u547d\u4ee4\u5982\u679c\u518d\u6b21\u62a5\u9519\u5982\u4e0b\uff1a
# dpkg \u88ab\u4e2d\u65ad\uff0c\u60a8\u5fc5\u987b\u624b\u5de5\u8fd0\u884c \u2018sudo dpkg --configure -a\u2019 \u89e3\u51b3\u6b64\u95ee\u9898\u3002
\u200b
# \u5219\u6839\u636e\u63d0\u793a\u6267\u884c\u547d\u4ee4\u4ee5\u4e0b\u547d\u4ee4\uff0c\u518d\u6b21\u5b89\u88c5mysqlclient
# sudo dpkg --configure -a
# apt-get install libmysqlclient-dev python3-dev
\u200b
\u89e3\u51b3\u4e86mysqlclient\u95ee\u9898\u4ee5\u540e\uff0c\u91cd\u65b0\u5b89\u88c5 flask-mysqldb\u5373\u53ef\u3002
pip install flask-mysqldb -i https:\/\/pypi.tuna.tsinghua.edu.cn\/simple<\/pre>\n\n\n\n\u539f\u751fSQLAlchemy\u8fde\u63a5\u914d\u7f6e\u64cd\u4f5c<\/p>\n\n\n\n
db.py\uff0c\u4ee3\u7801\uff1a<\/p>\n\n\n\n
from sqlalchemy import create_engine # \u9a71\u52a8\u5f15\u64ce
from sqlalchemy.ext.declarative import declarative_base # \u6570\u636e\u5e93\u57fa\u7c7b
from sqlalchemy import Column, Integer, String, Boolean, Numeric, Text # \u5b57\u6bb5\u3001\u6574\u578b
from sqlalchemy.orm import sessionmaker # \u8fde\u63a5\u4f1a\u8bdd
\u200b
engine = create_engine(
# \u8fde\u63a5\u6570\u636e\u5e93\u7684URL
# url=\"mysql+pymysql:\/\/root:123@127.0.0.1:3306\/students?charset=utf8mb4\", # \u5982\u679c\u5e95\u5c42\u9a71\u52a8\u662fpymysql
url=\"mysql:\/\/root:123@127.0.0.1:3306\/students?charset=utf8mb4\", # \u5982\u679c\u5e95\u5c42\u9a71\u52a8\u662fMysqlDB
echo=True, # \u5f53\u8bbe\u7f6e\u4e3aTrue\u65f6\u4f1a\u5c06orm\u8bed\u53e5\u8f6c\u5316\u4e3asql\u8bed\u53e5\u6253\u5370\uff0c\u4e00\u822cdebug\u7684\u65f6\u5019\u53ef\u7528
pool_size=8, # \u8fde\u63a5\u6c60\u7684\u5927\u5c0f\uff0c\u9ed8\u8ba4\u4e3a5\u4e2a\uff0c\u8bbe\u7f6e\u4e3a0\u65f6\u8868\u793a\u8fde\u63a5\u65e0\u9650\u5236
pool_recycle=60*30 # \u8bbe\u7f6e\u65f6\u95f4\u4ee5\u9650\u5236\u6570\u636e\u5e93\u591a\u4e45\u6ca1\u8fde\u63a5\u81ea\u52a8\u65ad\u5f00
)
\u200b
DbSession = sessionmaker(bind=engine)
session = DbSession()
\u200b
# \u521b\u5efa\u6570\u636e\u57fa\u7c7b
Model = declarative_base()<\/pre>\n\n\n\nmanage.py\uff0c\u4ee3\u7801\uff1a<\/p>\n\n\n\n
import db
class Student(db.Model):
__tablename__ = \"tb_student\"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(255))
sex = db.Column(db.Boolean)
age = db.Column(db.Integer)
class_name = db.Column(\"class\", db.String(255),)
description = db.Column(db.Text)
is_delete = db.Column(db.Boolean, nullable=True, default=False)
\u200b
if __name__ == '__main__':
# \u5982\u679c\u6ca1\u6709\u63d0\u524d\u58f0\u660e\u6a21\u578b\u4e2d\u7684\u6570\u636e\u8868\uff0c\u5219\u53ef\u4ee5\u91c7\u7528\u4ee5\u4e0b\u4ee3\u7801\u751f\u6210\u6570\u636e\u8868\uff0c
# \u5982\u679c\u6570\u636e\u5e93\u4e2d\u5df2\u7ecf\u58f0\u660e\u4e86\u6709\u6570\u636e\u8868\uff0c\u5219\u4e0d\u4f1a\u7ee7\u7eed\u751f\u6210
db.Model.metadata.create_all(db.engine)
\u200b
# \u6839\u636eID\u67e5\u8be2\u4e00\u6761\u6570\u636e
# ret = db.session.query(Student).get(19)
# print(ret)
# print(ret.name)
# print(ret.class_name)
#
# # \u67e5\u8be2\u6240\u6709\u6570\u636e
# data_list = db.session.query(Student).all()
# print(data_list)
# for data in data_list:
# print(data.name)
\u200b
# # \u6309\u6761\u4ef6\u67e5\u8be2
# data_list = db.session.query(Student).filter(Student.sex==True, Student.class_name=='301').all()
# print(data_list)
# for data in data_list:
# print(data.sex, data.name)
\u200b
# \u6dfb\u52a0\u4e00\u6761\u6570\u636e
# student = Student(
# name=\"xiaohua\",
# class_name=\"305\",
# sex=False,
# age=18,
# description=\"\u7f8e\u7f8e\u7684..\",
# )
# db.session.add(student)
# db.session.commit()
#
\u200b
# # \u6dfb\u52a0\u591a\u6761\u6570\u636e
# student_list = [
# Student(name=\"xiaohei\", class_name=\"305\", sex=False, age=18, description=\"\u7f8e\u7f8e\u7684..\",),
# Student(name=\"xiaobai\", class_name=\"304\", sex=True, age=18, description=\"\u7f8e\u7f8e\u7684..\",),
# Student(name=\"xiaohui\", class_name=\"303\", sex=False, age=18, description=\"\u7f8e\u7f8e\u7684..\",),
# ]
#
# db.session.add_all(student_list)
# db.session.commit()
\u200b
# \u66f4\u65b0\u4e00\u6761\u6570\u636e
# student = db.session.query(Student).get(35)
# student.name = \"\u5c0f\u4f1a\"
# db.session.commit()
\u200b
# \u66f4\u65b0\u591a\u6761\u6570\u636e
# db.session.query(Student).filter(Student.class_name==\"303\").update({Student.age:Student.age+1})
# db.session.commit()
\u200b
\u200b
# \u5220\u9664\u4e00\u6761\u6570\u636e
# student = db.session.query(Student).get(35)
# db.session.delete(student)
# db.session.commit()
\u200b
# \u5220\u9664\u591a\u6761\u6570\u636e
db.session.query(Student).filter(Student.class_name == \"401\").delete()
\u200b
# \u539f\u751fSQL\u8bed\u53e5
# \u8bfb
cursor = db.session.execute('select * from tb_student')
# \u4e00\u6761
# data = cursor.fetchone()
# print(data)
\u200b
# \u591a\u6761
# data_list = cursor.fetchall()
# print(data_list)
\u200b
# \u5199[\u6dfb\u52a0\u3001\u5220\u9664\u3001\u4fee\u6539]
cursor = db.session.execute(
'insert into tb_student(name, class, age, sex, description) values(:name, :class, :age, :sex, :description)',
params={
\"name\": \"xiaohong\",
\"class\": \"307\",
\"age\": 19,
\"sex\": 0,
\"description\": \".....\",
})
db.session.commit()
print(cursor.lastrowid) # \u83b7\u53d6\u6700\u540e\u6dfb\u52a0\u7684\u4e3b\u952eID
\u200b
\u200b
\u200b<\/pre>\n\n\n\n\u6570\u636e\u5e93\u8fde\u63a5\u8bbe\u7f6e<\/h3>\n\n\n\n\n- \u5728 Flask-SQLAlchemy \u4e2d\uff0c\u6570\u636e\u5e93\u7684\u94fe\u63a5\u914d\u7f6e\u4fe1\u606f\u4f7f\u7528URL\u6307\u5b9a\uff0c\u800c\u4e14\u7a0b\u5e8f\u4f7f\u7528\u7684\u6570\u636e\u5e93\u5fc5\u987b\u4fdd\u5b58\u5230Flask\u7684 SQLALCHEMY_DATABASE_URI<\/strong> \u914d\u7f6e\u9879\u4e2d<\/li>\n<\/ul>\n\n\n\n
manage.py\uff0c\u4ee3\u7801\uff1a<\/p>\n\n\n\n
# SQLAlchemy\u7684\u94fe\u63a5\u914d\u7f6e\uff1a\"\u6570\u636e\u5e93\u540d:\/\/\u8d26\u6237\u540d:\u5bc6\u7801@\u670d\u52a1\u5668\u5730\u5740:\u7aef\u53e3\/\u6570\u636e\u5e93\u540d\u79f0?\u914d\u7f6e\u53c2\u6570\u9009\u9879\"
app.config[\"SQLALCHEMY_DATABASE_URI\"] = \"mysql:\/\/root:123@127.0.0.1:3306\/flaskdemo?charset=utf8mb4\"
\u200b
\u200b
# \u5982\u679c\u4e0d\u4f7f\u7528mysqldb\u6539\u7528pymysql\uff0c\u5219\u9700\u8981\u5728\u8fde\u63a5\u65f6\u6307\u5b9apymysql
# app.config[\"SQLALCHEMY_DATABASE_URI\"] = \"mysql+pymysql:\/\/root:123@127.0.0.1:3306\/flaskdemo?charset=utf8mb4\"<\/pre>\n\n\n\n\u5176\u4ed6\u8bbe\u7f6e\u9879\uff1a<\/p>\n\n\n\n
# \u52a8\u6001\u8ffd\u8e2a\u4fee\u6539\u8bbe\u7f6e\uff0c\u5982\u672a\u8bbe\u7f6e\u53ea\u4f1a\u63d0\u793a\u8b66\u544a
SQLALCHEMY_TRACK_MODIFICATIONS = True
#\u67e5\u8be2\u65f6\u4f1a\u663e\u793a\u539f\u59cbSQL\u8bed\u53e5
SQLALCHEMY_ECHO = True<\/pre>\n\n\n\n
\n- \u914d\u7f6e\u5b8c\u6210\u9700\u8981\u53bb MySQL \u4e2d\u521b\u5efa\u9879\u76ee\u6240\u4f7f\u7528\u7684\u6570\u636e\u5e93<\/li>\n<\/ul>\n\n\n\n
mysql -uroot -p123
create database flaskdemo charset=utf8mb4;<\/pre>\n\n\n\n\u5e38\u7528\u7684SQLAlchemy\u5b57\u6bb5\u7c7b\u578b<\/h3>\n\n\n\n| \u6a21\u578b\u5b57\u6bb5\u7c7b\u578b\u540d<\/th> | python\u4e2d\u6570\u636e\u7c7b\u578b<\/th> | \u8bf4\u660e<\/th><\/tr><\/thead> |
|---|
Integer<\/strong><\/td>| int<\/td> | \u666e\u901a\u6574\u6570\uff0c\u4e00\u822c\u662f32\u4f4d<\/td><\/tr> | SmallInteger<\/strong><\/td>| int<\/td> | \u53d6\u503c\u8303\u56f4\u5c0f\u7684\u6574\u6570\uff0c\u4e00\u822c\u662f16\u4f4d<\/td><\/tr> | | BigInteger<\/td> | int<\/td> | \u4e0d\u9650\u5236\u7cbe\u5ea6\u7684\u6574\u6570<\/td><\/tr> | | Float<\/td> | float<\/td> | \u6d6e\u70b9\u6570<\/td><\/tr> | Numeric<\/strong><\/td>| decimal.Decimal<\/td> | \u666e\u901a\u6570\u503c\uff0c\u4e00\u822c\u662f32\u4f4d<\/td><\/tr> | String<\/strong><\/td>| str<\/td> | \u53d8\u957f\u5b57\u7b26\u4e32<\/td><\/tr> | Text<\/strong><\/td>| str<\/td> | \u53d8\u957f\u5b57\u7b26\u4e32\uff0c\u5bf9\u8f83\u957f\u6216\u4e0d\u9650\u957f\u5ea6\u7684\u5b57\u7b26\u4e32\u505a\u4e86\u4f18\u5316<\/td><\/tr> | | Unicode<\/td> | unicode<\/td> | \u53d8\u957fUnicode\u5b57\u7b26\u4e32<\/td><\/tr> | | UnicodeText<\/td> | unicode<\/td> | \u53d8\u957fUnicode\u5b57\u7b26\u4e32\uff0c\u5bf9\u8f83\u957f\u6216\u4e0d\u9650\u957f\u5ea6\u7684\u5b57\u7b26\u4e32\u505a\u4e86\u4f18\u5316<\/td><\/tr> | Boolean<\/strong><\/td>| bool<\/td> | \u5e03\u5c14\u503c<\/td><\/tr> | DateTime<\/strong><\/td>| datetime.datetime<\/td> | \u65e5\u671f\u548c\u65f6\u95f4<\/td><\/tr> | | Date<\/td> | datetime.date<\/td> | \u65e5\u671f<\/td><\/tr> | | Time<\/td> | datetime.time<\/td> | \u65f6\u95f4<\/td><\/tr> | | LargeBinary<\/td> | bytes<\/td> | \u4e8c\u8fdb\u5236\u6587\u4ef6\u5185\u5bb9<\/td><\/tr> | Enum<\/strong><\/td>| enum.Enum<\/td> | \u679a\u4e3e\u7c7b\u578b\uff0c\u76f8\u5f53\u4e8edjango\u7684choices\uff0c\u4f46\u662f\u529f\u80fd\u6ca1\u6709choices\u90a3\u4e48\u5f3a\u5927<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n\u5e38\u7528\u7684SQLAlchemy\u5217\u7ea6\u675f\u9009\u9879<\/h3>\n\n\n\n| \u9009\u9879\u540d<\/th> | \u8bf4\u660e<\/th><\/tr><\/thead> |
|---|
| primary_key<\/td> | \u5982\u679c\u4e3aTrue\uff0c\u4ee3\u8868\u5f53\u524d\u6570\u636e\u8868\u7684\u4e3b\u952e<\/td><\/tr> | | unique<\/td> | \u5982\u679c\u4e3aTrue\uff0c\u4e3a\u8fd9\u5217\u521b\u5efa\u552f\u4e00 \u7d22\u5f15\uff0c\u4ee3\u8868\u8fd9\u5217\u4e0d\u5141\u8bb8\u51fa\u73b0\u91cd\u590d\u7684\u503c<\/td><\/tr> | | index<\/td> | \u5982\u679c\u4e3aTrue\uff0c\u4e3a\u8fd9\u5217\u521b\u5efa\u666e\u901a\u7d22\u5f15\uff0c\u63d0\u9ad8\u67e5\u8be2\u6548\u7387<\/td><\/tr> | | nullable<\/td> | \u5982\u679c\u4e3aTrue\uff0c\u5141\u8bb8\u6709\u7a7a\u503c\uff0c\u5982\u679c\u4e3aFalse\uff0c\u4e0d\u5141\u8bb8\u6709\u7a7a\u503c<\/td><\/tr> | | default<\/td> | \u4e3a\u8fd9\u5217\u5b9a\u4e49\u9ed8\u8ba4\u503c<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n\u6570\u636e\u5e93\u57fa\u672c\u64cd\u4f5c<\/h2>\n\n\n\n\n- \u5728SQLAlchemy\u4e2d\uff0c\u6dfb\u52a0\u3001\u4fee\u6539\u3001\u5220\u9664\u64cd\u4f5c\uff0c\u5747\u7531\u6570\u636e\u5e93\u4f1a\u8bdd(sessionSM)\u7ba1\u7406\u3002\n
\n- \u4f1a\u8bdd\u7528 db.session \u8868\u793a\u3002\u5728\u51c6\u5907\u628a\u6570\u636e\u5199\u5165\u6570\u636e\u5e93\u524d\uff0c\u8981\u5148\u5c06\u6570\u636e\u6dfb\u52a0\u5230\u4f1a\u8bdd\u4e2d\u7136\u540e\u8c03\u7528 db.commit() \u65b9\u6cd5\u63d0\u4ea4\u4f1a\u8bdd\u3002<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- \u5728SQLAlchemy \u4e2d\uff0c\u67e5\u8be2\u64cd\u4f5c\u662f\u901a\u8fc7 query \u5bf9\u8c61\u64cd\u4f5c\u6570\u636e\u3002\n
\n- \u6700\u57fa\u672c\u7684\u67e5\u8be2\u662f\u8fd4\u56de\u8868\u4e2d\u6240\u6709\u6570\u636e\uff0c\u4e5f\u53ef\u4ee5\u901a\u8fc7filter\u8fc7\u6ee4\u5668\u8fdb\u884c\u66f4\u7cbe\u786e\u7684\u6570\u636e\u5e93\u67e5\u8be2\u3002<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n
\u6a21\u578b\u7c7b\u5b9a\u4e49<\/h2>\n\n\n\n\u6211\u4eec\u540e\u9762\u4f1a\u628a\u6a21\u578b\u521b\u5efa\u5230\u5355\u72ec\u7684\u6587\u4ef6\u4e2d\uff0c\u4f46\u662f\u73b0\u5728\u6211\u4eec\u5148\u628a\u6a21\u578b\u7c7b\u5199\u5728main.py\u6587\u4ef6\u4e2d\u3002<\/p>\n\n\n\n from flask import Flask
from flask_sqlalchemy import SQLAlchemy
\u200b
app = Flask(__name__)
class Config(object):
DEBUG = True
# \u6570\u636e\u5e93\u94fe\u63a5\u914d\u7f6e = \u6570\u636e\u5e93\u540d\u79f0:\/\/\u767b\u5f55\u8d26\u53f7:\u767b\u5f55\u5bc6\u7801@\u6570\u636e\u5e93\u4e3b\u673aIP:\u6570\u636e\u5e93\u8bbf\u95ee\u7aef\u53e3\/\u6570\u636e\u5e93\u540d\u79f0?charset=\u7f16\u7801\u7c7b\u578b
SQLALCHEMY_DATABASE_URI = \"mysql:\/\/root:123@127.0.0.1:3306\/flaskdemo?charset=utf8mb4\"
# \u52a8\u6001\u8ffd\u8e2a\u4fee\u6539\u8bbe\u7f6e\uff0c\u5982\u672a\u8bbe\u7f6e\u53ea\u4f1a\u63d0\u793a\u8b66\u544a
SQLALCHEMY_TRACK_MODIFICATIONS = False
# ORM\u8fd0\u884c\u65f6\u4f1a\u663e\u793aORM\u751f\u6210\u7684\u539f\u59cbSQL\u8bed\u53e5[\u8c03\u8bd5]
SQLALCHEMY_ECHO = True
\u200b
app.config.from_object(Config)
\u200b
\u200b
\"\"\"\u6a21\u578b\u7c7b\u5b9a\u4e49\"\"\"
db = SQLAlchemy(app=app)
# \u7b49\u540c\u4e8e
# db = SQLAlchemy()
# db.init_app(app) # \u52a0\u8f7d\u914d\u7f6e\u5e76\u5b8c\u6210\u521d\u59cb\u5316\u8fc7\u7a0b
\u200b
class Student(db.Model):
\"\"\"\u5b66\u751f\u4fe1\u606f\u6a21\u578b\"\"\"
# \u58f0\u660e\u4e0e\u5f53\u524d\u6a21\u578b\u7ed1\u5b9a\u7684\u6570\u636e\u8868\u540d\u79f0
__tablename__ = \"tb_student\"
\"\"\"
# \u4f01\u4e1a\u4e2d\uff0c\u5f80\u5f80\u5927\u90e8\u5206\u7684\u516c\u53f8\u4f1a\u51fa\u73b0\u4ee5\u4e0b2\u79cd\u4e0d\u540c\u7684\u6570\u636e\u5e93\u5f00\u53d1\u60c5\u51b5\u3002
# 1. \u4f01\u4e1a\u4e2d\u6709DBA\uff0cDBA\u4f1a\u63d0\u524d\u521b\u5efa\u6570\u636e\u5e93\u548c\u9879\u76ee\u4e2d\u5177\u4f53\u4e1a\u52a1\u7684\u6570\u636e\u8868\u3002
\u4e5f\u5c31\u662f\u8bf4\u6211\u4eec\u4e0d\u9700\u8981\u81ea\u5df1\u624b\u52a8\u5efa\u5e93\u5efa\u8868\uff0c\u53ea\u9700\u8981\u6839\u636e\u6570\u636e\u5e93\u8868\u7ed3\u6784\uff0c\u4f7f\u7528python\u58f0\u660e\u5bf9\u5e94\u7684\u6a21\u578b\u4e0e\u4e4b\u5339\u914d\uff0c\u5c31\u53ef\u4ee5\u64cd\u4f5c\u6570\u636e\u5e93\u4e86\u3002
# 2. \u4f01\u4e1a\u6ca1\u6709DBA\uff0c\u6bd4\u8f83\u5751\u7239\uff1a
# 2.1 \u5f00\u53d1\u4eba\u5458\uff0c\u81ea\u5df1\u624b\u64b8SQL\u8bed\u53e5\uff0c\u624b\u52a8\u5efa\u5e93\u5efa\u8868\u3002
# 2.2 \u5f00\u53d1\u4eba\u5458\uff0c\u7f16\u5199\u6a21\u578b\uff0c\u4f7f\u7528\u6570\u636e\u8fc1\u79fb\uff0c\u624b\u52a8\u5efa\u5e93\u548c\u6570\u636e\u8fc1\u79fb\u5efa\u8868\u3002
# \u539f\u751fSQL\u8bed\u53e5
create table db_student(
id int primary key auto_increment comment \"\u4e3b\u952e\",
name varchar(15) comment \"\u59d3\u540d\",
age smallint comment \"\u5e74\u9f84\",
sex tinyint default 1 comment \"\u6027\u522b\",
email varchar(128) comment \"\u90ae\u7bb1\u5730\u5740\",
money NUMERIC(10,2) default 0.0 comment \"\u94b1\u5305\",
key (name),
unique key (email)
);
# \u5b57\u6bb5\u6839\u636eSQL\u8bed\u53e5\u6765\u58f0\u660e
\"\"\"
id = db.Column(db.Integer, primary_key=True,comment=\"\u4e3b\u952e\")
name = db.Column(db.String(15), index=True, comment=\"\u59d3\u540d\")
age = db.Column(db.SmallInteger, comment=\"\u5e74\u9f84\")
sex = db.Column(db.Boolean, default=True, comment=\"\u6027\u522b\")
email = db.Column(db.String(128), unique=True, comment=\"\u90ae\u7bb1\u5730\u5740\")
money = db.Column(db.Numeric(10,2), default=0.0, comment=\"\u94b1\u5305\")
\u200b
def __repr__(self): # \u76f8\u5f53\u4e8edjango\u7684__str__
return f\"{self.name}<{self.__class__.__name__}>\"
\u200b
\u200b
# \u6240\u6709\u7684\u6a21\u578b\u5fc5\u987b\u76f4\u63a5\u6216\u95f4\u63a5\u7ee7\u627f\u4e8edb.Model
class Course(db.Model):
\"\"\"\u8bfe\u7a0b\u6570\u636e\u6a21\u578b\"\"\"
__tablename__ = \"db_course\"
\"\"\"
# \u539f\u751fSQL\u8bed\u53e5
create table db_course (
id int primary key auto_increment comment \"\u4e3b\u952e\",
name varchar(64) comment \"\u8bfe\u7a0b\",
price NUMERIC(7,2) comment \"\u4ef7\u683c\",
unique (name)
);
# \u5b57\u6bb5\u6839\u636eSQL\u8bed\u53e5\u6765\u58f0\u660e
\"\"\"
id = db.Column(db.Integer, primary_key=True, comment=\"\u4e3b\u952e\")
name = db.Column(db.String(64), unique=True, comment=\"\u8bfe\u7a0b\")
price = db.Column(db.Numeric(7, 2), comment=\"\u4ef7\u683c\")
# repr()\u65b9\u6cd5\u7c7b\u4f3c\u4e8edjango\u7684__str__\uff0c\u7528\u4e8e\u6253\u5370\u6a21\u578b\u5bf9\u8c61\u65f6\u663e\u793a\u7684\u5b57\u7b26\u4e32\u4fe1\u606f
def __repr__(self):
return f\"{self.name}<{self.__class__.__name__}>\"
\u200b
class Teacher(db.Model):
\"\"\"\u8001\u5e08\u6570\u636e\u6a21\u578b\"\"\"
__tablename__ = \"db_teacher\"
\"\"\"
# \u539f\u751fSQL\u8bed\u53e5
create table db_teacher (
id int primary key auto_increment comment \"\u4e3b\u952e\",
name varchar(64) comment \"\u59d3\u540d\",
option enum(\"\u8bb2\u5e08\", \"\u52a9\u6559\", \"\u73ed\u4e3b\u4efb\") comment \"\u804c\u4f4d\",
unique (`name`)
);
# \u5b57\u6bb5\u6839\u636eSQL\u8bed\u53e5\u6765\u58f0\u660e
\"\"\"
id = db.Column(db.Integer, primary_key=True, comment=\"\u4e3b\u952e\")
name = db.Column(db.String(64), unique=True, comment=\"\u59d3\u540d\")
option = db.Column(db.Enum(\"\u8bb2\u5e08\", \"\u52a9\u6559\", \"\u73ed\u4e3b\u4efb\"), default=\"\u8bb2\u5e08\")
\u200b
def __repr__(self):
return f\"{self.name}<{self.__class__.__name__}>\"
\u200b
@app.route(\"\/\")
def index():
return \"ok\"
\u200b
if __name__ == '__main__':
app.run()<\/pre>\n\n\n\n\u6570\u636e\u8868\u64cd\u4f5c<\/h1>\n\n\n\n\u521b\u5efa\u548c\u5220\u9664\u8868<\/h3>\n\n\n\n\u521b\u5efa\u8868<\/p>\n\n\n\n # \u5728\u89c6\u56fe\u5185\u8c03\u7528\uff1a
@app.route(\"\/create\")
def create_table():
db.create_all() # \u4e3a\u9879\u76ee\u4e2d\u88ab\u8bc6\u522b\u7684\u6240\u6709\u6a21\u578b\u521b\u5efa\u6570\u636e\u8868
return \"ok\"
\u200b
\u200b
# \u5728\u89c6\u56fe\u4ee5\u5916\u7684\u5730\u65b9\u8c03\u7528\uff1a
with app.app_context():
# create_all()\u65b9\u6cd5\u6267\u884c\u7684\u65f6\u5019\uff0c\u9700\u8981\u653e\u5728\u6a21\u578b\u7684\u540e\u9762
# \u68c0\u6d4b\u6570\u636e\u5e93\u4e2d\u662f\u5426\u5b58\u5728\u548c\u6a21\u578b\u5339\u914d\u7684\u6570\u636e\u8868\u3002
# \u5982\u679c\u6ca1\u6709\uff0c\u5219\u6839\u636e\u6a21\u578b\u8f6c\u6362\u7684\u5efa\u8868\u8bed\u53e5\u8fdb\u884c\u5efa\u8868\u3002
# \u5982\u679c\u627e\u5230\uff0c\u5219\u4e0d\u4f1a\u8fdb\u884c\u989d\u5916\u5904\u7406
db.create_all()<\/pre>\n\n\n\n\u5220\u9664\u8868<\/p>\n\n\n\n # \u5728\u89c6\u56fe\u5185\u8c03\u7528\uff1a
@app.route(\"\/drop\")
def drop_table():
db.drop_all() # \u4e3a\u9879\u76ee\u4e2d\u88ab\u8bc6\u522b\u7684\u6240\u6709\u6a21\u578b\u5220\u9664\u6570\u636e\u8868
return \"ok\"
\u200b
\u200b
# \u5728\u89c6\u56fe\u4ee5\u5916\u7684\u5730\u65b9\u8c03\u7528\uff1a
with app.app_context():
db.drop_all() # \u614e\u7528\uff0c\u5f88\u7ed9\u529b\u7684\uff01\uff01\u8fd9\u8868\u793a\u5220\u9664\u6570\u636e\u5e93\u4e2d\u6240\u6709\u6a21\u578b\u5bf9\u5e94\u7684\u8868\u3002<\/pre>\n\n\n\n\u4ee3\u7801\uff1a<\/p>\n\n\n\n from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy
\u200b
db = SQLAlchemy()
app = Flask(__name__, template_folder=\"templates\", static_folder=\"static\")
\u200b
# \u914d\u7f6e
app.config.update({
\"DEBUG\": True,
\"SQLALCHEMY_DATABASE_URI\": \"mysql:\/\/root:123@127.0.0.1:3306\/flaskdemo?charset=utf8mb4\",
# \u5982\u679c\u4f7f\u7528pymysql\uff0c\u5219\u9700\u8981\u5728\u8fde\u63a5\u65f6\u6307\u5b9apymysql
# \"SQLALCHEMY_DATABASE_URI\": \"mysql+pymysql:\/\/root:123@127.0.0.1:3306\/flaskdemo?charset=utf8mb4\"
# \u52a8\u6001\u8ffd\u8e2a\u4fee\u6539\u8bbe\u7f6e\uff0c\u5982\u672a\u8bbe\u7f6e\u53ea\u4f1a\u63d0\u793a\u8b66\u544a\uff0c\u8bbe\u7f6eFalse\u5373\u53ef
\"SQLALCHEMY_TRACK_MODIFICATIONS\": False,
# ORM\u6267\u884cSQL\u67e5\u8be2\u65f6\u662f\u54e6\u5426\u663e\u793a\u539f\u59cbSQL\u8bed\u53e5\uff0cdebug\u6a21\u5f0f\u4e0b\u53ef\u4ee5\u5f00\u542f
\"SQLALCHEMY_ECHO\": True,
})
\u200b
db.init_app(app)
\u200b
\u200b
\u200b
class Student(db.Model):
\"\"\"\u5b66\u751f\u7ba1\u7406\"\"\"
__tablename__ = \"db_student\" # \u8868\u540d
# __abstract__ = True # \u62bd\u8c61\u6a21\u578b\uff0c\u6570\u636e\u8fc1\u79fb\/\u5efa\u8868\u7684\u65f6\u5019\uff0c\u4e0d\u4f1a\u8ba4\u4e3a\u8fd9\u662f\u4e00\u4e2a\u6a21\u578b\uff0c\u4e5f\u5c31\u4e0d\u4f1a\u5efa\u8868\uff0c\u5f80\u5f80\u7528\u4e8e\u8bbe\u7f6e\u516c\u5171\u6a21\u578b\uff0c\u4fdd\u5b58\u516c\u5171\u5b57\u6bb5
\"\"\"
# \u4f01\u4e1a\u4e2d\uff0c\u5f80\u5f80\u5927\u90e8\u5206\u7684\u516c\u53f8\u4f1a\u51fa\u73b0\u4ee5\u4e0b2\u79cd\u4e0d\u540c\u7684\u6570\u636e\u5e93\u5f00\u53d1\u60c5\u51b5\u3002
# 1. \u4f01\u4e1a\u4e2d\u6709DBA\uff0cDBA\u4f1a\u63d0\u524d\u521b\u5efa\u6570\u636e\u5e93\u548c\u9879\u76ee\u4e2d\u5177\u4f53\u4e1a\u52a1\u7684\u6570\u636e\u8868\u3002
\u4e5f\u5c31\u662f\u8bf4\u6211\u4eec\u4e0d\u9700\u8981\u81ea\u5df1\u624b\u52a8\u5efa\u5e93\u5efa\u8868\uff0c\u53ea\u9700\u8981\u6839\u636e\u6570\u636e\u5e93\u8868\u7ed3\u6784\uff0c\u4f7f\u7528python\u58f0\u660e\u5bf9\u5e94\u7684\u6a21\u578b\u4e0e\u4e4b\u5339\u914d\uff0c\u5c31\u53ef\u4ee5\u64cd\u4f5c\u6570\u636e\u5e93\u4e86\u3002
# 2. \u4f01\u4e1a\u6ca1\u6709DBA\uff0c\u6bd4\u8f83\u5751\u7239\uff1a
# 2.1 \u5f00\u53d1\u4eba\u5458\uff0c\u81ea\u5df1\u624b\u64b8SQL\u8bed\u53e5\uff0c\u624b\u52a8\u5efa\u5e93\u5efa\u8868\u3002
# 2.2 \u5f00\u53d1\u4eba\u5458\uff0c\u7f16\u5199\u6a21\u578b\uff0c\u4f7f\u7528\u6570\u636e\u8fc1\u79fb\u6216\u8005ORM\u63d0\u4f9b\u5efa\u8868\u65b9\u6cd5\uff0c\u624b\u52a8\u5efa\u5e93\u548c\u6570\u636e\u8fc1\u79fb\u5efa\u8868\u3002
# \u539f\u751fSQL\u8bed\u53e5
create table db_student(
id int primary key auto_increment comment \"\u4e3b\u952e\",
name varchar(15) comment \"\u59d3\u540d\",
age smallint comment \"\u5e74\u9f84\",
sex tinyint(1) default 1 comment \"\u6027\u522b\",
email varchar(255) comment \"\u90ae\u7bb1\u5730\u5740\",
money NUMERIC(10,2) default 0.0 comment \"\u94b1\u5305\",
key (name),
unique key (email)
);
# \u5b57\u6bb5\u6839\u636eSQL\u8bed\u53e5\u6765\u58f0\u660e
\"\"\"
# \u5c5e\u6027\u540d = db.Column(\u5b57\u6bb5\u7c7b\u578b, \u5b57\u6bb5\u5217\u7ea6\u675f\u9009\u9879)
# \u5982\u679cSQL\u8bed\u53e5\u4e2d\u7684\u5b57\u6bb5\u540d\u5728python\u4e2d\u662f\u5173\u952e\u5b57\/\u4fdd\u7559\u5b57\uff0c\u5219\u5efa\u8bae\u6539\u5199\u7ed1\u5b9a\u5b57\u6bb5\u540d
# \u5c5e\u6027\u540d = db.Column(\"\u5b57\u6bb5\u540d\", \u5b57\u6bb5\u7c7b\u578b, \u5b57\u6bb5\u5217\u7ea6\u675f\u9009\u9879)
id = db.Column(\"student_id\", db.Integer, primary_key=True, comment=\"\u4e3b\u952e\")
name = db.Column(db.String(15), index=True, comment=\"\u59d3\u540d\")
age = db.Column(db.SmallInteger, comment=\"\u5e74\u9f84\")
sex = db.Column(db.SmallInteger, comment=\"\u6027\u522b\")
email = db.Column(db.String(255), unique=True, comment=\"\u90ae\u7bb1\u5730\u5740\")
money = db.Column(db.Numeric(10,2), default=0.0, comment=\"\u94b1\u5305\")
\u200b
# repr()\u65b9\u6cd5\u7c7b\u4f3c\u4e8edjango\u7684__str__\uff0c\u7528\u4e8e\u6253\u5370\u6a21\u578b\u5bf9\u8c61\u65f6\u663e\u793a\u7684\u5b57\u7b26\u4e32\u4fe1\u606f
def __repr__(self):
return f\"{self.name}<{self.__class__.__name__}>\"
\u200b
# \u6240\u6709\u7684\u6a21\u578b\u5fc5\u987b\u76f4\u63a5\u6216\u95f4\u63a5\u7ee7\u627f\u4e8edb.Model
class Course(db.Model):
\"\"\"\u8bfe\u7a0b\u6570\u636e\u6a21\u578b\"\"\"
__tablename__ = \"db_course\"
\"\"\"
# \u539f\u751fSQL\u8bed\u53e5
create table db_course (
id int primary key auto_increment comment \"\u4e3b\u952e\",
name varchar(64) comment \"\u8bfe\u7a0b\",
price NUMERIC(7,2) comment \"\u4ef7\u683c\",
unique (name)
);
# \u5b57\u6bb5\u6839\u636eSQL\u8bed\u53e5\u6765\u58f0\u660e
\"\"\"
id = db.Column(db.Integer, primary_key=True, comment=\"\u4e3b\u952e\")
name = db.Column(db.String(64), unique=True, comment=\"\u8bfe\u7a0b\")
price = db.Column(db.Numeric(7, 2), comment=\"\u4ef7\u683c\")
# repr()\u65b9\u6cd5\u7c7b\u4f3c\u4e8edjango\u7684__str__\uff0c\u7528\u4e8e\u6253\u5370\u6a21\u578b\u5bf9\u8c61\u65f6\u663e\u793a\u7684\u5b57\u7b26\u4e32\u4fe1\u606f
def __repr__(self):
return f\"{self.name}<{self.__class__.__name__}>\"
\u200b
class Teacher(db.Model):
\"\"\"\u8001\u5e08\u6570\u636e\u6a21\u578b\"\"\"
__tablename__ = \"db_teacher\"
\"\"\"
# \u539f\u751fSQL\u8bed\u53e5
create table db_teacher (
id int primary key auto_increment comment \"\u4e3b\u952e\",
name varchar(64) comment \"\u59d3\u540d\",
option enum(\"\u8bb2\u5e08\", \"\u52a9\u6559\", \"\u73ed\u4e3b\u4efb\") comment \"\u804c\u4f4d\",
unique (`name`)
);
# \u5b57\u6bb5\u6839\u636eSQL\u8bed\u53e5\u6765\u58f0\u660e
\"\"\"
id = db.Column(db.Integer, primary_key=True, comment=\"\u4e3b\u952e\")
name = db.Column(db.String(64), unique=True, comment=\"\u59d3\u540d\")
option = db.Column(db.Enum(\"\u8bb2\u5e08\", \"\u52a9\u6559\", \"\u73ed\u4e3b\u4efb\"), default=\"\u8bb2\u5e08\")
\u200b
def __repr__(self):
return f\"{self.name}<{self.__class__.__name__}>\"
\u200b
\u200b
@app.route(\"\/\")
def index():
title = \"\u7f51\u7ad9\u9996\u9875\"
return render_template(\"index.html\", **locals())
\u200b
\u200b
@app.route(\"\/create\")
def create_table():
db.create_all() # \u4e3a\u9879\u76ee\u4e2d\u88ab\u8bc6\u522b\u7684\u6240\u6709\u6a21\u578b\u521b\u5efa\u6570\u636e\u8868
return \"ok\"
\u200b
\u200b
@app.route(\"\/drop\")
def drop_table():
db.drop_all() # \u4e3a\u9879\u76ee\u4e2d\u88ab\u8bc6\u522b\u7684\u6240\u6709\u6a21\u578b\u5220\u9664\u6570\u636e\u8868
return \"ok\"
\u200b
if __name__ == '__main__':
app.run()
\u200b<\/pre>\n\n\n\n\u6570\u636e\u64cd\u4f5c<\/h1>\n\n\n\n\u6dfb\u52a0\u4e00\u6761\u6570\u636e<\/p>\n\n\n\n # \u6dfb\u52a0\u4e00\u6761\u6570\u636e
student = Student(name=\"\u5c0f\u660e\", age=17, email=\"xiaoming@qq.com\", money=100) # \u5b9e\u4f8b\u5316\u6a21\u578b\u5bf9\u8c61
db.session.add(student) # \u628a\u6a21\u578b\u5bf9\u8c61\u6dfb\u52a0\u6570\u636e\u5e93session\u4f1a\u8bdd\u5bf9\u8c61\u4e2d\u3002db.session\u662fSQLAlchemy\u4e2d\u5185\u7f6e\u7684\u4f1a\u8bdd\u7ba1\u7406\u5bf9\u8c61sessionSM\u7684\u6210\u5458
db.session.commit() # \u63d0\u4ea4\u4f1a\u8bdd
\u200b
# \u518d\u6b21\u63d2\u5165\u4e00\u6761\u6570\u636e
student2 = Student(name='\u5c0f\u7ea2', sex=False, age=13, email=\"16565666@qq.com\", money=600)
db.session.add(student2)
db.session.commit() # \u63d0\u4ea4\u4f1a\u8bdd<\/pre>\n\n\n\n\u4e00\u6b21\u63d2\u5165\u591a\u6761\u6570\u636e<\/p>\n\n\n\n # 1. \u5148\u5b9e\u4f8b\u5316\u8981\u521b\u5efa\u7684\u6a21\u578b\u5bf9\u8c61
student = Student(name=\"\u5c0f\u7ea2\", age=17, email=\"xiaohong@qq.com\", money=200)
# 2. \u628a\u5b9e\u4f8b\u5bf9\u8c61\u6dfb\u52a0\u5230\u8fde\u63a5\u4f1a\u8bdd
db.session.add(student)
# 1. \u5148\u5b9e\u4f8b\u5316\u8981\u521b\u5efa\u7684\u6a21\u578b\u5bf9\u8c61
student = Student(name=\"\u5c0f\u82b1\", age=16, email=\"xiaohua@qq.com\", money=200)
# 2. \u628a\u5b9e\u4f8b\u5bf9\u8c61\u6dfb\u52a0\u5230\u8fde\u63a5\u4f1a\u8bdd
db.session.add(student)
# 3. \u53ea\u9700\u8981\u5728\u7ed3\u675f\u7684\u65f6\u5019\u63d0\u4ea4\u4e8b\u52a1\u5373\u53ef
db.session.commit()<\/pre>\n\n\n\n# 1. \u5148\u521b\u5efa\u7684\u5217\u8868\u8981\u6dfb\u52a0\u7684\u5b9e\u4f8b\u5316\u6a21\u578b\u5bf9\u8c61\u5217\u8868
student_list = [
Student(name='wang', email='wang@163.com', age=20),
Student(name='zhang', email='zhang@189.com', age=21),
Student(name='chen', email='chen@126.com', age=19),
Student(name='zhou', email='zhou@163.com', age=18),
Student(name='tang', email='tang@163.com', age=16),
Student(name='wu', email='wu@gmail.com', age=20),
Student(name='qian', email='qian@gmail.com', age=21),
Student(name='liu', email='liu@163.com', age=21),
Student(name='li', email='li@163.com', age=18),
Student(name='sun', email='sun@163.com', age=17),
]
\u200b
# 2. \u4e00\u6b21\u6027\u6dfb\u52a0\u5230\u8fde\u63a5\u4f1a\u8bdd\u4e2d
db.session.add_all(student_list)
db.session.commit()<\/pre>\n\n\n\n\u5220\u9664\u6570\u636e<\/p>\n\n\n\n # \u65b9\u6cd51[\u5148\u67e5\u8be2\u540e\u5220\u9664\uff0c2\u6761\u8bed\u53e5\u5b8c\u6210\u5220\u9664\u64cd\u4f5c]
# \u5148\u67e5\u8be2\u51fa\u6765
student = Student.query.first()
print(student)
# \u518d\u8fdb\u884c\u5220\u9664
db.session.delete(student)
db.session.commit()
\u200b
# \u65b9\u6cd52\u30101\u6761\u8bed\u53e5\u5b8c\u6210\u5220\u9664\u64cd\u4f5c\uff0c\u6027\u80fd\u66f4\u597d\u66f4\u9ad8\u6548\u3011
# \u7c7b\u4f3c\u4e50\u89c2\u9501\uff0c\u5728\u6570\u636e\u6539\u52a8\u65f6\u6dfb\u52a0\u6761\u4ef6\u5e76\u5224\u65ad\u6761\u4ef6\u6210\u7acb\u4ee5\u540e\u624d\u8fdb\u884c\u6570\u636e\u64cd\u4f5c\uff0c\u8fd9\u79cd\u7528\u6cd5\u5c31\u662f\u4e50\u89c2\u9501
Student.query.filter(Student.id == 5).delete()
db.session.commit()
\u200b
\u200b
\"\"\"
\u60b2\u89c2\u9501\uff0c\u662f\u5c5e\u4e8e\u6570\u636e\u5e93\u4e2d\u7684\u4e00\u79cd\u9501\u673a\u5236\uff0c\u4f46\u662f\u4e50\u89c2\u9501\u5e76\u975e\u771f\u6b63\u7684\u6570\u636e\u5e93\u9501\u3002
2\u79cd\u9501\u90fd\u662f\u6570\u636e\u5e93\u5728\u5e94\u5bf9\u5e76\u53d1\u64cd\u4f5c\u65f6\uff0c\u9632\u6b62\u51fa\u73b0\u8d44\u6e90\u62a2\u593a\u7684\uff0c\u57fa\u4e8e\u4e0d\u540c\u4eba\u751f\u89c2\u6240\u5b9e\u73b02\u79cd\u89e3\u51b3\u65b9\u6848\u3002
\u60b2\u89c2\u9501\u7684\u57fa\u672c\u4f7f\u7528\uff1a
>>> \u6570\u636e\u5e93\u7ec8\u7aef\u5f00\u59cb
\u200b
begin; -- \u5f00\u542f\u4e8b\u52a1
select * from db_student where student_id = 5 for update; -- \u6dfb\u52a0\u4e00\u628a\u66f4\u65b0\u9501\u3010\u60b2\u89c2\u9501\u3011
.... -- \u5728\u4e8b\u52a1\u63d0\u4ea4\u4e4b\u524d\uff0c\u4efb\u4f55\u7b2c\u4e09\u65b9\u8fde\u63a5\u90fd\u4e0d\u80fd\u4fee\u6539 student_id = 5\u8fd9\u6761\u6570\u636e
commit; -- \u63d0\u4ea4\u4e8b\u52a1
\u200b
<<< \u6570\u636e\u5e93\u7ec8\u7aef\u5f00\u59cb
\u200b
\u60b2\u89c2\u9501\u7684\u95ee\u9898\uff1a
1. \u63d0\u524d\u9501\u5b9a\u6570\u636e\uff0c\u5f62\u6210\u4e32\u884c\u5316\uff0c\u5f62\u6210\u963b\u585e\uff0c\u4e0d\u5229\u4e8e\u6027\u80fd\u53d1\u6325\uff0c\u4e0d\u9002\u7528\u9ad8\u5e76\u53d1\u573a\u666f\u3002
2. \u60b2\u89c2\u9501\u53ea\u80fd\u4fdd\u8bc1\u6570\u636e\u7684\u4e00\u81f4\u6027\uff0c\u4e0d\u80fd\u4fdd\u8bc1\u810f\u6570\u636e\u7684\u51fa\u73b0
\u200b
\u4e50\u89c2\u9501\u7684\u51fa\u73b0\u5c31\u662f\u4e3a\u4e86\u89e3\u51b3\u60b2\u89c2\u9501\u7684\u95ee\u9898\u3002
\u4e3e\u4f8b\uff1a\u53cc11\u6d3b\u52a8\uff0c\u5546\u57ce\u91cc\u9762id=5\u7684\u5546\u54c1\u7684\u5e93\u5b58=10\u4e86\uff0c\u73b0\u5728\u6211\u4eec\u8981\u57fa\u4e8e\u4e50\u89c2\u9501\u548c\u60b2\u89c2\u9501\u6765\u89e3\u51b3\u4e0b\u5355\u8fc7\u7a0b\u4e2d\uff0c\u51fa\u73b0\u7684\u8d44\u6e90\u62a2\u593a\u73b0\u8c61\uff0c\u907f\u514d\u51fa\u73b0\u8d85\u5356\uff08\u5546\u54c1\u6570\u91cf\u4e0d\u80fd\u4e3a\u8d1f\u6570\uff09\u3002
\u200b
\u4e50\u89c2\u9501\uff1a
---> begin; \u5f00\u542f\u4e8b\u52a1
---> \u5148\u67e5\u770b\u5e93\u5b58\uff0c\u8bb0\u5f55\u5f53\u524d\u5e93\u5b58 num=10
---> \u8fdb\u884c\u4e0b\u5355\u64cd\u4f5c\uff0c\u4e706\u4ef6
---> \u4ed8\u6b3e
---> \u6263\u9664\u5e93\u5b58 update goods set num=num-6 where num=10 and id=5; # \u589e\u52a0\u66f4\u65b0\u6761\u4ef6\uff0c\u5224\u65ad\u5e93\u5b58\u662f\u5426\u8fd8\u662f\u539f\u6765
---> \u5982\u679c\u6267\u884c\u6210\u529f\uff0c\u5219\u8868\u793a\u6ca1\u6709\u4eba\u62a2\uff0c\u8d2d\u4e70\u6210\u529f
\u5982\u679c\u6267\u884c\u4e8b\u53d8\uff0c\u5219\u8868\u793a\u5df2\u7ecf\u6709\u4eba\u5148\u62a2\u8d2d
---> commit;
\u200b
\u60b2\u89c2\u9501\uff1a
---> begin; \u5f00\u542f\u4e8b\u52a1
---> \u5148\u7ed9id=5\u7684\u6570\u636e\uff0c\u52a0\u9501
select * from goods where id=5 for update;
---> \u8fdb\u884c\u4e0b\u5355\u64cd\u4f5c\uff0c\u4e706\u4ef6
---> \u4ed8\u6b3e
---> \u6263\u9664\u5e93\u5b58 update goods set num=num-6 where id=5
---> \u6267\u884c\u6210\u529f\u89e3\u9501
---- commit; \u63d0\u4ea4\u4e8b\u52a1
\"\"\"<\/pre>\n\n\n\n\u66f4\u65b0\u6570\u636e<\/p>\n\n\n\n from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy
\u200b
db = SQLAlchemy()
app = Flask(__name__, template_folder=\"templates\", static_folder=\"static\")
\u200b
# \u914d\u7f6e
app.config.update({
\"DEBUG\": True,
\"SQLALCHEMY_DATABASE_URI\": \"mysql:\/\/root:123@127.0.0.1:3306\/flaskdemo?charset=utf8mb4\",
# \u5982\u679c\u4f7f\u7528pymysql\uff0c\u5219\u9700\u8981\u5728\u8fde\u63a5\u65f6\u6307\u5b9apymysql
# \"SQLALCHEMY_DATABASE_URI\": \"mysql+pymysql:\/\/root:123@127.0.0.1:3306\/flaskdemo?charset=utf8mb4\"
# \u52a8\u6001\u8ffd\u8e2a\u4fee\u6539\u8bbe\u7f6e\uff0c\u5982\u672a\u8bbe\u7f6e\u53ea\u4f1a\u63d0\u793a\u8b66\u544a\uff0c\u8bbe\u7f6eFalse\u5373\u53ef
\"SQLALCHEMY_TRACK_MODIFICATIONS\": False,
# ORM\u6267\u884cSQL\u67e5\u8be2\u65f6\u662f\u54e6\u5426\u663e\u793a\u539f\u59cbSQL\u8bed\u53e5\uff0cdebug\u6a21\u5f0f\u4e0b\u53ef\u4ee5\u5f00\u542f
\"SQLALCHEMY_ECHO\": True,
})
\u200b
db.init_app(app)
\u200b
\u200b
\u200b
class Student(db.Model):
\"\"\"\u5b66\u751f\u7ba1\u7406\"\"\"
__tablename__ = \"db_student\" # \u8868\u540d
# \u5c5e\u6027\u540d = db.Column(\u5b57\u6bb5\u7c7b\u578b, \u5b57\u6bb5\u5217\u7ea6\u675f\u9009\u9879)
# \u5982\u679cSQL\u8bed\u53e5\u4e2d\u7684\u5b57\u6bb5\u540d\u5728python\u4e2d\u662f\u5173\u952e\u5b57\/\u4fdd\u7559\u5b57\uff0c\u5219\u5efa\u8bae\u6539\u5199\u7ed1\u5b9a\u5b57\u6bb5\u540d
# \u5c5e\u6027\u540d = db.Column(\"\u5b57\u6bb5\u540d\", \u5b57\u6bb5\u7c7b\u578b, \u5b57\u6bb5\u5217\u7ea6\u675f\u9009\u9879)
id = db.Column(\"student_id\", db.Integer, primary_key=True, comment=\"\u4e3b\u952e\")
name = db.Column(db.String(15), index=True, comment=\"\u59d3\u540d\")
age = db.Column(db.SmallInteger, comment=\"\u5e74\u9f84\")
sex = db.Column(db.SmallInteger, comment=\"\u6027\u522b\")
email = db.Column(db.String(255), unique=True, comment=\"\u90ae\u7bb1\u5730\u5740\")
money = db.Column(db.Numeric(10,2), default=0.0, comment=\"\u94b1\u5305\")
\u200b
# repr()\u65b9\u6cd5\u7c7b\u4f3c\u4e8edjango\u7684__str__\uff0c\u7528\u4e8e\u6253\u5370\u6a21\u578b\u5bf9\u8c61\u65f6\u663e\u793a\u7684\u5b57\u7b26\u4e32\u4fe1\u606f
def __repr__(self):
return f\"{self.name}<{self.__class__.__name__}>\"
\u200b
\u200b
# \u6240\u6709\u7684\u6a21\u578b\u5fc5\u987b\u76f4\u63a5\u6216\u95f4\u63a5\u7ee7\u627f\u4e8edb.Model
class Course(db.Model):
\"\"\"\u8bfe\u7a0b\u6570\u636e\u6a21\u578b\"\"\"
__tablename__ = \"db_course\"
\"\"\"
# \u539f\u751fSQL\u8bed\u53e5
create table db_course (
id int primary key auto_increment comment \"\u4e3b\u952e\",
name varchar(64) comment \"\u8bfe\u7a0b\",
price NUMERIC(7,2) comment \"\u4ef7\u683c\",
unique (name)
);
# \u5b57\u6bb5\u6839\u636eSQL\u8bed\u53e5\u6765\u58f0\u660e
\"\"\"
id = db.Column(db.Integer, primary_key=True, comment=\"\u4e3b\u952e\")
name = db.Column(db.String(64), unique=True, comment=\"\u8bfe\u7a0b\")
price = db.Column(db.Numeric(7, 2), comment=\"\u4ef7\u683c\")
# repr()\u65b9\u6cd5\u7c7b\u4f3c\u4e8edjango\u7684__str__\uff0c\u7528\u4e8e\u6253\u5370\u6a21\u578b\u5bf9\u8c61\u65f6\u663e\u793a\u7684\u5b57\u7b26\u4e32\u4fe1\u606f
def __repr__(self):
return f\"{self.name}<{self.__class__.__name__}>\"
\u200b
\u200b
class Teacher(db.Model):
\"\"\"\u8001\u5e08\u6570\u636e\u6a21\u578b\"\"\"
__tablename__ = \"db_teacher\"
\"\"\"
# \u539f\u751fSQL\u8bed\u53e5
create table db_teacher (
id int primary key auto_increment comment \"\u4e3b\u952e\",
name varchar(64) comment \"\u59d3\u540d\",
option enum(\"\u8bb2\u5e08\", \"\u52a9\u6559\", \"\u73ed\u4e3b\u4efb\") comment \"\u804c\u4f4d\",
unique (`name`)
);
# \u5b57\u6bb5\u6839\u636eSQL\u8bed\u53e5\u6765\u58f0\u660e
\"\"\"
id = db.Column(db.Integer, primary_key=True, comment=\"\u4e3b\u952e\")
name = db.Column(db.String(64), unique=True, comment=\"\u59d3\u540d\")
option = db.Column(db.Enum(\"\u8bb2\u5e08\", \"\u52a9\u6559\", \"\u73ed\u4e3b\u4efb\"), default=\"\u8bb2\u5e08\")
\u200b
def __repr__(self):
return f\"{self.name}<{self.__class__.__name__}>\"
\u200b
\u200b
@app.route(\"\/\")
def index():
return \"ok\"
\u200b
\u200b
@app.route(\"\/create\")
def create_table():
db.create_all() # \u4e3a\u9879\u76ee\u4e2d\u88ab\u8bc6\u522b\u7684\u6240\u6709\u6a21\u578b\u521b\u5efa\u6570\u636e\u8868
return \"ok\"
\u200b
\u200b
@app.route(\"\/drop\")
def drop_table():
db.drop_all() # \u4e3a\u9879\u76ee\u4e2d\u88ab\u8bc6\u522b\u7684\u6240\u6709\u6a21\u578b\u5220\u9664\u6570\u636e\u8868
return \"ok\"
\u200b
\u200b
\"\"\"\u4e00\u6b21\u6dfb\u52a0\u4e00\u6761\u6570\u636e\"\"\"
@app.route(\"\/add\")
def add_student():
# 1. \u5148\u5b9e\u4f8b\u5316\u8981\u521b\u5efa\u7684\u6a21\u578b\u5bf9\u8c61
student = Student(name=\"\u5c0f\u660e\", age=17, email=\"xiaoming@qq.com\", money=100) # \u5b9e\u4f8b\u5316\u6a21\u578b\u5bf9\u8c61
# 2. \u628a\u5b9e\u4f8b\u5bf9\u8c61\u6dfb\u52a0\u5230\u8fde\u63a5\u4f1a\u8bdd
db.session.add(student)
# 3. \u63d0\u4ea4\u4e8b\u52a1
db.session.commit()
return \"ok\"
\u200b
\u200b
\"\"\"\u4e00\u6b21\u6dfb\u52a0\u591a\u6761\u6570\u636e\"\"\"
@app.route(\"\/madd\")
def multi_add():
# 1. \u5148\u5b9e\u4f8b\u5316\u8981\u521b\u5efa\u7684\u6a21\u578b\u5bf9\u8c61
student = Student(name=\"\u5c0f\u7ea2\", age=17, email=\"xiaohong@qq.com\", money=200)
# 2. \u628a\u5b9e\u4f8b\u5bf9\u8c61\u6dfb\u52a0\u5230\u8fde\u63a5\u4f1a\u8bdd
db.session.add(student)
# 1. \u5148\u5b9e\u4f8b\u5316\u8981\u521b\u5efa\u7684\u6a21\u578b\u5bf9\u8c61
student = Student(name=\"\u5c0f\u82b1\", age=16, email=\"xiaohua@qq.com\", money=200)
# 2. \u628a\u5b9e\u4f8b\u5bf9\u8c61\u6dfb\u52a0\u5230\u8fde\u63a5\u4f1a\u8bdd
db.session.add(student)
# 3. \u53ea\u9700\u8981\u5728\u7ed3\u675f\u7684\u65f6\u5019\u63d0\u4ea4\u4e8b\u52a1\u5373\u53ef
db.session.commit()
return \"ok\"
\u200b
\u200b
@app.route(\"\/madd2\")
def multi_add2():
# 1. \u5148\u521b\u5efa\u7684\u5217\u8868\u8981\u6dfb\u52a0\u7684\u5b9e\u4f8b\u5316\u6a21\u578b\u5bf9\u8c61\u5217\u8868
student_list = [
Student(name='wang', email='wang@163.com', age=20),
Student(name='zhang', email='zhang@189.com', age=21),
Student(name='chen', email='chen@126.com', age=19),
Student(name='zhou', email='zhou@163.com', age=18),
Student(name='tang', email='tang@163.com', age=16),
Student(name='wu', email='wu@gmail.com', age=20),
Student(name='qian', email='qian@gmail.com', age=21),
Student(name='liu', email='liu@163.com', age=21),
Student(name='li', email='li@163.com', age=18),
Student(name='sun', email='sun@163.com', age=17),
]
\u200b
# 2. \u4e00\u6b21\u6027\u6dfb\u52a0\u5230\u8fde\u63a5\u4f1a\u8bdd\u4e2d
db.session.add_all(student_list)
db.session.commit()
\u200b
return \"ok\"
\u200b
\u200b
\u200b
@app.route(\"\/del\")
def delete_student():
\"\"\"\u5220\u9664\u4e00\u6761\u6570\u636e\"\"\"
# \u5148\u67e5\u8be2\u51fa\u6765
student = Student.query.first()
# student = db.session.query(Student).first()
# \u518d\u8fdb\u884c\u5220\u9664
db.session.delete(student)
db.session.commit()
\u200b
return \"ok\"
\u200b
\u200b
@app.route(\"\/mdel\")
def multi_delete_student():
\"\"\"\u6309\u6761\u4ef6\u5220\u9664\u591a\u6761\u6570\u636e\"\"\"
Student.query.filter(Student.id > 5).delete()
# db.session.query(Student).filter(Student.id > 5).delete()
db.session.commit()
\u200b
return \"ok\"
\u200b
\u200b
@app.route(\"\/update\")
def update():
\"\"\"\u66f4\u65b0\u4e00\u6761\"\"\"
# \u5148\u67e5\u8be2\u51fa\u6765
student = Student.query.filter(Student.id == 4).first()
student.name = \"\u5c0f\u767d\"
db.session.commit()
return \"ok\"
\u200b
\u200b
@app.route(\"\/update2\")
def update2():
\"\"\"\u76f4\u63a5\u6839\u636e\u6761\u4ef6\u66f4\u65b0\u4e00\u6761\u6216\u591a\u6761\u6570\u636e\"\"\"
Student.query.filter(Student.name == 'zhang', Student.money == -99.00).update({'money': 1998})
db.session.commit()
return \"ok\"
\u200b
\u200b
@app.route(\"\/update3\")
def update3():
# \u5b57\u6bb5\u5f15\u7528[\u5229\u7528\u5f53\u524d\u4e00\u6761\u6570\u636e\u7684\u5b57\u5178\u503c\u8fdb\u884c\u8f85\u52a9\u64cd\u4f5c,\u5b9e\u73b0\u7c7b\u4f3cdjango\u91cc\u9762F\u51fd\u6570\u7684\u6548\u679c]
# \u6bcf\u6b21\u81ea\u589e100
Student.query.filter(Student.name == \"\u5c0f\u82b1\").update({\"money\": Student.money + 100})
db.session.commit()
return \"ok\"
\u200b
\u200b
@app.route(\"\/update4\")
def update4():
# \u5b57\u6bb5\u5f15\u7528[\u5229\u7528\u5f53\u524d\u4e00\u6761\u6570\u636e\u7684\u5b57\u5178\u503c\u8fdb\u884c\u8f85\u52a9\u64cd\u4f5c,\u5b9e\u73b0\u7c7b\u4f3cdjango\u91cc\u9762F\u51fd\u6570\u7684\u6548\u679c]
# \u5728\u539f\u6709money\u7684\u57fa\u7840\u4e0a\u6309age\u8865\u8d341000*age
Student.query.filter(Student.name == \"zhang\").update({\"money\": Student.money + 1000 * Student.age})
db.session.commit()
return \"ok\"
\u200b
if __name__ == '__main__':
app.run()<\/pre>\n\n\n\n\u57fa\u672c\u67e5\u8be2<\/h2>\n\n\n\nSQLAlchemy\u5e38\u7528\u7684\u67e5\u8be2\u8fc7\u6ee4\u5668<\/h3>\n\n\n\n| \u8fc7\u6ee4\u5668<\/th> | \u8bf4\u660e<\/th><\/tr><\/thead> |
|---|
filter()<\/strong><\/td>| \u628a\u8fc7\u6ee4\u5668\u6dfb\u52a0\u5230\u539f\u67e5\u8be2\u4e0a\uff0c\u8fd4\u56de\u4e00\u4e2a\u65b0\u67e5\u8be2<\/td><\/tr> | | filter_by()<\/td> | \u628a\u7b49\u503c\u8fc7\u6ee4\u5668\u6dfb\u52a0\u5230\u539f\u67e5\u8be2\u4e0a\uff0c\u8fd4\u56de\u4e00\u4e2a\u65b0\u67e5\u8be2<\/td><\/tr> | limit()<\/strong><\/td>\u4f7f\u7528\u6307\u5b9a\u7684\u503c\u9650\u5b9a\u539f\u67e5\u8be2\u8fd4\u56de\u7684\u7ed3\u679c\u6570\u91cf<\/strong><\/td><\/tr>offset()<\/strong><\/td>\u8bbe\u7f6e\u7ed3\u679c\u8303\u56f4\u7684\u5f00\u59cb\u4f4d\u7f6e<\/strong>\uff0c\u504f\u79fb\u539f\u67e5\u8be2\u8fd4\u56de\u7684\u7ed3\u679c\uff0c\u8fd4\u56de\u4e00\u4e2a\u65b0\u67e5\u8be2<\/td><\/tr>order_by()<\/strong><\/td>\u6839\u636e\u6307\u5b9a\u6761\u4ef6\u5bf9\u539f\u67e5\u8be2\u7ed3\u679c\u8fdb\u884c\u6392\u5e8f<\/strong>\uff0c\u8fd4\u56de\u4e00\u4e2a\u65b0\u67e5\u8be2<\/td><\/tr>group_by()<\/strong><\/td>\u6839\u636e\u6307\u5b9a\u6761\u4ef6\u5bf9\u539f\u67e5\u8be2\u7ed3\u679c\u8fdb\u884c\u5206\u7ec4<\/strong>\uff0c\u8fd4\u56de\u4e00\u4e2a\u65b0\u67e5\u8be2<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\nSQLAlchemy\u5e38\u7528\u7684\u67e5\u8be2\u7ed3\u679c\u65b9\u6cd5<\/h3>\n\n\n\n| \u65b9\u6cd5<\/th> | \u8bf4\u660e<\/th><\/tr><\/thead> |
|---|
all()<\/strong><\/td>\u4ee5\u5217\u8868\u5f62\u5f0f<\/strong>\u8fd4\u56de\u67e5\u8be2\u7684\u6240\u6709\u7ed3\u679c<\/td><\/tr>first()<\/strong><\/td>\u8fd4\u56de\u67e5\u8be2\u7684\u7b2c\u4e00\u4e2a\u7ed3\u679c\uff0c\u6a21\u578b\u5bf9\u8c61<\/strong>\uff0c\u5982\u679c\u672a\u67e5\u5230\uff0c\u8fd4\u56deNone<\/strong><\/td><\/tr>| first_or_404()<\/td> | \u8fd4\u56de\u67e5\u8be2\u7684\u7b2c\u4e00\u4e2a\u7ed3\u679c\uff0c\u6a21\u578b\u5bf9\u8c61<\/strong>\uff0c\u5982\u679c\u672a\u67e5\u5230\uff0c\u901a\u8fc7abort \u8fd4\u56de404\u5f02\u5e38<\/td><\/tr>get()<\/strong><\/td>\u8fd4\u56de\u6307\u5b9a\u4e3b\u952e<\/strong>\u5bf9\u5e94\u7684\u6a21\u578b\u5bf9\u8c61<\/strong>\uff0c\u5982\u4e0d\u5b58\u5728\uff0c\u8fd4\u56deNone<\/td><\/tr>| get_or_404()<\/td> | \u8fd4\u56de\u6307\u5b9a\u4e3b\u952e\u5bf9\u5e94\u7684\u884c\uff0c\u5982\u4e0d\u5b58\u5728\uff0cabort \u8fd4\u56de404<\/td><\/tr> | count()<\/strong><\/td>\u8fd4\u56de\u67e5\u8be2\u7ed3\u679c\u7684\u6570\u91cf<\/strong><\/td><\/tr>paginate()<\/strong><\/td>\u8fd4\u56de\u4e00\u4e2aPaginate\u5206\u9875\u5668\u5bf9\u8c61<\/strong>\uff0c\u5b83\u5305\u542b\u6307\u5b9a\u8303\u56f4\u5185\u7684\u7ed3\u679c<\/td><\/tr>having()<\/strong><\/td>| \u8fd4\u56de\u7ed3\u679c\u4e2d\u7b26\u5408\u6761\u4ef6\u7684\u6570\u636e\uff0c\u5fc5\u987b\u8ddf\u5728group by\u540e\u9762<\/strong>\uff0c\u5176\u4ed6\u5730\u65b9\u65e0\u6cd5\u4f7f\u7528\u3002<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n get():\u53c2\u6570\u4e3a\u4e3b\u952e\uff0c\u8868\u793a\u6839\u636e\u4e3b\u952e\u67e5\u8be2\u6570\u636e\uff0c\u5982\u679c\u4e3b\u952e\u4e0d\u5b58\u5728\u8fd4\u56deNone<\/p>\n\n\n\n \u200b
@app.route(\"\/get\")
def get():
\"\"\"\u6309\u4e3b\u952e\u83b7\u53d6\u4e00\u6761\"\"\"
# student = Student.query.get({\"id\": 5})
# student = Student.query.get((5,))
# student = db.session.query(Student).get(5)
student = Student.query.get(5)
print(student)
return \"ok\"
\u200b<\/pre>\n\n\n\n\u8bfe\u5802\u4ee3\u7801\uff1a<\/p>\n\n\n\n # \u524d\u9762\u4ee3\u7801\u7701\u7565
\u200b
\u200b
@app.route(\"\/\")
def index():
return \"ok\"
\u200b
\u200b
@app.route(\"\/create\")
def create_table():
db.create_all() # \u4e3a\u9879\u76ee\u4e2d\u88ab\u8bc6\u522b\u7684\u6240\u6709\u6a21\u578b\u521b\u5efa\u6570\u636e\u8868
return \"ok\"
\u200b
\u200b
@app.route(\"\/drop\")
def drop_table():
db.drop_all() # \u4e3a\u9879\u76ee\u4e2d\u88ab\u8bc6\u522b\u7684\u6240\u6709\u6a21\u578b\u5220\u9664\u6570\u636e\u8868
return \"ok\"
\u200b
@app.route(\"\/query\")
def query():
query1 = Student.query # \u7b80\u5199\u64cd\u4f5c
query2 = db.session.query(Student)
print(type(query1), query1)
print(type(query2), query2)
return \"ok\"
\u200b
\u200b
@app.route(\"\/get\")
def get():
\"\"\"get\u6839\u636e\u4e3b\u952e\u83b7\u53d6\u6570\u636e\"\"\"
# student1 = Student.query.get({\"id\":10})
student1 = Student.query.get(30)
# student2 = db.session.query(Student).get({\"id\":10})
student2 = db.session.query(Student).get(30)
# \u7ed3\u679c\u662f\u6a21\u578b\u5bf9\u8c61
print(type(student1), student1)
print(type(student2), student2)
# \u67e5\u8be2\u4e0d\u5230\u7ed3\u679c\uff0c\u5219\u8fd4\u56de\u503c\u4e3aNone
if student1:
print(student1.name, student1.age)
if student2:
print(student2.name, student2.age)
return \"ok\"
\u200b
if __name__ == '__main__':
app.run()
\u200b<\/pre>\n\n\n\nall()\u8fd4\u56de\u67e5\u8be2\u5230\u7684\u6240\u6709\u5bf9\u8c61<\/p>\n\n\n\n \u6a21\u578b\u7c7b.query.all()
\u200b
\"\"\"\u83b7\u53d6\u591a\u4e2a\u6570\u636e\"\"\"
student = Student.query.all()
print(student) # [dong<Student>, \u5c0f\u7ea2<Student>, wang<Student>, chen<Student>, zhou<Student>, tang<Student>, wu<Student>, qian<Student>, liu<Student>, li<Student>, sun<Student>]
\u200b
student = Student.query.filter(Student.id<5).all() # \u6ca1\u6709\u7ed3\u679c\u8fd4\u56de\u7a7a\u5217\u8868[]
print(student) # [dong<Student>, \u5c0f\u7ea2<Student>, wang<Student>]
\u200b
# all()\u7684\u8fd4\u56de\u503c\u662f\u4e00\u4e2apython\u5217\u8868\uff0c\u53ef\u4ee5\u76f4\u63a5\u4f7f\u7528\u5207\u7247\uff0c\u4e0edjango\u7684QuerySet\u5b8c\u5168\u4e0d\u662f\u4e00\u56de\u4e8b\u3002
student = Student.query.filter(Student.id < 5).all()[:-1] # \u6ca1\u6709\u7ed3\u679c\u8fd4\u56de\u7a7a\u5217\u8868[]
print(student) # [dong<Student>, \u5c0f\u7ea2<Student>]<\/pre>\n\n\n\ncount \u8fd4\u56de\u7ed3\u679c\u7684\u6570\u91cf<\/p>\n\n\n\n # \u8fd4\u56de\u7ed3\u679c\u7684\u6570\u91cf
ret = Student.query.filter(Student.id < 5).count()
print(f\"ret={ret}\")<\/pre>\n\n\n\nfirst()\u8fd4\u56de\u67e5\u8be2\u5230\u7684\u7b2c\u4e00\u4e2a\u5bf9\u8c61\u3010first\u83b7\u53d6\u4e00\u6761\u6570\u636e,all\u83b7\u53d6\u591a\u6761\u6570\u636e\u3011<\/p>\n\n\n\n \u6a21\u578b\u7c7b.query.first()
\u200b
\"\"\"\u83b7\u53d6\u7b2c\u4e00\u4e2a\u6570\u636e\"\"\"
student = Student.query.first()
print(student)
\u200b
student = Student.query.filter(Student.id==5).first() # \u6ca1\u6709\u7ed3\u679c\u8fd4\u56deNone
print(student)<\/pre>\n\n\n\nfilter\u6761\u4ef6\u67e5\u8be2\uff0c\u652f\u6301\u5404\u79cd\u8fd0\u7b97\u7b26\u548c\u67e5\u8be2\u65b9\u6cd5\u6216\u8005\u6a21\u7cca\u67e5\u8be2\u65b9\u6cd5\u3002<\/p>\n\n\n\n \u8fd4\u56de\u540d\u5b57\u7ed3\u5c3e\u5b57\u7b26\u4e3ag\u7684\u6240\u6709\u6570\u636e\u3002<\/p>\n\n\n\n # \u6a21\u7cca\u67e5\u8be2
# \u4f7f\u7528163\u90ae\u7bb1\u7684\u6240\u6709\u7528\u6237
student_list = Student.query.filter(Student.email.endswith(\"@163.com\")).all()
print(student_list)
\u200b
# \u59d3\u540d\u4ee5\"zh\"\u5f00\u5934\u7684
student_list = Student.query.filter(Student.name.startswith(\"zh\")).all()
print(student_list)
\u200b
# \u540d\u5b57\u4e2d\u5e26\u6709\"a\"\u5b57\u6bcd\u7684\u6570\u636e
student_list = Student.query.filter(Student.name.contains(\"a\")).all()
print(student_list)
\u200b
\"\"\"\u5355\u6761\u4ef6\u6bd4\u8f83\"\"\"
# \u5219\u9700\u8981\u6307\u5b9a\u6761\u4ef6\u683c\u5f0f\u4e3a: filter(\u6a21\u578b.\u5b57\u6bb5 \u6bd4\u8f83\u8fd0\u7b97\u7b26 \u503c)\u3002
# \u8fd0\u7b97\u7b26\u53ef\u4ee5\u662f: ==\u8868\u793a\u76f8\u7b49, !=\u4e0d\u76f8\u7b49\uff0c> \u8868\u793a\u5927\u4e8e < \u8868\u793a\u5c0f\u4e8e\uff0c>=\u5927\u4e8e\u7b49\u4e8e\uff0c<=\u5c0f\u4e8e\u7b49\u4e8e
# student_list = Student.query.filter(Student.age > 18).all()
# print(student_list) # [wang<Student>, chen<Student>, zhou<Student>,...]
\u200b
\"\"\"\u591a\u6761\u4ef6\u6bd4\u8f83\"\"\"
# \u8981\u6c42\u591a\u4e2a\u6761\u4ef6\u90fd\u8981\u6ee1\u8db3\uff0c\u76f8\u5f53\u4e8e\u903b\u8f91\u67e5\u8be2\u4e2d\u7684 \u5e76\u4e14(and)\uff01\uff01
student_list = Student.query.filter(Student.age > 18, Student.sex == True).all()
print(student_list) # [wang<Student>, chen<Student>, qian<Student>, liu<Student>]<\/pre>\n\n\n\nfilter_by\u7cbe\u786e\u6761\u4ef6\u67e5\u8be2<\/p>\n\n\n\n filter_by \u53ea\u652f\u6301\u5b57\u6bb5\u7684\u503c\u662f\u5426\u76f8\u7b49<\/strong>\u7684\u60c5\u51b5\uff0c\u5bf9\u4e8e\u5927\u4e8e\u3001\u7b49\u4e8e\u3001\u7b49\u7b49\u5176\u4ed6\u6761\u4ef6\u662f\u4e0d\u652f\u6301\u7684\u3002<\/p>\n\n\n\n\u4f8b\u5982\uff1a\u8fd4\u56deage\u7b49\u4e8e22\u7684\u5b66\u751f<\/p>\n\n\n\n # \u5355\u6761\u4ef6
student_list = Student.query.filter_by(age=22).all() # \u5b57\u6bb5\u6dfb\u52a0\u4e0d\u9700\u8981\u9644\u5e26\u6a21\u578b\u7c7b
print(student_list)
\u200b
# \u591a\u6761\u4ef6
student_list = Student.query.filter_by(age=22,sex=True).all()
print(student_list)<\/pre>\n\n\n\n\u7ec3\u4e60\uff1a<\/p>\n\n\n\n \u67e5\u8be2\u6240\u6709\u7537\u751f[Student.sex==True]\u6570\u636e
\u200b
\u67e5\u8be2id\u4e3a4\u7684\u5b66\u751f[3\u79cd\u65b9\u5f0f]
\u67e5\u8be2\u5e74\u9f84\u7b49\u4e8e22\u7684\u6240\u6709\u5b66\u751f\u6570\u636e
\u200b
\u67e5\u8be2name\u4e3a\u5c0f\u767d\u7684\u5b66\u751f\u6570\u636e
\u200b
\u67e5\u8be220\u5c81\u7684\u7537\u751f<\/pre>\n\n\n\n\u4ee3\u7801\uff1a<\/p>\n\n\n\n from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy
\u200b
db = SQLAlchemy()
app = Flask(__name__, template_folder=\"templates\", static_folder=\"static\")
\u200b
# \u914d\u7f6e
app.config.update({
\"DEBUG\": True,
\"SQLALCHEMY_DATABASE_URI\": \"mysql:\/\/root:123@127.0.0.1:3306\/flaskdemo?charset=utf8mb4\",
# \u5982\u679c\u4f7f\u7528pymysql\uff0c\u5219\u9700\u8981\u5728\u8fde\u63a5\u65f6\u6307\u5b9apymysql
# \"SQLALCHEMY_DATABASE_URI\": \"mysql+pymysql:\/\/root:123@127.0.0.1:3306\/flaskdemo?charset=utf8mb4\"
# \u52a8\u6001\u8ffd\u8e2a\u4fee\u6539\u8bbe\u7f6e\uff0c\u5982\u672a\u8bbe\u7f6e\u53ea\u4f1a\u63d0\u793a\u8b66\u544a\uff0c\u8bbe\u7f6eFalse\u5373\u53ef
\"SQLALCHEMY_TRACK_MODIFICATIONS\": False,
# ORM\u6267\u884cSQL\u67e5\u8be2\u65f6\u662f\u54e6\u5426\u663e\u793a\u539f\u59cbSQL\u8bed\u53e5\uff0cdebug\u6a21\u5f0f\u4e0b\u53ef\u4ee5\u5f00\u542f
\"SQLALCHEMY_ECHO\": True,
})
\u200b
db.init_app(app)
\u200b
\u200b
class Student(db.Model):
\"\"\"\u5b66\u751f\u7ba1\u7406\"\"\"
__tablename__ = \"db_student\" # \u8868\u540d
# \u5c5e\u6027\u540d = db.Column(\u5b57\u6bb5\u7c7b\u578b, \u5b57\u6bb5\u5217\u7ea6\u675f\u9009\u9879)
# \u5982\u679cSQL\u8bed\u53e5\u4e2d\u7684\u5b57\u6bb5\u540d\u5728python\u4e2d\u662f\u5173\u952e\u5b57\/\u4fdd\u7559\u5b57\uff0c\u5219\u5efa\u8bae\u6539\u5199\u7ed1\u5b9a\u5b57\u6bb5\u540d
# \u5c5e\u6027\u540d = db.Column(\"\u5b57\u6bb5\u540d\", \u5b57\u6bb5\u7c7b\u578b, \u5b57\u6bb5\u5217\u7ea6\u675f\u9009\u9879)
id = db.Column(\"student_id\", db.Integer, primary_key=True, comment=\"\u4e3b\u952e\")
name = db.Column(db.String(15), index=True, comment=\"\u59d3\u540d\")
age = db.Column(db.SmallInteger, comment=\"\u5e74\u9f84\")
sex = db.Column(db.SmallInteger, comment=\"\u6027\u522b\")
email = db.Column(db.String(255), unique=True, comment=\"\u90ae\u7bb1\u5730\u5740\")
money = db.Column(db.Numeric(10,2), default=0.0, comment=\"\u94b1\u5305\")
\u200b
# repr()\u65b9\u6cd5\u7c7b\u4f3c\u4e8edjango\u7684__str__\uff0c\u7528\u4e8e\u6253\u5370\u6a21\u578b\u5bf9\u8c61\u65f6\u663e\u793a\u7684\u5b57\u7b26\u4e32\u4fe1\u606f
def __repr__(self):
return f\"<{self.__class__.__name__} {self.name}>\"
\u200b
\u200b
# \u6240\u6709\u7684\u6a21\u578b\u5fc5\u987b\u76f4\u63a5\u6216\u95f4\u63a5\u7ee7\u627f\u4e8edb.Model
class Course(db.Model):
\"\"\"\u8bfe\u7a0b\u6570\u636e\u6a21\u578b\"\"\"
__tablename__ = \"db_course\"
\"\"\"
# \u539f\u751fSQL\u8bed\u53e5
create table db_course (
id int primary key auto_increment comment \"\u4e3b\u952e\",
name varchar(64) comment \"\u8bfe\u7a0b\",
price NUMERIC(7,2) comment \"\u4ef7\u683c\",
unique (name)
);
# \u5b57\u6bb5\u6839\u636eSQL\u8bed\u53e5\u6765\u58f0\u660e
\"\"\"
id = db.Column(db.Integer, primary_key=True, comment=\"\u4e3b\u952e\")
name = db.Column(db.String(64), unique=True, comment=\"\u8bfe\u7a0b\")
price = db.Column(db.Numeric(7, 2), comment=\"\u4ef7\u683c\")
# repr()\u65b9\u6cd5\u7c7b\u4f3c\u4e8edjango\u7684__str__\uff0c\u7528\u4e8e\u6253\u5370\u6a21\u578b\u5bf9\u8c61\u65f6\u663e\u793a\u7684\u5b57\u7b26\u4e32\u4fe1\u606f
def __repr__(self):
return f\"{self.name}<{self.__class__.__name__}>\"
\u200b
\u200b
class Teacher(db.Model):
\"\"\"\u8001\u5e08\u6570\u636e\u6a21\u578b\"\"\"
__tablename__ = \"db_teacher\"
\"\"\"
# \u539f\u751fSQL\u8bed\u53e5
create table db_teacher (
id int primary key auto_increment comment \"\u4e3b\u952e\",
name varchar(64) comment \"\u59d3\u540d\",
option enum(\"\u8bb2\u5e08\", \"\u52a9\u6559\", \"\u73ed\u4e3b\u4efb\") comment \"\u804c\u4f4d\",
unique (`name`)
);
# \u5b57\u6bb5\u6839\u636eSQL\u8bed\u53e5\u6765\u58f0\u660e
\"\"\"
id = db.Column(db.Integer, primary_key=True, comment=\"\u4e3b\u952e\")
name = db.Column(db.String(64), unique=True, comment=\"\u59d3\u540d\")
option = db.Column(db.Enum(\"\u8bb2\u5e08\", \"\u52a9\u6559\", \"\u73ed\u4e3b\u4efb\"), default=\"\u8bb2\u5e08\")
\u200b
def __repr__(self):
return f\"{self.name}<{self.__class__.__name__}>\"
\u200b
\u200b
@app.route(\"\/\")
def index():
return \"ok\"
\u200b
\u200b
@app.route(\"\/create\")
def create_table():
db.create_all() # \u4e3a\u9879\u76ee\u4e2d\u88ab\u8bc6\u522b\u7684\u6240\u6709\u6a21\u578b\u521b\u5efa\u6570\u636e\u8868
return \"ok\"
\u200b
\u200b
@app.route(\"\/drop\")
def drop_table():
db.drop_all() # \u4e3a\u9879\u76ee\u4e2d\u88ab\u8bc6\u522b\u7684\u6240\u6709\u6a21\u578b\u5220\u9664\u6570\u636e\u8868
return \"ok\"
\u200b
@app.route(\"\/exam\")
def exam():
# \u67e5\u8be2\u6240\u6709\u7537\u751f[Student.sex == True]\u6570\u636e
# student_list = Student.query.filter(Student.sex == True).all()
# print(student_list)
\u200b
# \u67e5\u8be2id\u4e3a4\u7684\u5b66\u751f[3\u79cd\u65b9\u5f0f]
# 1. get
# student = Student.query.get(4)
# print(student)
\u200b
# 2. filter+first
# student = Student.query.filter(Student.id == 4).first()
# print(student)
\u200b
# 3. filter_by + first
# student = Student.query.filter_by(id=4).first()
# print(student)
\u200b
# \u67e5\u8be2\u5e74\u9f84\u7b49\u4e8e20\u7684\u6240\u6709\u5b66\u751f\u6570\u636e
# student_list = Student.query.filter(Student.age == 20).all()
# print(student_list)
\u200b
# \u67e5\u8be2name\u4e3a\u5c0f\u767d\u7684\u5b66\u751f\u6570\u636e
# student = Student.query.filter(Student.name == \"\u5c0f\u767d\").first()
# print(student)
\u200b
# \u67e5\u8be220\u5c81\u7684\u7537\u751f
student_list = Student.query.filter(Student.age == 20, Student.sex == True).all()
print(student_list)
\u200b
return \"ok\"
\u200b
if __name__ == '__main__':
app.run()
\u200b<\/pre>\n\n\n\n\u591a\u6761\u4ef6\u67e5\u8be2<\/h3>\n\n\n\n\u903b\u8f91\u4e0e\uff0c\u9700\u8981\u5bfc\u5165and_<\/code>\uff0c\u8fd4\u56deand_()<\/code>\u6761\u4ef6\u6ee1\u8db3\u7684\u6240\u6709\u6570\u636e<\/p>\n\n\n\nfrom sqlalchemy import and_
Student.query.filter(and_(Student.name!='wang',Student.email.endswith('163.com'))).all()
\u200b
\u200b
# # and_(\u6761\u4ef61,\u6761\u4ef62,....) \u7b49\u4ef7\u4e8e filter(\u6761\u4ef61,\u6761\u4ef62,.....)
# # age > 18 and email like \"%163.com\"
# # student_list = Student.query.filter(Student.age > 18, Student.email.endswith(\"163.com\")).all()
#
# student_list = Student.query.filter(
# and_(
# Student.age > 18,
# Student.email.endswith(\"163.com\")
# )
# ).all()<\/pre>\n\n\n\n\u903b\u8f91\u6216\uff0c\u9700\u8981\u5bfc\u5165or_<\/p>\n\n\n\n from sqlalchemy import or_
Student.query.filter(or_(Student.name!='wang',Student.email.endswith('163.com'))).all()
\u200b
# \u67e5\u8be2\u5e74\u9f84\u572820\u5c81\uff0c\u4f7f\u7528\u7684\u90ae\u7bb1\u662fqq\u6216\u8005163\u90ae\u7bb1\u7684
student_list = Student.query.filter(
Student.age == 20,
or_(
Student.email.endswith(\"qq.com\"),
Student.email.endswith(\"163.com\")
)
).all()
\u200b
\u200b
# \u590d\u5408\u6761\u4ef6\u7684\u67e5\u8be2\u60c5\u51b5
# \u67e5\u8be2\u5e74\u9f84>17\u5c81\u7684\u5973\u751f\u6216\u8005\u5e74\u9f84>18\u5c81\u7684\u7537\u751f
student_list = Student.query.filter(
or_(
and_(Student.age > 17, Student.sex == False),
and_(Student.age > 18, Student.sex == True),
)
).all()
print(student_list)
\u200b
print(student_list)<\/pre>\n\n\n\n\u903b\u8f91\u975e\uff0c\u8fd4\u56de\u540d\u5b57\u4e0d\u7b49\u4e8e”\u5c0f\u767d”\u7684\u6240\u6709\u6570\u636e<\/p>\n\n\n\n Student.query.filter(Student.name!='\u5c0f\u767d').all()<\/pre>\n\n\n\nnot_ \u76f8\u5f53\u4e8e\u53d6\u53cd<\/p>\n\n\n\n from sqlalchemy import not_
Student.query.filter(not_(Student.name=='\u5c0f\u767d')).all()
\u200b
# # \u67e5\u8be2\u5e74\u9f84\u4e0d\u7b49\u4e8e22
# student_list = Student.query.filter(Student.age != 22).all()
# print(student_list)
# student_list = Student.query.filter(not_(Student.age==22)).all()
# print(student_list)<\/pre>\n\n\n\nin_\u8303\u56f4\u67e5\u8be2<\/p>\n\n\n\n # \u67e5\u8be2id\u662f 5, 7, 10 \u7684\u5b66\u751f\u4fe1\u606f
student_list = Student.query.filter(Student.id.in_([5, 7, 10])).all()
print(student_list)
\u200b
# \u67e5\u8be2id\u4e0d\u662f 1 3 5 \u7684\u5b66\u751f\u4fe1\u606f
student_list = Student.query.filter(not_(Student.id.in_([1, 3, 5]))).all()
print( student_list )<\/pre>\n\n\n\nis_\u5224\u65ad\u503c\u67e5\u8be2<\/p>\n\n\n\n \"\"\"\u5224\u65ad\u503c\u67e5\u8be2\"\"\"
student_list = Student.query.filter(Student.email.is_(None)).all()
print(student_list)
\u200b
student_list = Student.query.filter(Student.email == None).all()
print(student_list)<\/pre>\n\n\n\norder_by \u6392\u5e8f<\/p>\n\n\n\n # \u5012\u5e8f[\u503c\u4ece\u5927\u5230\u5c0f]
student_list = Student.query.order_by(Student.id.desc()).all()
# \u5347\u5e8f[\u503c\u4ece\u5c0f\u5230\u5927]
student_list = Student.query.order_by(Student.id.asc()).all()
\u200b
# \u591a\u5b57\u6bb5\u6392\u5e8f[\u7b2c\u4e00\u4e2a\u5b57\u6bb5\u503c\u4e00\u6837\u65f6\uff0c\u6bd4\u8f83\u7b2c\u4e8c\u4e2a\u5b57\u6bb5\uff0c\u8fdb\u884c\u6392\u5e8f]
student_list = Student.query.order_by(Student.money.asc(), Student.age.asc(), Student.id.asc()).all()
print(student_list)<\/pre>\n\n\n\ncount\u7edf\u8ba1<\/p>\n\n\n\n # \u67e5\u8be2age>=19\u7684\u7537\u751f\u7684\u6570\u91cf
from sqlalchemy import and_
# ret = Student.query.filter( and_(Student.age>=19,Student.sex==True) ).count()
ret = Student.query.filter( Student.age>=19, Student.sex==True ).count()<\/pre>\n\n\n\n\u5bf9\u7ed3\u679c\u8fdb\u884c\u504f\u79fb\u91cf\u548c\u6570\u91cf\u7684\u9650\u5236<\/p>\n\n\n\n # \u67e5\u8be2\u5e74\u9f84\u6700\u5927\u76843\u4e2a\u5b66\u751f
student_list = Student.query.order_by(Student.age.desc()).limit(3).all()
print(student_list)
\u200b
# \u67e5\u8be2\u5e74\u9f84\u6392\u7b2c4\u5230\u7b2c7\u540d\u7684\u5b66\u751f
student_list = Student.query.order_by(Student.age.desc()).offset(3).limit(4).all()
print(student_list)
\u200b
# \u67e5\u8be2\u5e74\u9f84\u6700\u5c0f\u76843\u4e2a\u4eba
student_list = Student.query.order_by(Student.age.asc()).limit(3).all()
print(student_list)<\/pre>\n\n\n\nSQL<\/p>\n\n\n\n # \u67e5\u8be2\u5e74\u9f84\u6700\u5927\u76843\u4e2a\u5b66\u751f
# select * from db_student order by age desc limit 3;
\u200b
# \u67e5\u8be2\u5e74\u9f84\u6392\u7b2c4\u5230\u7b2c7\u540d\u7684\u5b66\u751f
select * from db_student order by age desc limit 3, 4;
# select * from db_student order by age desc limit 4 offset 3;
\u200b
# \u67e5\u8be2\u5e74\u9f84\u6700\u5c0f\u76843\u4e2a\u4eba
# select * from db_student order by age asc limit 3;<\/pre>\n\n\n\n\u7ec3\u4e60<\/p>\n\n\n\n # \u67e5\u8be2age\u662f18 \u6216\u8005 \u4f7f\u7528163\u90ae\u7bb1\u7684\u6240\u6709\u5b66\u751f
Student.query.filter(or_(Student.age==18,Student.email.endswith(\"163.com\"))).all()
# \u67e5\u8be2id\u4e3a [1, 3, 5, 7, 9] \u7684\u5b66\u751f\u5217\u8868
student_list = Student.query.filter(Student.id.in_([1, 3, 5, 7, 9])).all()
print(student_list)<\/pre>\n\n\n\n\u5206\u9875\u5668<\/h3>\n\n\n\nmanage.py\uff0c\u4ee3\u7801\uff1a<\/p>\n\n\n\n from flask import Flask
from flask_sqlalchemy import SQLAlchemy
\u200b
app = Flask(__name__)
class Config(object):
DEBUG = True
# \u6570\u636e\u5e93\u94fe\u63a5\u914d\u7f6e = \u6570\u636e\u5e93\u540d\u79f0:\/\/\u767b\u5f55\u8d26\u53f7:\u767b\u5f55\u5bc6\u7801@\u6570\u636e\u5e93\u4e3b\u673aIP:\u6570\u636e\u5e93\u8bbf\u95ee\u7aef\u53e3\/\u6570\u636e\u5e93\u540d\u79f0?charset=\u7f16\u7801\u7c7b\u578b
SQLALCHEMY_DATABASE_URI = \"mysql:\/\/root:123@127.0.0.1:3306\/flaskdemo?charset=utf8mb4\"
# \u52a8\u6001\u8ffd\u8e2a\u4fee\u6539\u8bbe\u7f6e\uff0c\u5982\u672a\u8bbe\u7f6e\u53ea\u4f1a\u63d0\u793a\u8b66\u544a
SQLALCHEMY_TRACK_MODIFICATIONS = False
# ORM\u8fd0\u884c\u65f6\u4f1a\u663e\u793aORM\u751f\u6210\u7684\u539f\u59cbSQL\u8bed\u53e5[\u8c03\u8bd5]
SQLALCHEMY_ECHO = True
\u200b
app.config.from_object(Config)
\u200b
\u200b
\"\"\"\u6a21\u578b\u7c7b\u5b9a\u4e49\"\"\"
db = SQLAlchemy(app=app)
\u200b
class Student(db.Model):
\"\"\"\u5b66\u751f\u4fe1\u606f\u6a21\u578b\"\"\"
# \u58f0\u660e\u4e0e\u5f53\u524d\u6a21\u578b\u7ed1\u5b9a\u7684\u6570\u636e\u8868\u540d\u79f0
__tablename__ = \"db_student\"
id = db.Column(db.Integer, primary_key=True,comment=\"\u4e3b\u952e\")
name = db.Column(db.String(15), index=True, comment=\"\u59d3\u540d\")
age = db.Column(db.SmallInteger, comment=\"\u5e74\u9f84\")
sex = db.Column(db.Boolean, default=True, comment=\"\u6027\u522b\")
email = db.Column(db.String(128), unique=True, comment=\"\u90ae\u7bb1\u5730\u5740\")
money = db.Column(db.Numeric(10,2), default=0.0, comment=\"\u94b1\u5305\")
\u200b
def __repr__(self): # \u76f8\u5f53\u4e8edjango\u7684__str__
return f\"{self.name}<{self.__class__.__name__}>\"
\u200b
\u200b
# \u6240\u6709\u7684\u6a21\u578b\u5fc5\u987b\u76f4\u63a5\u6216\u95f4\u63a5\u7ee7\u627f\u4e8edb.Model
class Course(db.Model):
\"\"\"\u8bfe\u7a0b\u6570\u636e\u6a21\u578b\"\"\"
__tablename__ = \"db_course\"
id = db.Column(db.Integer, primary_key=True, comment=\"\u4e3b\u952e\")
name = db.Column(db.String(64), unique=True, comment=\"\u8bfe\u7a0b\")
price = db.Column(db.Numeric(7, 2), comment=\"\u4ef7\u683c\")
def __repr__(self):
return f\"{self.name}<{self.__class__.__name__}>\"
\u200b
class Teacher(db.Model):
\"\"\"\u8001\u5e08\u6570\u636e\u6a21\u578b\"\"\"
__tablename__ = \"db_teacher\"
id = db.Column(db.Integer, primary_key=True, comment=\"\u4e3b\u952e\")
name = db.Column(db.String(64), unique=True, comment=\"\u59d3\u540d\")
option = db.Column(db.Enum(\"\u8bb2\u5e08\", \"\u52a9\u6559\", \"\u73ed\u4e3b\u4efb\"), default=\"\u8bb2\u5e08\")
\u200b
def __repr__(self):
return f\"{self.name}<{self.__class__.__name__}>\"
\u200b
from flask import request,render_template
\u200b
@app.route(\"\/\")
def index():
# \u5206\u9875\u5668
page = int(request.args.get(\"page\", 1))
size = int(request.args.get(\"size\", 3))
pagination = Student.query.paginate(page, size)
print(pagination)
\"\"\"
from flask_sqlalchemy import Pagination
# \u5e38\u7528\u5c5e\u6027
total \u603b\u6570\u636e\u91cf
items \u6bcf\u4e00\u9875\u6570\u636e\u9879\u5217\u8868
pages \u603b\u9875\u7801===> math.ceil( total\/per_page )
# \u5e38\u7528\u65b9\u6cd5
prev \u4e0a\u4e00\u9875\u5206\u9875\u5bf9\u8c61
prev_num \u4e0a\u4e00\u9875\u9875\u7801
has_prev \u662f\u5426\u6709\u4e0a\u4e00\u9875
next \u4e0b\u4e00\u9875\u5206\u9875\u5bf9\u8c61
next_num \u4e0b\u4e00\u9875\u9875\u7801
has_next \u662f\u5426\u6709\u4e0b\u4e00\u9875
\"\"\"
print(pagination.items) # \u5f53\u524d\u9875\u7684\u6570\u636e\u9879\u5217\u8868
print(pagination.total) # \u603b\u6570\u636e\u91cf
print(pagination.pages) # \u603b\u9875\u7801\u6570\u91cf
print(pagination.prev_num) # \u4e0a\u4e00\u9875\u9875\u7801
print(pagination.next_num) # \u4e0b\u4e00\u9875\u9875\u7801
print(pagination.has_prev) # \u662f\u5426\u6709\u4e0a\u4e00\u9875
print(pagination.has_next) # \u662f\u5426\u6709\u4e0b\u4e00\u9875
print(pagination.prev()) # \u4e0a\u4e00\u9875\u7684\u5206\u9875\u5bf9\u8c61
print(pagination.next()) # \u4e0b\u4e00\u9875\u7684\u5206\u9875\u5bf9\u8c61
\u200b
# \"\"\"\u524d\u540e\u7aef\u5206\u79bb\"\"\"
# data = {
# \"page\": pagination.page, # \u5f53\u524d\u9875\u7801
# \"pages\": pagination.pages, # \u603b\u9875\u7801
# \"has_prev\": pagination.has_prev, # \u662f\u5426\u6709\u4e0a\u4e00\u9875
# \"prev_num\": pagination.prev_num, # \u4e0a\u4e00\u9875\u9875\u7801
# \"has_next\": pagination.has_next, # \u662f\u5426\u6709\u4e0b\u4e00\u9875
# \"next_num\": pagination.next_num, # \u4e0b\u4e00\u9875\u9875\u7801
# \"items\": [{
# \"id\": item.id,
# \"name\": item.name,
# \"age\": item.age,
# \"sex\": item.sex,
# \"money\": item.money,
# } for item in pagination.items]
# }
# return data
\u200b
\u200b
\"\"\"\u524d\u540e\u7aef\u4e0d\u5206\u79bb\"\"\"
return render_template(\"list.html\",**locals())
\u200b
\u200b
\u200b
\u200b
\u200b
if __name__ == '__main__':
with app.app_context():
db.create_all()
app.run()<\/pre>\n\n\n\nlist.html\uff0c\u4ee3\u7801\uff1a<\/p>\n\n\n\n <!DOCTYPE html>
<html lang=\"en\">
<head>
<meta charset=\"UTF-8\">
<title>Title<\/title>
<style>
.page a,.page span{
padding: 2px 6px;
color: #fff;
background: #6666ff;
text-decoration: none;
}
.page span{
color: #fff;
background: orange;
}
\u200b
<\/style>
<\/head>
<body>
<table border=\"1\" align=\"center\" width=\"600\">
<tr>
<th>ID<\/th>
<th>age<\/th>
<th>name<\/th>
<th>sex<\/th>
<th>money<\/th>
<\/tr>
{% for student in pagination.items %}
<tr>
<td>{{ student.id }}<\/td>
<td>{{ student.age }}<\/td>
<td>{{ student.name }}<\/td>
<td>{{ \"\u7537\" if student.sex else \"\u5973\" }}<\/td>
<td>{{ student.money }}<\/td>
<\/tr>
{% endfor %}
<tr align=\"center\">
<td colspan=\"5\" class=\"page\">
{% if pagination.has_prev %}
<a href=\"?page=1\">\u9996 \u9875<\/a>
<a href=\"?page={{ pagination.page-1 }}\">\u4e0a\u4e00\u9875<\/a>
<a href=\"?page={{ pagination.page-1 }}\">{{ pagination.page-1 }}<\/a>
{% endif %}
<span>{{ pagination.page }}<\/span>
{% if pagination.has_next %}
<a href=\"?page={{ pagination.page+1 }}\">{{ pagination.page+1 }}<\/a>
<a href=\"?page={{ pagination.page+1 }}\">\u4e0b\u4e00\u9875<\/a>
<a href=\"?page={{ pagination.pages }}\">\u5c3e \u9875<\/a>
{% endif %}
<\/td>
<\/tr>
<\/table>
<\/body>
<\/html><\/pre>\n\n\n\n\u805a\u5408\u5206\u7ec4<\/h3>\n\n\n\n\u5206\u7ec4\u67e5\u8be2\u548c\u5206\u7ec4\u67e5\u8be2\u7ed3\u679c\u8fc7\u6ee4<\/p>\n\n\n\n \u4e00\u822c\u5206\u7ec4\u90fd\u4f1a\u7ed3\u5408\u805a\u5408\u51fd\u6570<\/strong>\u6765\u4e00\u8d77\u4f7f\u7528\u3002SQLAlchemy\u4e2d\u6240\u6709\u7684\u805a\u5408\u51fd\u6570\u90fd\u5728func<\/code>\u6a21\u5757\u4e2d\u58f0\u660e\u7684\u3002<\/p>\n\n\n\nfrom sqlalchemy import func<\/pre>\n\n\n\n| \u51fd\u6570\u540d<\/th> | \u8bf4\u660e<\/th> | <\/th><\/tr><\/thead> |
|---|
| func.count<\/td> | \u7edf\u8ba1\u603b\u6570<\/td> | <\/td><\/tr> | | func.avg<\/td> | \u5e73\u5747\u503c<\/td> | <\/td><\/tr> | | func.min<\/td> | \u6700\u5c0f\u503c<\/td> | <\/td><\/tr> | | func.max<\/td> | \u6700\u5927\u503c<\/td> | <\/td><\/tr> | | func.sum<\/td> | \u6c42\u548c<\/td> | <\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n \u4ee3\u7801\uff1a<\/p>\n\n\n\n \"\"\"\u805a\u5408\u51fd\u6570\"\"\"
from sqlalchemy import func
# \u83b7\u53d6\u6240\u6709\u5b66\u751f\u7684money\u603b\u6570
# SELECT sum(db_student.money) AS sum_1 FROM db_student LIMIT %s
# ret = db.session.query(func.sum(Student.money)).first()[0]
# print(ret) # 3998.0
# # \u67e5\u8be2\u5973\u751f\u7684\u6570\u91cf
# ret = db.session.query(func.count(Student.id)).filter(Student.sex==False).first()[0]
# print(ret) # 7
# # \u67e5\u8be2\u6240\u6709\u5b66\u751f\u7684\u5e73\u5747\u5e74\u9f84
# ret = db.session.query(func.avg(Student.age)).first()[0]
# print(ret) # 18.2727
\u200b
\"\"\"
\u805a\u5408\u5206\u7ec4
\u5728\u805a\u5408\u5206\u7ec4\u7684\u60c5\u51b5\u4e0b\uff0cdb.session.query\u4e2d\u7684\u53c2\u6570\u53ea\u80fd\u662f\u88ab\u5206\u7ec4\u7684\u5b57\u6bb5\u6216\u8005\u662f\u88ab\u805a\u5408\u7684\u6570\u636e
\"\"\"
# # \u67e5\u8be2\u5f53\u524d\u6240\u6709\u7537\u751f\u5973\u751f\u7684\u6570\u91cf
# ret = db.session.query(Student.sex,func.count(Student.id)).group_by(Student.sex).all()
# print(ret) # [(False, 7), (True, 4)]
\u200b
# # \u67e5\u8be2\u5404\u4e2a\u5e74\u9f84\u6bb5\u7684\u5b66\u751f\u6570\u91cf
# ret = db.session.query(Student.age, func.count(Student.id)).group_by(Student.age).all()
# print(ret) # [(15, 2), (13, 1), (22, 4), (19, 1), (18, 1), (16, 1), (17, 1)]
#
# # \u67e5\u770b\u5f53\u524d\u7537\u751f\u5973\u751f\u7684\u5e73\u5747\u5e74\u9f84
# ret = db.session.query(Student.sex, func.avg(Student.age)).group_by(Student.sex).all()
# ret = [{\"sex\":\"\u7537\" if item[0] else \"\u5973\",\"age\":float(item[1])} for item in ret]
# print(ret) # [{'sex': '\u5973', 'age': 18.0}, {'sex': '\u7537', 'age': 18.75}]
\u200b
# # \u5206\u7ec4\u540e\u7684\u8fc7\u6ee4\u64cd\u4f5c having
# # \u5728\u6240\u6709\u5b66\u751f\u4e2d\uff0c\u627e\u51fa\u5404\u4e2a\u5e74\u9f84\u4e2d\u62e5\u6709\u6700\u591a\u94b1\u7684\u540c\u5b66\uff0c\u5e76\u5728\u8fd9\u4e9b\u540c\u5b66\u91cc\u9762\u7b5b\u9009\u51famoney > 500\u7684\u6570\u636e
# subquery = func.max(Student.money)
# print(subquery) # max(db_student.money)
# ret = db.session.query(Student.age, subquery).group_by(Student.age).having(subquery > 500).all()
# print(ret) # [(18, Decimal('1000.00')), (22, Decimal('26000.00')), (23, Decimal('1998.00'))]
\u200b
\"\"\"
\u591a\u5b57\u6bb5\u5206\u7ec4
\u5b57\u6bb51 \u5b57\u6bb52
1 3
2 4
\u200b
\u5206\u7ec4\u5982\u4e0b\uff1a
13
14
23
24
\"\"\"
# \u5404\u4e2a\u5e74\u9f84\u91cc\uff0c\u7537\u751f\u548c\u5973\u751f\u7684money\u603b\u6570
subquery = func.sum(Student.money)
ret = db.session.query(Student.sex, Student.age, subquery).group_by(Student.sex, Student.age).all()
print(ret) # [(False, 15, 1000.0), (False, 13, 600.0), (True, 15, 0.0), (True, 22, 1998.0), (False, 19, 0.0), (False, 22, 400.0), (False, 18, 0.0), (True, 16, 0.0), (False, 17, 0.0)]<\/pre>\n\n\n\nSQL\u65b9\u6cd5\u4e2d\u7684\u5173\u952e\u5b57\u987a\u5e8f\uff1a<\/p>\n\n\n\n \u6a21\u578b.query. \/\/ db.session.query.
filter\/ filter_by
group by
having
order_by
limit offset
all \/ get \/ first \/ count \/ paginate<\/pre>\n\n\n\n\u6267\u884c\u539f\u751fSQL\u8bed\u53e5<\/h3>\n\n\n\n\"\"\"\u6267\u884c\u539f\u751fSQL\u8bed\u53e5\"\"\"
# # \u67e5\u8be2\u591a\u6761\u6570\u636e
# ret = db.session.execute(\"select * from db_student\").fetchall()
# print(ret)
# # \u67e5\u8be2\u4e00\u6761\u6570\u636e
# ret = db.session.execute(\"select * from db_student\").fetchone()
# print(ret)
\u200b
\"\"\"
name age achievement
80
\u5c0f\u660e 17 81
83
\u200b
group_concat \u9017\u53f7\u5408\u5e76
\u5c0f\u660e 17 80,81,83
\u200b
concat \u5b57\u7b26\u4e32\u62fc\u63a5
\u5c0f\u660e 17 808183
\"\"\"
\u200b
# # \u6dfb\u52a0\u6570\u636e
# db.session.execute(\"insert db_student (name,age,sex,email,money) select name,age,sex,concat(now(),email),money from db_student\")
# db.session.commit()
\u200b
# # # \u66f4\u65b0\/\u5220\u9664
# db.session.execute(\"UPDATE db_student SET money=(db_student.money + %s) WHERE db_student.age = %s\" % (200, 22))
# db.session.commit()
\u200b
\u200b
\"\"\"\u5206\u7ec4\u5408\u5e76\"\"\"
# \u7edf\u8ba1\u5404\u4e2a\u5e74\u9f84\u6bb5\u7684\u5b66\u751f\u4eba\u6570\uff0c\u5e76\u8bb0\u5f55\u5bf9\u5e94\u5e74\u9f84\u6bb5\u7684\u5b66\u751fID
ret = db.session.execute(\"select age,count(id),group_concat(id) from db_student group by age\").fetchall()
print(ret)
return \"ok\"<\/pre>\n\n\n\n\u5173\u8054\u67e5\u8be2<\/h2>\n\n\n\n\u5e38\u7528\u7684SQLAlchemy\u5173\u7cfb\u9009\u9879<\/h3>\n\n\n\n| \u9009\u9879\u540d<\/th> | \u8bf4\u660e<\/th><\/tr><\/thead> |
|---|
| backref<\/td> | \u5728\u5173\u7cfb\u7684\u53e6\u4e00\u6a21\u578b\u4e2d\u6dfb\u52a0\u53cd\u5411\u5f15\u7528<\/strong>,\u7528\u4e8e\u8bbe\u7f6e\u5916\u952e\u540d\u79f0,\u57281\u67e5\u591a\u7684<\/td><\/tr>| primary join<\/td> | \u660e\u786e\u6307\u5b9a\u4e24\u4e2a\u6a21\u578b\u4e4b\u95f4\u4f7f\u7528\u7684\u8fde\u8868\u6761\u4ef6, \u7528\u4e8e1\u5bf91 \u6216\u80051\u5bf9\u591a\u8fde\u8868\u4e2d<\/td><\/tr> | | lazy<\/td> | \u6307\u5b9a\u5982\u4f55\u52a0\u8f7d\u5173\u8054\u6a21\u578b\u6570\u636e\u7684\u65b9\u5f0f\uff0c\u7528\u4e8e1\u5bf91\u62161\u5bf9\u591a\u94fe\u8868\u4e2d\u3002\u53c2\u6570\u503c: select\uff08\u7acb\u5373\u52a0\u8f7d\uff0c\u67e5\u8be2\u6240\u6709\u76f8\u5173\u6570\u636e\u663e\u793a\uff0c\u76f8\u5f53\u4e8elazy=True\uff09 subquery\uff08\u7acb\u5373\u52a0\u8f7d\uff0c\u4f46\u4f7f\u7528\u5b50\u67e5\u8be2\uff09 dynamic\uff08\u4e0d\u7acb\u5373\u52a0\u8f7d\uff0c\u4f46\u63d0\u4f9b\u52a0\u8f7d\u8bb0\u5f55\u7684\u67e5\u8be2\u5bf9\u8c61\uff09<\/td><\/tr> | | uselist<\/td> | \u6307\u5b9a1\u5bf91\u62161\u5bf9\u591a\u8fde\u8868\u65f6\uff0c\u8fd4\u56de\u7684\u6570\u636e\u7ed3\u679c\u662f\u6a21\u578b\u5bf9\u8c61\u8fd8\u662f\u6a21\u578b\u5217\u8868\uff0c\u5982\u679c\u4e3aFalse\uff0c\u4e0d\u4f7f\u7528\u5217\u8868\uff0c\u800c\u4f7f\u7528\u6a21\u578b\u5bf9\u8c61\u3002 1\u5bf91\u6216\u591a\u5bf91\u5173\u7cfb\u4e2d\uff0c\u9700\u8981\u8bbe\u7f6erelationship\u4e2d\u7684uselist=Flase\uff0c1\u5bf9\u591a\u6216\u591a\u5bf9\u591a\u5173\u7cfb\u4e2d\uff0c\u9700\u8981\u8bbe\u7f6erelationshio\u4e2d\u7684uselist=True\u3002<\/td><\/tr> | | secondary<\/td> | \u6307\u5b9a\u591a\u5bf9\u591a\u5173\u7cfb\u4e2d\u5173\u7cfb\u8868\u7684\u540d\u5b57\u3002 \u591a\u5bf9\u591a\u5173\u7cfb\u4e2d\uff0c\u9700\u5efa\u7acb\u5173\u7cfb\u8868\uff0c\u8bbe\u7f6e secondary=\u5173\u7cfb\u8868<\/td><\/tr> | | secondary join<\/td> | \u5728SQLAlchemy\u4e2d\u65e0\u6cd5\u81ea\u884c\u51b3\u5b9a\u65f6\uff0c\u6307\u5b9a\u591a\u5bf9\u591a\u5173\u7cfb\u4e2d\u7684\u4e8c\u7ea7\u8fde\u8868\u6761\u4ef6\uff0c\u7ed1\u5b9a\u4e3b\u5916\u952e\u3002<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n \u8303\u5f0f\u7406\u8bba\uff1a\u4e00\u5957\u63d0\u4f9b\u7ed9\u6570\u636e\u5e93\u5f00\u53d1\u8005\u8bbe\u7f6e\u6807\u51c6\u3001\u89c4\u8303\u7684\u6570\u636e\u5e93\u7684\u7406\u8bba\u3002<\/p>\n\n\n\n 1NF. \u6570\u636e\u4e0d\u53ef\u518d\u5206\uff0c\u5fc5\u987b\u4fdd\u8bc1\u539f\u5b50\u6027\u3002\u6570\u636e\u7684\u503c\u4fdd\u8bc1\u53ef\u4ee5\u65b9\u4fbf\u5b58\u50a8\uff0c\u4e0d\u53ef\u518d\u5206\u3002
2NF. \u6570\u636e\u4e0d\u80fd\u91cd\u590d\uff0c\u5fc5\u987b\u4fdd\u8bc1\u552f\u4e00\u6027\u3002\u5fc5\u987b\u4f7f\u7528\u4e3b\u952e\u6765\u8fdb\u884c\u533a\u5206\u6bcf\u4e00\u884c\u6570\u636e\u3002
3NF. \u6570\u636e\u4e0d\u80fd\u5197\u4f59\uff0c\u5fc5\u987b\u4fdd\u8bc1\u5173\u8054\u6027\u3002\u5197\u4f59\u7684\u6570\u636e\u5fc5\u987b\u4f7f\u7528\u53e6\u5916\u7684\u6570\u636e\u8868\u5b58\u653e\uff0c\u5e76\u4e0e\u5f53\u524d\u8868\u8fdb\u884c\u5173\u8054\u3002
\u200b
\u57fa\u4e8e\u5b9e\u9645\u4e1a\u52a1\u7684\u89d2\u5ea6\u51fa\u53d1\uff0c\u8bbe\u8ba1\u51fa\u8fdd\u80cc\u4e86\u8303\u5f0f\u7406\u8bba\u7684\u8868\u7ed3\u6784\u3002
\u9006\u8303\u5f0f\uff1a\u4ee5\u7a7a\u95f4\u6362\u65f6\u95f4
\u200b
class table:
id name teacher
1 301\u73ed \u674e\u8001\u5e08
2 302\u73ed \u738b\u8001\u5e08
\u200b
student table:
id name class_id
1 xiaoming 1
2 xiaohong 2
3 xiaohui 3
\u200b<\/pre>\n\n\n\n\u4e09\u8303\u5f0f+\u9006\u8303\u5f0f<\/p>\n\n\n\n \u7b2c\u4e09\u8303\u5f0f\uff1a\u6570\u636e\u4e0d\u80fd\u5197\u4f59\uff0c\u628a\u5173\u8054\u6027\u4e0d\u5f3a\u7684\u6570\u636e\u53ef\u4ee5\u79fb\u9664\u5230\u53e6\u4e00\u4e2a\u8868\u4e2d\u3002\u4f7f\u7528\u5916\u952e\u8fdb\u884c\u7ba1\u7406\u3002<\/p>\n\n\n\n 1\u5bf91\uff1a\u628a\u4e3b\u8868\u7684\u4e3b\u952e\u653e\u5230\u9644\u52a0\u8868\u4e2d\u4f5c\u4e3a\u5916\u952e\u5b58\u5728\u3002
\u200b
\u5546\u54c1\u4fe1\u606f\u8868
id name price image \u63cf\u8ff0 \u552e\u540e \u914d\u7f6e \u5305\u88c5
1 PC-1-gr 100 1.png
2 PC-1-re 100 1.png
3 PC-1-he 100 1.png
4 PC-1-bu 100 1.png
\u200b
1\u5bf9\u591a\uff1a\u628a\u4e3b\u8868(1) \u7684\u4e3b\u952e\u653e\u5230\u9644\u52a0\u8868(\u591a)\u4f5c\u4e3a\u5916\u952e\u5b58\u5728\u3002
\u8ba2\u5355\u4fe1\u606f\u8868 \u8ba2\u5355\u8be6\u60c5\u8868
1\u4e2a\u8ba2\u5355 ----> \u591a\u4e2a\u5546\u54c1
\u200b
\u591a\u5bf9\u591a\uff1a\u628a\u4e3b\u8868(\u591a)\u7684\u4e3b\u952e\u548c\u9644\u52a0\u8868\u7684(\u591a)\u4e3b\u952e\uff0c\u653e\u5230\u7b2c\u4e09\u65b9\u8868(\u5173\u7cfb\u8868)\u4e2d\u4f5c\u4e3a\u5916\u952e\u3002
\u7528\u6237\u8868 \u8bfe\u7a0b\u8868
1 xiaoming 1 python
2 xiaohong 2 django
3 xiaolong 3 flask
\u7528\u6237\u4e0e\u8bfe\u7a0b\u7684\u8d2d\u4e70\u5173\u7cfb\u8868
user_id course
1 1
1 2
1 3<\/pre>\n\n\n\n\u6a21\u578b\u4e4b\u95f4\u7684\u5173\u8054<\/h3>\n\n\n\n\u4e00\u5bf9\u4e00<\/h4>\n\n\n\n\u5e38\u89c1\u7684\u4e1a\u52a1\uff1a\u4e3b\u8868\u548c\u8be6\u60c5\u8868\uff08\u7528\u6237\u3001\u4f1a\u5458\u3001\u5b66\u751f\u3001\u5546\u54c1\u3001\u6587\u7ae0\u3001\u4e3b\u673a\uff09<\/p>\n\n\n\n class Student(db.Model):
\"\"\"\u4e2a\u4eba\u4fe1\u606f\u4e3b\u8868\"\"\"
....
# \u5173\u8054\u5c5e\u6027\uff0c\u8fd9\u4e2a\u4e0d\u4f1a\u88ab\u89c6\u4f5c\u8868\u5b57\u6bb5\uff0c\u53ea\u662f\u6a21\u578b\u5bf9\u8c61\u7684\u5c5e\u6027\u3002
# \u56e0\u4e3aStudentInfo\u548cStudent\u662f\u4e00\u5bf9\u4e00\u7684\u5173\u7cfb\uff0c\u6240\u4ee5uselist=False\u8868\u793a\u5173\u8054\u4e00\u4e2a\u6570\u636e
info = db.relationship(\"StudentInfo\", uselist=False, backref=\"own\")
\u200b
\u200b
class StudentInfo(db.Model):
\"\"\"\u4e2a\u4eba\u4fe1\u606f\u9644\u52a0\u8868\"\"\"
\u200b
# \u5916\u952e\uff0c
# \u5982\u679c\u662f\u4e00\u5bf9\u4e00\uff0c\u5219\u5916\u952e\u653e\u5728\u9644\u52a0\u8868\u5bf9\u5e94\u7684\u6a21\u578b\u4e2d
# \u5982\u679c\u662f\u4e00\u5bf9\u591a\uff0c\u5219\u5916\u952e\u653e\u5728\u591a\u7684\u8868\u5bf9\u8c61\u7684\u6a21\u578b\u4e2d
# sid = db.Column(db.Integer, db.ForeignKey(Student.id),comment=\"\u5916\u952e\")
sid = db.Column(db.Integer, db.ForeignKey(\"student\u8868\u540d.\u4e3b\u952e\"),comment=\"\u5916\u952e\")<\/pre>\n\n\n\n\u5173\u8054\u5c5e\u6027\u58f0\u660e\u5728\u4e3b\u6a21\u578b\u4e2d\u3010\u6700\u5e38\u7528\u3011<\/h5>\n\n\n\n\u4ee3\u7801\uff1a<\/p>\n\n\n\n import json
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import func
\u200b
db = SQLAlchemy()
app = Flask(__name__, template_folder=\"templates\", static_folder=\"static\")
\u200b
# \u914d\u7f6e
app.config.update({
\"DEBUG\": True,
\"SQLALCHEMY_DATABASE_URI\": \"mysql:\/\/root:123@127.0.0.1:3306\/flaskdemo?charset=utf8mb4\",
# \u5982\u679c\u4f7f\u7528pymysql\uff0c\u5219\u9700\u8981\u5728\u8fde\u63a5\u65f6\u6307\u5b9apymysql
# \"SQLALCHEMY_DATABASE_URI\": \"mysql+pymysql:\/\/root:123@127.0.0.1:3306\/flaskdemo?charset=utf8mb4\"
# \u52a8\u6001\u8ffd\u8e2a\u4fee\u6539\u8bbe\u7f6e\uff0c\u5982\u672a\u8bbe\u7f6e\u53ea\u4f1a\u63d0\u793a\u8b66\u544a\uff0c\u8bbe\u7f6eFalse\u5373\u53ef
\"SQLALCHEMY_TRACK_MODIFICATIONS\": False,
# ORM\u6267\u884cSQL\u67e5\u8be2\u65f6\u662f\u54e6\u5426\u663e\u793a\u539f\u59cbSQL\u8bed\u53e5\uff0cdebug\u6a21\u5f0f\u4e0b\u53ef\u4ee5\u5f00\u542f
\"SQLALCHEMY_ECHO\": True,
})
\u200b
db.init_app(app)
\u200b
\u200b
class Student(db.Model):
\"\"\"\u5b66\u751f\u7ba1\u7406\"\"\"
__tablename__ = \"tb_student\" # \u8868\u540d
id = db.Column(db.Integer, primary_key=True, comment=\"\u4e3b\u952e\")
name = db.Column(db.String(15), index=True, comment=\"\u59d3\u540d\")
age = db.Column(db.SmallInteger, comment=\"\u5e74\u9f84\")
sex = db.Column(db.SmallInteger, comment=\"\u6027\u522b\")
email = db.Column(db.String(255), unique=True, comment=\"\u90ae\u7bb1\u5730\u5740\")
money = db.Column(db.Numeric(10,2), default=0.0, comment=\"\u94b1\u5305\")
# \u6a21\u578b\u7684\u5173\u8054\u5c5e\u6027\uff0c\u4e0d\u4f1a\u5728\u6570\u636e\u8868\u521b\u5efa\u5b57\u6bb5
# \u56e0\u4e3aStudentInfo\u548cStudent\u662f\u4e00\u5bf9\u4e00\u7684\u5173\u7cfb\uff0c\u6240\u4ee5uselist=False\u8868\u793a\u5173\u8054\u4e00\u4e2a\u6570\u636e
info = db.relationship(\"StudentInfo\", uselist=False, backref=\"student\")
\u200b
def __repr__(self):
return json.dumps(self.__to_dict__, ensure_ascii=False)
\u200b
@property
def __to_dict__(self):
return {
\"id\": self.id,
\"name\": self.name,
\"age\": self.age,
\"sex\": self.sex,
\"email\": self.email,
\"money\": float(self.money),
}
\u200b
\u200b
class StudentInfo(db.Model):
__tablename__ = \"tb_student_info\" # \u8868\u540d
id = db.Column(db.Integer, primary_key=True, comment=\"\u4e3b\u952e\")
# \u9644\u52a0\u8868\u7684\u5916\u952e\uff0c\u5c31\u662f\u4e3b\u8868\u7684\u4e3b\u952e
# \u5982\u679c\u662f\u4e00\u5bf9\u4e00\uff0c\u5219\u5916\u952e\u653e\u5728\u9644\u52a0\u8868\u5bf9\u5e94\u7684\u6a21\u578b\u4e2d
# \u5982\u679c\u662f\u4e00\u5bf9\u591a\uff0c\u5219\u5916\u952e\u653e\u5728\u591a\u7684\u9644\u52a0\u8868\u5bf9\u5e94\u7684\u6a21\u578b\u4e2d
sid = db.Column(db.Integer, db.ForeignKey(\"tb_student.id\"), comment=\"\u5916\u952e\") # mysql\u5b57\u6bb5
# student = db.relationship(\"Student\", uselist=False, backref=\"info\") # ORM\u5173\u8054\u5c5e\u6027
mobile = db.Column(db.String(15), index=True, comment=\"\u624b\u673a\u53f7\u7801\")
address = db.Column(db.String(255), nullable=True, comment=\"\u5bb6\u5ead\u5730\u5740\")
\u200b
def __repr__(self):
return json.dumps(self.__to_dict__, ensure_ascii=False)
\u200b
@property
def __to_dict__(self):
return {
\"id\": self.id,
\"sid\": self.sid,
\"mobile\": self.mobile,
\"address\": self.address,
}
\u200b
\u200b
@app.route(\"\/create\")
def create_table():
db.create_all() # \u4e3a\u9879\u76ee\u4e2d\u88ab\u8bc6\u522b\u7684\u6240\u6709\u6a21\u578b\u521b\u5efa\u6570\u636e\u8868
return \"ok\"
\u200b
\u200b
@app.route(\"\/drop\")
def drop_table():
db.drop_all() # \u4e3a\u9879\u76ee\u4e2d\u88ab\u8bc6\u522b\u7684\u6240\u6709\u6a21\u578b\u5220\u9664\u6570\u636e\u8868
return \"ok\"
\u200b
\u200b
@app.route(\"\/a1\")
def a1():
\"\"\"\u6dfb\u52a0\u64cd\u4f5c\"\"\"
# \u6dfb\u52a0\u4e3b\u8868\u4fe1\u606f\u7684\u65f6\u5019\u901a\u8fc7\u5173\u8054\u5c5e\u6027db.relationship\u540c\u6b65\u6dfb\u52a0\u9644\u4ef6\u8868\u4fe1\u606f
student = Student(
name=\"xiaolan01\",
age=16,
sex=False,
money=10000,
email=\"xiaolan01@qq.com\",
info=StudentInfo(
address=\"\u5317\u4eac\u5e02\u660c\u5e73\u533a\u767e\u6c99\u8def203\u53f7\",
mobile=\"13312345672\"
)
)
\u200b
db.session.add(student)
db.session.commit()
\u200b
return \"ok\"
\u200b
\u200b
@app.route(\"\/a2\")
def a2():
\"\"\"\u6dfb\u52a0\u64cd\u4f5c\"\"\"
# \u5047\u8bbe\u5df2\u7ecf\u5b58\u5728\u4e3b\u8868\u4fe1\u606f\uff0c\u540e\u9762\u518d\u8865\u5145\u9644\u52a0\u8868\u4fe1\u606f
# student = Student(
# name=\"xiaolan03\",
# age=16,
# sex=False,
# money=10000,
# email=\"xiaolan03@qq.com\",
# )
#
# db.session.add(student)
# db.session.commit()
\u200b
# \u4e0a\u9762\u4ee3\u7801\u5148\u6267\u884c\u4e86\uff0c\u73b0\u67091\u4e2a\u5b66\u751f\u6ca1\u6709\u9644\u52a0\u4fe1\u606f\u7684\uff0c\u6211\u4eec\u53ef\u4ee5\u5728\u540e\u7eed\u4ee3\u7801\u901a\u8fc7\u67e5\u8be2\u4e3b\u8868\u7684\u4e3b\u952e\uff0c\u8865\u5145\u9644\u52a0\u8868\u6570\u636e
student = Student.query.get(3)
if not student.info:
\"\"\"\u6dfb\u52a0\u9644\u52a0\u8868\u6570\u636e\"\"\"
\"\"\"\u65b9\u5f0f1\"\"\"
# student.info = StudentInfo(mobile=13300010002, address=\"\u5317\u4eac\u5e02\u660c\u5e73\u533a\u767e\u6c99\u8def205\u53f7\",)
# db.session.commit()
\u200b
\"\"\"\u65b9\u5f0f2\"\"\"
# info = StudentInfo(
# student=student, # \u5173\u8054\u5c5e\u6027
# mobile=13300010002,
# address=\"\u5317\u4eac\u5e02\u660c\u5e73\u533a\u767e\u6c99\u8def205\u53f7\",
# )
#
# db.session.add(info)
# db.session.commit()
\u200b
\"\"\"\u65b9\u5f0f3\"\"\"
info = StudentInfo(
sid=student.id, # \u5916\u952e
mobile=13300010002,
address=\"\u5317\u4eac\u5e02\u660c\u5e73\u533a\u767e\u6c99\u8def205\u53f7\",
)
\u200b
db.session.add(info)
db.session.commit()
\u200b
return \"ok\"
\u200b
\u200b
@app.route(\"\/a3\")
def a3():
\"\"\"\u6dfb\u52a0\u64cd\u4f5c\"\"\"
# \u6dfb\u52a0\u9644\u52a0\u6a21\u578b\u6570\u636e\u7684\u540c\u65f6\uff0c\u628a\u4e3b\u6a21\u578b\u4e5f\u8fdb\u884c\u6dfb\u52a0
info = StudentInfo(
mobile=13300010003,
address=\"\u5317\u4eac\u5e02\u660c\u5e73\u533a\u767e\u6c99\u8def206\u53f7\",
student=Student(
name=\"xiaolan04\",
age=17,
sex=False,
money=10000,
email=\"xiaolan04@qq.com\",
),
)
\u200b
db.session.add(info)
db.session.commit()
\u200b
return \"ok\"
\u200b
\u200b
@app.route(\"\/g1\")
def g1():
\"\"\"\u67e5\u8be2\u64cd\u4f5c\"\"\"
# # \u6b63\u5411\u5173\u8054----> \u4ece\u4e3b\u6a21\u578b\u67e5\u8be2\u5916\u952e\u6a21\u578b
# student = Student.query.get(2)
# print(student.name) # xiaolan01
# # \u5982\u679c\u4e3b\u6a21\u578b\u6ca1\u6709\u8c03\u7528\u5173\u8054\u5c5e\u6027\u67e5\u8be2\u9644\u52a0\u6a21\u578b\u5bf9\u8c61\uff0c\u5219ORM\u4e0d\u4f1a\u6267\u884c\u67e5\u8be2\u5173\u8054\u6a21\u578b\u7684SQL\u8bed\u53e5
# print(student.info) # {\"id\": 2, \"sid\": 2, \"mobile\": \"13312345672\", \"address\": \"\u5317\u4eac\u5e02\u660c\u5e73\u533a\u767e\u6c99\u8def203\u53f7\"}
# print(student.info.address) # \u5317\u4eac\u5e02\u660c\u5e73\u533a\u767e\u6c99\u8def203\u53f7
\u200b
# \u53cd\u5411\u5173\u8054----> \u4ece\u5916\u952e\u6a21\u578b\u67e5\u8be2\u4e3b\u6a21\u578b
student_info = StudentInfo.query.filter(StudentInfo.mobile == \"15012345678\").first()
print(student_info.address) # \u5317\u4eac\u5e02\u660c\u5e73\u533a\u767e\u6c99\u8def206\u53f7
# \u5982\u679c\u9644\u52a0\u6a21\u578b\u6ca1\u6709\u8c03\u7528\u5173\u8054\u5c5e\u6027\u67e5\u8be2\u4e3b\u6a21\u578b\u5bf9\u8c61\uff0c\u5219ORM\u4e0d\u4f1a\u6267\u884c\u67e5\u8be2\u4e3b\u6a21\u578b\u7684SQL\u8bed\u53e5
print(student_info.student) # {\"id\": 4, \"name\": \"xiaolan04\", \"age\": 17, \"sex\": 0, \"email\": \"xiaolan04@qq.com\", \"money\": 10000.0}
print(student_info.student.name) # xiaolan04
print(student_info.sid) # 4
\u200b
return \"ok\"
\u200b
\u200b
@app.route(\"\/u1\")
def u1():
\"\"\"\u4fee\u6539\u6570\u636e\"\"\"
# # \u901a\u8fc7\u4e3b\u6a21\u578b\u4f7f\u7528\u5173\u8054\u5c5e\u6027\u4fee\u6539\u9644\u52a0\u6a21\u578b\u7684\u6570\u636e
# student = Student.query.get(2)
# student.info.address = \"\u5e7f\u5dde\u5e02\u5929\u6cb3\u533a\u5929\u6cb3\u4e1c\u8def103\u53f7\"
# db.session.commit()
\u200b
# \u4e5f\u53ef\u4ee5\u901a\u8fc7\u9644\u52a0\u6a21\u578b\u76f4\u63a5\u4fee\u6539\u4e3b\u6a21\u578b\u7684\u6570\u636e
student_info = StudentInfo.query.filter(StudentInfo.mobile == \"13312345678\").first()
# \u5982\u679c\u8981\u4fee\u6539\u7684\u6570\u636e\uff0c\u4e0e\u6570\u636e\u8868\u7684\u6ca1\u6709\u6539\u52a8\uff0c\u5219\u4e0d\u4f1a\u6267\u884c\u66f4\u65b0\u7684SQL\u8bed\u53e5
student_info.student.age = 23
db.session.commit()
\u200b
return \"ok\"
\u200b
\u200b
@app.route(\"\/d1\")
def d1():
\"\"\"\u5220\u9664\u6570\u636e\"\"\"
\u200b
# # 1. \u5982\u679c\u5220\u9664\u4e3b\u6a21\u578b\u6570\u636e\uff0c\u5219\u4f1a\u5148\u5c06\u9644\u52a0\u6a21\u578b\u7684\u5916\u952e\u8bbe\u7f6e\u4e3anull\uff0c\u7136\u540e\u624d\u4f1a\u5220\u9664\u4e3b\u6a21\u578b\u5bf9\u8c61
# student = Student.query.get(4)
# db.session.delete(student)
# db.session.commit()
\u200b
# 2. \u5982\u679c\u5220\u9664\u9644\u52a0\u6a21\u578b\u6570\u636e\uff0c\u5219\u76f4\u63a5\u5220\u9664\uff0c\u4e0d\u4f1a\u4fee\u6539\u4e3b\u6a21\u578b\u6570\u636e
StudentInfo.query.filter(StudentInfo.mobile == \"13312345678\").delete()
db.session.commit()
return \"ok\"
\u200b
\u200b
if __name__ == '__main__':
app.run()<\/pre>\n\n\n\n\u5728\u5916\u952e\u6a21\u578b\u4e2d\u58f0\u660e\u5173\u8054\u5c5e\u6027<\/h5>\n\n\n\n\u4ee3\u7801\uff1a<\/p>\n\n\n\n import json
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import backref
\u200b
db = SQLAlchemy()
app = Flask(__name__, template_folder=\"templates\", static_folder=\"static\")
\u200b
# \u914d\u7f6e
app.config.update({
\"DEBUG\": True,
\"SQLALCHEMY_DATABASE_URI\": \"mysql:\/\/root:123@127.0.0.1:3306\/flaskdemo?charset=utf8mb4\",
# \u5982\u679c\u4f7f\u7528pymysql\uff0c\u5219\u9700\u8981\u5728\u8fde\u63a5\u65f6\u6307\u5b9apymysql
# \"SQLALCHEMY_DATABASE_URI\": \"mysql+pymysql:\/\/root:123@127.0.0.1:3306\/flaskdemo?charset=utf8mb4\"
# \u52a8\u6001\u8ffd\u8e2a\u4fee\u6539\u8bbe\u7f6e\uff0c\u5982\u672a\u8bbe\u7f6e\u53ea\u4f1a\u63d0\u793a\u8b66\u544a\uff0c\u8bbe\u7f6eFalse\u5373\u53ef
\"SQLALCHEMY_TRACK_MODIFICATIONS\": False,
# ORM\u6267\u884cSQL\u67e5\u8be2\u65f6\u662f\u54e6\u5426\u663e\u793a\u539f\u59cbSQL\u8bed\u53e5\uff0cdebug\u6a21\u5f0f\u4e0b\u53ef\u4ee5\u5f00\u542f
\"SQLALCHEMY_ECHO\": True,
})
\u200b
db.init_app(app)
\u200b
\u200b
class Student(db.Model):
\"\"\"\u5b66\u751f\u7ba1\u7406\"\"\"
__tablename__ = \"tb_student\" # \u8868\u540d
id = db.Column(db.Integer, primary_key=True, comment=\"\u4e3b\u952e\")
name = db.Column(db.String(15), index=True, comment=\"\u59d3\u540d\")
age = db.Column(db.SmallInteger, comment=\"\u5e74\u9f84\")
sex = db.Column(db.SmallInteger, comment=\"\u6027\u522b\")
email = db.Column(db.String(255), unique=True, comment=\"\u90ae\u7bb1\u5730\u5740\")
money = db.Column(db.Numeric(10, 2), default=0.0, comment=\"\u94b1\u5305\")
\u200b
def __repr__(self):
return json.dumps(self.__to_dict__, ensure_ascii=False)
\u200b
@property
def __to_dict__(self):
return {
\"id\": self.id,
\"name\": self.name,
\"age\": self.age,
\"sex\": self.sex,
\"email\": self.email,
\"money\": float(self.money),
}
\u200b
\u200b
class StudentInfo(db.Model):
__tablename__ = \"tb_student_info\" # \u8868\u540d
id = db.Column(db.Integer, primary_key=True, comment=\"\u4e3b\u952e\")
# \u9644\u52a0\u8868\u7684\u5916\u952e\uff0c\u5c31\u662f\u4e3b\u8868\u7684\u4e3b\u952e
# \u5982\u679c\u662f\u4e00\u5bf9\u4e00\uff0c\u5219\u5916\u952e\u653e\u5728\u9644\u52a0\u8868\u5bf9\u5e94\u7684\u6a21\u578b\u4e2d
# \u5982\u679c\u662f\u4e00\u5bf9\u591a\uff0c\u5219\u5916\u952e\u653e\u5728\u591a\u7684\u9644\u52a0\u8868\u5bf9\u5e94\u7684\u6a21\u578b\u4e2d
sid = db.Column(db.Integer, db.ForeignKey(\"tb_student.id\"), comment=\"\u5916\u952e\") # mysql\u5b57\u6bb5
# \u6a21\u578b\u7684\u5173\u8054\u5c5e\u6027\uff0c\u4e0d\u4f1a\u5728\u6570\u636e\u8868\u521b\u5efa\u5b57\u6bb5
# \u56e0\u4e3aStudentInfo\u548cStudent\u662f\u4e00\u5bf9\u4e00\u7684\u5173\u7cfb\uff0c\u6240\u4ee5uselist=False\u8868\u793a\u5173\u8054\u4e00\u4e2a\u6570\u636e
student = db.relationship(\"Student\", uselist=False, backref=backref(\"info\", uselist=False)) # ORM\u5173\u8054\u5c5e\u6027
mobile = db.Column(db.String(15), index=True, comment=\"\u624b\u673a\u53f7\u7801\")
address = db.Column(db.String(255), nullable=True, comment=\"\u5bb6\u5ead\u5730\u5740\")
\u200b
def __repr__(self):
return json.dumps(self.__to_dict__, ensure_ascii=False)
\u200b
@property
def __to_dict__(self):
return {
\"id\": self.id,
\"sid\": self.sid,
\"mobile\": self.mobile,
\"address\": self.address,
}
\u200b
\u200b
@app.route(\"\/create\")
def create_table():
db.create_all() # \u4e3a\u9879\u76ee\u4e2d\u88ab\u8bc6\u522b\u7684\u6240\u6709\u6a21\u578b\u521b\u5efa\u6570\u636e\u8868
return \"ok\"
\u200b
\u200b
@app.route(\"\/drop\")
def drop_table():
db.drop_all() # \u4e3a\u9879\u76ee\u4e2d\u88ab\u8bc6\u522b\u7684\u6240\u6709\u6a21\u578b\u5220\u9664\u6570\u636e\u8868
return \"ok\"
\u200b
\u200b
@app.route(\"\/a1\")
def a1():
\"\"\"\u6dfb\u52a0\u64cd\u4f5c\"\"\"
# \u6dfb\u52a0\u4e3b\u8868\u4fe1\u606f\u7684\u65f6\u5019\u901a\u8fc7\u5173\u8054\u5c5e\u6027db.relationship\u540c\u6b65\u6dfb\u52a0\u9644\u4ef6\u8868\u4fe1\u606f
student = Student(
name=\"xiaolan01\",
age=16,
sex=False,
money=10000,
email=\"xiaolan01@qq.com\",
info=StudentInfo(
address=\"\u5317\u4eac\u5e02\u660c\u5e73\u533a\u767e\u6c99\u8def203\u53f7\",
mobile=\"13312345672\"
)
)
\u200b
db.session.add(student)
db.session.commit()
\u200b
return \"ok\"
\u200b
\u200b
@app.route(\"\/a2\")
def a2():
\"\"\"\u6dfb\u52a0\u64cd\u4f5c\"\"\"
# \u5047\u8bbe\u5df2\u7ecf\u5b58\u5728\u4e3b\u8868\u4fe1\u606f\uff0c\u540e\u9762\u518d\u8865\u5145\u9644\u52a0\u8868\u4fe1\u606f
# student = Student(
# name=\"xiaolan03\",
# age=16,
# sex=False,
# money=10000,
# email=\"xiaolan03@qq.com\",
# )
#
# db.session.add(student)
# db.session.commit()
\u200b
# \u4e0a\u9762\u4ee3\u7801\u5148\u6267\u884c\u4e86\uff0c\u73b0\u67091\u4e2a\u5b66\u751f\u6ca1\u6709\u9644\u52a0\u4fe1\u606f\u7684\uff0c\u6211\u4eec\u53ef\u4ee5\u5728\u540e\u7eed\u4ee3\u7801\u901a\u8fc7\u67e5\u8be2\u4e3b\u8868\u7684\u4e3b\u952e\uff0c\u8865\u5145\u9644\u52a0\u8868\u6570\u636e
student = Student.query.get(2)
if student and not student.info:
\"\"\"\u6dfb\u52a0\u9644\u52a0\u8868\u6570\u636e\"\"\"
\"\"\"\u65b9\u5f0f1\"\"\"
# student.info = StudentInfo(mobile=13300010002, address=\"\u5317\u4eac\u5e02\u660c\u5e73\u533a\u767e\u6c99\u8def205\u53f7\",)
# db.session.commit()
\u200b
\"\"\"\u65b9\u5f0f2\"\"\"
# info = StudentInfo(
# student=student, # \u5173\u8054\u5c5e\u6027
# mobile=13300010002,
# address=\"\u5317\u4eac\u5e02\u660c\u5e73\u533a\u767e\u6c99\u8def205\u53f7\",
# )
#
# db.session.add(info)
# db.session.commit()
\u200b
\"\"\"\u65b9\u5f0f3\"\"\"
info = StudentInfo(
sid=student.id, # \u5916\u952e
mobile=13300010002,
address=\"\u5317\u4eac\u5e02\u660c\u5e73\u533a\u767e\u6c99\u8def205\u53f7\",
)
\u200b
db.session.add(info)
db.session.commit()
\u200b
return \"ok\"
\u200b
\u200b
@app.route(\"\/a3\")
def a3():
\"\"\"\u6dfb\u52a0\u64cd\u4f5c\"\"\"
# \u6dfb\u52a0\u9644\u52a0\u6a21\u578b\u6570\u636e\u7684\u540c\u65f6\uff0c\u628a\u4e3b\u6a21\u578b\u4e5f\u8fdb\u884c\u6dfb\u52a0
info = StudentInfo(
mobile=13300010003,
address=\"\u5317\u4eac\u5e02\u660c\u5e73\u533a\u767e\u6c99\u8def206\u53f7\",
student=Student(
name=\"xiaolan04\",
age=17,
sex=False,
money=10000,
email=\"xiaolan04@qq.com\",
),
)
\u200b
db.session.add(info)
db.session.commit()
\u200b
return \"ok\"
\u200b
\u200b
@app.route(\"\/g1\")
def g1():
\"\"\"\u67e5\u8be2\u64cd\u4f5c\"\"\"
# # \u6b63\u5411\u5173\u8054----> \u4ece\u4e3b\u6a21\u578b\u67e5\u8be2\u5916\u952e\u6a21\u578b
# student = Student.query.get(2)
# print(student.name) # xiaolan01
# # \u5982\u679c\u4e3b\u6a21\u578b\u6ca1\u6709\u8c03\u7528\u5173\u8054\u5c5e\u6027\u67e5\u8be2\u9644\u52a0\u6a21\u578b\u5bf9\u8c61\uff0c\u5219ORM\u4e0d\u4f1a\u6267\u884c\u67e5\u8be2\u5173\u8054\u6a21\u578b\u7684SQL\u8bed\u53e5
# print(student.info) # {\"id\": 2, \"sid\": 2, \"mobile\": \"13312345672\", \"address\": \"\u5317\u4eac\u5e02\u660c\u5e73\u533a\u767e\u6c99\u8def203\u53f7\"}
# print(student.info.address) # \u5317\u4eac\u5e02\u660c\u5e73\u533a\u767e\u6c99\u8def203\u53f7
\u200b
# \u53cd\u5411\u5173\u8054----> \u4ece\u5916\u952e\u6a21\u578b\u67e5\u8be2\u4e3b\u6a21\u578b
student_info = StudentInfo.query.filter(StudentInfo.mobile == \"13300010003\").first()
print(student_info.address) # \u5317\u4eac\u5e02\u660c\u5e73\u533a\u767e\u6c99\u8def206\u53f7
# \u5982\u679c\u9644\u52a0\u6a21\u578b\u6ca1\u6709\u8c03\u7528\u5173\u8054\u5c5e\u6027\u67e5\u8be2\u4e3b\u6a21\u578b\u5bf9\u8c61\uff0c\u5219ORM\u4e0d\u4f1a\u6267\u884c\u67e5\u8be2\u4e3b\u6a21\u578b\u7684SQL\u8bed\u53e5
print(
student_info.student) # {\"id\": 3, \"name\": \"xiaolan04\", \"age\": 17, \"sex\": 0, \"email\": \"xiaolan04@qq.com\", \"money\": 10000.0}
print(student_info.student.name) # xiaolan04
print(student_info.sid) # 3
\u200b
return \"ok\"
\u200b
\u200b
@app.route(\"\/u1\")
def u1():
\"\"\"\u4fee\u6539\u6570\u636e\"\"\"
# \u901a\u8fc7\u4e3b\u6a21\u578b\u4f7f\u7528\u5173\u8054\u5c5e\u6027\u4fee\u6539\u9644\u52a0\u6a21\u578b\u7684\u6570\u636e
student = Student.query.get(2)
student.info.address = \"\u5e7f\u5dde\u5e02\u5929\u6cb3\u533a\u5929\u6cb3\u4e1c\u8def103\u53f7\"
db.session.commit()
\u200b
# # \u4e5f\u53ef\u4ee5\u901a\u8fc7\u9644\u52a0\u6a21\u578b\u76f4\u63a5\u4fee\u6539\u4e3b\u6a21\u578b\u7684\u6570\u636e
# student_info = StudentInfo.query.filter(StudentInfo.mobile == \"13312345672\").first()
# # \u5982\u679c\u8981\u4fee\u6539\u7684\u6570\u636e\uff0c\u4e0e\u6570\u636e\u8868\u7684\u6ca1\u6709\u6539\u52a8\uff0c\u5219\u4e0d\u4f1a\u6267\u884c\u66f4\u65b0\u7684SQL\u8bed\u53e5
# student_info.student.age = 23
# db.session.commit()
\u200b
return \"ok\"
\u200b
\u200b
@app.route(\"\/d1\")
def d1():
\"\"\"\u5220\u9664\u6570\u636e\"\"\"
\u200b
# # 1. \u5982\u679c\u5220\u9664\u4e3b\u6a21\u578b\u6570\u636e\uff0c\u5219\u4f1a\u5148\u5c06\u9644\u52a0\u6a21\u578b\u7684\u5916\u952e\u8bbe\u7f6e\u4e3anull\uff0c\u7136\u540e\u624d\u4f1a\u5220\u9664\u4e3b\u6a21\u578b\u5bf9\u8c61
student = Student.query.get(2)
db.session.delete(student)
db.session.commit()
\u200b
# 2. \u5982\u679c\u5220\u9664\u9644\u52a0\u6a21\u578b\u6570\u636e\uff0c\u5219\u76f4\u63a5\u5220\u9664\uff0c\u4e0d\u4f1a\u4fee\u6539\u4e3b\u6a21\u578b\u6570\u636e
# StudentInfo.query.filter(StudentInfo.mobile == \"13312345672\").delete()
# db.session.commit()
return \"ok\"
\u200b
\u200b
if __name__ == '__main__':
app.run()
\u200b<\/pre>\n\n\n\n\u4e00\u5bf9\u591a<\/h4>\n\n\n\n\u5e38\u89c1\u4e1a\u52a1\uff1a\u5546\u54c1\u5206\u7c7b\u548c\u5546\u54c1\u3001\u6587\u7ae0\u5206\u7c7b\u548c\u6587\u7ae0\u3001\u73ed\u7ea7\u4e0e\u5b66\u751f\u3001\u90e8\u95e8\u4e0e\u5458\u5de5\u3001\u89d2\u8272\u4e0e\u4f1a\u5458\u3001\u8ba2\u5355\u4e0e\u8ba2\u5355\u8be6\u60c5\u3001\u7528\u6237\u4e0e\u6536\u8d27\u5730\u5740\u3002\u3002\u3002<\/p>\n\n\n\n class User(db.Model):
...
# \u5173\u8054\u5c5e\u6027\uff0c\u4e00\u7684\u4e00\u65b9\u6dfb\u52a0\u6a21\u578b\u5173\u8054\u5c5e\u6027
address_list = db.relationship(\"UserAddress\", uselist=True, backref=\"user\", lazy='dynamic')
class UsertAddress(db.Model):
...
# \u5916\u952e\uff0c\u591a\u7684\u4e00\u65b9\u6a21\u578b\u4e2d\u6dfb\u52a0\u5916\u95f4
user_id = db.Column(db.ForeignKey(User.id))<\/pre>\n\n\n\n \n- \u5176\u4e2drealtionship\u63cf\u8ff0\u4e86Student\u548cStudentAddress\u7684\u5173\u7cfb\u3002\u7b2c1\u4e2a\u53c2\u6570\u4e3a\u5bf9\u5e94\u53c2\u7167\u7684\u7c7b”StudentAddress”<\/li>\n\n\n\n
- \u7b2c3\u4e2a\u53c2\u6570backref\u4e3a\u7c7bStudentAddress\u58f0\u660e\u5173\u8054\u5c5e\u6027<\/li>\n\n\n\n
- \u7b2c4\u4e2a\u53c2\u6570lazy\u51b3\u5b9a\u4e86\u4ec0\u4e48\u65f6\u5019SQLALchemy\u4ec0\u4e48\u65f6\u5019\u6267\u884c\u8bfb\u53d6\u5173\u8054\u6a21\u578b\u7684SQL\u8bed\u53e5\n
\n- lazy=’subquery’\uff0c\u67e5\u8be2\u5f53\u524d\u6570\u636e\u6a21\u578b\u65f6\uff0c\u91c7\u7528\u5b50\u67e5\u8be2(subquery)\uff0c\u628a\u5916\u952e\u6a21\u578b\u7684\u5c5e\u6027\u4e5f\u540c\u65f6\u67e5\u8be2\u51fa\u6765\u4e86\u3002<\/li>\n\n\n\n
- lazy=True\u6216lazy=’select’\uff0c\u67e5\u8be2\u5f53\u524d\u6570\u636e\u6a21\u578b\u65f6\uff0c\u4e0d\u4f1a\u628a\u5916\u952e\u6a21\u578b\u7684\u6570\u636e\u67e5\u8be2\u51fa\u6765\uff0c\u53ea\u6709\u64cd\u4f5c\u5230\u5916\u952e\u5173\u8054\u5c5e\u6027\u65f6\uff0c\u624d\u8fdb\u884c\u8fde\u8868\u67e5\u8be2\u6570\u636e[\u6267\u884cSQL]<\/li>\n\n\n\n
- lazy=’dynamic’\uff0c\u67e5\u8be2\u5f53\u524d\u6570\u636e\u6a21\u578b\u65f6\uff0c\u4e0d\u4f1a\u628a\u5916\u952e\u6a21\u578b\u7684\u6570\u636e\u7acb\u523b\u67e5\u8be2\u51fa\u6765\uff0c\u53ea\u6709\u64cd\u4f5c\u5230\u5916\u952e\u5173\u8054\u5c5e\u6027\u5e76\u64cd\u4f5c\u5916\u952e\u6a21\u578b\u5177\u4f53\u5b57\u6bb5\u65f6\uff0c\u624d\u8fdb\u884c\u8fde\u8868\u67e5\u8be2\u6570\u636e[\u6267\u884cSQL]<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- \u5e38\u7528\u7684lazy\u9009\u9879\uff1adynamic\u548cselect<\/li>\n<\/ul>\n\n\n\n
\u8bfe\u5802\u4ee3\u7801\uff1a<\/p>\n\n\n\n manage.py\uff0c\u4ee3\u7801\uff1a<\/p>\n\n\n\n import json
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import backref
\u200b
db = SQLAlchemy()
app = Flask(__name__, template_folder=\"templates\", static_folder=\"static\")
\u200b
# \u914d\u7f6e
app.config.update({
\"DEBUG\": True,
\"SQLALCHEMY_DATABASE_URI\": \"mysql:\/\/root:123@127.0.0.1:3306\/flaskdemo?charset=utf8mb4\",
# \u5982\u679c\u4f7f\u7528pymysql\uff0c\u5219\u9700\u8981\u5728\u8fde\u63a5\u65f6\u6307\u5b9apymysql
# \"SQLALCHEMY_DATABASE_URI\": \"mysql+pymysql:\/\/root:123@127.0.0.1:3306\/flaskdemo?charset=utf8mb4\"
# \u52a8\u6001\u8ffd\u8e2a\u4fee\u6539\u8bbe\u7f6e\uff0c\u5982\u672a\u8bbe\u7f6e\u53ea\u4f1a\u63d0\u793a\u8b66\u544a\uff0c\u8bbe\u7f6eFalse\u5373\u53ef
\"SQLALCHEMY_TRACK_MODIFICATIONS\": False,
# ORM\u6267\u884cSQL\u67e5\u8be2\u65f6\u662f\u54e6\u5426\u663e\u793a\u539f\u59cbSQL\u8bed\u53e5\uff0cdebug\u6a21\u5f0f\u4e0b\u53ef\u4ee5\u5f00\u542f
\"SQLALCHEMY_ECHO\": True,
})
\u200b
db.init_app(app)
\u200b
\u200b
class User(db.Model):
__tablename__ = \"tb_user\"
id = db.Column(db.Integer, primary_key=True, comment=\"\u4e3b\u952e\")
username = db.Column(db.String(50), unique=True, comment=\"\u7528\u6237\u540d\")
nickname = db.Column(db.String(50), index=True, comment=\"\u6635\u79f0\")
sex = db.Column(db.Boolean, default=True, comment=\"\u6027\u522b\")
money = db.Column(db.Numeric(8,2), default=0.0, comment=\"\u94b1\u5305\u4f59\u989d\")
address_list = db.relationship(\"UserAddress\", uselist=True, backref=\"user\", lazy=\"dynamic\")
\u200b
def __repr__(self):
return json.dumps(self.__to_dict__, ensure_ascii=False)
\u200b
@property
def __to_dict__(self):
return {
\"id\": self.id,
\"username\": self.username,
\"nickname\": self.nickname,
\"sex\": self.sex,
\"money\": float(self.money),
}
\u200b
\u200b
class UserAddress(db.Model):
__tablename__ = \"tb_user_address\"
id = db.Column(db.Integer, primary_key=True, comment=\"\u4e3b\u952e\")
name = db.Column(db.String(50), default=\"\u9ed8\u8ba4\", comment=\"\u540d\u79f0\")
province = db.Column(db.String(50), comment=\"\u7701\u4efd\")
city = db.Column(db.String(50), comment=\"\u57ce\u5e02\")
area = db.Column(db.String(50), comment=\"\u5730\u533a\")
address = db.Column(db.String(500), comment=\"\u8be6\u7ec6\u5730\u5740\")
mobile = db.Column(db.String(15), comment=\"\u6536\u8d27\u4eba\u7535\u8bdd\")
user_id = db.Column(db.Integer, db.ForeignKey(\"tb_user.id\"), comment=\"\u5916\u952e\")
\u200b
def __repr__(self):
return json.dumps(self.__to_dict__, ensure_ascii=False)
\u200b
@property
def __to_dict__(self):
return {
\"id\": self.id,
\"name\": self.name,
\"province\": self.province,
\"city\": self.city,
\"area\": self.area,
\"address\": self.address,
\"mobile\": self.mobile,
\"user_id\": self.user_id,
}
\u200b
\u200b
@app.route(\"\/create\")
def create_table():
db.create_all() # \u4e3a\u9879\u76ee\u4e2d\u88ab\u8bc6\u522b\u7684\u6240\u6709\u6a21\u578b\u521b\u5efa\u6570\u636e\u8868
return \"ok\"
\u200b
\u200b
@app.route(\"\/drop\")
def drop_table():
db.drop_all() # \u4e3a\u9879\u76ee\u4e2d\u88ab\u8bc6\u522b\u7684\u6240\u6709\u6a21\u578b\u5220\u9664\u6570\u636e\u8868
return \"ok\"
\u200b
\u200b
@app.route(\"\/\")
def index():
return \"ok\"
\u200b
\u200b
@app.route(\"\/a1\")
def a1():
\"\"\"\u6dfb\u52a0\u6570\u636e\"\"\"
# # \u6dfb\u52a0\u4e3b\u6a21\u578b\u7684\u540c\u65f6\u4e5f\u4f1a\u7ed9\u9644\u52a0\u6a21\u578b\u6dfb\u52a0\u6570\u636e\uff0c\u8fd9\u79cd\u60c5\u51b5\uff0c\u9644\u52a0\u6a21\u578b\u7684\u503c\u53ef\u4ee5\u4ee5\u5217\u8868\u5f62\u5f0f\u8fdb\u884c\u6dfb\u52a0\uff0c\u4e00\u6b21\u6027\u6dfb\u52a0\u591a\u4e2a\u9644\u52a0\u6a21\u578b\u6570\u636e\u8fdb\u53bb\u3002
# user = User(
# username=\"xiaolan001\",
# nickname=\"xiaolan001\",
# sex=False,
# money=10000,
# address_list=[
# UserAddress(name=\"\u516c\u53f8\", province=\"\u5317\u4eac\u5e02\", city=\"\u5317\u4eac\u5e02\", area=\"\u660c\u5e73\u533a\", address=\"\u767e\u6c99\u8def201\", mobile=\"13012345678\"),
# UserAddress(name=\"\u95e8\u53e3\u5c0f\u5356\u90e8\", province=\"\u5317\u4eac\u5e02\", city=\"\u5317\u4eac\u5e02\", area=\"\u660c\u5e73\u533a\", address=\"\u767e\u6c99\u8def202\", mobile=\"13012345677\"),
# UserAddress(name=\"\u5c0f\u533a\u95e8\u53e3\", province=\"\u5317\u4eac\u5e02\", city=\"\u5317\u4eac\u5e02\", area=\"\u660c\u5e73\u533a\", address=\"\u767e\u6c99\u8def203\", mobile=\"13012345676\"),
# ]
# )
# db.session.add(user)
# db.session.commit()
\u200b
# \u6dfb\u52a0\u5916\u952e\u6a21\u578b\u6570\u636e\u7684\u540c\u65f6\uff0c\u6dfb\u52a0\u4e3b\u6a21\u578b\u6570\u636e
address = UserAddress(
province=\"\u5929\u6d25\u5e02\",
city=\"\u5929\u6d25\u5e02\",
area=\"\u9759\u6d77\u533a\",
address=\"\u9759\u5b89\u8def1103\u53f7\",
user=User(
username=\"xiaolan02\",
nickname=\"xiaolan02\",
money=10000,
sex=False,
)
)
db.session.add(address)
db.session.commit()
\u200b
return \"ok\"
\u200b
\u200b
@app.route(\"\/q1\")
def q1():
# \u6b63\u5411\u5173\u8054----> \u4ece\u4e3b\u6a21\u578b\u67e5\u8be2\u5916\u952e\u6a21\u578b
student = User.query.filter(User.username == \"xiaolan001\").first()
\u200b
\u200b
# \u89c2\u5bdf\u8fde\u8868SQL\u8bed\u53e5\u7684\u6267\u884c
\"\"\" lazy=\"select\" || lazy=True \"\"\"
# print(student) # \u6ca1\u6709\u67e5\u8be2\u9644\u52a0\u8868
# print(student.address_list) # \u67e5\u8be2\u4e86\u9644\u52a0\u8868
\u200b
\"\"\" lazy=\"subquery\" \"\"\"
# print(student) # \u5df2\u7ecf\u67e5\u8be2\u4e86\u9644\u52a0\u8868\uff0c\u5982\u679c\u6ca1\u6709\u4f7f\u7528\u5230\u5916\u952e\u6a21\u578b\u7684\u6570\u636e\uff0c\u5219\u672c\u6b21\u67e5\u8be2\u5b58\u5728\u8d44\u6e90\u6d6a\u8d39\uff01\uff01
\u200b
\"\"\" lazy=\"dynamic\" \"\"\"
# print(student) # \u6ca1\u6709\u67e5\u8be2\u9644\u52a0\u8868
# student.address_list # \u6ca1\u6709\u67e5\u8be2\u9644\u52a0\u6a21\u578b\u7684SQL\u8bed\u53e5
# print( student.address_list ) # \u53ea\u8981\u8bbf\u95ee\u5173\u8054\u6a21\u578b\u7684\u5177\u4f53\u5b57\u6bb5\u624d\u771f\u6b63\u6267\u884c\uff0c\u5982\u679c\u6d77\u91cf\u6570\u91cf\u67e5\u8be2\uff0c\u5219\u672c\u6b21\u67e5\u8be2\u4f1a\u5f71\u54cd\u8fd4\u56de\u6570\u636e\u7ed9\u5ba2\u6237\u7aef\u7684\u65f6\u95f4
\u200b
\u200b
print( student.address_list[0] ) # \u83b7\u53d6\u8fd4\u56de\u5217\u8868\u7684\u7b2c1\u4e2a\u6210\u5458
\u200b
\u200b
return \"ok\"
\u200b
if __name__ == '__main__':
app.run()<\/pre>\n\n\n\n\u591a\u5bf9\u591a<\/h4>\n\n\n\n\u5e38\u89c1\u4e1a\u52a1\uff1a\u7528\u6237\u6536\u85cf\u6587\u7ae0\u3001\u7528\u6237\u4e0e\u7528\u6237\u4e4b\u95f4\u7684\u597d\u53cb\u5173\u7cfb\u3001\u70b9\u8d5e\u3001\u8bc4\u8bba\u3001\u5173\u6ce8\u3001\u7528\u6237\u6d4f\u89c8\u5546\u54c1\u7684\u5386\u53f2\u8bb0\u5f55\u3001\u8ba2\u9605\u6587\u7ae0\u3001\u4e13\u9898\u4e0e\u5546\u54c1\/\u6587\u7ae0\u7684\u5173\u7cfb\u3001\u6d3b\u52a8\u4e0e\u5546\u54c1\u3002\u3002\u3002\u3002\u3002<\/p>\n\n\n\n # \u5173\u7cfb\u8868[\u8fd9\u79cd\u8868\uff0c\u65e0\u6cd5\u63d0\u4f9b\u7ed9python\u8fdb\u884c\u64cd\u4f5c\u7684\uff0c\u4ec5\u4ec5\u7528\u4e8e\u5728\u6570\u636e\u5e93\u4e2d\u8bb0\u5f55\u4e24\u4e2a\u6a21\u578b\u4e4b\u95f4\u7684\u5173\u7cfb]
# \u5173\u7cfb\u8868[\u8fd9\u79cd\u8868\uff0c\u65e0\u6cd5\u63d0\u4f9b\u7ed9python\u8fdb\u884c\u64cd\u4f5c\u7684\uff0c\u4ec5\u4ec5\u7528\u4e8e\u5728\u6570\u636e\u5e93\u4e2d\u8bb0\u5f55\u4e24\u4e2a\u6a21\u578b\u4e4b\u95f4\u7684\u5173\u7cfb]
student_and_course = db.Table(
\"table_student_course\",
db.Column(\"id\", db.Integer, primary_key=True, comment=\"\u4e3b\u952eID\"),
db.Column(\"sid\", db.Integer, db.ForeignKey(\"table_student.id\"), comment=\"\u5b66\u751f\"),
db.Column(\"cid\", db.Integer, db.ForeignKey(\"table_course.id\"), comment=\"\u8bfe\u7a0b\"),
db.Column(\"created_time\", db.DateTime, default=datetime.now, comment=\"\u8d2d\u4e70\u65f6\u95f4\"), # \u5f53\u524d\u5b57\u6bb5\u65e0\u6cd5\u64cd\u4f5c
)
\u200b
class Student(db.Model):
id = db.Column(db.Integer, primary_key=True,comment=\"\u4e3b\u952e\")
...
course_list = db.relationship(\"Course\", secondary=student_and_course, backref=\"student_list\", lazy=\"dynamic\")
\u200b
class Course(db.Model):
...
# \u5173\u7cfb\u6a21\u578b\uff0c[\u5173\u7cfb\u6a21\u578b\u548c\u5173\u7cfb\u8868\uff0c\u4efb\u9009\u5176\u4e00]
class Achievement(db.Model):
...<\/pre>\n\n\n\n\u57fa\u4e8e\u7b2c\u4e09\u65b9\u5173\u7cfb\u8868\u6784\u5efa\u591a\u5bf9\u591a<\/h5>\n\n\n\n\u4ee3\u7801\uff1a<\/p>\n\n\n\n import json
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import backref
from datetime import datetime
\u200b
db = SQLAlchemy()
app = Flask(__name__, template_folder=\"templates\", static_folder=\"static\")
\u200b
# \u914d\u7f6e
app.config.update({
\"DEBUG\": True,
\"SQLALCHEMY_DATABASE_URI\": \"mysql:\/\/root:123@127.0.0.1:3306\/flaskdemo?charset=utf8mb4\",
# \u5982\u679c\u4f7f\u7528pymysql\uff0c\u5219\u9700\u8981\u5728\u8fde\u63a5\u65f6\u6307\u5b9apymysql
# \"SQLALCHEMY_DATABASE_URI\": \"mysql+pymysql:\/\/root:123@127.0.0.1:3306\/flaskdemo?charset=utf8mb4\"
# \u52a8\u6001\u8ffd\u8e2a\u4fee\u6539\u8bbe\u7f6e\uff0c\u5982\u672a\u8bbe\u7f6e\u53ea\u4f1a\u63d0\u793a\u8b66\u544a\uff0c\u8bbe\u7f6eFalse\u5373\u53ef
\"SQLALCHEMY_TRACK_MODIFICATIONS\": False,
# ORM\u6267\u884cSQL\u67e5\u8be2\u65f6\u662f\u54e6\u5426\u663e\u793a\u539f\u59cbSQL\u8bed\u53e5\uff0cdebug\u6a21\u5f0f\u4e0b\u53ef\u4ee5\u5f00\u542f
\"SQLALCHEMY_ECHO\": True,
})
\u200b
db.init_app(app)
\u200b
# \u5173\u7cfb\u8868[\u8fd9\u79cd\u8868\uff0c\u65e0\u6cd5\u63d0\u4f9b\u7ed9python\u8fdb\u884c\u64cd\u4f5c\u7684\uff0c\u4ec5\u4ec5\u7528\u4e8e\u5728\u6570\u636e\u5e93\u4e2d\u8bb0\u5f55\u4e24\u4e2a\u6a21\u578b\u4e4b\u95f4\u7684\u5173\u7cfb]
student_and_course = db.Table(
\"table_student_course\",
db.Column(\"id\", db.Integer, primary_key=True, comment=\"\u4e3b\u952eID\"),
db.Column(\"sid\", db.Integer, db.ForeignKey(\"table_student.id\"), comment=\"\u5b66\u751f\"),
db.Column(\"cid\", db.Integer, db.ForeignKey(\"table_course.id\"), comment=\"\u8bfe\u7a0b\"),
db.Column(\"created_time\", db.DateTime, default=datetime.now, comment=\"\u8d2d\u4e70\u65f6\u95f4\"), # \u5f53\u524d\u5b57\u6bb5\u65e0\u6cd5\u64cd\u4f5c
)
\u200b
\u200b
class Student(db.Model):
\"\"\"\u5b66\u751f\u4fe1\u606f\u6a21\u578b\"\"\"
# \u58f0\u660e\u4e0e\u5f53\u524d\u6a21\u578b\u7ed1\u5b9a\u7684\u6570\u636e\u8868\u540d\u79f0
__tablename__ = \"table_student\"
id = db.Column(db.Integer, primary_key=True,comment=\"\u4e3b\u952e\")
name = db.Column(db.String(15), comment=\"\u59d3\u540d\")
age = db.Column(db.SmallInteger, comment=\"\u5e74\u9f84\")
sex = db.Column(db.Boolean, default=True, comment=\"\u6027\u522b\")
email = db.Column(db.String(128), comment=\"\u90ae\u7bb1\u5730\u5740\")
money = db.Column(db.Numeric(10, 2), default=0.0, comment=\"\u94b1\u5305\")
course_list = db.relationship(\"Course\", secondary=student_and_course, backref=\"student_list\", lazy=\"dynamic\")
\u200b
def __repr__(self):
return json.dumps(self.__to_dict__, ensure_ascii=False)
\u200b
@property
def __to_dict__(self):
return {
\"id\": self.id,
\"name\": self.name,
\"age\": self.age,
\"sex\": self.sex,
\"email\": self.email,
\"money\": float(self.money),
}
\u200b
\u200b
class Course(db.Model):
\"\"\"\u8bfe\u7a0b\u6570\u636e\u6a21\u578b\"\"\"
__tablename__ = \"table_course\"
id = db.Column(db.Integer, primary_key=True, comment=\"\u4e3b\u952e\")
name = db.Column(db.String(64), unique=True, comment=\"\u8bfe\u7a0b\")
price = db.Column(db.Numeric(7, 2), default=0.0, comment=\"\u4ef7\u683c\")
\u200b
def __repr__(self):
return json.dumps(self.__to_dict__, ensure_ascii=False)
\u200b
@property
def __to_dict__(self):
return {
\"id\": self.id,
\"name\": self.name,
\"price\": self.price,
}
\u200b
\u200b
@app.route(\"\/create\")
def create_table():
db.create_all() # \u4e3a\u9879\u76ee\u4e2d\u88ab\u8bc6\u522b\u7684\u6240\u6709\u6a21\u578b\u521b\u5efa\u6570\u636e\u8868
return \"ok\"
\u200b
\u200b
@app.route(\"\/drop\")
def drop_table():
db.drop_all() # \u4e3a\u9879\u76ee\u4e2d\u88ab\u8bc6\u522b\u7684\u6240\u6709\u6a21\u578b\u5220\u9664\u6570\u636e\u8868
return \"ok\"
\u200b
\u200b
@app.route(\"\/\")
def index():
return \"ok\"
\u200b
\u200b
@app.route(\"\/a1\")
def a1():
\"\"\"\u6dfb\u52a0\u6570\u636e\"\"\"
\"\"\"\u6dfb\u52a0\u5176\u4e2d\u4e00\u4e2a\u4e3b\u6a21\u578b\u6570\u636e\u65f6\uff0c\u540c\u65f6\u7ed1\u5b9a\u6dfb\u52a0\u53e6\u5916\u4e00\u4e2a\u4e3b\u6a21\u578b\u7684\u6570\u636e\uff0c\u8fd9\u4e2a\u8fc7\u7a0b\u4e2d\uff0c\u5173\u7cfb\u8868\u4f1a\u81ea\u52a8\u5199\u51652\u8005\u7684\u5173\u7cfb\u6570\u636e\uff0c\u7ed1\u5b9a2\u4e2a\u6a21\u578b\u4e4b\u95f4\u7684\u5173\u7cfb\"\"\"
# student = Student(
# name=\"xiaozhao\",
# age=13,
# sex=False,
# money=30000,
# email=\"xiaozhao@qq.com\",
# course_list=[
# Course(name=\"python\u5165\u95e8\", price=99.99),
# Course(name=\"python\u521d\u7ea7\", price=199.99),
# Course(name=\"python\u8fdb\u9636\", price=299.99),
# ]
# )
# db.session.add(student)
# db.session.commit()
\u200b
\"\"\"\u5728\u5b66\u751f\u62a5\u8bfb\u8bfe\u7a0b\u7684\u57fa\u7840\u4e0a\uff0c\u65b0\u589e\u62a5\u8bfb\u8bfe\u7a0b\u3002\"\"\"
# student = Student(
# name=\"xiaohong\",
# age=14,
# sex=False,
# money=30000,
# email=\"300000@qq.com\",
# )
# db.session.add(student)
# db.session.commit()
\u200b
# student = Student.query.filter(Student.name==\"xiaohong\").first()
# # \u76f4\u63a5\u91c7\u7528python\u5185\u7f6e\u7684list\u65b9\u6cd5\u64cd\u4f5c
# student.course_list.append(Course.query.get(3)) # \u65b0\u589e\u5df2\u7ecf\u5b58\u5728\u7684\u8bfe\u7a0b
# student.course_list.append(Course(name=\"python\u9876\u7ea7\", price=399.99)) # \u5df2\u6709\u8bfe\u7a0b\uff0c\u5e76\u8ba9\u5f53\u524d\u5b66\u751f\u62a5\u8bfb\u8be5\u8bfe\u7a0b
# db.session.commit()
\u200b
\"\"\"\u6dfb\u52a0\u5b66\u751f\u62a5\u8bfb\u8bfe\u7a0b\u7684\u6d4b\u8bd5\u6570\u636e\"\"\"
# student1 = Student.query.get(1)
# course_list = Course.query.filter(Course.id.in_([1,2])).all()
# student1.course_list.extend(course_list)
# db.session.commit()
\u200b
student2 = Student.query.get(2)
course_list = Course.query.filter(Course.id.in_([3,2])).all()
student2.course_list.extend(course_list)
db.session.commit()
\u200b
return \"ok\"
\u200b
\u200b
@app.route(\"\/q1\")
def q1():
\"\"\"\u67e5\u8be2\u64cd\u4f5c\"\"\"
# \u67e5\u8be2ID=4\u7684\u5b66\u751f\u62a5\u8bfb\u7684\u8bfe\u7a0b\u5217\u8868
# student = Student.query.get(2)
# course_list = [{\"name\":item.name,\"price\":float(item.price)} for item in student.course_list]
# print(course_list)
\u200b
# \u67e5\u8be2\u51fa2\u53f7\u8bfe\u7a0b\uff0c\u90fd\u6709\u90a3\u4e9b\u5b66\u751f\u5728\u8bfb\uff1f
course = Course.query.get(2)
student_list = [{\"name\":item.name,\"age\":item.age} for item in course.student_list]
print(student_list)
\u200b
return \"ok\"
\u200b
@app.route(\"\/u1\")
def u1():
\"\"\"\u66f4\u65b0\u6570\u636e\"\"\"
# # \u7ed9\u62a5\u8bfb\u4e863\u53f7\u8bfe\u7a0b\u7684\u540c\u5b66\uff0c\u8fd4\u73b0\u7ea2\u5305200\u5757\u94b1
# course = Course.query.get(3)
# for student in course.student_list:
# student.money+=200
# db.session.commit()
\u200b
# db.Table\u7684\u7f3a\u9677: \u65e0\u6cd5\u901a\u8fc7\u4e3b\u6a21\u578b\u76f4\u63a5\u64cd\u4f5cdb.Table\u4e2d\u7684\u5916\u952e\u4e4b\u5916\u7684\u5176\u4ed6\u5b57\u6bb5\uff0c\u4f8b\u5982\uff1a\u62a5\u8bfb\u8bfe\u7a0b\u7684\u65f6\u95f4
course = Course.query.get(3)
print(course.student_list)
\u200b
# \u89e3\u51b3\uff1a\u5728\u58f0\u660e2\u4e2a\u6a21\u578b\u662f\u591a\u5bf9\u591a\u7684\u5173\u8054\u5173\u7cfb\u65f6\uff0c\u628a\u5173\u8054\u5173\u7cfb\u4f7f\u7528\u7b2c\u4e09\u4e2a\u6a21\u578b\u6765\u521b\u5efa\u58f0\u660e\uff0c
# \u5c31\u662f\u4e0d\u8981\u4f7f\u7528db.Table\u6539\u6210\u6a21\u578b\u6765\u7ed1\u5b9a\u5173\u7cfb\uff0c\u628a\u6a21\u578b\u7684\u5bf9\u591a\u5bf9\u62c6\u5206\u62102\u4e2a1\u5bf9\u591a
\u200b
return \"ok\"
\u200b
\u200b
\u200b
if __name__ == '__main__':
app.run()
\u200b<\/pre>\n\n\n\n\u591a\u5bf9\u591a\uff0c\u4e5f\u53ef\u4ee5\u62c6\u89e3\u62103\u4e2a\u6a21\u578b\uff082\u4e2a\u4e3b\u6a21\u578b\uff0c1\u4e2a\u5173\u7cfb\u6a21\u578b\uff0c\u5173\u7cfb\u6a21\u578b\u4fdd\u5b58\u4e862\u4e2a\u4e3b\u6a21\u578b\u7684\u5916\u952e\uff09\uff0c\u5176\u4e2dtb_achievement\u4f5c\u4e3a\u5355\u72ec\u6a21\u578b\u5b58\u5728\u3002<\/p>\n\n\n\n \u57fa\u4e8e\u7b2c\u4e09\u65b9\u5173\u7cfb\u6a21\u578b\u6784\u5efa\u591a\u5bf9\u591a<\/h5>\n\n\n\n\u5728SQLAlchemy\u4e2d\uff0c\u57fa\u4e8edb.Table\u521b\u5efa\u7684\u5173\u7cfb\u8868\uff0c\u5982\u679c\u9700\u8981\u65b0\u589e\u9664\u4e86\u5916\u952e\u4ee5\u5916\u5176\u4ed6\u5b57\u6bb5\uff0c\u65e0\u6cd5\u64cd\u4f5c\u3002\u6240\u4ee5\u5c06\u6765\u5b9e\u73b0\u591a\u5bf9\u591a\u7684\u65f6\u5019\uff0c\u9664\u4e86\u4e0a\u9762db.Table\u65b9\u6848\u4ee5\u5916\uff0c\u8fd8\u53ef\u4ee5\u628a\u5173\u7cfb\u8868\u58f0\u660e\u6210\u6a21\u578b\u7684\u65b9\u6cd5\uff0c\u5982\u679c\u58f0\u660e\u6210\u6a21\u578b\uff0c\u5219\u539f\u6765\u8bfe\u7a0b\u548c\u5b66\u751f\u4e4b\u95f4\u7684\u591a\u5bf9\u591a\u7684\u5173\u7cfb\uff0c\u5c31\u4f1a\u53d8\u6210\u8fdc\u7a0b\u76841\u5bf9\u591a\u4e86\u3002<\/p>\n\n\n\n \u4ee3\u7801\uff1a<\/p>\n\n\n\n import json
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import backref
from datetime import datetime
\u200b
db = SQLAlchemy()
app = Flask(__name__, template_folder=\"templates\", static_folder=\"static\")
\u200b
# \u914d\u7f6e
app.config.update({
\"DEBUG\": True,
\"SQLALCHEMY_DATABASE_URI\": \"mysql:\/\/root:123@127.0.0.1:3306\/flaskdemo?charset=utf8mb4\",
# \u5982\u679c\u4f7f\u7528pymysql\uff0c\u5219\u9700\u8981\u5728\u8fde\u63a5\u65f6\u6307\u5b9apymysql
# \"SQLALCHEMY_DATABASE_URI\": \"mysql+pymysql:\/\/root:123@127.0.0.1:3306\/flaskdemo?charset=utf8mb4\"
# \u52a8\u6001\u8ffd\u8e2a\u4fee\u6539\u8bbe\u7f6e\uff0c\u5982\u672a\u8bbe\u7f6e\u53ea\u4f1a\u63d0\u793a\u8b66\u544a\uff0c\u8bbe\u7f6eFalse\u5373\u53ef
\"SQLALCHEMY_TRACK_MODIFICATIONS\": False,
# ORM\u6267\u884cSQL\u67e5\u8be2\u65f6\u662f\u54e6\u5426\u663e\u793a\u539f\u59cbSQL\u8bed\u53e5\uff0cdebug\u6a21\u5f0f\u4e0b\u53ef\u4ee5\u5f00\u542f
\"SQLALCHEMY_ECHO\": True,
})
\u200b
db.init_app(app)
\u200b
# \u5173\u7cfb\u8868[\u8fd9\u79cd\u8868\uff0c\u65e0\u6cd5\u63d0\u4f9b\u7ed9python\u8fdb\u884c\u64cd\u4f5c\u7684\uff0c\u4ec5\u4ec5\u7528\u4e8e\u5728\u6570\u636e\u5e93\u4e2d\u8bb0\u5f55\u4e24\u4e2a\u6a21\u578b\u4e4b\u95f4\u7684\u5173\u7cfb]
# student_and_course = db.Table(
# \"demo_student_course\",
# db.Column(\"id\", db.Integer, primary_key=True, comment=\"\u4e3b\u952eID\"),
# db.Column(\"sid\", db.Integer, db.ForeignKey(\"demo_student.id\"), comment=\"\u5b66\u751f\"),
# db.Column(\"cid\", db.Integer, db.ForeignKey(\"demo_course.id\"), comment=\"\u8bfe\u7a0b\"),
# db.Column(\"created_time\", db.DateTime, default=datetime.now, comment=\"\u8d2d\u4e70\u65f6\u95f4\"), # \u5f53\u524d\u5b57\u6bb5\u65e0\u6cd5\u64cd\u4f5c
# )
\u200b
\u200b
class StudentCourse(db.Model):
__tablename__ = \"demo_student_course\"
id = db.Column(db.Integer, primary_key=True, comment=\"\u4e3b\u952e\")
sid = db.Column(db.Integer, db.ForeignKey(\"demo_student.id\"))
cid = db.Column(db.Integer, db.ForeignKey(\"demo_course.id\"))
created_time = db.Column(db.DateTime, default=datetime.now, comment=\"\u8d2d\u4e70\u65f6\u95f4\")
\u200b
# \u5173\u8054\u5c5e\u6027
student = db.relationship(\"Student\", uselist=False, backref=backref(\"to_relation\", uselist=True))
course = db.relationship(\"Course\", uselist=False, backref=backref(\"to_relation\", uselist=True))
\u200b
\u200b
class Student(db.Model):
\"\"\"\u5b66\u751f\u4fe1\u606f\u6a21\u578b\"\"\"
# \u58f0\u660e\u4e0e\u5f53\u524d\u6a21\u578b\u7ed1\u5b9a\u7684\u6570\u636e\u8868\u540d\u79f0
__tablename__ = \"demo_student\"
id = db.Column(db.Integer, primary_key=True,comment=\"\u4e3b\u952e\")
name = db.Column(db.String(15), comment=\"\u59d3\u540d\")
age = db.Column(db.SmallInteger, comment=\"\u5e74\u9f84\")
sex = db.Column(db.Boolean, default=True, comment=\"\u6027\u522b\")
email = db.Column(db.String(128), comment=\"\u90ae\u7bb1\u5730\u5740\")
money = db.Column(db.Numeric(10, 2), default=0.0, comment=\"\u94b1\u5305\")
\u200b
def __repr__(self):
return json.dumps(self.__to_dict__, ensure_ascii=False)
\u200b
@property
def __to_dict__(self):
return {
\"id\": self.id,
\"name\": self.name,
\"age\": self.age,
\"sex\": self.sex,
\"email\": self.email,
\"money\": float(self.money),
}
\u200b
\u200b
class Course(db.Model):
\"\"\"\u8bfe\u7a0b\u6570\u636e\u6a21\u578b\"\"\"
__tablename__ = \"demo_course\"
id = db.Column(db.Integer, primary_key=True, comment=\"\u4e3b\u952e\")
name = db.Column(db.String(64), unique=True, comment=\"\u8bfe\u7a0b\")
price = db.Column(db.Numeric(7, 2), default=0.0, comment=\"\u4ef7\u683c\")
\u200b
def __repr__(self):
return json.dumps(self.__to_dict__, ensure_ascii=False)
\u200b
@property
def __to_dict__(self):
return {
\"id\": self.id,
\"name\": self.name,
\"price\": self.price,
}
\u200b
\u200b
@app.route(\"\/create\")
def create_table():
db.create_all() # \u4e3a\u9879\u76ee\u4e2d\u88ab\u8bc6\u522b\u7684\u6240\u6709\u6a21\u578b\u521b\u5efa\u6570\u636e\u8868
return \"ok\"
\u200b
\u200b
@app.route(\"\/drop\")
def drop_table():
db.drop_all() # \u4e3a\u9879\u76ee\u4e2d\u88ab\u8bc6\u522b\u7684\u6240\u6709\u6a21\u578b\u5220\u9664\u6570\u636e\u8868
return \"ok\"
\u200b
\u200b
@app.route(\"\/\")
def index():
return \"ok\"
\u200b
\u200b
@app.route(\"\/a1\")
def a1():
\"\"\"\u6dfb\u52a0\u6570\u636e\"\"\"
\"\"\"\u6dfb\u52a0\u5176\u4e2d\u4e00\u4e2a\u4e3b\u6a21\u578b\u6570\u636e\u65f6\uff0c\u540c\u65f6\u7ed1\u5b9a\u6dfb\u52a0\u53e6\u5916\u4e00\u4e2a\u4e3b\u6a21\u578b\u7684\u6570\u636e\"\"\"
# student = Student(
# name=\"xiaozhao\",
# age=13,
# sex=False,
# money=30000,
# email=\"xiaozhao@qq.com\",
# to_relation=[
# StudentCourse(
# course=Course(name=\"python\u57fa\u7840\", price=99.9)
# )
# ]
# )
# db.session.add(student)
# db.session.commit()
\u200b
\"\"\"\u5728\u5b66\u751f\u62a5\u8bfb\u8bfe\u7a0b\u7684\u57fa\u7840\u4e0a\uff0c\u65b0\u589e\u62a5\u8bfb\u8bfe\u7a0b\u3002\"\"\"
# student = Student(
# name=\"xiaohong\",
# age=14,
# sex=False,
# money=30000,
# email=\"300000@qq.com\",
# )
# db.session.add(student)
# db.session.commit()
\u200b
# student = Student.query.filter(Student.name == \"xiaohong\").first()
# # \u76f4\u63a5\u91c7\u7528python\u5185\u7f6e\u7684list\u65b9\u6cd5\u64cd\u4f5c
# student.to_relation.extend([
# StudentCourse(
# course=Course.query.get(1) # \u5df2\u7ecf\u5b58\u5728\u7684\u8bfe\u7a0b\uff0c\u7ed9\u5b66\u751f\u62a5\u8bfb
# ),
# StudentCourse(
# course=Course(name=\"python\u8fdb\u9636\", price=399.99) # \u65b0\u589e\u8bfe\u7a0b\uff0c\u5e76\u8ba9\u5f53\u524d\u5b66\u751f\u62a5\u8bfb\u8be5\u8bfe\u7a0b
# )
# ])
#
# db.session.commit()
\u200b
\u200b
\"\"\"\u6dfb\u52a0\u5b66\u751f\u62a5\u8bfb\u8bfe\u7a0b\u7684\u6d4b\u8bd5\u6570\u636e\"\"\"
student1 = Student.query.get(1)
course_list = Course.query.filter(Course.id.in_([1,2])).all()
student1.to_relation.extend([StudentCourse(course=course) for course in course_list])
db.session.commit()
\u200b
return \"ok\"
\u200b
\u200b
@app.route(\"\/q1\")
def q1():
\"\"\"\u67e5\u8be2\u64cd\u4f5c\"\"\"
# \u67e5\u8be2ID=2\u7684\u5b66\u751f\u62a5\u8bfb\u7684\u8bfe\u7a0b\u5217\u8868
# student = Student.query.get(2)
# course_list = [{\"name\":item.course.name,\"price\":float(item.course.price)} for item in student.to_relation]
# print(course_list)
\u200b
# \u67e5\u8be2\u51fa2\u53f7\u8bfe\u7a0b\uff0c\u90fd\u6709\u90a3\u4e9b\u5b66\u751f\u5728\u8bfb\uff1f
course = Course.query.get(2)
student_list = [{\"name\":item.student.name,\"age\":item.student.age} for item in course.to_relation]
print(student_list)
\u200b
return \"ok\"
\u200b
@app.route(\"\/u1\")
def u1():
\"\"\"\u66f4\u65b0\u6570\u636e\"\"\"
# # \u7ed9\u62a5\u8bfb\u4e862\u53f7\u8bfe\u7a0b\u7684\u540c\u5b66\uff0c\u8fd4\u73b0\u7ea2\u5305200\u5757\u94b1
# course = Course.query.get(2)
# for relation in course.to_relation:
# relation.student.money += 200
# db.session.commit()
\u200b
# \u83b7\u53d6\u4e2d\u95f4\u7684\u5173\u7cfb\u6a21\u578b\u7684\u5b57\u6bb5
course = Course.query.get(2)
for relation in course.to_relation:
print(relation.created_time)
\u200b
return \"ok\"
\u200b
\u200b
\u200b
if __name__ == '__main__':
app.run()
\u200b<\/pre>\n\n\n\nrelationship\u8fd8\u6709\u4e00\u4e2a\u8bbe\u7f6e\u5916\u952e\u7ea7\u8054\u7684\u5c5e\u6027\uff1acascade=”all, delete, delete-orphan”<\/p>\n\n\n\n \u4f5c\u4e1a\uff1a
1. \u6211\u4eec\u73b0\u5728\u5b66\u4e60\u7684flask\u6846\u67b6\u96c6\u6210SQLAlchemy\u64cd\u4f5c\u6570\u636e\u5e93\u4f7f\u7528\u7684\u662fflask-SQLAlchemy\u6a21\u5757\u3002\u5982\u679c\u539f\u751f\u7684python\u4e0b\u9762\u5e94\u8be5\u5982\u4f55\u4f7f\u7528SQLAlchemy\u8fdb\u884c\u521d\u59cb\u5316\u6570\u636e\u5e93\u8fde\u63a5\u548c\u58f0\u660e\u6a21\u578b\u5e76\u5b9e\u73b0\u6a21\u578b\u7684\u57fa\u672c\u64cd\u4f5c[\u589e\u5220\u67e5\u6539\uff0c\u5173\u8054\u67e5\u8be2]\uff1f
\u200b
2. flask\u4e2d\u7684SQLAlchemy\u5982\u4f55\u8fdb\u884c\u81ea\u5173\u8054\u67e5\u8be2\uff1f \u8fd9\u91cc\u81ea\u5df1\u5199\u4e00\u4e2a\u5173\u4e8e\u884c\u653f\u533a\u5212\u7684\u81ea\u5173\u8054\u64cd\u4f5c\u3002<\/pre>\n\n\n\n\u903b\u8f91\u5916\u952e<\/h2>\n\n\n\n\u4e5f\u53eb\u865a\u62df\u5916\u952e\u3002\u4e3b\u8981\u5c31\u662f\u5728\u5f00\u53d1\u4e2d\u4e3a\u4e86\u51cf\u5c11\u6570\u636e\u5e93\u7684\u6027\u80fd\u6d88\u8017\uff0c\u63d0\u5347\u7cfb\u7edf\u8fd0\u884c\u6548\u7387\uff0c\u4e00\u822c\u9879\u76ee\u4e2d\u5982\u679c\u5355\u8868\u6570\u636e\u592a\u5927[\u5343\u4e07\u7ea7\u522b]\u5c31\u4e0d\u4f1a\u4f7f\u7528\u6570\u636e\u5e93\u672c\u8eab\u7ef4\u62a4\u7684\u7269\u7406\u5916\u952e\uff0c\u800c\u662f\u91c7\u7528\u7531ORM\u6216\u8005\u6211\u4eec\u903b\u8f91\u4ee3\u7801\u8fdb\u884c\u67e5\u8be2\u5173\u8054\u7684\u903b\u8f91\u5916\u952e\u3002<\/p>\n\n\n\n SQLAlchemy\u8bbe\u7f6e\u5916\u952e\u6a21\u578b\u7684\u865a\u62df\u5916\u952e\uff0c\u67092\u79cd\u65b9\u6848\uff1a<\/p>\n\n\n\n \u65b9\u68481\uff0c\u67e5\u8be2\u6570\u636e\u65f6\u4e34\u65f6\u6307\u5b9a\u903b\u8f91\u5916\u952e<\/strong>\u7684\u6620\u5c04\u5173\u7cfb\uff1a<\/p>\n\n\n\n\u6a21\u578b\u7c7b.query.join(\u6a21\u578b\u7c7b,\u4e3b\u6a21\u578b.\u4e3b\u952e==\u5916\u952e\u6a21\u578b.\u5916\u952e).join(\u6a21\u578b\u7c7b,\u4e3b\u6a21\u578b.\u4e3b\u952e==\u5916\u952e\u6a21\u578b.\u5916\u952e).with_entities(\u5b57\u6bb51,\u5b57\u6bb52.label(\"\u5b57\u6bb5\u522b\u540d\"),....).all()<\/pre>\n\n\n\n\u65b9\u68482\uff0c\u5728\u6a21\u578b\u58f0\u660e\u65f6\u6307\u5b9a\u903b\u8f91\u5916\u952e\u7684\u6620\u5c04\u5173\u7cfb<\/strong>(\u6700\u5e38\u7528\uff0c\u8fd9\u79cd\u8bbe\u7f6e\u65b9\u6848\uff0c\u5728\u64cd\u4f5c\u6a21\u578b\u65f6\u4e0e\u539f\u6765\u9ed8\u8ba4\u8bbe\u7f6e\u7684\u7269\u7406\u5916\u952e\u7684\u5173\u8054\u64cd\u4f5c\u662f\u4e00\u6a21\u4e00\u6837\u7684\u5199\u6cd5)\uff1a<\/p>\n\n\n\nclass Student(db.Model):
id = db.Column(db.Integer, primary_key=True, comment=\"\u4e3b\u952e\")
# \u865a\u62df\u5916\u952e\uff0c\u539f\u6709\u53c2\u6570\u4e0d\u53d8\uff0c\u65b0\u589e2\u4e2a\u8868\u8fbe\u5173\u8054\u5173\u7cfb\u7684\u5c5e\u6027\uff1a
# primaryjoin, \u6307\u5b9a2\u4e2a\u6a21\u578b\u4e4b\u95f4\u7684\u4e3b\u5916\u952e\u5173\u7cfb\uff0c\u76f8\u5f53\u4e8e\u539f\u751fSQL\u8bed\u53e5\u4e2d\u7684join
# foreign_keys\uff0c\u6307\u5b9a\u5916\u952e
address_list = db.relationship(\"StudentAddress\", uselist=True, backref=\"student\", lazy=\"subquery\", primaryjoin=\"Student.id==StudentAddress.student_id\", foreign_keys=\"StudentAddress.student_id\")
\u200b
class StudentAddress(db.Model):
# \u539f\u6765\u7684\u5916\u952e\u8bbe\u7f6e\u4e3a\u666e\u901a\u7d22\u5f15\u5373\u53ef\u3002
student_id = db.Column(db.Integer, comment=\"\u5b66\u751fid\")<\/pre>\n\n\n\n\u4f8b1\uff0c\u865a\u62df\u5916\u952e\u4f7f\u7528\u7684\u65b9\u68481\uff0c\u4ee3\u7801\uff1a<\/p>\n\n\n\n import json
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import backref
from datetime import datetime
\u200b
db = SQLAlchemy()
app = Flask(__name__, template_folder=\"templates\", static_folder=\"static\")
\u200b
# \u914d\u7f6e
app.config.update({
\"DEBUG\": True,
\"SQLALCHEMY_DATABASE_URI\": \"mysql:\/\/root:123@127.0.0.1:3306\/flaskdemo?charset=utf8mb4\",
# \u5982\u679c\u4f7f\u7528pymysql\uff0c\u5219\u9700\u8981\u5728\u8fde\u63a5\u65f6\u6307\u5b9apymysql
# \"SQLALCHEMY_DATABASE_URI\": \"mysql+pymysql:\/\/root:123@127.0.0.1:3306\/flaskdemo?charset=utf8mb4\"
# \u52a8\u6001\u8ffd\u8e2a\u4fee\u6539\u8bbe\u7f6e\uff0c\u5982\u672a\u8bbe\u7f6e\u53ea\u4f1a\u63d0\u793a\u8b66\u544a\uff0c\u8bbe\u7f6eFalse\u5373\u53ef
\"SQLALCHEMY_TRACK_MODIFICATIONS\": False,
# ORM\u6267\u884cSQL\u67e5\u8be2\u65f6\u662f\u54e6\u5426\u663e\u793a\u539f\u59cbSQL\u8bed\u53e5\uff0cdebug\u6a21\u5f0f\u4e0b\u53ef\u4ee5\u5f00\u542f
\"SQLALCHEMY_ECHO\": True,
})
\u200b
db.init_app(app)
\u200b
\u200b
class Student(db.Model):
\"\"\"\u5b66\u751f\u4fe1\u606f\u6a21\u578b\"\"\"
# \u58f0\u660e\u4e0e\u5f53\u524d\u6a21\u578b\u7ed1\u5b9a\u7684\u6570\u636e\u8868\u540d\u79f0
__tablename__ = \"td_student\"
id = db.Column(db.Integer, primary_key=True,comment=\"\u4e3b\u952e\")
name = db.Column(db.String(15), comment=\"\u59d3\u540d\")
age = db.Column(db.SmallInteger, comment=\"\u5e74\u9f84\")
sex = db.Column(db.Boolean, default=True, comment=\"\u6027\u522b\")
email = db.Column(db.String(128), comment=\"\u90ae\u7bb1\u5730\u5740\")
money = db.Column(db.Numeric(10,2), default=0.0, comment=\"\u94b1\u5305\")
\u200b
def __repr__(self):
return f\"{self.name}<Student>\"
\u200b
\u200b
class Course(db.Model):
\"\"\"\u8bfe\u7a0b\u6570\u636e\u6a21\u578b\"\"\"
__tablename__ = \"td_course\"
id = db.Column(db.Integer, primary_key=True, comment=\"\u4e3b\u952e\")
name = db.Column(db.String(64), unique=True, comment=\"\u8bfe\u7a0b\")
price = db.Column(db.Numeric(7, 2))
\u200b
def __repr__(self):
return f'{self.name}<{self.__class__.__name__}>'
\u200b
\u200b
class StudentCourse(db.Model):
\"\"\"\u5b66\u751f\u548c\u8bfe\u7a0b\u4e4b\u95f4\u7684\u5173\u7cfb\u6a21\u578b\"\"\"
__tablename__ = \"td_student_course\"
id = db.Column(db.Integer, primary_key=True, comment=\"\u4e3b\u952e\")
student_id = db.Column(db.Integer, index=True)
course_id = db.Column(db.Integer, index=True)
score = db.Column(db.Numeric(4,1), default=0, comment=\"\u6210\u7ee9\")
time = db.Column(db.DateTime, default=datetime.now, comment=\"\u8003\u8bd5\u65f6\u95f4\")
\u200b
\u200b
@app.route(\"\/create\")
def create_table():
db.create_all() # \u4e3a\u9879\u76ee\u4e2d\u88ab\u8bc6\u522b\u7684\u6240\u6709\u6a21\u578b\u521b\u5efa\u6570\u636e\u8868
return \"ok\"
\u200b
\u200b
@app.route(\"\/drop\")
def drop_table():
db.drop_all() # \u4e3a\u9879\u76ee\u4e2d\u88ab\u8bc6\u522b\u7684\u6240\u6709\u6a21\u578b\u5220\u9664\u6570\u636e\u8868
return \"ok\"
\u200b
\u200b
@app.route(\"\/\")
def index():
return \"ok\"
\u200b
@app.route(\"\/a1\")
def a1():
# \u6dfb\u52a0\u6d4b\u8bd5\u6570\u636e
stu0 = Student(name=\"xiaoming-0\",age=15,sex=True,email=\"xiaoming0@qq.com\", money=1000)
stu1 = Student(name=\"xiaoming-1\",age=15,sex=True,email=\"xiaoming1@qq.com\", money=1000)
stu2 = Student(name=\"xiaoming-2\",age=15,sex=True,email=\"xiaoming2@qq.com\", money=1000)
stu3 = Student(name=\"xiaoming-3\",age=15,sex=True,email=\"xiaoming3@qq.com\", money=1000)
stu4 = Student(name=\"xiaoming-4\",age=15,sex=True,email=\"xiaoming4@qq.com\", money=1000)
\u200b
db.session.add_all([stu0,stu1,stu2,stu3,stu4])
course1 = Course(name=\"python\u57fa\u7840\u7b2c1\u5b63\", price=1000)
course2 = Course(name=\"python\u57fa\u7840\u7b2c2\u5b63\", price=1000)
course3 = Course(name=\"python\u57fa\u7840\u7b2c3\u5b63\", price=1000)
course4 = Course(name=\"python\u57fa\u7840\u7b2c4\u5b63\", price=1000)
course5 = Course(name=\"python\u57fa\u7840\u7b2c5\u5b63\", price=1000)
db.session.add_all([course1, course2, course3, course4, course5])
\u200b
data = [
StudentCourse(student_id=1,course_id=1,score=60,time=datetime.now()),
StudentCourse(student_id=1,course_id=2,score=60,time=datetime.now()),
StudentCourse(student_id=1,course_id=3,score=60,time=datetime.now()),
StudentCourse(student_id=2,course_id=1,score=60,time=datetime.now()),
StudentCourse(student_id=2,course_id=2,score=60,time=datetime.now()),
StudentCourse(student_id=3,course_id=3,score=60,time=datetime.now()),
StudentCourse(student_id=3,course_id=4,score=60,time=datetime.now()),
StudentCourse(student_id=4,course_id=5,score=60,time=datetime.now()),
StudentCourse(student_id=4,course_id=1,score=60,time=datetime.now()),
StudentCourse(student_id=4,course_id=2,score=60,time=datetime.now()),
StudentCourse(student_id=5,course_id=1,score=60,time=datetime.now()),
StudentCourse(student_id=5,course_id=2,score=60,time=datetime.now()),
StudentCourse(student_id=5,course_id=3,score=60,time=datetime.now()),
StudentCourse(student_id=5,course_id=4,score=60,time=datetime.now()),
]
db.session.add_all(data)
db.session.commit()
\u200b
return \"ok\"
\u200b
\u200b
@app.route(\"\/q1\")
def q1():
# \u4f7f\u7528\u903b\u8f91\u5916\u952e\u6765\u67e5\u8be2\u6570\u636e
# \u4e3b\u6a21\u578b.query.join(\u4ece\u6a21\u578b\u7c7b\u540d, \u5173\u7cfb\u8bed\u53e5)
# \u4e3b\u6a21\u578b.query.join(\u4ece\u6a21\u578b\u7c7b\u540d, \u4e3b\u6a21\u578b.\u4e3b\u952e==\u4ece\u6a21\u578b\u7c7b\u540d.\u5916\u952e)
\u200b
# \u8bfe\u7a0b[python\u57fa\u7840\u7b2c3\u5b63]\u6709\u591a\u5c11\u5b66\u751f\u5728\u8bfb\uff1f
# \u52062\u6b65\u67e5\u8be2
course = Course.query.filter(Course.name == \"python\u57fa\u7840\u7b2c3\u5b63\").first()
num = StudentCourse.query.filter(StudentCourse.course_id == course.id).count()
print(course, num)
\u200b
# \u5173\u8054\u67e5\u8be2, 2\u8868\u5173\u8054
# data = Course.query.join(
# StudentCourse,
# Course.id==StudentCourse.course_id
# ).with_entities(
# Course.id, Course.name, Course.price, StudentCourse.student_id, StudentCourse.score
# ).filter(Course.name == \"python\u57fa\u7840\u7b2c3\u5b63\").all()
# print(data)
\u200b
# \u76f4\u63a5\u7edf\u8ba1\uff0c\u4e0d\u9700\u8981\u4efb\u4f55\u5b57\u6bb5
ret = Course.query.join(
StudentCourse,
Course.id==StudentCourse.course_id
).filter(Course.name == \"python\u57fa\u7840\u7b2c3\u5b63\").count()
\u200b
print(ret)
\u200b
return \"ok\"
\u200b
\u200b
@app.route(\"\/q2\")
def q2():
# \u67e5\u8be2\u8bfe\u7a0b[python\u57fa\u7840\u7b2c3\u5b63]\u6709\u54ea\u4e9b\u5b66\u751f\u5728\u8bfb\uff1f3\u8868\u5173\u8054
# \u6b63\u5411\u67e5\u8be2\u548c\u9006\u5411\u67e5\u8be2\u90fd\u662f\u901a\u8fc7\u58f0\u660e\u4e34\u65f6\u5916\u952e\u5173\u7cfb\u6765\u5b8c\u6210\u5173\u8054\u67e5\u8be2\u64cd\u4f5c\u3002
student_list = Course.query.join(
StudentCourse,
Course.id == StudentCourse.course_id
).join(
Student,
Student.id == StudentCourse.student_id
).with_entities(
Course.name.label(\"course_name\"), StudentCourse.score.label(\"score\"), Student.name.label(\"student_name\"),
).filter(Course.name == \"python\u57fa\u7840\u7b2c3\u5b63\").all()
\u200b
print(student_list)
\u200b
return \"ok\"
\u200b
\u200b
@app.route(\"\/q3\")
def q3():
# xiaoming-2 \u8d2d\u4e70\u4e86\u90a3\u4e9b\u8bfe\u7a0b\uff1f
course_list = Student.query.join(
StudentCourse,
Student.id == StudentCourse.student_id
).join(
Course,
Course.id == StudentCourse.course_id
).with_entities(
Course.name.label(\"course_name\"),
StudentCourse.score.label(\"score\")
).filter(Student.name == \"xiaoming-2\").all()
\u200b
print(course_list)
\u200b
return \"ok\"
\u200b
if __name__ == '__main__':
app.run()
\u200b<\/pre>\n\n\n\n\u4f8b2\uff0c\u865a\u62df\u5916\u952e\u4f7f\u7528\u7684\u65b9\u68482\uff0c\u4ee3\u7801\uff1a<\/p>\n\n\n\n import json
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import backref
from datetime import datetime
\u200b
db = SQLAlchemy()
app = Flask(__name__, template_folder=\"templates\", static_folder=\"static\")
\u200b
# \u914d\u7f6e
app.config.update({
\"DEBUG\": True,
\"SQLALCHEMY_DATABASE_URI\": \"mysql:\/\/root:123@127.0.0.1:3306\/flaskdemo?charset=utf8mb4\",
# \u5982\u679c\u4f7f\u7528pymysql\uff0c\u5219\u9700\u8981\u5728\u8fde\u63a5\u65f6\u6307\u5b9apymysql
# \"SQLALCHEMY_DATABASE_URI\": \"mysql+pymysql:\/\/root:123@127.0.0.1:3306\/flaskdemo?charset=utf8mb4\"
# \u52a8\u6001\u8ffd\u8e2a\u4fee\u6539\u8bbe\u7f6e\uff0c\u5982\u672a\u8bbe\u7f6e\u53ea\u4f1a\u63d0\u793a\u8b66\u544a\uff0c\u8bbe\u7f6eFalse\u5373\u53ef
\"SQLALCHEMY_TRACK_MODIFICATIONS\": False,
# ORM\u6267\u884cSQL\u67e5\u8be2\u65f6\u662f\u54e6\u5426\u663e\u793a\u539f\u59cbSQL\u8bed\u53e5\uff0cdebug\u6a21\u5f0f\u4e0b\u53ef\u4ee5\u5f00\u542f
\"SQLALCHEMY_ECHO\": True,
})
\u200b
db.init_app(app)
\u200b
\u200b
class Student(db.Model):
\"\"\"\u5b66\u751f\u4fe1\u606f\u6a21\u578b\"\"\"
# \u58f0\u660e\u4e0e\u5f53\u524d\u6a21\u578b\u7ed1\u5b9a\u7684\u6570\u636e\u8868\u540d\u79f0
__tablename__ = \"ts_student\"
id = db.Column(db.Integer, primary_key=True,comment=\"\u4e3b\u952e\")
name = db.Column(db.String(15), comment=\"\u59d3\u540d\")
age = db.Column(db.SmallInteger, comment=\"\u5e74\u9f84\")
sex = db.Column(db.Boolean, default=True, comment=\"\u6027\u522b\")
email = db.Column(db.String(128), comment=\"\u90ae\u7bb1\u5730\u5740\")
money = db.Column(db.Numeric(10,2), default=0.0, comment=\"\u94b1\u5305\")
\u200b
def __repr__(self):
return f\"{self.name}<Student>\"
\u200b
\u200b
class Course(db.Model):
\"\"\"\u8bfe\u7a0b\u6570\u636e\u6a21\u578b\"\"\"
__tablename__ = \"ts_course\"
id = db.Column(db.Integer, primary_key=True, comment=\"\u4e3b\u952e\")
name = db.Column(db.String(64), unique=True, comment=\"\u8bfe\u7a0b\")
price = db.Column(db.Numeric(7, 2))
\u200b
def __repr__(self):
return f'{self.name}<{self.__class__.__name__}>'
\u200b
\u200b
class StudentCourse(db.Model):
\"\"\"\u5b66\u751f\u548c\u8bfe\u7a0b\u4e4b\u95f4\u7684\u5173\u7cfb\u6a21\u578b\"\"\"
__tablename__ = \"ts_student_course\"
id = db.Column(db.Integer, primary_key=True, comment=\"\u4e3b\u952e\")
student_id = db.Column(db.Integer, index=True)
course_id = db.Column(db.Integer, index=True)
score = db.Column(db.Numeric(4,1), default=0, comment=\"\u6210\u7ee9\")
time = db.Column(db.DateTime, default=datetime.now, comment=\"\u8003\u8bd5\u65f6\u95f4\")
\u200b
student = db.relationship(
\"Student\",
uselist=False,
backref=backref(\"to_course\", uselist=True),
lazy=\"subquery\",
primaryjoin=\"Student.id==StudentCourse.student_id\",
foreign_keys=\"StudentCourse.student_id\"
)
\u200b
course = db.relationship(
\"Course\",
uselist=False,
backref=backref(\"to_student\", uselist=True),
lazy=\"subquery\",
primaryjoin=\"Course.id==StudentCourse.course_id\",
foreign_keys=\"StudentCourse.course_id\"
)
\u200b
@app.route(\"\/create\")
def create_table():
db.create_all() # \u4e3a\u9879\u76ee\u4e2d\u88ab\u8bc6\u522b\u7684\u6240\u6709\u6a21\u578b\u521b\u5efa\u6570\u636e\u8868
return \"ok\"
\u200b
\u200b
@app.route(\"\/drop\")
def drop_table():
db.drop_all() # \u4e3a\u9879\u76ee\u4e2d\u88ab\u8bc6\u522b\u7684\u6240\u6709\u6a21\u578b\u5220\u9664\u6570\u636e\u8868
return \"ok\"
\u200b
\u200b
@app.route(\"\/\")
def index():
return \"ok\"
\u200b
@app.route(\"\/a1\")
def a1():
# \u6dfb\u52a0\u6d4b\u8bd5\u6570\u636e
stu0 = Student(name=\"xiaoming-0\",age=15,sex=True,email=\"xiaoming0@qq.com\", money=1000)
stu1 = Student(name=\"xiaoming-1\",age=15,sex=True,email=\"xiaoming1@qq.com\", money=1000)
stu2 = Student(name=\"xiaoming-2\",age=15,sex=True,email=\"xiaoming2@qq.com\", money=1000)
stu3 = Student(name=\"xiaoming-3\",age=15,sex=True,email=\"xiaoming3@qq.com\", money=1000)
stu4 = Student(name=\"xiaoming-4\",age=15,sex=True,email=\"xiaoming4@qq.com\", money=1000)
\u200b
db.session.add_all([stu0,stu1,stu2,stu3,stu4])
course1 = Course(name=\"python\u57fa\u7840\u7b2c1\u5b63\", price=1000)
course2 = Course(name=\"python\u57fa\u7840\u7b2c2\u5b63\", price=1000)
course3 = Course(name=\"python\u57fa\u7840\u7b2c3\u5b63\", price=1000)
course4 = Course(name=\"python\u57fa\u7840\u7b2c4\u5b63\", price=1000)
course5 = Course(name=\"python\u57fa\u7840\u7b2c5\u5b63\", price=1000)
db.session.add_all([course1, course2, course3, course4, course5])
\u200b
data = [
StudentCourse(student_id=1,course_id=1,score=60,time=datetime.now()),
StudentCourse(student_id=1,course_id=2,score=60,time=datetime.now()),
StudentCourse(student_id=1,course_id=3,score=60,time=datetime.now()),
StudentCourse(student_id=2,course_id=1,score=60,time=datetime.now()),
StudentCourse(student_id=2,course_id=2,score=60,time=datetime.now()),
StudentCourse(student_id=3,course_id=3,score=60,time=datetime.now()),
StudentCourse(student_id=3,course_id=4,score=60,time=datetime.now()),
StudentCourse(student_id=4,course_id=5,score=60,time=datetime.now()),
StudentCourse(student_id=4,course_id=1,score=60,time=datetime.now()),
StudentCourse(student_id=4,course_id=2,score=60,time=datetime.now()),
StudentCourse(student_id=5,course_id=1,score=60,time=datetime.now()),
StudentCourse(student_id=5,course_id=2,score=60,time=datetime.now()),
StudentCourse(student_id=5,course_id=3,score=60,time=datetime.now()),
StudentCourse(student_id=5,course_id=4,score=60,time=datetime.now()),
]
db.session.add_all(data)
db.session.commit()
\u200b
return \"ok\"
\u200b
\u200b
@app.route(\"\/q1\")
def q1():
student = Student.query.filter(Student.name == \"xiaoming-0\").first()
# print(student.to_course)
# [<StudentCourse 1>, <StudentCourse 2>, <StudentCourse 3>]
\u200b
print([{
\"course_name\": item.course.name,
\"score\": item.score,
\"student_name\": item.student.name,
} for item in student.to_course])
return \"ok\"
\u200b
\u200b
if __name__ == '__main__':
app.run()<\/pre>\n","protected":false},"excerpt":{"rendered":"Flask-SQLAlchemy flask\u9ed8\u8ba4\u63d0\u4f9b\u6a21\u578b\u64cd\u4f5c\uff0c\u4f46\u662f\u5e76\u6ca1\u6709\u63d0\u4f9bORM\uff0c\u6240\u4ee5\u4e00\u822c\u5f00\u53d1\u7684\u65f6\u5019\u6211\u4eec\u4f1a\u91c7 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-13","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/22z.top\/index.php?rest_route=\/wp\/v2\/posts\/13","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/22z.top\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/22z.top\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/22z.top\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/22z.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=13"}],"version-history":[{"count":1,"href":"https:\/\/22z.top\/index.php?rest_route=\/wp\/v2\/posts\/13\/revisions"}],"predecessor-version":[{"id":15,"href":"https:\/\/22z.top\/index.php?rest_route=\/wp\/v2\/posts\/13\/revisions\/15"}],"wp:attachment":[{"href":"https:\/\/22z.top\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=13"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/22z.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=13"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/22z.top\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=13"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}} | |
|
| | | | | | | | | | | | |
| | | | | | | | |
|
| | | | | | | | |