将SQL转换为SQL炼金术 [英] Convert SQL to SQL alchemy
问题描述
我是Flask SQl炼金术的新手;虽然我知道炼金术可以抽象sql语法并使创建模型变得容易;有时候,我们可能想以一种非常特定的方式在前端可视化数据.
I am new to Flask SQl alchemy; Though i understand that alchemy abstracts the sql syntax and makes things easy while creating models; there could be times when we want to visualize data in the front end in a very specific way.
我有以下查询,我想在使用alchemy的session.query和filter以及可能的分组中使用.
I have the following query which i would like to use using alchemy using session.query and filter and possibly grouping.
我的查询显示为:
- mysql>
SELECT status, COUNT(id) FROM bar_baz where not name = 'Foo' and not name = 'Bar' GROUP BY status
-
select (select COUNT(id) FROM instance where not name = 'erf' and not tiername = 'wer' and type='app') as app, (select COUNT(1) FROM instance_2 where not name = 'visq' and not name = 'werf' and type='adc') as adc from dual;
- mysql>
SELECT status, COUNT(id) FROM bar_baz where not name = 'Foo' and not name = 'Bar' GROUP BY status
select (select COUNT(id) FROM instance where not name = 'erf' and not tiername = 'wer' and type='app') as app, (select COUNT(1) FROM instance_2 where not name = 'visq' and not name = 'werf' and type='adc') as adc from dual;
我验证了以下查询可用于MySQL;我想知道我们是否有类似
I verified that the following queries works with the MySQL; I was wondering if we have a function similar to
c = conn.cursor()
query = 'SELECT status, COUNT(id) FROM bar_baz where not name = 'Foo' and not name = 'Bar' GROUP BY status'
c.execute(query)
print c.fetchall()
class Instance(Base):
__tablename__ = 'instance'
id = Column(Integer, primary_key=True)
name = Column(String)
status = Column(String)
type = Column(String)
class Instance2(Base):
__tablename__ = 'instance_2'
id = Column(Integer, primary_key=True)
name = Column(String)
status = Column(String)
type = Column(String)
inc = Column(Integer)
感兴趣的查询:
select (select COUNT(id) FROM instance where not name = 'erf' and not tiername = 'wer' and type='app') as app, (select COUNT(1) FROM instance_2 where not name = 'visq' and not name = 'werf' and type='adc') as adc from dual;`
推荐答案
对于第一个查询,请使用db.func.count
生成count表达式.通过文档.
For the first query, use db.func.count
to produce the count expression. Everything else should be obvious from the docs.
status_counts = db.session.query(BarBaz.status, db.func.count(BarBaz.id).label('count_id')
).filter(db.not_(db.or_(BarBaz.name == 'Foo', BarBaz.name == 'Bar'))
).group_by(BarBaz.status
).all()
对于第二个查询,请使用 subquery()
产生可选查询.
For the second query, use subquery()
to produce selectable queries.
sub_app = db.session.query(db.func.count(Instance.id).label('app')
).filter(db.not_(db.or_(Instance.name == 'erf', Instance.tiername == 'wer')), Instance.type == 'app'
).subquery()
sub_adc = db.session.query(db.func.count(Instance.id).label('adc')
).filter(db.not_(db.or_(Instance2.name == 'visq', Instance2.name == 'werf')), Instance2.type == 'adc'
).subquery()
out = db.session.query(sub_app.c.app, sub_adc.c.adc).all()
这篇关于将SQL转换为SQL炼金术的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!