SQLAlchemy使用

Kate • 2021年9月5日 17:00 • Python • 6467 阅读

一、简介

SQLAlchemy是用Python编程语言开发的一个开源项目。它提供了SQL工具包和ORM（对象关系映射）工具，使用MIT许可证发行。

SQLAlchemy最初在2006年2月发行，发行后便很快的成为Python社区中最广泛使用的ORM工具之一，丝毫不亚于Django自带的ORM框架。

SQLAlchemy采用简单的Python语言，提供高效和高性能的数据库访问，实现了完整的企业级持久模型。它的理念是，SQL数据库的量级和性能比对象集合重要，而对象集合的抽象又重要于表和行。

二、基本用法

1、安装

安装sqlalchemy

activate python_test
pip install sqlalchemy
pip install pymysql

本文使用MySQL作为数据库，使用pymysql作为驱动，因此需要安装pymysql

2、连接数据库

1、项目目录

图片.jpg

2、配置信息

在连接数据库前，需要使用到一些配置信息，然后把它们组合成满足以下条件的字符串：

dialect+driver://username:password@host:port/database

dialect：数据库，如：sqlite、mysql、oracle等
driver：数据库驱动，用于连接数据库的，本文使用pymysql
username：用户名
password：密码
host：IP地址
port：端口
database：数据库

3、配置文件

settings.py

# 配置标识
ENV = 'DEV'

# MYSQL配置文件
HOST = '111.229.15.125'
PORT = 3306
USERNAME = 'root'
PASSWORD = 'Xiaohao584521.0'
DB = 'SQLAlchemy_db'

4、创建引擎并连接数据库

mysql_config.py

from settings import settings
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session


# 当 autoflush 为 True 时（默认是 True），session 进行查询之前会自动把当前累计的修改发送到数据库
# （注意：autoflush 并不是说在 session.add 之后会自动 flush）

# commit 对应的概念是事务（transaction），默认情况下，session 参数 autocommit 的值是 False，SQLAlchemy 也推荐将它设置为 False。
# 注：MySQL client 默认是将 autocommit 设为 True 的，所以我们在 cli 中执行一条 SQL 语句，数据库的数据就会发生变化

class MysqlConfig():
    def __init__(self):
        is_echo = True if settings.ENV == 'DEV' else False
        self.engine = create_engine(
            f'mysql+pymysql://{settings.USERNAME}:{settings.PASSWORD}@{settings.HOST}:{settings.PORT}/{settings.DB}',
            pool_recycle=7000, pool_timeout=30, pool_size=10, echo=is_echo)  # 创建引擎
        self.session = scoped_session(
            sessionmaker(autocommit=True, autoflush=False, bind=self.engine))  # 创建scoped_session

    def __del__(self):
        # 调用scoped_session的remove()方法会调用ScopedSession.close()关闭 session，释放连接资源、把数据库 transaction 状态恢复到初始状态等，
        # 最后销毁 session 本身，
        self.session.remove()

    def get_engine(self):
        return self.engine

    def get_session(self):
        return self.session


mysql_config = MysqlConfig()
engine = mysql_config.get_engine()
session = mysql_config.get_session()

3、创建ORM模型并映射到数据库中

create_table.py

# 表创建
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, DateTime, Boolean, ForeignKey
from config.mysql_config import engine
from common.decorate import *
import datetime
Base = declarative_base(engine)  # SQLORM基类

# @sqlalchemy_model_to_json注解实现，请看本文最后

# 学生表
@sqlalchemy_model_to_json
class Student(Base):
    __tablename__ = 'student'  # 表名
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50))
    age = Column(Integer)
    sex = Column(String(10))
    c_id = Column(Integer, ForeignKey('classes.id'), default=1)
    create_at = Column(DateTime, default=datetime.datetime.now)
    update_at = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now)
    active = Column(Boolean, default=True)


# 班级表
@sqlalchemy_model_to_json
class Classes(Base):
    __tablename__ = 'classes'  # 表名__table
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50))
    create_at = Column(DateTime, default=datetime.datetime.now)
    update_at = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now)
    active = Column(Boolean, default=True)


Base.metadata.create_all()  # 将模型映射到数据库中

执行后输出：

2021-09-05 16:04:16,485 INFO sqlalchemy.engine.Engine SHOW VARIABLES LIKE 'sql_mode'
2021-09-05 16:04:16,485 INFO sqlalchemy.engine.Engine [raw sql] {}
2021-09-05 16:04:16,519 INFO sqlalchemy.engine.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
2021-09-05 16:04:16,519 INFO sqlalchemy.engine.Engine [generated in 0.00017s] {}
2021-09-05 16:04:16,581 INFO sqlalchemy.engine.Engine SELECT DATABASE()
2021-09-05 16:04:16,581 INFO sqlalchemy.engine.Engine [raw sql] {}
2021-09-05 16:04:16,655 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-09-05 16:04:16,656 INFO sqlalchemy.engine.Engine SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = %(table_schema)s AND table_name = %(table_name)s
2021-09-05 16:04:16,656 INFO sqlalchemy.engine.Engine [generated in 0.00013s] {'table_schema': 'SQLAlchemy_db', 'table_name': 'student'}
2021-09-05 16:04:16,678 INFO sqlalchemy.engine.Engine SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = %(table_schema)s AND table_name = %(table_name)s
2021-09-05 16:04:16,678 INFO sqlalchemy.engine.Engine [cached since 0.02225s ago] {'table_schema': 'SQLAlchemy_db', 'table_name': 'classes'}
2021-09-05 16:04:16,702 INFO sqlalchemy.engine.Engine 
CREATE TABLE classes (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	name VARCHAR(50), 
	create_at DATETIME, 
	update_at DATETIME, 
	active BOOL, 
	PRIMARY KEY (id)
)


2021-09-05 16:04:16,702 INFO sqlalchemy.engine.Engine [no key 0.00014s] {}
2021-09-05 16:04:16,827 INFO sqlalchemy.engine.Engine 
CREATE TABLE student (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	name VARCHAR(50), 
	age INTEGER, 
	sex VARCHAR(10), 
	c_id INTEGER, 
	create_at DATETIME, 
	update_at DATETIME, 
	active BOOL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(c_id) REFERENCES classes (id)
)


2021-09-05 16:04:16,827 INFO sqlalchemy.engine.Engine [no key 0.00013s] {}
2021-09-05 16:04:16,919 INFO sqlalchemy.engine.Engine COMMIT

4、增删改查

1、新增数据

add_data.py

from create_table import Classes, Student
from config.mysql_config import session

# 单条数据添加
classes = Classes(name='一年级一班', active=False)  # 创建对象
session.add(classes)  # 添加操作
session.flush()  # 预处理刷写入内存，session.commit()自动执行（autocommit=True）

student = Student(name='小黑', age=8, sex='女', c_id=1, active=False)
session.add(student)

# 批量添加数据
session.add_all([
    Classes(name='一年级二班', active=True),
    Classes(name='一年级三班'),
    Classes(name='一年级四班', active=True),
    Classes(name='一年级五班'),
])

session.add_all([
    Student(name='小李', age=6, sex='男', c_id=2, active=True),
    Student(name='小魏', age=7, sex='男', c_id=3),
    Student(name='小高', age=8, sex='男', c_id=4, active=True),
    Student(name='小朱', age=9, sex='女', c_id=5),
])
session.flush()

2、删除数据

delete_data.py

from create_table import Classes, Student
from config.mysql_config import session

# 添加一条信息
session.add(Student(name='小曹', age=10, sex='女', c_id=5))
session.flush()

# 查询所有
students = session.query(Student).all()
for student in students:
    print(student.to_json())
# {'name': '小黑', 'sex': '女', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': False, 'id': 3, 'age': 8, 'c_id': 1, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}
# {'name': '小李', 'sex': '男', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 4, 'age': 6, 'c_id': 2, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}
# {'name': '小魏', 'sex': '男', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 5, 'age': 7, 'c_id': 3, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}
# {'name': '小高', 'sex': '男', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 6, 'age': 8, 'c_id': 4, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}
# {'name': '小朱', 'sex': '女', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 7, 'age': 9, 'c_id': 5, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}
# {'name': '小曹', 'sex': '女', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 36), 'active': True, 'id': 8, 'age': 10, 'c_id': 5, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 36)}

# 删除指定的记录
session.query(Student).filter(Student.name == '小曹').delete()
session.flush()

# 查询所有
students = session.query(Student).all()
for student in students:
    print(student.to_json())
# {'name': '小黑', 'sex': '女', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': False, 'id': 3, 'age': 8, 'c_id': 1, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}
# {'name': '小李', 'sex': '男', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 4, 'age': 6, 'c_id': 2, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}
# {'name': '小魏', 'sex': '男', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 5, 'age': 7, 'c_id': 3, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}
# {'name': '小高', 'sex': '男', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 6, 'age': 8, 'c_id': 4, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}
# {'name': '小朱', 'sex': '女', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 7, 'age': 9, 'c_id': 5, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}

3、修改数据

update_date.py

from create_table import Classes, Student
from config.mysql_config import session

# 查看原数据
student = session.query(Student).filter(Student.name == '小李').first()
print(student.to_json())
# {'name': '小李', 'sex': '男', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 4, 'age': 8, 'c_id': 2, 'update_at': datetime.datetime(2021, 9, 5, 16, 13, 32)}

# 修改数据
session.query(Student).filter(Student.name == '小李').update({'age': 10})
session.flush()

# 查询刚刚修改的数据
student = session.query(Student).filter(Student.name == '小李').first()
print(student.to_json())
# {'name': '小李', 'sex': '男', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 4, 'age': 10, 'c_id': 2, 'update_at': datetime.datetime(2021, 9, 5, 16, 13, 32)}

4、查询数据

select_data.py

from create_table import Classes, Student
from config.mysql_config import session
from sqlalchemy import or_

# 获取所有数据
students = session.query(Student).all()
for student in students:
    print(student.to_json())  # 注入方法取字典
    print(student.id, student.name, student.age, student.sex, student.c_id)  # 原生取值方式
# {'name': '小黑', 'sex': '女', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': False, 'id': 3, 'age': 8, 'c_id': 1, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}
# 3 小黑 8 女 1
# {'name': '小李', 'sex': '男', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 4, 'age': 10, 'c_id': 2, 'update_at': datetime.datetime(2021, 9, 5, 16, 15, 20)}
# 4 小李 10 男 2
# {'name': '小魏', 'sex': '男', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 5, 'age': 7, 'c_id': 3, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}
# 5 小魏 7 男 3
# {'name': '小高', 'sex': '男', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 6, 'age': 8, 'c_id': 4, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}
# 6 小高 8 男 4
# {'name': '小朱', 'sex': '女', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 7, 'age': 9, 'c_id': 5, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}
# 7 小朱 9 女 5

# 获取所有数据
classess = session.query(Classes).all()
for classes in classess:
    print(classes.to_json())
    print(classes.id, classes.name)
# {'update_at': datetime.datetime(2021, 9, 5, 16, 11, 19), 'active': False, 'id': 1, 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 19), 'name': '一年级一班'}
# 1 一年级一班
# {'update_at': datetime.datetime(2021, 9, 5, 16, 11, 20), 'active': True, 'id': 2, 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 20), 'name': '一年级二班'}
# 2 一年级二班
# {'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 3, 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'name': '一年级三班'}
# 3 一年级三班
# {'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 4, 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'name': '一年级四班'}
# 4 一年级四班
# {'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 5, 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'name': '一年级五班'}
# 5 一年级五班

# 指定查询列
student_names = session.query(Student.name).all()
print(student_names)  # [('小黑',), ('小李',), ('小魏',), ('小高',), ('小朱',)]

# 获取返回数据的第一行
student_name = session.query(Student.name).first()
print(student_name)  # ('小黑',)

# 使用filter()方法进行筛选过滤
student_names = session.query(Student.name).filter(Student.age >= 8).all()
print(student_names)  # [('小黑',), ('小李',), ('小高',), ('小朱',)]

# 使用order_by()进行排序 desc():降序 asc():升序
students = session.query(Student.name, Student.age).order_by(Student.age.desc()).all()
print(students)  # [('小李', 10), ('小朱', 9), ('小黑', 8), ('小高', 8), ('小魏', 7)]

# 使用order_by()进行排序 desc():降序 asc():升序
students = session.query(Student.name, Student.age).order_by(Student.age.asc()).all()
print(students)  # [('小魏', 7), ('小黑', 8), ('小高', 8), ('小朱', 9), ('小李', 10)]

# 使用filter() 和 order_by()进行排序 desc():降序 asc():升序
students = session.query(Student.name, Student.age).filter(Student.age >= 8).order_by(Student.age.asc()).all()
print(students)  # [('小黑', 8), ('小高', 8), ('小朱', 9), ('小李', 10)]

# 多个查询条件(and)
students = session.query(Student).filter(Student.c_id != 1, Student.age >= 8, Student.sex == '男').order_by(
    Student.age.asc()).all()
for student in students:
    print(student.to_json())
# {'name': '小高', 'sex': '男', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 6, 'age': 8, 'c_id': 4, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}
# {'name': '小李', 'sex': '男', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 4, 'age': 10, 'c_id': 2, 'update_at': datetime.datetime(2021, 9, 5, 16, 15, 20)}

# 多个查询条件(or)
students = session.query(Student).filter(or_(Student.c_id != 1, Student.age >= 8, Student.sex == '男')).order_by(
    Student.age.asc()).all()
for student in students:
    print(student.to_json())
# {'name': '小魏', 'sex': '男', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 5, 'age': 7, 'c_id': 3, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}
# {'name': '小黑', 'sex': '女', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': False, 'id': 3, 'age': 8, 'c_id': 1, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}
# {'name': '小高', 'sex': '男', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 6, 'age': 8, 'c_id': 4, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}
# {'name': '小朱', 'sex': '女', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 7, 'age': 9, 'c_id': 5, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}
# {'name': '小李', 'sex': '男', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 4, 'age': 10, 'c_id': 2, 'update_at': datetime.datetime(2021, 9, 5, 16, 15, 20)}

# like(模糊查询)
students = session.query(Student).filter(Student.name.like('%黑')).all()
for student in students:
    print(
        student.to_json())  # {'name': '小黑', 'sex': '女', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': False, 'id': 3, 'age': 8, 'c_id': 1, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}

students = session.query(Student).filter(Student.name.like('%黑%')).all()
for student in students:
    print(
        student.to_json())  # {'name': '小黑', 'sex': '女', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': False, 'id': 3, 'age': 8, 'c_id': 1, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}

# in(区间查询)
students = session.query(Student).filter(Student.age.in_([7, 8])).order_by(Student.age.asc()).all()
for student in students:
    print(student.to_json())
# {'name': '小魏', 'sex': '男', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 5, 'age': 7, 'c_id': 3, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}
# {'name': '小黑', 'sex': '女', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': False, 'id': 3, 'age': 8, 'c_id': 1, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}
# {'name': '小高', 'sex': '男', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 6, 'age': 8, 'c_id': 4, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}

# count计算个数
count = session.query(Student).count()
print(count)  # 5

# 切片
student_names = session.query(Student.name)[:2]
print(student_names)  # [('小黑',), ('小李',)]

# limit 取前几个
students = session.query(Student).limit(2).all()
for student in students:
    print(student.to_json())
# {'name': '小黑', 'sex': '女', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': False, 'id': 3, 'age': 8, 'c_id': 1, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}
# {'name': '小李', 'sex': '男', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 4, 'age': 10, 'c_id': 2, 'update_at': datetime.datetime(2021, 9, 5, 16, 15, 20)}

# offset 偏移量
students = session.query(Student).offset(2).all()
for student in students:
    print(student.to_json())
# {'name': '小魏', 'sex': '男', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 5, 'age': 7, 'c_id': 3, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}
# {'name': '小高', 'sex': '男', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 6, 'age': 8, 'c_id': 4, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}
# {'name': '小朱', 'sex': '女', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 7, 'age': 9, 'c_id': 5, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}

# limit offset 联合使用 偏移offset后取limit个
students = session.query(Student).offset(2).limit(2).all()
for student in students:
    print(student.to_json())
# {'name': '小魏', 'sex': '男', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 5, 'age': 7, 'c_id': 3, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}
# {'name': '小高', 'sex': '男', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 6, 'age': 8, 'c_id': 4, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}

students = session.query(Student).filter(Student.active == True).all()
for student in students:
    print(student.to_json())
# {'name': '小李', 'sex': '男', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 4, 'age': 10, 'c_id': 2, 'update_at': datetime.datetime(2021, 9, 5, 16, 15, 20)}
# {'name': '小魏', 'sex': '男', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 5, 'age': 7, 'c_id': 3, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}
# {'name': '小高', 'sex': '男', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 6, 'age': 8, 'c_id': 4, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}
# {'name': '小朱', 'sex': '女', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 7, 'age': 9, 'c_id': 5, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}

students = session.query(Student).filter(Student.active == False).all()
for student in students:
    print(
        student.to_json())  # {'name': '小黑', 'sex': '女', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': False, 'id': 3, 'age': 8, 'c_id': 1, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}

students = session.query(Student).filter(Student.active != None).all()
for student in students:
    print(student.to_json())
# {'name': '小黑', 'sex': '女', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': False, 'id': 3, 'age': 8, 'c_id': 1, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}
# {'name': '小李', 'sex': '男', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 4, 'age': 10, 'c_id': 2, 'update_at': datetime.datetime(2021, 9, 5, 16, 15, 20)}
# {'name': '小魏', 'sex': '男', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 5, 'age': 7, 'c_id': 3, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}
# {'name': '小高', 'sex': '男', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 6, 'age': 8, 'c_id': 4, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}
# {'name': '小朱', 'sex': '女', 'create_at': datetime.datetime(2021, 9, 5, 16, 11, 21), 'active': True, 'id': 7, 'age': 9, 'c_id': 5, 'update_at': datetime.datetime(2021, 9, 5, 16, 11, 21)}

三、sqlalchemy_model_to_json注解是如何实现的

decorate.py

import copy


# 向被注解类注入方法(好用)
def sqlalchemy_model_to_json(cls):
    def to_json(self):
        # 总结：
        # 　　1） 内置的数据类型没有__dict__属性
        # 　　2） 每个类有自己的__dict__属性，就算存着继承关系，父类的__dict__ 并不会影响子类的__dict__
        # 　　3） 对象也有自己的__dict__属性， 存储self.xxx 信息，父子类对象公用__dict__
        entries = copy.deepcopy(self.__dict__)
        # self.__dict__: {'_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x000001BE4DFB83C8>, 'name': '小黄', 'sex': '女', 'id': 1, 'age': 8, 'c_id': 1}
        if '_sa_instance_state' in entries:
            del entries['_sa_instance_state']
        return entries

    cls.to_json = to_json
    return cls

四、写在最后

入门使用就这些，更高深的用法在实践中去发现吧。。。

THE END

代码编程

二维码

打赏

setuptools使用

< <上一篇

WordPress教程：网站添加运行时间显示

下一篇>>

搜索内容