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

[Alan Gauld]

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.

Typically you have an API call that looks like:

getOpenOrders(custID)
 -> tuple(orderID, Order date, productID, Value)

But suddenly your code has to handle

getOpenOrders(custID)
 -> tuple(orderID, Order date, SalesRep, productID, Priority, Value)

The fields you want are now at different indexes in the
tuple, all the code that extracts those fields has to change.

Whereas if your qurery specifies the fields it wants
then the data schema can change and it doesn't affect
your API return values.

> I suppose there will be a similar parallel for writing data back into
> the database?

Yes, the INSERT command has the general shape

INSERT INTO table (col_name, col_name,...)
VALUES (val, val, ...)
WHERE filter_expression

and UPDATE has

UPDATE table
SET
col_name = val1
col_name = val2
...
WHERE filter_expression

and delete is just
DELETE FROM table
WHERE filter_expression

The critical thing to remember is that these all
work on ALL ROWS that match the filter. Its easy to
modify more than you intend with a lazily written
expression! So in practice, for a typical OO data
layer I usually have a WHERE clause like

...
WHERE primary_key = object.ID

-- 
Alan G
Author of the Learn to Program web site
http://www.alan-g.me.uk/
http://www.amazon.com/author/alan_gauld
Follow my photo-blog on Flickr at:
http://www.flickr.com/photos/alangauldphotos