[Tutor] How to write database-agnostic python code? (Is this even possible?)

[Cameron Simpson]

On 14Sep2017 09:15, Alan Gauld <alan.gauld at yahoo.co.uk> wrote:
>On 14/09/17 04:11, boB Stepp wrote:
>>> SELECT <COUNT, MAX, MIN> some, fields (never *)
>>
>> Why no "*"?  Does this open up a security vulnerability?
>
>Not so much security as resilience to change.
>If you use * and the data schema changes to include extra
>fields then your * query returns the extra fields and all
>the code using that query now has to handle those extra
>fields.

Alan's point about resilience is the core point here.

But also: efficiency. Fetch only the columns you need. Some columns may be very 
bulky (eg "BLOB"s in MySQL - arbitrary chunks of data), and some may require 
secondary accesses (i.e. the data may be variable sized and stored apart from 
the core fixed size column data). And data warehouses often empty "columnar 
storage", where each column is stored separated from theothers, supporting very 
fast scans of single columns.

So asking for more has performance implications, sometimes large.

Also, asking for exactly what you need promotes cleaner and more correct code 
(or code easier to prove correct and keep correct); you don't accidentally 
introduce dependence on some column you didn't really need; that can have flow 
on effects as surrounding code might start to take advantage of the extra 
information more than required.  Such code is harder to modify later because 
more semantics need to be preserved.

Cheers,
Cameron Simpson <cs at cskk.id.au> (formerly cs at zip.com.au)