SELECT * in SQLAlchemy?

Question

Is it possible to do SELECT * in SQLAlchemy?

Specifically, SELECT * WHERE foo=1?

Answer 1

If you don't list any columns, you get all of them.

query = users.select()
query = query.where(users.c.name=='jack')
result = conn.execute(query)
for row in result:
    print row

Should work.

Answer 2

Turns out you can do:

sa.select('*', ...)

Answer 3

You can always use a raw SQL too:

str_sql = sql.text("YOUR STRING SQL")
#if you have some args:
args = {
    'myarg1': yourarg1
    'myarg2': yourarg2}
#then call the execute method from your connection
results = conn.execute(str_sql,args).fetchall()

Answer 4

The following selection works for me in the core expression language (returning a RowProxy object):

foo_col = sqlalchemy.sql.column('foo')
s = sqlalchemy.sql.select(['*']).where(foo_col == 1)

Answer 5

For joins if columns are not defined manually, only columns of target table are returned. To get all columns for joins(User table joined with Group Table:

sql = User.select(from_obj(Group, User.c.group_id == Group.c.id))
# Add all coumns of Group table to select
sql = sql.column(Group)
session.connection().execute(sql)

Answer 6

Where Bar is the class mapped to your table and session is your sa session:

bars = session.query(Bar).filter(Bar.foo == 1)