Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
536 views
in Technique[技术] by (71.8m points)

python - Convert SQL to SQL alchemy

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.

I have the following query which i would like to use using alchemy using session.query and filter and possibly grouping.

My query reads:

  1. mysql> SELECT status, COUNT(id) FROM bar_baz where not name = 'Foo' and not name = 'Bar' GROUP BY status
  2. 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;

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)

The query of Interest:

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;`
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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()

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()

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...