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

[Alan Gauld]

On 12/09/17 20:05, boB Stepp wrote:
> As I continue to read about SQL, one thing jumps out:  There are many
> differences between how SQL statements are implemented 

> usage of SQL.  So how does one write one's python program to be
> DB-agnostic?  And if this is impossible, then what is the best way to
> structure the overall program to isolate this SQL-specific stuff by
> itself,

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.

Now looking at that architecture it should be apparent that
it implies certain things about the kind of SQL you should
be executing in the data layer. It should not be UI/presentation
focused - thats the GUI layer's job. So no formatting etc.
Also it should not be doing any fancy calculations - that's
the logic layer's job. It should just be fetching raw data.

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. (It is entirely possible to build database
apps with a thin GUI atop the DB and thats what Oracle
et al would like, but you then forget about database
portability, you will be using lots of proprietary
database vendor technology. Effectively you put both
the logic and data access into the database server)

Most of the non-standard SQL things are there to enable
you to write batch reports that can be sent direct to
a printer/text file without any other coding. If you
are using a   language like Python you can do much nicer
formatting there and perform much more sophisticated
processing, so you don't need those features of SQL.

Basically

SELECT <COUNT, MAX, MIN> some, fields (never *)
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.

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 %.

So you may want to write your data layer functions with
the insertion character parameterized so you only need
to change it in one place. The other area that differs
is login, but thees not much can be done with that and
you should only need to do it once.

HTH,

Alan G