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

[boB Stepp]

On Wed, Sep 13, 2017 at 2:41 AM, Alan Gauld via Tutor <tutor at python.org> wrote:

> The classic approach is to have 3 tiers in the architecture.
> 1) UI layer - usually a GUI toolkit - the VC part of MVC
> 2) Business logic - its the M bit of MVC... and where most OOP happens
> 3) data access - the ugly underbelly where theory hits physical storage
>
> By sticking to those roles you can build all the data
> access (SQL) stuff in a layer of its own that can be swapped
> out if needed. You simply(?!) create an API from the logic
> layer to fetch whatever data is needed.

[...]

> If you limit your SQL to queries fetching raw fields you
> should find you are working with the portable subset of
> standard SQL most of the time. Basically you are just
> mapping your logic layer object models to underlying
> data tables. ...

This sounds a lot like the approach I've evolved into taking with the
proprietary scripting language I use at work:  I only do what I
absolutely have to do in Pinnacle HotScripting to get the needed data
to my Python program, let Python do all necessary processing, and
finally have Python generate the needed Pinnacle HotScripting commands
that need to be run to affect the planning environment.

[...]

> Basically
>
> SELECT <COUNT, MAX, MIN> some, fields (never *)

Why no "*"?  Does this open up a security vulnerability?

> FROM some tables
> WHERE some conditions
> <GROUP BY some field>
> ORDER BY a key
>
> Should be much SQL as you need in the data access layer.

This simplifies things greatly!  Does this mean I can stop reading up
on SQL?  ~(:>))

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

> The one thing that bugs me with the dbapi is that it does
> not hide the data insertion character so in some databases
> you use ? and in others its %.

I will try to keep this in the back of my mind ...

Thanks, Alan.  This helps a lot!

-- 
boB