SQLAlchemy: Operating on results

Question

I'm trying to do something relatively simple, spit out the column names and respective column values, and possibly filter out some columns so they aren't shown.

This is what I attempted ( after the initial connection of course ):

metadata = MetaData(engine)

users_table = Table('fusion_users', metadata, autoload=True)

s = users_table.select(users_table.c.user_name == username)
results = s.execute()

if results.rowcount != 1:
    return 'Sorry, user not found.'
else:
    for result in results:
    for x, y in result.items()
        print x, y

I looked at the API on SQLAlchemy ( v.5 ) but was rather confused. my 'result' in 'results' is a RowProxy, yet I don't think it's returning the right object for the .items() invocation.

Let's say my table structure is so:

user_id    user_name    user_password    user_country
0          john         a9fu93f39uf      usa

i want to filter and specify the column names to show ( i dont want to show the user_password obviously ) - how can I accomplish this?

Answer 1

A SQLAlchemy RowProxy object has dict-like methods -- .items() to get all name/value pairs, .keys() to get just the names (e.g. to display them as a header line, then use .values() for the corresponding values or use each key to index into the RowProxy object, etc, etc -- so it being a "smart object" rather than a plain dict shouldn't inconvenience you unduly.

Answer 2

You can use results instantly as an iterator.

results = s.execute()

for row in results:
    print row

Selecting specific columns is done the following way:

from sqlalchemy.sql import select

s = select([users_table.c.user_name, users_table.c.user_country], users_table.c.user_name == username)

for user_name, user_country in s.execute():
   print user_name, user_country

To print the column names additional to the values the way you have done it in your question should be the best because RowProxy is really nothing more than a ordered dictionary.

IMO the API documentation for SqlAlchemy is not really helpfull to learn how to use it. I would suggest you to read the SQL Expression Language Tutorial. It contains the most vital information about basic querying with SqlAlchemy.