3 ms·

> Never use SELECT * within the context of an application, as column additions or removals can break the app. Use a dictionary cursor, or some other method to

> Never use SELECT * within the context of an application, as column additions or removals can break the app. Use a dictionary cursor, or some other method to convert whatever is returned into a hashmap like so: https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursordict.html https://dev.mysql.com/doc/connector-python/en/connector-pyth...

sk5t 5y ago
Not good enough, as 'select *' can in any event introduce ambiguity that is fatal to query processing.
- ted_dunning 5y ago
  Do you have an example to back that up?
- ted_dunning 5y ago
  It actually appears to go the other way. Explicitly referencing an ambiguous column causes the error while using select * avoids it, at least with duckdb (which is a very standard sort of SQL). This suggests to me that using select * in views is a very good practice. D create table a (amb integer, a1 integer, a2 integer); D create table b (amb varchar, b1 integer, b2 integer); D insert into a values (1,1,1); D insert into b values ('b',2,2); D insert into b values ('b',1,3); D select * from a, b where a.a1 = b.b1; ┌─────┬────┬────┬─────┬────┬────┐ │ amb │ a1 │ a2 │ amb │ b1 │ b2 │ ├─────┼────┼────┼─────┼────┼────┤ │ 1 │ 1 │ 1 │ b │ 1 │ 3 │ └─────┴────┴────┴─────┴────┴────┘ D select amb from a, b where a.a1 = b.b1; Error: Binder Error: Ambiguous reference to column name "amb" (use: "b.amb" or "a.amb")
  - listenallyall 5y ago
    The database query analyzer is doing you a favor by telling you there's an ambiguous column name and refusing to run, for the same reason a compiler won't allow you to define the same variable name twice.
RedShift1 5y ago
How does that help against columns being added or removed?
- willcipriano 5y ago
  If they are added they are just another key in the hashmap, no harm. If they are removed, I don't have a solution for that. Typically you don't remove columns of databases like that, but adding them is common.

sk5t 5y ago

Not good enough, as 'select *' can in any event introduce ambiguity that is fatal to query processing.

ted_dunning 5y ago
Do you have an example to back that up?
ted_dunning 5y ago
It actually appears to go the other way. Explicitly referencing an ambiguous column causes the error while using select * avoids it, at least with duckdb (which is a very standard sort of SQL). This suggests to me that using select * in views is a very good practice. D create table a (amb integer, a1 integer, a2 integer); D create table b (amb varchar, b1 integer, b2 integer); D insert into a values (1,1,1); D insert into b values ('b',2,2); D insert into b values ('b',1,3); D select * from a, b where a.a1 = b.b1; ┌─────┬────┬────┬─────┬────┬────┐ │ amb │ a1 │ a2 │ amb │ b1 │ b2 │ ├─────┼────┼────┼─────┼────┼────┤ │ 1 │ 1 │ 1 │ b │ 1 │ 3 │ └─────┴────┴────┴─────┴────┴────┘ D select amb from a, b where a.a1 = b.b1; Error: Binder Error: Ambiguous reference to column name "amb" (use: "b.amb" or "a.amb")
- listenallyall 5y ago
  The database query analyzer is doing you a favor by telling you there's an ambiguous column name and refusing to run, for the same reason a compiler won't allow you to define the same variable name twice.

RedShift1 5y ago

How does that help against columns being added or removed?

willcipriano 5y ago
If they are added they are just another key in the hashmap, no harm. If they are removed, I don't have a solution for that. Typically you don't remove columns of databases like that, but adding them is common.