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

[boB Stepp]

On Wed, Sep 13, 2017 at 11:33 PM, Mark Lawrence via Tutor
 wrote:

> SQLAlchemy isn't the only Python ORM of course.  There is a useful little
> article about ORMs and their availability here
> https://www.fullstackpython.com/object-relational-mappers-orms.html.  A more
> detailed comparison is given here
> http://pythoncentral.io/sqlalchemy-vs-orms/ which refers to storm, which is
> only mentioned in passing in the first link.  I've successfully used peewee,
> I can't really comment on the others.

Thanks for these links, Mark.  I had not come across Full Stack Python
previously.  It appears to be a goldmine of useful information!

I think I will put off doing ORM studies until I get into something
more substantial than I am currently working with.  Besides, after
reading the Joel Spolsky article on leaky data abstractions, it
appears to be a wise move to get proficient in SQL first before going
on to ORMs.


-- 
boB
___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

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

[boB Stepp]

On Thu, Sep 14, 2017 at 2:06 AM, Peter Otten <__pete...@web.de> wrote:
> That's the "Law of leaky abstractions",
> https://www.joelonsoftware.com/2002/11/11/the-law-of-leaky-abstractions/

Thanks for the link to this article.  I really enjoyed reading it.  In
fact, so far every article I have read by Joel Spolsky has been both
an enjoyable and educational read!

Near the very end of the article he says, "... And when you need to
hire a programmer to do mostly VB programming, it’s not good enough to
hire a VB programmer, because they will get completely stuck in tar
every time the VB abstraction leaks..."  That has me wondering:  Where
does the Python abstraction leak???

-- 
boB
___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

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

[Cameron Simpson]

On 14Sep2017 09:15, Alan Gauld  wrote:

On 14/09/17 04:11, boB Stepp wrote:

SELECT  some, fields (never *)


Why no "*"?  Does this open up a security vulnerability?


Not so much security as resilience to change.
If you use * and the data schema changes to include extra
fields then your * query returns the extra fields and all
the code using that query now has to handle those extra
fields.


Alan's point about resilience is the core point here.

But also: efficiency. Fetch only the columns you need. Some columns may be very 
bulky (eg "BLOB"s in MySQL - arbitrary chunks of data), and some may require 
secondary accesses (i.e. the data may be variable sized and stored apart from 
the core fixed size column data). And data warehouses often empty "columnar 
storage", where each column is stored separated from theothers, supporting very 
fast scans of single columns.


So asking for more has performance implications, sometimes large.

Also, asking for exactly what you need promotes cleaner and more correct code 
(or code easier to prove correct and keep correct); you don't accidentally 
introduce dependence on some column you didn't really need; that can have flow 
on effects as surrounding code might start to take advantage of the extra 
information more than required.  Such code is harder to modify later because 
more semantics need to be preserved.


Cheers,
Cameron Simpson  (formerly c...@zip.com.au)
___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

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

[Alan Gauld via Tutor]

On 14/09/17 04:40, boB Stepp wrote:

>> mapping your logic layer object models to underlying
>> data tables.
> 
> My initial thoughts here are that typically a particular class would
> map to a particular table and that each object instance would
> correspond to a row in said table.  Is this typically how this is
> done?

Simplistically yes, but it gets more tricky with inheritance.
Does the superclass data live in the same table as the subclass
data or is there a separate table for the superclass?

The first solution implies a lot of duplication of fields
across tables and can also lead to problems if you are
saving changes to a list of superclass references using
class methods (but hopefully you would be using polymorphic
methods which will solve the problem for you!)

More seriously there is a problem when you want a query
that operates at the superclass level - all red cars for example.
You now have to query all tables representing subclasses of car
- and modify the query code every time you create a new subclass...

The second solution requires links between child and parent
tables and the queries will need to navigate this link for
any fields used within the query that exist in the superclass.
This can be a significant overhead, especially on deep
inheritance trees.

There is no simple solution and it depends on the nature of your
application which evil is worst. It's a fundamental disjoint
between OOP and the relational model. Many RDBMS now have
mechanisms to assist in this by, for example creating views
that join all superclass tables together into a virtual
single table.

I don't think SQLite has anything automatic yet, you need
to decide your solution yourself - which for a small dataset
might even involve instantiating all objects and doing the
query on the in-memory objects!. (itertools is your friend!)

-- 
Alan G
Author of the Learn to Program web site
http://www.alan-g.me.uk/
http://www.amazon.com/author/alan_gauld
Follow my photo-blog on Flickr at:
http://www.flickr.com/photos/alangauldphotos


___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

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

[Alan Gauld via Tutor]

On 14/09/17 04:11, boB Stepp wrote:

>> SELECT  some, fields (never *)
> 
> Why no "*"?  Does this open up a security vulnerability?

Not so much security as resilience to change.
If you use * and the data schema changes to include extra
fields then your * query returns the extra fields and all
the code using that query now has to handle those extra
fields.

Typically you have an API call that looks like:

getOpenOrders(custID)
 -> tuple(orderID, Order date, productID, Value)

But suddenly your code has to handle

getOpenOrders(custID)
 -> tuple(orderID, Order date, SalesRep, productID, Priority, Value)

The fields you want are now at different indexes in the
tuple, all the code that extracts those fields has to change.

Whereas if your qurery specifies the fields it wants
then the data schema can change and it doesn't affect
your API return values.

> I suppose there will be a similar parallel for writing data back into
> the database?

Yes, the INSERT command has the general shape

INSERT INTO table (col_name, col_name,...)
VALUES (val, val, ...)
WHERE filter_expression

and UPDATE has

UPDATE table
SET
col_name = val1
col_name = val2
...
WHERE filter_expression

and delete is just
DELETE FROM table
WHERE filter_expression

The critical thing to remember is that these all
work on ALL ROWS that match the filter. Its easy to
modify more than you intend with a lazily written
expression! So in practice, for a typical OO data
layer I usually have a WHERE clause like

...
WHERE primary_key = object.ID

-- 
Alan G
Author of the Learn to Program web site
http://www.alan-g.me.uk/
http://www.amazon.com/author/alan_gauld
Follow my photo-blog on Flickr at:
http://www.flickr.com/photos/alangauldphotos


___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

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

[Mark Lawrence via Tutor]

On 14/09/2017 00:51, Cameron Simpson wrote:


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  (formerly c...@zip.com.au)


SQLAlchemy isn't the only Python ORM of course.  There is a useful 
little article about ORMs and their availability here 
https://www.fullstackpython.com/object-relational-mappers-orms.html.  A 
more detailed comparison is given here 
http://pythoncentral.io/sqlalchemy-vs-orms/ which refers to storm, which 
is only mentioned in passing in the first link.  I've successfully used 
peewee, I can't really comment on the others.


--
My fellow Pythonistas, ask not what our language can do for you, ask
what you can do for our language.

Mark Lawrence

---
This email has been checked for viruses by AVG.
http://www.avg.com


___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

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

[Peter Otten]

boB Stepp wrote:

>> 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...
> 
> I maybe did not realize the full import of Albert-Jan's suggestion
> then.  Will sqlalchemy (Or a similar product.) totally abstract away
> these annoying SQL variations amongst db vendors?

While I have absolutley no experience with sqlalchemy I would be surprised 
if it didn't abstract away the common variations like concatenation.
I would be even more surprised if there weren't any operations, vendor-
specific or standardised, that can be performed with SQL, but not highlevel  
sqlalchemy.

That's the "Law of leaky abstractions",
https://www.joelonsoftware.com/2002/11/11/the-law-of-leaky-abstractions/


___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

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

[Cameron Simpson]

On 12Sep2017 14: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 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  (formerly c...@zip.com.au)
___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

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

[boB Stepp]

I forgot one other thing you mentioned that I had a question about.

On Wed, Sep 13, 2017 at 2:41 AM, Alan Gauld via Tutor  wrote:

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

My initial thoughts here are that typically a particular class would
map to a particular table and that each object instance would
correspond to a row in said table.  Is this typically how this is
done?


-- 
boB
___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

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

[boB Stepp]

On Wed, Sep 13, 2017 at 2:55 AM, Peter Otten <__pete...@web.de> wrote:
> boB Stepp wrote:

>>
>> 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 ;)

And I was so badly hoping to achieve SQL nirvana!  ~(:>))

> 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))

Yeah, this just reinforces Alan's points.

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

I maybe did not realize the full import of Albert-Jan's suggestion
then.  Will sqlalchemy (Or a similar product.) totally abstract away
these annoying SQL variations amongst db vendors?

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

I've decided I'm going even simpler than the chess ratings project.
I've been lately taking lots of blood pressure readings lately
(Stressed out from all my efforts at Python, y'know!).  I think the
data will fit nicely in A SINGLE TABLE -- date, time of day, BP
reading, and a comments field.  I will apply TDD, OOP, sqlite3, etc.
to what I hope will be a simple but meaningful project.  The "business
logic", as Alan puts it, will be for a given date range report the
mean BP reading.  If I get really enthusiastic, I might add some trend
analysis features, ... -- see, already feature creep is sneaking in!

My real hope from doing this simplified project is that the amount of
code might be small enough that I can post it here in its entirety for
critiquing.  Hopefully this will get most of my GCEs (Gross Conceptual
Errors) out of the way and I can return to the chess ratings project,
my wife's projects, etc., and have a better hope of success.



-- 
boB
___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

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

[boB Stepp]

On Wed, Sep 13, 2017 at 2:41 AM, Alan Gauld via Tutor  wrote:

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

[...]

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

This sounds a lot like the approach I've evolved into taking with the
proprietary scripting language I use at work:  I only do what I
absolutely have to do in Pinnacle HotScripting to get the needed data
to my Python program, let Python do all necessary processing, and
finally have Python generate the needed Pinnacle HotScripting commands
that need to be run to affect the planning environment.

[...]

> Basically
>
> SELECT  some, fields (never *)

Why no "*"?  Does this open up a security vulnerability?

> FROM some tables
> WHERE some conditions
> 
> ORDER BY a key
>
> Should be much SQL as you need in the data access layer.

This simplifies things greatly!  Does this mean I can stop reading up
on SQL?  ~(:>))

I suppose there will be a similar parallel for writing data back into
the database?

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

I will try to keep this in the back of my mind ...

Thanks, Alan.  This helps a lot!

-- 
boB
___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

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

[Maxime S]

2017-09-13 3:58 GMT+02:00 boB Stepp :

> On Tue, Sep 12, 2017 at 2:17 PM, Mats Wichmann  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:
>

Concatenation is indeed part of the ISO standard (using the || operator).

Unfortunately no DB respect the standard 100%.

There is a nice comparaison of the different behaviours here:
http://troels.arvin.dk/db/rdbms/

It is very hard to create an application that is truly DB-independent. Most
applications just pick one and then have to stick with it ever after.



Garanti
sans virus. www.avast.com

<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

Re: [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  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.

___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

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

[Alan Gauld via Tutor]

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  some, fields (never *)
FROM some tables
WHERE some conditions

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

___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

Re: [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  wrote:
> On Tue, Sep 12, 2017 at 2:17 PM, Mats Wichmann  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
___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

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

[boB Stepp]

On Tue, Sep 12, 2017 at 2:17 PM, Mats Wichmann  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!

-- 
boB
___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

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

[Albert-Jan Roskam]

(sorry for top posting)

There are various SQL abstraction layers for this. I have only used Sqlalchemy. 
This package has two APIs: query and ORM. You might want to look at the query 
API. This is closer to SQL than ORM. You can use straight(obj) to inspect the 
SQL that's emitted

From: Tutor  on behalf of boB 
Stepp 
Sent: Tuesday, September 12, 2017 7:05:39 PM
To: tutor
Subject: [Tutor] How to write database-agnostic python code? (Is this even 
possible?)

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?

--
boB
___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

Re: [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...

___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor