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

[boB Stepp]

On Tue, Sep 12, 2017 at 8:58 PM, boB Stepp <robertvstepp at gmail.com> 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...

I misspoke somewhat here.  I suppose there are different database
wrappers to access different databases.  But the standard python
database api should still use a cursor.execute to run SQL commands no
matter the wrapper?

> ... 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!
>
> --
> boB



-- 
boB