python数据库orm_python与数据库的ORM-SQLAlchemy（三、复杂查询）

最新推荐文章于 2024-08-08 17:22:18 发布

手机队长

最新推荐文章于 2024-08-08 17:22:18 发布

阅读量472

点赞数

文章标签： python数据库orm

本文链接：https://blog.csdn.net/weixin_35911221/article/details/113982963

版权

本文详细介绍了如何使用SQLAlchemy进行复杂查询，包括使用`order_by`进行排序，利用`limit`, `offset`, `slice`进行数据切割，查询懒加载，以及聚合函数配合`group_by`和`having`的使用，最后还展示了如何实现子查询。" 123674294,9941233,Nacos、Feign与Spring Cloud Gateway实战,"['Nacos', 'Feign', 'Spring Cloud Gateway']

摘要由CSDN通过智能技术生成

SQLAlchemy在模型层进行复杂查询

1、使用order_by对查询出的数据进行排序

使用的数据如下所示：1

10mysql>select * from article;

+----+------------+-------+--------------+----------+

+----+------------+-------+--------------+----------+

| 1 | Mysql | 17 | 张三 | 100 |

| 2 | Java | 17 | 李四 | 400 |

| 3 | PHP | 19 | NULL | 90 |

| 4 | Android | 13 | 匿名作者 | 90 |

| 5 | Javascript | 12 | 码农三 | 110 |

+----+------------+-------+--------------+----------+

1.1、在查询语句中使用order_by

sqlAlchemy版本为1.3.13

模型如下：1

10class Article(Base):

__tablename__ = 'article'

id = Column(Integer, autoincrement=True, primary_key=True)

title = Column(String(50), nullable=False, unique=True)

price = Column(Float)

author = Column(String(20))

passtime = Column(Integer)

def __repr__(self):

return ''.format(self.title, self.price)

查询语句：1

27articles = session.query(Article).order_by(Article.price).all()

print(articles)

# [,

# ,

# ]

articles = session.query(Article).order_by(-Article.price).all()

# 不推荐，性能会比 Article.price.desc() 差很多

print(articles)

# [,

# ,

# ]

articles = session.query(Article).order_by(Article.price.desc()).all()

print(articles)

# [,

# ,

# ]

articles = session.query(Article).order_by('-price').all()

print(articles) # 报错，这种查询方法不支持在查询语句中排序，正向(小到大)为session.query(模型).order_by(模型.模型属性)

session.query(模型).order_by('字段名')

反向(大到小)为session.query(模型).order_by(模型.模型属性.desc())

session.query(模型).order_by(-模型.模型属性)

不支持session.query(模型).order_by('-字段名')

1.2、在模型层面使用__mapper_args__

模型中添加了__mapper_args__:1

15class Article(Base):

__tablename__ = 'article'

id = Column(Integer, autoincrement=True, primary_key=True)

title = Column(String(50), nullable=False, unique=True)

price = Column(Float)

author = Column(String(20))

passtime = Column(Integer)

__mapper_args__ = {

'order_by': price.desc() # 反向，大到小

# 'order_by': price # 正向，小到大

}

def __repr__(self):

return ''.format(self.title, self.price)

查询语句：1

15articles = session.query(Article).all() # 正向

print(articles)

# [,

# ,

# ]

articles = session.query(Article).all() # 反向

print(articles)

# [,

# ,

# ]

1.3、在模型中relationship中添加

表中数据为：1

19mysql>select * from article;

+----+------------+-------+-----------+

+----+------------+-------+-----------+

| 1 | Mysql | 17 | 1 |

| 2 | Java | 17 | 1 |

| 3 | PHP | 19 | 1 |

| 4 | Android | 13 | 1 |

| 5 | Javascript | 12 | 1 |

+----+------------+-------+-----------+

5 rows in set (0.00 sec)

mysql>select * from author;

+----+------+

| id | name |

+----+------+

| 1 | hcy |

+----+------+

1 row in set (0.00 sec)

模型为：1

17class Author(Base):

__tablename__ = 'author'

id = Column(Integer, primary_key=True, autoincrement=True)

name = Column(String(30), nullable=False)

class Article(Base):

__tablename__ = 'article'

id = Column(Integer, autoincrement=True, primary_key=True)

title = Column(String(50), nullable=False, unique=True)

price = Column(Float)

author_id = Column(Integer, ForeignKey('author.id'))

author = relationship('Author', backref=backref('articles', order_by=price.desc()))

# author = relationship('Author', backref=backref('articles', order_by=price))

def __repr__(self):

return ''.format(self.title, self.price)

查询语句为：1

15articles = session.query(Author).first().articles

print(articles) # 反向从大到小的情况

# [,

# ,

# ]

# 正向从小到大的情况

# author = relationship('Author', backref=backref('articles', order_by=price))

# [,

# ,

# ]

2、limit、offset、slice与切片切割查询数据

2.1、使用的模型和表数据如下：1

8class Article(Base):

__tablename__ = 'article'

id = Column(Integer, autoincrement=True, primary_key=True)

title = Column(String(50), nullable=False, unique=True)

price = Column(Float)

def __repr__(self):

return ''.format(self.title, self.price)1

16mysql> select * from article;

+----+------------+-------+

| id | title | price |

+----+------------+-------+

| 1 | Mysql | 17 |

| 2 | Java | 17 |

| 3 | PHP | 19 |

| 4 | Android | 13 |

| 5 | Javascript | 12 |

| 6 | Node.js | 30 |

| 7 | Nginx | 24 |

| 8 | 前端 | 21 |

| 9 | python | 50 |

| 10 | Linux | 40 |

+----+------------+-------+

10 rows in set (0.00 sec)

2.2、查询语句：1

46articles = session.query(Article).limit(4).all() # 从头开始限制查询4条数据

print(articles)

# [,

# ,

# ]

articles = session.query(Article).order_by(Article.id.desc()).limit(4).all()

# 以id反向排序，从头开始限制查询4条数据

print(articles)

# [,

# ,

# ]

articles = session.query(Article).slice(2, 6).all() # 查询2到6之间的数据，不包括第6条

print(articles)

# [,

# ,

# ]

articles = session.query(Article)[2:6] # 查询2到6之间的数据，不包括第6条

print(articles)

# [,

# ,

# ]

articles = session.query(Article)[2] # 查询第二条数据

print(articles)

articles = session.query(Article).limit(4).offset(2).all() # 偏移2条开始往后查询4条数据

print(articles)

# [,

# ,

# ]

articles = session.query(Article).offset(2).limit(4).all() # 偏移2条开始往后查询4条数据，和上面效果相同

print(articles)

# [,

# ,

# ]

2.3、查询条件方法分析：limit(step)：step为步长，限制查询长度，limit(4)为查询4条数据，需要加上all()

offset(start)：offset为设置起始偏移量，offset(2)即为从第三条数据开始查询，索引从0开始，需要加上all()

slice(start, stop)：从起始切割到结束，包含start不包含stop，需要加上all()

直接使用python中的切片语法，session.query('模型')[start: stop]，不需要加all()

3、查询懒加载

3.1、模型与数据1

17class Author(Base):

__tablename__ = 'author'

id = Column(Integer, primary_key=True, autoincrement=True)

name = Column(String(30), nullable=False)

class Article(Base):

__tablename__ = 'article'

id = Column(Integer, autoincrement=True, primary_key=True)

title = Column(String(50), nullable=False, unique=True)

price = Column(Float)

author_id = Column(Integer, ForeignKey(Author.id), nullable=False)

authors = relationship('Author', backref=backref('articles', lazy='dynamic'))

# lazy='dynamic' 可以设置查询懒加载

# authors = relationship('Author', backref=backref('articles'))

def __repr__(self):

return ''.format(self.title, self.price)1

24mysql> select * from author;

+----+------+

| id | name |

+----+------+

| 1 | hcy |

+----+------+

1 row in set (0.00 sec)

mysql> select * from article;

+----+------------+-------+-----------+

+----+------------+-------+-----------+

| 1 | Mysql | 17 | 1 |

| 2 | Java | 17 | 1 |

| 3 | PHP | 19 | 1 |

| 4 | Android | 13 | 1 |

| 5 | Javascript | 12 | 1 |

| 6 | Node.js | 30 | 1 |

| 7 | Nginx | 24 | 1 |

| 8 | 前端 | 21 | 1 |

| 9 | python | 50 | 1 |

| 10 | Linux | 40 | 1 |

+----+------------+-------+-----------+

10 rows in set (0.00 sec)

3.2、懒加载后使用查询语句1

19user = session.query(Author).first()

articles = user.articles

print(articles)

# 默认的 lazy='select',访问这个数据对象时全部查出来，返回一个列表

# [,

# ,

# ]

# lazy='dynamic',访问这个数据时返回的是query语句,可以再添加filter、order_by这些查询条件

# SELECT article.id AS article_id, article.title AS article_title, article.price AS article_price, article.author_id AS article_author_id

# FROM article

# WHERE %(param_1)s = article.author_id

添加查询条件的查询语句1

5user = session.query(Author).first()

articles = user.articles.filter(Article.price > 20).order_by(Article.price).limit(2).all()

print(articles)

# [,

# ]

4、聚合函数后再使用group_by和having

4.1、使用聚合函数需要导入的模块及内外连接参考1from sqlalchemy import func

4.2、模型与数据库数据1

8class Article(Base):

__tablename__ = 'article'

id = Column(Integer, autoincrement=True, primary_key=True)

title = Column(String(50), nullable=False, unique=True)

price = Column(Float)

def __repr__(self):

return ''.format(self.title, self.price)1

16mysql> select * from article;

+----+------------+-------+

| id | title | price |

+----+------------+-------+

| 1 | Mysql | 17 |

| 2 | Java | 17 |

| 3 | PHP | 19 |

| 4 | Android | 13 |

| 5 | Javascript | 12 |

| 6 | Node.js | 30 |

| 7 | Nginx | 24 |

| 8 | 前端 | 21 |

| 9 | python | 50 |

| 10 | Linux | 40 |

+----+------------+-------+

10 rows in set (0.00 sec)

4.3、查询分析根据稿子价格分组，统计各组稿子数量1

7articles = session.query(func.count(Article.id), Article.price).group_by(Article.price).all()

print(articles) # 不对聚合函数后查询结果再筛选

# [(2, 17.0), (1, 19.0), (1, 13.0), (1, 12.0), (1, 30.0), (1, 24.0), (1, 21.0), (1, 50.0), (1, 40.0)]

articles = session.query(func.count(Article.id), Article.price).group_by(Article.price).having(Article.price >= 17).all()

print(articles) # 聚合函数查询后的结果再根据价格大于等于17筛选

# [(2, 17.0), (1, 19.0), (1, 30.0), (1, 24.0), (1, 21.0), (1, 50.0), (1, 40.0)]这个和MySQL一样，聚合函数分组通过group_by，聚合函数查询的数据再次进行筛选使用having

5、内外连接join下面使用一个案例，不考虑外连接

5.1、模型与数据库数据1

18class Author(Base):

__tablename__ = 'author'

id = Column(Integer, primary_key=True, autoincrement=True)

name = Column(String(30), nullable=False)

def __repr__(self):

return ''.format(self.name)

class Article(Base):

__tablename__ = 'article'

id = Column(Integer, autoincrement=True, primary_key=True)

title = Column(String(50), nullable=False, unique=True)

price = Column(Float)

author_id = Column(Integer, ForeignKey(Author.id), nullable=False)

authors = relationship('Author', backref=backref('articles', lazy='dynamic'))

def __repr__(self):

return ''.format(self.title, self.price)1

25mysql> select * from author;

+----+------+

| id | name |

+----+------+

| 1 | hcy |

| 2 | yuki |

+----+------+

2 rows in set (0.00 sec)

mysql> select * from article;

+----+------------+-------+-----------+

+----+------------+-------+-----------+

| 1 | Mysql | 17 | 1 |

| 2 | Java | 17 | 1 |

| 3 | PHP | 19 | 1 |

| 4 | Android | 13 | 1 |

| 5 | Javascript | 12 | 1 |

| 6 | Node.js | 30 | 1 |

| 7 | Nginx | 24 | 2 |

| 8 | 前端 | 21 | 2 |

| 9 | python | 50 | 2 |

| 10 | Linux | 40 | 2 |

+----+------------+-------+-----------+

10 rows in set (0.00 sec)

5.2、MySQL语法写的1

8mysql> select author.name, count(article.id) from author join article on author.id = article.author_id group by author.name order by count(article.id) desc;

+------+-------------------+

| name | count(article.id) |

+------+-------------------+

| hcy | 6 |

| yuki | 4 |

+------+-------------------+

2 rows in set (0.00 sec)查询作者名已经作者文章数总计，根据作者名分组再根据作者文章数反向排序

5.3、sqlAlchemy版本1

9# 同上面MySQL查询结果相同

result = session.query(Author.name, func.count(Article.id)).join(Article, Article.author_id == Author.id).group_by(Author.name).order_by(func.count(Article.id).desc()).all()

print(result)

# [('hcy', 6), ('yuki', 4)]

# 或者是只查询作者名

result = session.query(Author.name).join(Article, Article.author_id == Author.id).group_by(Author.name).order_by(func.count(Article.id).desc()).all()

print(result)

# [('hcy',), ('yuki',)]query里的查询字段同MySQL在select后面接的查询字段

join第一个参数是连接的模型，第二个参数是连接条件，也就是同MySQL在on后面的连接条件

group_by同MySQL里的分组group by字段

order_by同MySQL里的排序order by字段

6、subquery进行子查询

6.1、模型与数据1

9class User(Base):

__tablename__ = 'user'

id = Column(Integer, primary_key=True, autoincrement=True)

name = Column(String(50), nullable=True)

city = Column(String(30))

age = Column(Integer)

def __repr__(self):

return ''.format(self.name)1

10mysql> select * from user;

+----+-------+------+------+

| id | name | city | age |

+----+-------+------+------+

| 1 | hcy | wh | 20 |

| 2 | ying | wh | 20 |

| 3 | yuki | wh | 20 |

| 4 | yuuki | hf | 16 |

+----+-------+------+------+

4 rows in set (0.00 sec)

6.2、MySQL版子查询查和name为hcy同龄且同城的人1

9mysql> select user.name, user.city, user.age from user, (select name, city, age from user where name = "hcy") as hcy where user.city = hcy.city and user.age = hcy.age;

+------+------+------+

| name | city | age |

+------+------+------+

| hcy | wh | 20 |

| ying | wh | 20 |

| yuki | wh | 20 |

+------+------+------+

3 rows in set (0.00 sec)在from后跟上子查询语句，再使用as取别名，因为后面会用上

6.3、SQLAlchemy版本不使用子查询使用多句SQL语句1

6hcy = session.query(User.name, User.age, User.city).filter(User.name == 'hcy').first()

result = session.query(User).filter(User.city == hcy.city, User.age == hcy.age).all()

print(result)