Actually what I would really find useful is the ability to select all columns except 2. In rails I sometimes dont need to see created_at and updated_at
Syntax like this is fine for ad hoc queries, but not in a query that's part of an application. You're writing those queries once to execute hundreds, thousands, millions of times. Making your query concrete is probably wise.
CREATE TABLE safe_users AS
SELECT *
EXCEPT users.pii
FROM users
;
"Root cause analysis: The {database administrators|programmers} were too lazy to explicitly define the column names they wanted, resulting in the unintended disclosure of HIV positive status of $bignum individuals."
Same as anything else in software: I considered the pros and cons, and made a choice to go a particular route.
> And what happens if a column is added to the table?
It will show up in the query, as noted in the documentation.
> What if you use positional indexing instead of column names in your application (it performs better in many systems) and the column order is changed?
Then you will have a bug. Don't use both at the same time.
> Does your RDBMS or the SQL standard even guarantee the order of columns returned by a ?
Read the documentation. If order is not guaranteed, do not assume it will always be the same.
> Syntax like this is fine for ad hoc queries, but not in a query that's part of an application.
It's my money, I'll* decide what belongs in my application and what doesn't.
> You're writing those queries once to execute hundreds, thousands, millions of times.
You are speculating.
All of the above arguments could be made about 50% of functions existing in software, yet life goes on. The world isn't that fragile.
Not a Ruby guy but the Ruby equivalent of something like:
rejected [:updated_at, :inserted_at]
to_select =
Enum.reject(fields, fn field ->
field in rejected
end) to_select = fields - [:created_at, :updated_at] cols = list(table.columns)
cols.remove(table.columns['updated_at'])
cols.remove(table.columns['inserted_at'])
query = select(cols).select_from(table)
With sqlalchemy.orm you could also define a Bundle, see https://docs.sqlalchemy.org/en/latest/orm/query.html?highlig...
http://docs.sqlalchemy.org/en/latest/orm/loading_columns.htm...
This is both at the 'object' level, and at the query level for example at the query level: from sqlalchemy.orm import defer, undefer
query = session.query(Book) query = query.options(defer('summary')) query = query.options(undefer('excerpt')) query.all()
You can also group columns for deferral/undeferral.
I use this loads.
I think if you strip columns for performance reasons for one specific query it would still be better to use Bundles or the equivalent loose select (i.e. session.query(Book.id, Book.title)) instead of query-local-deferred columns, because then you will notice if a code change leads to a down-stream access of an unwanted column for some reason.