SELECT * in SQLAlchemy?

Question

Is it possible to do SELECT * in SQLAlchemy?

Specifically, SELECT * WHERE foo=1?

Answer 1

As of SQLAlchemy 2.0, one of the ways to do this is passing the name of the object representing the table in the select function:

engine = sqlalchemy.create_engine("{DB_dialect}+{DBAPI}://{server}/{database}?trusted_connection=yes&driver=ODBC+Driver+17+for+SQL+Server")
conn = engine.connect()
metadata = sqlalchemy.MetaData()
metadata.reflect(bind=engine)
table = metadata.tables["user"]

query = sqlalchemy.select(table).where(table.c.first_name == 'John').where(table.c.last_name == 'Doe')

This code creates a trusted connection and a SQL query like:

SELECT * FROM user
WHERE first_name = "John" AND last_name = "DOE"

Answer 2

Is no one feeling the ORM love of SQLAlchemy today? The presented answers correctly describe the lower-level interface that SQLAlchemy provides. Just for completeness, this is the more-likely (for me) real-world situation where you have a session instance and a User class that is ORM mapped to the users table.

for user in session.query(User).filter_by(name='jack'):
     print(user)
     # ...

And this does an explicit select on all columns.

Answer 3

every_column = User.__table__.columns
records = session.query(*every_column).filter(User.foo==1).all()

When a ORM class is passed to the query function, e.g. query(User), the result will be composed of ORM instances. In the majority of cases, this is what the dev wants and will be easiest to deal with--demonstrated by the popularity of the answer above that corresponds to this approach.

In some cases, devs may instead want an iterable sequence of values. In these cases, one can pass the list of desired column objects to query(). This answer shows how to pass the entire list of columns without hardcoding them, while still working with SQLAlchemy at the ORM layer.

Answer 4

I had the same issue, I was trying to get all columns from a table as a list instead of getting ORM objects back. So that I can convert that list to pandas dataframe and display.

What works is to use .c on a subquery or cte as follows:

U = select(User).cte('U')
stmt = select(*U.c)
rows = session.execute(stmt)

Then you get a list of tuples with each column.

Another option is to use __table__.columns in the same way:

stmt = select(*User.__table__.columns)
rows = session.execute(stmt)

In case you want to convert the results to dataframe here is the one liner:

pd.DataFrame.from_records(rows, columns=rows.keys())

Answer 5

I had the same issue, I was trying to get all columns from a table as a list instead of getting ORM objects back. So that I can convert that list to pandas dataframe and display.

What works is to use .c on a subquery or cte as follows:

U = select(User).cte('U')
stmt = select(*U.c)
rows = session.execute(stmt)

Then you get a list of tuples with each column.

Another option is to use __table__.columns in the same way:

stmt = select(*User.__table__.columns)
rows = session.execute(stmt)

In case you want to convert the results to dataframe here is the one liner:

pd.DataFrame.from_records(dict(zip(r.keys(), r)) for r in rows)

Answer 6

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 7

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 8

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 9

If you're using the ORM, you can build a query using the normal ORM constructs and then execute it directly to get raw column values:

query = session.query(User).filter_by(name='jack')
for cols in session.connection().execute(query):
    print cols

Answer 10

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

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

Answer 11

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 12

Turns out you can do:

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