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

[Mats Wichmann]

On 09/12/2017 01:05 PM, boB Stepp 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?
> 


Well, sort of.  dbapi is designed to make access to databases agnostic
to the underlying db:

https://www.python.org/dev/peps/pep-0249/

(and bunches of tutorials on the internet)

But dealing with "local extension to SQL" is a different problem.  SQL
is actually a standard and should work the same everywhere, but then
people decide they need to improve it.  Being agnostic means avoiding
mysql-specific syntax, postgres-specific syntax, etc.  Which means you
need to know what is specific in the first place...