sql语句 转 sqlalchemy的问题

Question

MariaDB [blog]> desc posts;
+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| id            | int(11)     | NO   | PRI | NULL    | auto_increment |
| title         | varchar(64) | YES  | UNI | NULL    |                |
| body          | text        | YES  |     | NULL    |                |
| body_html     | text        | YES  |     | NULL    |                |
| timestamp     | datetime    | YES  |     | NULL    |                |
| author_id     | int(11)     | YES  | MUL | NULL    |                |
| comment_count | int(11)     | YES  |     | NULL    |                |
+---------------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

MariaDB [blog]> desc talks;
+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| id            | int(11)     | NO   | PRI | NULL    | auto_increment |
| title         | varchar(64) | YES  | UNI | NULL    |                |
| body          | text        | YES  |     | NULL    |                |
| body_html     | text        | YES  |     | NULL    |                |
| timestamp     | datetime    | YES  |     | NULL    |                |
| author_id     | int(11)     | YES  | MUL | NULL    |                |
| comment_count | int(11)     | YES  |     | NULL    |                |
+---------------+-------------+------+-----+---------+----------------+

这有两张结构相同的表。我向通过时间做聚合排序。sql语句如下：

select * from (select * from posts union select * from talks) as a_b order by timestamp;

python代码：

class Post(db.Model):
    __tablename__ = 'posts'
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(64), unique=True, index=True)
    body = db.Column(db.Text)
    body_html = db.Column(db.Text)
    timestamp = db.Column(db.DateTime, default=datetime.utcnow)
    tags = db.relationship('Tag',
                                secondary=pwt,
                                backref=db.backref('posts', lazy='dynamic'),
                                lazy='dynamic')
    author_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    comments = db.relationship('Comment', backref='post', lazy='dynamic')

class Talk(db.Model):
    __tablename__ = 'talks'
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(64), unique=True, index=True)
    body = db.Column(db.Text)
    body_html = db.Column(db.Text)
    timestamp = db.Column(db.DateTime, default=datetime.utcnow)
    tags = db.relationship('Tag',
                                secondary=twt,
                                backref=db.backref('talks', lazy='dynamic'),
                                lazy='dynamic')
    author_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    comments = db.relationship('Comment', backref='talk', lazy='dynamic')

请问转化成sqlalchemy怎么写？

Answer 1

import sqlalchemy

post = Post.query.with_entitied(Post.id, Post.title).cte(name='somename', recursive=True)
post = post.union_all(Talk.query.with_entities(Talk.id, Talk.title))
db.query(post).order_by(post.c.id.asc()).all()

应该没问题，建议多看看源码或官方文档。
http://docs.sqlalchemy.org/en...

Answer 2

from sqlalchemy.sql import union

sbq = union(Post.query, Talk.query).subquery()

# data = sbq.query.order_by(sbq.c.timestamp).all()

data = db.session.query(sbq.c.title, sbq.c.body, sbq.c.body_html, sbq.c.timestamp).order_by(sbq.c.timestamp).all()

for d in data:
    print(d.title, d.body, d.body_html, d.timestamp)
    

Answer 3

感谢上面几位的帮助。这里自己想出一种方法。

q1 = db.session.query(Post.id)    # 需要聚合的表
q2 = db.session.query(Talk.id)    # 需要集合的表
post_talk = q1.union(q2).with_entities(Post, Post.timestamp.label('time')).subquery() # 将多表聚合，在指定排序的别名，如timestamp 
result = db.session.query(post_talk).order_by(post_talk.c.time.desc()).all()   # 最后指定排序