0. 下载相关依赖包，Navicat，连接Navicat和MySql (这些不赘述了，网上都有教程）
一 、 创建数据表
1. app.py
from flask import Flask
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship, backref
from config import DB_URI
from sqlalchemy.ext.declarative import declarative_base
app = Flask(__name__)
engine = create_engine(DB_URI)#建立引擎
Base = declarative_base(engine)# 建立 sql rom基类
#todo 创建数据库表
#todo 创建数据库表student
class Student(Base):
__tablename__ = "student"
id = Column(Integer,primary_key=True,autoincrement=True)
name = Column(String(50))
age = Column(Integer)
sex = Column(String(10))
#todo 创建数据库表student——extension，与student一对一
class StudentExtension(Base):
__tablename__ = "student——extension"
id = Column(Integer,primary_key=True,autoincrement=True)
school = Column(String(50))
# 创建外键
student_id = Column(Integer, ForeignKey("student.id"))
# back reference 反向引用，如果需要传入多个变量，则使用backref（）函数
# userList=False 说明不能使用列表，只能存放一个值
student_name = relationship("Student", backref=backref("extensions", uselist=False))
#todo 创建数据库表article，与student一对多
class Article(Base):
__tablename__ = "article"
id = Column(Integer,primary_key=True,autoincrement=True)
title = Column(String(10),nullable = False)
# 创建外键
author_id = Column(Integer, ForeignKey("student.id"))
# back reference 反向引用，articles代表对方(Student)访问我的时候我的字段名称
author_name = relationship("Student", backref="articles")
Base.metadata.create_all()# 将模型映射到数据库中
2. config.py
HOST = '127.0.0.1'
PORT = 3306
USERNAME = 'root'
PASSWORD = '111111'
DB = 'girls'
DB_URI = f'mysql+pymysql://{USERNAME}:{PASSWORD}@{HOST}:{PORT}/{DB}'
创建成功后表的内容如下：
我的数据库名：girls
我的数据表：
1. student (id,name,age,sex)
2. article (id,title,author_id)
3. student_extension(id,school,author_id)
二、增删改查数据
1. app.py（进行一些更改，如：表创建完以后就不需要再创建了，对代码进行一些拆分和重构）
# 配合models config exts 一起使用
from flask import Flask
from flask_migrate import Migrate
from models import Article,Student,StudentExtension
from exts import session
app = Flask(__name__)
# 以下为多表连接部分
@app.route("/otm")
def one_to_many():
article1 = Article(id=111,title="math")
article2 = Article(id=222, title="Eng")
student = Student(name='Tony', age=16, sex='male')
# 一个作者的两篇文章
article1.author_name = student
article2.author_name = student
session.add(article1,article2)
session.commit()
# student访问我的时候，根据student的
print(student.articles)
return "success"
@app.route("/oto")
def one_to_one():
student = Student(name='Tony', age=16, sex='male')
# student = session.query.filter_by(id=1)[0]
extension = StudentExtension(school="qinghua")
student.extensions = extension
session.add(student)
session.commit()
return "success"
# 以下为单个表中数据操作
#todo 插入数据
'''
student = Student(name='Tony',age = 16,sex = 'male')
session.add(student)#单个数据插入
session.commit()
session.add_all([
Student(name='Jane', age=16, sex='female'),
Student(name='Ben', age=20, sex='male')
])#批量插入数据
session.commit()
'''
#todo 查询所有数据 --sqlalchemy提供了query()方法来查询数据
'''
data_list = session.query(Student).all()
print(data_list)
for data in data_list:
print(data.name,data.age,data.sex)
'''
#todo 指定查询 只查询名字
'''
#name_list = session.query(Student.name).all() #[('Tony',), ('Jane',), ('Ben',)]
name_list = session.query(Student.name).first()#获取返回数据的第一行 ('Tony',)
print(name_list)
'''
#todo 使用filter()方法进行筛选过滤
'''
filter_list = session.query(Student.name).filter(Student.age>=18).all()
print(filter_list) #输出：[ ('Ben',)]
'''
#todo 使用order_by()进行排序
'''
item_list = session.query(Student.name, Student.age).order_by(Student.age.desc()).all() # desc()表示倒序
print(item_list)
#[('Ben', 20), ('Tony', 16), ('Jane', 16)]
'''
# todo 多个查询条件(and和or)
'''
# 默认为and, 在filter()中用,分隔多个条件表示and
item_list = session.query(Student.name, Student.age, Student.sex).filter(
Student.age >= 10, Student.sex == 'female'
).all()
print(item_list) # [('Jane', 16, 'female')]
'''
from sqlalchemy import or_
#todo 使用or_连接多个条件
'''
item_list = session.query(Student.name, Student.age, Student.sex).filter(
or_(Student.age >= 20, Student.sex == 'female')
).all()
print(item_list) # [('Jane', 16, 'female'), ('Ben', 20, 'male')]
'''
# todo equal/like/in
'''
# 等于
item_list = session.query(Student.name, Student.age, Student.sex).filter(
Student.age == 18
).all()
print(item_list) # []
# 不等于
item_list = session.query(Student.name, Student.age, Student.sex).filter(
Student.age != 18
).all()
print(item_list) # [('Tony', 16, 'male'), ('Jane', 16, 'female'), ('Ben', 20, 'male')]
# like
item_list = session.query(Student.name, Student.age, Student.sex).filter(
Student.name.like('%To%')
).all()
print(item_list) # [('Tony', 16, 'male')]
# in(16或20)
item_list = session.query(Student.name, Student.age, Student.sex).filter(
Student.age.in_([16, 20])
).all()
print(item_list) # [(('Tony', 16, 'male'), ('Jane', 16, 'female'), ('Ben', 20, 'male')]
'''
# todo count计算个数
'''
count = session.query(Student).count()
print(count) # 3
'''
# todo 切片
'''
item_list = session.query(Student.name).all()[:2]
print(item_list) # [('Tony',), ('Jane',)]
'''
#todo 修改数据
'''
#修改数据可以使用update()方法，update完成后记得执行session.commit()
# 修改Tony的age为22
session.query(Student).filter(Student.name == 'Tony').update({'age': 22})
session.commit()
item = session.query(Student.name, Student.age).filter(Student.name == 'Tony').first()
print(item) #('Tony', 22)
'''
# todo 删除数据
'''
# 删除数据使用delete()方法，同样也需要执行session.commit()提交事务
# 删除名称为Ben的数据
session.query(Student).filter(Student.name == 'Ben').delete()
session.commit()
item_list = session.query(Student.name, Student.age).all()
print(item_list)#[('Tony', 22), ('Jane', 16)]
'''
if __name__ == '__main__':
app.run()
. config.py 配置数据库连接（未改动）
HOST = '127.0.0.1'
PORT = 3306
USERNAME = 'root'
PASSWORD = '111111'
DB = 'girls'
DB_URI = f'mysql+pymysql://{USERNAME}:{PASSWORD}@{HOST}:{PORT}/{DB}'
3. models.py 存放表模型（比如一对一，一对多模型）
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship, backref
from exts import Base
#todo 创建数据库表
#todo 创建数据库表student
class Student(Base):
__tablename__ = "student"
id = Column(Integer,primary_key=True,autoincrement=True)
name = Column(String(50))
age = Column(Integer)
sex = Column(String(10))
# 与student一对一
class StudentExtension(Base):
__tablename__ = "student——extension"
id = Column(Integer,primary_key=True,autoincrement=True)
school = Column(String(50))
# 创建外键
student_id = Column(Integer, ForeignKey("student.id"))
# back reference 反向引用，如果需要传入多个变量，则使用backref（）函数
# userList=False 说明不能使用列表，只能存放一个值
student_name = relationship("Student", backref=backref("extensions", uselist=False))
# 与student一对多
class Article(Base):
__tablename__ = "article"
id = Column(Integer,primary_key=True,autoincrement=True)
title = Column(String(10),nullable = False)
# 创建外键
author_id = Column(Integer, ForeignKey("student.id"))
# back reference 反向引用，articles代表对方(Student)访问我的时候我的字段名称
author_name = relationship("Student", backref="articles")
4. exts.py 部分代码放入extension以免造成类似死锁的问题
# 为解决app和models死锁问题而存在 from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import create_engine from config import DB_URI from sqlalchemy.orm import sessionmaker # todo 创建ORM模型并映射到数据库中 engine = create_engine(DB_URI)#建立引擎 Base = declarative_base(engine)# 建立 sql rom基类 session = sessionmaker(engine)() #构建session对象 Base.metadata.drop_all()# 删除表 Base.metadata.create_all()# 将模型映射到数据库中
