SQL AlCHEMY

SQLCHEMY 单表创建增删改查

ORM 框架通用

ORM 框架 Django-Model

安装ORM 框架

pip3 install sqlchemy

框架大纲

创建数据表

create_table.py


# -*- coding: utf-8 -*-
# @Time    : 2022/10/14 13:12
# @Author  : lzc
# @Email   : hybpjx@163.com
# @blogs    : <https://www.cnblogs.com/zichliang>
# @Software: PyCharm

from flask import Flask

from sqlalchemy.ext.declarative import declarative_base

BaseModel = declarative_base()

#  orm 对象关系映射
# object relationship mapping

# class 操作table
from sqlalchemy import Column, INT, INTEGER, Integer, CHAR, VARCHAR, NCHAR, NVARCHAR, String

# """
# INT INTEGER 都是继承 Integer
#
# CHAR,VARCHAR,NCHAR,NVARCHAR 继承 string
#
# """

class User(BaseModel):
    __tablename__ = "user"  # 创建table时的名字
    # 主键， 自赠
    id = Column(Integer, primary_key=True, autoincrement=True, )
    # 长度为32 非空 索引  唯一
    name = Column(String(32), nullable=False, index=True, unique=True)
    # Column 定义数据列
    # int  string 数据 类型

# 数据库引擎的创建
from sqlalchemy.engine import create_engine

engine = create_engine("mysql+pymysql://root:admin*123@127.0.0.1:3306/s21?charset=utf8")  # 数据库连接驱动语句

# 利用User 去数据库 创建 User table
BaseModel.metadata.create_all(engine)  # 数据库引擎
# 数据库 呢？ 数据库服务器地址

插入数据


# -*- coding: utf-8 -*-
# @Time    : 2022/10/14 13:12
# @Author  : lzc
# @Email   : hybpjx@163.com
# @blogs    : <https://www.cnblogs.com/zichliang>
# @Software: PyCharm

# 模拟 navicat
# 1.选择数据库
from sqlalchemy.engine import create_engine

engine = create_engine("mysql+pymysql://root:admin*123@127.0.0.1:3306/s21?charset=utf8")
# 2.选择表
from create_table import User
# 3.创建查询窗口
from sqlalchemy.orm import sessionmaker

select_db = sessionmaker(engine)  # 选中数据库
db_session = select_db()  # 已经打开查询端口了

# 4.写入sql语句
# user = User(name="lzc")  # = INSERT into user(`name`) VALUE ("lzc");

# 插入多条数据
user_list = [User(name="lzc_1"), User(name="lzc_2"), User(name="lzc_3"), User(name="lzc_4"), ]

# 放入查询端口
# db_session.add(user)

# 插入多条数据
db_session.add_all(user_list)

# 5.提交sql语句
db_session.commit()

# 6.关闭查询窗口
db_session.close()

查询数据


# -*- coding: utf-8 -*-
# @Time    : 2022/10/14 13:12
# @Author  : lzc
# @Email   : hybpjx@163.com
# @blogs    : <https://www.cnblogs.com/zichliang>
# @Software: PyCharm

# 模拟 navicat
# 1.选择数据库
from sqlalchemy.engine import create_engine

engine = create_engine("mysql+pymysql://root:admin*123@127.0.0.1:3306/s21?charset=utf8")
# 2.选择表
from create_table import User
# 3.创建查询窗口
from sqlalchemy.orm import sessionmaker

select_db = sessionmaker(engine)  # 选中数据库
db_session = select_db()  # 已经打开查询端口了

# 简单无条件查询
"""
select * from user table_user=class_user ...
"""
# # 查询所有数据
# result = db_session.query(User).all()
# print(result)  # [<create_table.User object at 0x7f7f22e08dd8>, <create_table.User object at 0x7f7f22e08e48>, <create_table.User object at 0x7f7f22e08eb8>, <create_table.User object at 0x7f7f22e08f28>]

# 查询符合条件的第一条数据
# result = db_session.query(User).first()
# # print(result)  # <create_table.User object at 0x7fedcda7de80>
# print(result.id,result.name)

# 简单条件查询
# """
# # select * from user where id=3
# """

# 查询id = 4 的数据
# res = db_session.query(User).filter(User.id == 4).all()
# # res = db_session.query(User).filter_by(id=4).all()
# print(res[0].id, res[0].name)

# 当id = 4 并且 name = lzc_1
res = db_session.query(User).filter(User.id == 4, User.name == "lzc_1").all()
print(res)

修改数据


# -*- coding: utf-8 -*-
# @Time    : 2022/10/14 13:12
# @Author  : lzc
# @Email   : hybpjx@163.com
# @blogs    : <https://www.cnblogs.com/zichliang>
# @Software: PyCharm

# 模拟 navicat
# 1.选择数据库
from sqlalchemy.engine import create_engine

engine = create_engine("mysql+pymysql://root:admin*123@127.0.0.1:3306/s21?charset=utf8")
# 2.选择表
from create_table import User
# 3.创建查询窗口
from sqlalchemy.orm import sessionmaker

select_db = sessionmaker(engine)  # 选中数据库
db_session = select_db()  # 已经打开查询端口了

# 修改数据 update
res = db_session.query(User).filter(User.id == 4).update({"name": "我叫coda"})
print(res)

# 除了查询 其他都要提交且关闭
db_session.commit()
db_session.close()

删除数据


# -*- coding: utf-8 -*-
# @Time    : 2022/10/14 13:12
# @Author  : lzc
# @Email   : hybpjx@163.com
# @blogs    : <https://www.cnblogs.com/zichliang>
# @Software: PyCharm

# 模拟 navicat
# 1.选择数据库
from sqlalchemy.engine import create_engine

engine = create_engine("mysql+pymysql://root:admin*123@127.0.0.1:3306/s21?charset=utf8")
# 2.选择表
from create_table import User
# 3.创建查询窗口
from sqlalchemy.orm import sessionmaker

select_db = sessionmaker(engine)  # 选中数据库
db_session = select_db()  # 已经打开查询端口了

# 删除数据
res = db_session.query(User).filter(User.id == 4).delete()
print(res)
db_session.commit()
db_session.close()

SQLCHEMY 一对多创建增删改查

Create_foregin_key.py 创建表并且增加外键等操作


# -*- coding: utf-8 -*-
# @Time    : 2022/10/14 13:12
# @Author  : lzc
# @Email   : hybpjx@163.com
# @blogs    : <https://www.cnblogs.com/zichliang>
# @Software: PyCharm

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.engine import create_engine

# orm的精髓就是 relationship
from sqlalchemy.orm import relationship  # 你把 relationship写在哪个类中 哪个类就是正向类 反之则反向类

engine = create_engine("mysql+pymysql://root:admin*123@127.0.0.1:3306/s21?charset=utf8")

BaseModel = declarative_base()

class School(BaseModel):
    __tablename__ = "school"
    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=False)

class Student(BaseModel):
    __tablename__ = "student"
    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=False)
    sch_id = Column(Integer, ForeignKey("school.id"))

    stu2sch = relationship("School",backref="stu2sch")

BaseModel.metadata.create_all(engine)

Relation正向添加数据

CURD.py


# -*- coding: utf-8 -*-
# @Time    : 2022/10/14 13:12
# @Author  : lzc
# @Email   : hybpjx@163.com
# @blogs    : <https://www.cnblogs.com/zichliang>
# @Software: PyCharm

from sqlalchemy.orm import sessionmaker
from sqlalchemy.engine import create_engine

from create_foreign_key import Student, School

engine = create_engine("mysql+pymysql://root:admin*123@127.0.0.1:3306/s21?charset=utf8")

select_db = sessionmaker(engine)

db_session=select_db()

# 增加数据
# relationship 正向添加 relationship字段出现在哪个类
stu = Student(name="Dra",stu2sch=School(name="GoodSchool"))
# stu 是sql 语句

db_session.add(stu)
db_session.commit()
db_session.close()

Relation反向添加数据


# -*- coding: utf-8 -*-
# @Time    : 2022/10/14 13:12
# @Author  : lzc
# @Email   : hybpjx@163.com
# @blogs    : <https://www.cnblogs.com/zichliang>
# @Software: PyCharm

from sqlalchemy.orm import sessionmaker
from sqlalchemy.engine import create_engine

from create_foreign_key import Student, School

engine = create_engine("mysql+pymysql://root:admin*123@127.0.0.1:3306/s21?charset=utf8")

select_db = sessionmaker(engine)

db_session = select_db()

# 增加数据
# relationship 反向添加 relationship字段出现在哪个类
sch = School(name="BadSchool")
sch.stu2sch = [
    Student(name="wjk"),
    Student(name="yyqx"),
    Student(name="wy"),
]

# stu 是sql 语句
db_session.add(sch)
db_session.commit()
db_session.close()

增删与上文一样

Relation 正向查询


# -*- coding: utf-8 -*-
# @Time    : 2022/10/14 13:12
# @Author  : lzc
# @Email   : hybpjx@163.com
# @blogs    : <https://www.cnblogs.com/zichliang>
# @Software: PyCharm

from sqlalchemy.orm import sessionmaker
from sqlalchemy.engine import create_engine

from create_foreign_key import Student, School

engine = create_engine("mysql+pymysql://root:admin*123@127.0.0.1:3306/s21?charset=utf8")

select_db = sessionmaker(engine)

db_session = select_db()

# 增加数据
# relationship 正向查询
res = db_session.query(Student).all()
for i in res:
    print(i.name, i.stu2sch.name)

结果：


Dra GoodSchool
wjk BadSchool
yyqx BadSchool
wy BadSchool

Relation 反向查询


# -*- coding: utf-8 -*-
# @Time    : 2022/10/14 13:12
# @Author  : lzc
# @Email   : hybpjx@163.com
# @blogs    : <https://www.cnblogs.com/zichliang>
# @Software: PyCharm

from sqlalchemy.orm import sessionmaker
from sqlalchemy.engine import create_engine

from create_foreign_key import Student, School

engine = create_engine("mysql+pymysql://root:admin*123@127.0.0.1:3306/s21?charset=utf8")

select_db = sessionmaker(engine)

db_session = select_db()

# 增加数据
# relationship 反向查询
res = db_session.query(School).all()
for sch in res:
    # print(i.name, len(i.stu2sch)) # 学校里面有多少学生
    for stu in sch.stu2sch:
        print(sch.name, stu.name)

SQLCHEMY 多对多创建增删改查

创建三张表


# -*- coding: utf-8 -*-
# @Time    : 2022/10/14 13:12
# @Author  : lzc
# @Email   : hybpjx@163.com
# @blogs    : <https://www.cnblogs.com/zichliang>
# @Software: PyCharm

from sqlalchemy.engine import create_engine
from sqlalchemy.orm import relationship
from sqlalchemy import String, Integer, Column, ForeignKey
from sqlalchemy.ext.declarative import declarative_base

BaseModel = declarative_base()

engine = create_engine("mysql+pymysql://root:admin*123@127.0.0.1:3306/s21?charset=utf8")

# 多对多
class Girl(BaseModel):
    __tablename__ = "girl"
    id = Column(Integer, primary_key=True)
    name = Column(String(16), nullable=False)

    gyb = relationship("Boy", backref="byg", secondary="hotel") # secondary="hotel"  才能证明两者关系

class Boy(BaseModel):
    __tablename__ = "boy"
    id = Column(Integer, primary_key=True)
    name = Column(String(16), nullable=False)

class Hotel(BaseModel):
    __tablename__ = "hotel"
    id = Column(Integer, primary_key=True)
    bid = Column(Integer, ForeignKey("boy.id"))
    gid = Column(Integer, ForeignKey("girl.id"))

BaseModel.metadata.create_all(engine)

relationship 正向添加数据


# -*- coding: utf-8 -*-
# @Time    : 2022/10/14 13:12
# @Author  : lzc
# @Email   : hybpjx@163.com
# @blogs    : <https://www.cnblogs.com/zichliang>
# @Software: PyCharm

from create_many_To_many import engine, Girl, Boy
from sqlalchemy.orm import sessionmaker

select_db = sessionmaker(engine)
db_session = select_db()

# 增加数据 relationship 正向增加
g = Girl(name="赵丽", gyb=[Boy(name="风少"), Boy(name="王少")])

db_session.add(g)
db_session.commit()
db_session.close()

relationship 反向添加数据


# -*- coding: utf-8 -*-
# @Time    : 2022/10/14 13:12
# @Author  : lzc
# @Email   : hybpjx@163.com
# @blogs    : <https://www.cnblogs.com/zichliang>
# @Software: PyCharm

from create_many_To_many import engine, Girl, Boy
from sqlalchemy.orm import sessionmaker

select_db = sessionmaker(engine)
db_session = select_db()

# 增加数据 relationship 反向
b = Boy(name="wjk")

b.byg = [
    Girl(name="wy"),
    Girl(name="qbl"),
    Girl(name="zya"),
]

db_session.add(b)
db_session.commit()
db_session.close()

relationship 正向查询


# -*- coding: utf-8 -*-
# @Time    : 2022/10/14 13:12
# @Author  : lzc
# @Email   : hybpjx@163.com
# @blogs    : <https://www.cnblogs.com/zichliang>
# @Software: PyCharm

from create_many_To_many import engine, Girl, Boy
from sqlalchemy.orm import sessionmaker

select_db = sessionmaker(engine)
db_session = select_db()

# 查询数据 relationship 正向
res = db_session.query(Girl).all()

for g in res:
    print(g.name,len(g.gyb))

relationship 反向查询


# -*- coding: utf-8 -*-
# @Time    : 2022/10/14 13:12
# @Author  : lzc
# @Email   : hybpjx@163.com
# @blogs    : <https://www.cnblogs.com/zichliang>
# @Software: PyCharm

from create_many_To_many import engine, Girl, Boy
from sqlalchemy.orm import sessionmaker

select_db = sessionmaker(engine)
db_session = select_db()

# 查询数据 relationship 反向
res = db_session.query(Boy).all()
for b in res:
    print(b.name,len(b.byg))

Flask SQLALCHEMY

前不久刚刚认识过了SQLAlchemy,点击这里复习一下

当 Flask 与 SQLAlchemy 发生火花会怎么样呢?

Flask-SQLAlchemy就这么诞生了

首先要先安装一下Flask-SQLAlchemy这个模块

pip install Flask-SQLAlchemy

然后你要下载一个干净的Flask项目 点击下载

接下来基于这个Flask项目,我们要加入Flask-SQLAlchemy让项目变得生动起来

Flask 大体结构如下

加入Flask-SQLAlchemy第三方组件


# -*- coding: utf-8 -*-
# @Time    : 2022/10/14 13:12
# @Author  : lzc
# @Email   : hybpjx@163.com
# @blogs    : <https://www.cnblogs.com/zichliang>
# @Software: PyCharm

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()
db.initapp()

init.app部分源码内容详情

init.py


# -*- coding: utf-8 -*-
# @Time    : 2022/10/14 13:12
# @Author  : lzc
# @Email   : hybpjx@163.com
# @blogs    : <https://www.cnblogs.com/zichliang>
# @Software: PyCharm

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()

# 创建db时 一定要注意蓝图的顺序
from app01.views import user

# 创建app
def create_app():
    app = Flask(__name__)
    app.config["DEBUG"] = True
    app.config["SESSION_COOKIE_NAME"] = "I AM DSB"
    app.config["SQLALCHEMY_DATABASE_URI"] = "mysql+pymysql://root:admin*123@127.0.0.1:3306/s21?charset=utf8"
    app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
    # 加载蓝图的位置
    app.register_blueprint(user.user)
    db.init_app(app)

    return app

建立models.py


# -*- coding: utf-8 -*-
# @Time    : 2022/10/14 13:12
# @Author  : lzc
# @Email   : hybpjx@163.com
# @blogs    : <https://www.cnblogs.com/zichliang>
# @Software: PyCharm

"""

    导入 flask sqlalchemy

"""
from app01 import db

# db 是 flask 封装的全新的SQLALCHEMY对象

class Users(db.Model):
    __tablename__ = "users"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(32), nullable=False)

if __name__ == '__main__': # 当文件被导入时 不会执行如下内容
    from app01 import create_app
    app=create_app()
    db.create_all(app=app)

登录视图函数的应用


# -*- coding: utf-8 -*-
# @Time    : 2022/10/14 13:12
# @Author  : lzc
# @Email   : hybpjx@163.com
# @blogs    : <https://www.cnblogs.com/zichliang>
# @Software: PyCharm

"""
    此文件 可以当作是蓝图
"""
from app01.models import db, Users
from flask import Blueprint

user = Blueprint("user", __name__)

@user.route("/register/<username>")
def register(username):
    u = Users(name=username)
    db.session.add(u)
    db.session.commit()

    return "register 200 ok!"

@user.route("/user_list")
def user_list():
    res = Users.query.filter().all()
    print(res)

    return f"当前有{len(res)}个"

其实Flask-SQLAlchemy比起SQLAlchemy更加的简单自如,用法几乎一模一样,就是在配置和启动上需要注意与Flask的配合就好啦

manager.py


# -*- coding: utf-8 -*-
# @Time    : 2022/10/14 13:12
# @Author  : lzc
# @Email   : hybpjx@163.com
# @blogs    : <https://www.cnblogs.com/zichliang>
# @Software: PyCharm

# 启动flask
from app01 import create_app

app = create_app()

if __name__ == '__main__':
    app.run("0.0.0.0")

Flask Flask_script

在我们在运行我们的项目的时候可能需要使用Django 那样的写法

类如：python manager.py runserver

所以我们需要安装一个库

pip install flask_script


# -*- coding: utf-8 -*-
# @Time    : 2022/10/14 13:12
# @Author  : lzc
# @Email   : hybpjx@163.com
# @blogs    : <https://www.cnblogs.com/zichliang>
# @Software: PyCharm

# 启动flask
from app01 import create_app
from flask_script import Manager

app = create_app()
#  一定要在创建 app完事儿之后 再创建
manager = Manager(app)

#  可以改变 runserver 为aaa
@manager.command
def aaa(args):
    print(args)
    return args

@manager.option("-w", "--who", dest="who")
def kjkj(who):
    # python3 manager.py kjkj -w alex
    print(f"{who},你好")

if __name__ == '__main__':
    manager.run()

Flask Flask_migrate

安装

pip install flask_migrate


# -*- coding: utf-8 -*-
# @Time    : 2022/10/14 13:12
# @Author  : lzc
# @Email   : hybpjx@163.com
# @blogs    : <https://www.cnblogs.com/zichliang>
# @Software: PyCharm

# 启动flask
from app01 import create_app, db
from flask_script import Manager
from flask_migrate import Migrate, MigrateCommand

app = create_app()
#  一定要在创建 app完事儿之后 再创建
manager = Manager(app)
Migrate(app, db)
# 初始化指令集
manager.add_command("db", MigrateCommand)  # python manager.py db init

"""
数据库迁移指令
python manager.py db init
python manager.py db migrate # django 中的makemigration
python manager.py db upgrade  # django 中的migrate

"""

#  可以改变 runserver 为aaa
@manager.command
def aaa(args):
    print(args)
    return args

@manager.option("-w", "--who", dest="who")
def kjkj(who):
    # python3 manager.py kjkj -w alex
    print(f"{who},你好")

if __name__ == '__main__':
    manager.run()