[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 <__peter__ at 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