translate SQL query to flask-sqlalchemy statements
<p>I am changing my old SQL implementations of a web app to flask-alchemy and having some difficulties about the correspondence.</p>

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

<pre><code>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()
conn.close()
rows = [','.join(map(str, row)) for row in results]
return header + rows
</code></pre>

<p>The new code</p>

<pre><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.id, user.bbs_id, user.city, user.state, user.latitude, user.longitude]) + '\n'
return header + rows
</code></pre>

<p>I am not happy with the new code since it's so verbose. </p>

<ul>
<li>Is there a way to select only the ones in <code>cols</code> instead of query all columns and then pick the needed columns? </li>
<li>If not, is it possible to write the <code>','.join()</code> more succinctly? It seems <code>user['id']</code> does not work and I have to do <code>user.id</code>.</li>
</ul>

<p>As it seems that you want to output comma separated values, use the <a href="https://docs.python.org/3/library/csv.html" rel="nofollow">proper module</a> for that. You can override the query's entities with <a href="http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.with_entities" rel="nofollow">with_entities</a>:</p>

<pre><code>import csv
import io

...

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

headers = ['id', 'bbs_id', 'city', 'state', 'latitude', 'longitude'] 
writer.writerow(headers)

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

return output.getvalue()
</code></pre>

<p>If you're still on python 2, <a href="http://stackoverflow.com/questions/13120127/how-can-i-use-io-stringio-with-the-csv-module">replace <code>io.StringIO</code> with <code>io.BytesIO</code></a>.</p>

python sql sqlalchemy flask-sqlalchemy