【大模型应用开发-FastAPI框架】(九)Sqlalchemy+postgresql分页查询和执行sql

一、postgresql安装

【大模型应用开发-Postgresql数据库】（二）Linux使用Docker安装Postgresql-CSDN博客

二、简单增删改查参考

【大模型应用开发-FastAPI框架】(八)Sqlalchemy+postgresql简单增删改查-CSDN博客

三、分页查询和执行sql

1、分页查询

# 分页查询
def find_by_page(page: int, size: int):
    offset = (page - 1) * size
    data1 = session.query(TestData).offset(offset).limit(size).all()
    if data1:
        for item in data1:
            print(item.id, item.name, item.uuid, item.add_time, item.data)
    return []

2、执行sql语句

# 执行sql
def execute_sql(sql: str):
    res = session.execute(text(sql))
    res = res.all()
    session.commit()
    return res

完整代码

import time
import uuid
import datetime
import sqlalchemy
import sqlalchemy.orm as orm
from sqlalchemy.orm import declarative_base
from sqlalchemy import text

# pip install sqlalchemy psycopg2

Base = declarative_base()

# 创建数据库连接
url = 'postgresql://admin:admin@172.30.1.23:55433/mytest'
engine = sqlalchemy.create_engine(url)

Session = orm.sessionmaker(bind=engine)
session = Session()


# 定义数据模型
class TestData(Base):
    __tablename__ = "tb_test_1"

    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True, autoincrement=True)
    name = sqlalchemy.Column(sqlalchemy.String(256), nullable=True)
    uuid = sqlalchemy.Column(sqlalchemy.String(64), unique=True)
    add_time = sqlalchemy.Column(sqlalchemy.DateTime())
    data = sqlalchemy.Column(sqlalchemy.Text(), default='{}')

    def __init__(self, name, uuid, data='{}', add_time=None):
        self.name = name
        self.uuid = uuid
        self.data = data
        self.add_time = add_time if None != add_time else datetime.datetime.now()


# 分页查询
def find_by_page(page: int, size: int):
    offset = (page - 1) * size
    data1 = session.query(TestData).offset(offset).limit(size).all()
    if data1:
        for item in data1:
            print(item.id, item.name, item.uuid, item.add_time, item.data)
    return []


# 执行sql
def execute_sql(sql: str):
    res = session.execute(text(sql))
    res = res.all()
    session.commit()
    return res


def run():
    # 查询第1页，每页2行
    # find_by_page(1, 2)

    # 执行sql
    res = execute_sql(f"select count(*) from {TestData.__tablename__}")
    if res:
        print(res[0][0])


if __name__ == '__main__':
    run()

四、其他查询写法

1、SQL 与 SQLalchemy 的写法区别

为了更好的理解 SQL 与 SQLalchemy 的写法区别，可以参照以下内容：

• query ：对应 SELECT xxx FROM xxx
• filter/filter_by ：对应 WHERE ，fillter 可以进行比较运算(==, >, < ...)来对条件进行灵活的运用，不同的条件用逗号分割，fillter_by 只能指定参数传参来获取查询结果。
• limit ：对应 limit()
• order by ：对应 order_by()
• group by ：对应 group_by()

2、like查询

# like 
data_like = session.query(Person).filter(Person.desc.like("活%")).all()
# not like
data_like = session.query(Person).filter(Person.desc.notlike("活%")).all()

3、is查询

# is_ 相当于 ==
result = session.query(Person).filter(Person.username.is_(None)).all()
result = session.query(Person).filter(Person.username == None).all()
# isnot 相当于 !=
result = session.query(Person).filter(Person.username.isnot(None)).all()
result = session.query(Person).filter(Person.username != None).all()

4、正则查询

data_regexp = session.query(Person).filter(Person.password.op("regexp")(r"^[\u4e00-\u9fa5]+")).all()

5、统计数量

data_like_count = session.query(Person).filter(Person.desc.like("活%")).count()

6、IN 查询

more_person = session.query(Person).filter(Person.username.in_(['Mark', 'Tony'])).all()

7、NOT IN 查询

# ~代表取反,转换成sql就是关键字not
more_person = session.query(Person).filter(~Person.username.in_(['Mark', 'Tony'])).all()
# 或 notin_
more_person = session.query(Person).filter(~Person.username.notin_(['Mark', 'Tony'])).all()

8、AND 查询

from sqlalchemy import and_

more_person = session.query(Person).filter(and_(Person.password=='123456',Person.desc=="可爱'")).all()

9、OR 查询

from sqlalchemy import or_

more_person = session.query(Person).filter(or_(Person.password=='123456',Person.desc=="活泼'")).all()

10、分组查询

std_group_by = session.query(Person).group_by(Person.desc).all()
# 或是
from sqlalchemy.sql import func

res = session.query(Person.desc,
                    func.count(Person.desc),
                   ).group_by(Person.desc).all()

# 遍历查看，已无ed用户记录
for person in res:
    print(person)

11、排序查询

std_order_by = session.query(Person).order_by(Person.username.desc()).all()

12、limit 查询

# limit 限制数量查询, limit里传入一个整型来约束查看的数量, 当limit里面的参数大于实例表中的数量时，会返回所有的查询结果
data_limit = session.query(Person).filter(Person.desc.notlike("活%")).limit(1).all()

13、偏移量查询

# offset 偏移量查询,offset中传入一个整型，从表中的该位置开始查询，offset可以和limit混用来进行限制
data_like = session.query(Person).filter(Person.desc.like("活%")).offset(1).all()
result = session.query(Person).offset(1).limit(6).all()

14、聚合函数

from sqlalchemy import func, extract
# count
result = session.query(Person.password, func.count(Person.id)).group_by(Person.password).all()
# sum
result = session.query(Person.password, func.sum(Person.id)).group_by(Person.password).all()
# max
result = session.query(Person.password, func.max(Person.id)).group_by(Person.password).all()
# min
result = session.query(Person.password, func.min(Person.id)).group_by(Person.password).all()
# having
result = session.query(Person.password, func.count(Person.id)).group_by(Person.password).having(func.count(Person.id) > 1).all()

15、关于返回结果数量

all()
- 查询所有
- 返回一个列表对象

first()
- 查询第一个符合条件的对象
- 返回一个对象

16、关于传参

filter = (Person.username=='Mark')

our_user = session.query(Person).filter(filter).first()
print(our_user)