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

[Peter Otten]

boB Stepp wrote:

> On Tue, Sep 12, 2017 at 2:17 PM, Mats Wichmann <mats at wichmann.us> wrote:
>> 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!
> 
> [...]
> 
>> 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...
> 
> In the example I was alluding to, concatenating fields, surely this is
> a "standard SQL" query?  So, for instance, if I had a Sales table with
> FirstName and LastName fields and wanted to use SQL to get the full
> name from these:
> 
> 1) In Microsoft SQL Server:
> SELECT
> FirstName + ' ' + LastName
> FROM Sales
> 
> 2) MySQL:
> SELECT
> CONCAT(FirstName, ' ', LastName)
> FROM Sales;
> 
> 3)SQLite:
> SELECT
> FirstName || ' ' || LastName
> FROM Sales;
> 
> And these would be the SQL commands/statements I would have
> cursor.execute use from the sqlite3 module.  They would be different
> depending on which database product I was using.  Am I horribly
> misunderstanding something???  And if not, I have several other
> apparently fundamental SQL examples where similar situations exist
> based on the book on SQL I am only 43 pages into!

Life is hard ;)

While the specific problem can be worked around by performing the 
concatenation in Python

cs.execute("select firstname, lastname from sales;")
for firstname, lastname in iter(cursor.fetchone, None):
    print("{} {}".format(firstname, lastname))
 
at some point you will need to write database specific SQL or delegate that 
task to a library -- sqlalchemy was already mentioned. I suspect that you 
will then end up learning both the SQL dialects and the ORM API...

Personally I would start with a single database, try to achieve a clean 
structure and good unit test coverage -- and worry about generalisation 
later.