Database & data access consideration: one step deeper with SqlAlchemy

In a previous article "Database & data access consideration: Client-Server model or ORM approach", I was wondering about the best choice between ORM approach vs Client-Server approach.

The interest of SqlAlchemy is so appealing that I decided to explore this approach with more consideration. With SqlAlchemy, you can make really advanced thing still while having the hand over the underlaying database. With SqlAlchemy, the ORM doesn't make decision for you... it is a tool, not a wizard.
If you did not yet have a look to the "Introduction to SqlAlchemy" YouTube Video (Mike Bayer, PyCon US 2013) I strongly encourage you to do so.

Hand Coded Applications with SQLAlchemy
While looking at the "SqlAlchemy Introduction" video, YouTube proposed me to view the "Hand Coded Applications with SQLAlchemy".

Behind this "strange video name", I did discover very pertinent information about "Polymorphic association" that I'm already using in my own databases. So, this new video should also be seen!


Source: Hand Coded Applications with SQLAlchemy (YouTube)

What's an Hand Coded Application and what's not?
The hand coded approach means:

• That we makes decisions
• We implement and automate those decision with tools.
• We retain control over the architecture 

In hand coded application, we want "automation", we do not want "hiding"

• We are best off when we design and control the database schema/query as well as our own object model and how the object model does interact with the database schema.
• We still need tool to help us day-by-day in automating our development process.
• Hand Coded = explicit decision + automation with tools.
  

We lost our decisional implication and hand coded design of our application

• when we use tools that make schema design decision for us,
• when we use tools that hides the database schema and geometry
• when we use tools that give us a "pass" to comply to ACID.
  Sometime, the best solution is not to be ACID compliant.
  ACID should be the reference in any case... but we should be able to desobey (and follow our own design decision)
• when we use tools that control the architecture of our software.
  We give up the control of our architecture dans our software design to a third party software.

Hand coded software is the opposite of:

• Application written by "wizards"
• Application that heavily rely on third party applications or Plug-ins.
• Using API that makes implementation decision
  When some critical development decisions are rules by an API, your are in the same situation than a "Wizards" created APP. Trouble will comes when you will need to change the API'ed software part.

Hand coded software does not mean that:

• We do not use library
  We should use them as much as possible to simplify the work.
  Choose a stable, popular, well documented, well support library would be a good choice.
• We don't use Framework
  Framework are great if they don't make the things harder.
• We don't use defaults.
  Defaults are the results of past experiences. relying on experience is wiser when starting a new project.

Audacious Project = Hand Coded Application
All those points (coming from the SqlAlchemy video) are in mind of the Audacious Project.
This means that Audacious will be a "Hand Coded Application" and also that SqlAlchemy may be a good tool to help the project.

Polymorphic Association Pattern
A polymorphic association happens when the data of a table (or a list of object) have a one of many possible parent (record/object).

Polymorphic Association explained
In an inventory, you may have many inventory variation details for a given article.
Those details explains how many pieces has been added/removed for this given article,  when and with a reference to the data that caused the inventory variation (let's call it "origin").

When pieces are removed from inventory, it is usually to server an order... so the "inventory variation" record will be attached to a "send note" (the origin of inventory variation is a send note).

But when the supplier deliver the product, we are refilling the inventory for that same article. So, the inventory variation will be positive and "inventory variation" will be attached to a "Sypply delivery Note" (this time, origin of inventory variation is another object / record of another table).

So allow those both usecase to cohabit, we do use the Polymorphic Association. The Inventory Variation receive an additional field named Origin_Type with allows you to identify the kind of parent (say "SUP" for supplier... so a Supplier Note and "SNO" for an order's Send Note).
The Origin field will then point-out the data corresponding in the appropriate table/object list

Polymorphic Association Excerpt
Here some excerpt coming from the YouTube video. This sample is based on FinancialTransaction that  may be attached (may reference) either a BankAccount either a PortfolioAsset

Source: Hand Coded Applications with SQLAlchemy (YouTube)

Source: Hand Coded Applications with SQLAlchemy (YouTube)

In this sample, the polymorphic is taken in charge by the magic_library.... and this has implicit consequence on the design:

• It add a "magic_" table in the schema
• There is some python code in the database (the module name and the class name)
• The storage of transaction record is made into a monolothic table... as opposed to one table per class.
• The constraint to portfolio and bank_account must be handled by the application layer (database cannot handle a conditional foreign to one table or another table depending on condition) --> it is not a very good implementation option.

The SqlAlchemy Approach of Polymorphic association

•  SQLAlchemy will encourage us to specify how the tables are designed and explicitely mapped to the classes.
• SqlAlchemy will encourage us to use regular python technique to elaborate composable patterns.

This approach expresses our design fully and eliminate boilerplate at the same time.

Source: Hand Coded Applications with SQLAlchemy (YouTube)

With patterns, it is possible to use mixins and function to define common patterns.

Source: Hand Coded Applications with SQLAlchemy (YouTube)

Since "Base" class will:

• Take care to give a __tablename__ derived from the class name (CamelCase --will becode--> camel_case)
• Associate/create an unique identifier "id" column to the class (if not done).

then, we can simplify the declarative to:

Source: Hand Coded Applications with SQLAlchemy (YouTube)

HasTransaction Convention: defines a convention for polymorphic associate while still using table-per-class.

It is the base of a transaction which contains money.

Source: Hand Coded Applications with SQLAlchemy (YouTube)

We define a HasTransaction convention where:

• We use Type to create a typed object with our convention (eg:BankAccountTransaction)
  which inherit from Base and TransactionBase (so have money information)
• associate with a table name for storing the transation 
• and having a parent_id to the parent row while using ForeignKey constraint

Source: Hand Coded Applications with SQLAlchemy (YouTube)

So we resume the declarative part of the object to:

Source: Hand Coded Applications with SQLAlchemy (YouTube)

So here, wa have the following tables:

• bank_account
• portfolio_asset
• bank_account_transaction (with foreign-key to bank_account)
• portfolio_asset_transaction (with foreign-key to portfolio_asset)

Source: Hand Coded Applications with SQLAlchemy (YouTube)

 The rudimentary usage would be done like this:

Source: Hand Coded Applications with SQLAlchemy (YouTube)

Whoaw... kindly powerful. My brain is paining to understand how this work so fine.
That recipe is:

1. Very elegant... totaly pythonic
2. Normalized and Allows foreign key constraint
3. Can easily been reused for any polymorphic Association
4. Can be used with other model as well.

here a sample of query usage.

Source: Hand Coded Applications with SQLAlchemy (YouTube)

SQL Geometry inside SqlAlchemy
Now, if we want a "report of average balance per month across all accounts", we will have to use subqueries and/or window function to produce balances as sum of amounts.

If we have millions rows, we should take care about the database querying and the sql statement.
You cannot figure to load the data in memory and process it to generate the report.

Here to deal in between SqlAchemy and Sql Statement.

Start with a query that extract all the start/end dates of each month in the bank_account_transaction table

Source: Hand Coded Applications with SQLAlchemy (YouTube)

The data we get looks like this

Hand Coded Applications with SQLAlchemy (YouTube)

Article to be continued.....