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?

fedorqui · Accepted Answer · 2016-12-12 11:10:43Z

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.

gliptak · Accepted Answer · 2017-01-01 14:36:42Z

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.

The first snippet is what I initially came up with and returned everything. For the second snippet I get "ValueError: too many values to unpack" - hrm?
TypeError: select() takes at most 2 arguments (4 given) >> s = users_table.select(users_table.c.user_name, users_table.c.user_location,users_table.c.user_name == username)
Try again, I confused the syntax of select() with table.select().
Thanks - your modified version seems to work. But how could I also print the column name in addition to the value? So right now it spits out 'jeff', 'usa' whereas I actually want user_name: jeff, country: usa. It just feels a bit weird because the object returned is a "smart object" and not a regular simple dictionary/array that I can process the keys/values with...

Collectives™ on Stack Overflow

SQLAlchemy: Operating on results

2 Answers 2

Comments

7 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Related