nos nos - 5 months ago 27
Python Question

translate SQL query to flask-sqlalchemy statements

I am changing my old SQL implementations of a web app to flask-alchemy and having some difficulties about the correspondence.

The old code looks like this. It does the name query about some properties and returns a csv style text.

header = 'id,display_name,city,state,latitude,longitude\n'
base_query = '''SELECT id, bbs_id, city, state,
latitude, longitude FROM mytable'''
conn = sqlite3.connect(path.join(ROOT,'db.sqlite'))
c = conn.execute(base_query+'WHERE name=?', (name,))
results = c.fetchall()
rows = [','.join(map(str, row)) for row in results]
return header + rows

The new code

header = 'id,display_name,city,state,latitude,longitude\n'
cols = ['id', 'bbs_id', 'city', 'state', 'latitude', 'longitude']
users = User.query.filter_by(name=name).all()
rows = ''
for user in users:
rows += ','.join([, user.bbs_id,, user.state, user.latitude, user.longitude]) + '\n'
return header + rows

I am not happy with the new code since it's so verbose.

  • Is there a way to select only the ones in
    instead of query all columns and then pick the needed columns?

  • If not, is it possible to write the
    more succinctly? It seems
    does not work and I have to do


As it seems that you want to output comma separated values, use the proper module for that. You can override the query's entities with with_entities:

import csv
import io


output = io.StringIO()
writer = csv.writer(output)

headers = ['id', 'bbs_id', 'city', 'state', 'latitude', 'longitude'] 

# The other option is to db.session.query(...)
users = User.query.with_entities(
    *(getattr(User, hdr) for hdr in headers)

return output.getvalue()

If you're still on python 2, replace io.StringIO with io.BytesIO.