sql語句轉 sqlalchemy的問題

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怎么寫？

回答

編輯回答

巷尾

感謝上面幾位的幫助。這里自己想出一種方法。

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()   # 最后指定排序

2018年2月13日 19:51

編輯回答

別瞎鬧

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)

2018年4月15日 20:09

編輯回答

汐顏

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...

2018年3月3日 04:32