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

[Cameron Simpson]

On 12Sep2017 14:05, boB Stepp <robertvstepp at gmail.com> wrote:
>As I continue to read about SQL, one thing jumps out:  There are many
>differences between how SQL statements are implemented among the
>different database products.  Even for relatively simple,
>straightforward things like field concatenation.  One DB might use
>"||" as the operator.  Another uses "+".  Yet another only uses a
>"CONCAT" function.  This is crazy!
>
>It almost seems like I would need to write a DB-translator class that
>takes a SQL statement (In some DB's version) and the target DB as
>inputs, and then translates the statement into that DB's particular
>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, insofar as possible, so any migrations to a new DB type is as
>easy as possible?

I have two approaches myself.

First, all DB drivers present the DBI interface from PEP 249, so once connected 
you have a standard suite of methods. Most databases speak a superset of SQL92, 
so provided your SQL is not esoteric you can just get on with things. Um, 
except for parameter syntax ("$9", "?", ":foo_9")? You can work around that.

Secondly, there's SQLAlchemy. It knows the dialects and asks you to write 
"native" looking python syntax for selects etc. So stuff like:

  db_conn.select(t.col1 == 9 and t.col2 == 10)

where "t" is a "table" object it has handed you. I believe these are just 
special objects with attributes for columns and the right __eq__ etc dunder 
methods to compute the correct SQL syntax. No escaping or param substitution in 
your own code. It also has an ORM, which I've not used.

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