一、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)