Python - SqlAlchemy - Alembic : Installing test environment to learn Alembic doc under WINDOWS

Introduction
Alembic does have a comprehensive userguide available here.
However, it requires to install python on our computer and SqlAlchemy.
In this company, as in most company in the world, we are using Windows... so, let's have a look to the installation process.

Installing Python 3 for Windows
That operation is quite easy, just browse the site www.python.org/downloads/windows/ a download the latest version of Python.
At the time of this article, we did installed Python 3.4.3 to have full unicode support.

Installing PIP - Python Package Installer for Windows
As far I know, this utilitity is now distributed along with Python3.x for Windows.
In my recollection, I didn't install it.
The only think to know about it is its executable is not named "pip.exe" but "pip3.exe" under Python3 !!!

So any package installation under Python3 is done with "pip3 install ..."

Installing PSYCOPG2 under Windows
PSYCOPG is the most popular Python DB API 2.0 for Python.
To install it with PIP tool, you need to have the VC compiler 10 on your computer.
That's weird! Windows is not distributed with a native compiler... and I'm using VC compiler V9 for my HarbourProject developement. So I can't rely on PIP!

The other solution is to use a "PsycoPg2  Windows Installer" which contains the needed binaries.
If you are willing to do so, you must install the PSYCOPG2. Just choose the version according to python language (I'm using Pyhton 3.4)

• initd.org/psycopg/download/

It even exists a Windows Installer for Window... you will find them on win-psycopg
The drawback of Windows Installer approach is that we can't install the PSYCOPG2 inside a Python virtual environment (windows installer doesn't take care of it).
So we have to install it on at the system level (said "site-package") and then before we do initiate a virtual environment.

Installing Python VirtualEnv under Windows
Presenting Virtual Env
VirtualEnv is a tool that you must absolutely know and use as soon as possible.
This tool will become your best friend and save you lot of worries.

Virtual Environment allows you to create isolated python environment (named "virtual environnement" or "VirtualEnv" for short) and install every package you want into this isolated environment without putting the mess on your regular system or other virtual environment.

You can create as many virtual environement as you which... they are stored into sub-folder on the file system.
To use a "virtual environment", you navigate into the target sub-folder and activate the environment by calling an activation script.

If you ruine something in your Virtual Environement, you just get out of it (deactivate it), drop it (so drop a sub-folder), and create a new one where you can try to catch what goes wrong with the installed/upgraded packages. But your system is safe... NO MESS :-)
Everybody knows what the hell a wrong version of library can do in your software... and how it could be difficult to revert the installation process.

Installing VirtualEnv for Windows
Just type the following  command under the command prompt

pip3 install virtualenv

and wait the installation process to get completed.

Creating our sqlachemy-test virtual environment
On my Windows computer, I did created the c:\python folder to store all my python development projects.
I will create & activate the "sqlalchemy-test" virtual environment to test Alembic database upgrade without putting the mess on the computer.

By creating the "sqlalchemy-test" virtual environment, we will create a sqlaclchemy-test subfolder.

cd c:\python
virtualenv --system-site-packages sqlalchemy-test

The command line includes the parameter system-site-packages which is very important in this case.
This would make the psycopg2 available in the virtual environment (otherwise SqlAlchemy will crash because it would not be possible to import the psycopg2 package)

the "sqlalchemy-test" virtual environment is contained within the "sqlalchemy-subfolder"

Activating our sqlalchemy-test virtual environment
Just go inside the sqlalchemy-test subfolder and call the activation script/batch.

c:\>cd c:\Python\sqlalchemy-test

c:\Python\sqlalchemy-test>.\Scripts\activate
(sqlalchemy-test) c:\Python\sqlalchemy-test>

Note: the layout of the command prompt did also changed! it now includes the virtual environement name inside paranthesis!

Need to deactivate the virtual environment?
Just use the .\Scripts\deactivate on a command line instead of the .\Scripts\activate  ;-)

Installing SQLAlchemy
SqlAlchemy is the prerequisite for Alembic... so we will install it.

Since we did created and activated the sqlalchemy-test virtual environment we don't have to take care about overwriting a system package.
We don't have to be afraid to put the mess... we did created and activated an isolated environment.

pip3 install sqlalchemy

Now, you can check the version with the following code that you can run with Python

>>> import sqlalchemy
>>> sqlalchemy.__version__
'1.0.8'

Installing Alembic
Now, we will install Alembic with the following pip command.

pip3 install alembic

Checking  the installation is as simple as ...

(sqlalchemy-test) c:\Python\sqlalchemy-test>python
Python 3.4.3 (v3.4.3:9b73f1c3e601, Feb 24 2015, 22:43:06) [MSC v.1600 32 bit
tel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import alembic
>>> alembic.__version__
'0.8.2'

What to do now?
Just follow the "Alembic documentation" and learn how to use it.

Ressource
SqlAlchemy

• www.sqlalchemy.org

Alembic

• Alembic documentation (on readthedocs.org)

VirtualEnvWrapper-Powershell
It also exists advanced VirtualEnv tools (VirtualEnvWrapper-powershell) that may help you to manage higher level operation with VirtualEnv (like copying, switching, checking, etc) from Windows PowerShell.
If you are interested, please read the following article:

• How To Install Python, pip, and virtualenv on Windows with PowerShell

Database Upgrade with SqlAlchemy Migrate or Alembic

In a previous "Database Upgrade with SqlAlchemy Migrate" I was more in favour of SqlAlchemy Migrate because you strictly control the content of the upgrade.

So, today, I did start to look for the SqlAlchemy-Migrate documentation and realize that this project development is now stopped.
By browsing the Net for "Migrate under Python 3" (which doesn't exist so forth), I did find the article "Flask-Migrate: Alembic database migration wrapper for Flask" from Flask Mega Tutorial by Miguel Grinberg.

There I learned that Alembic is still actively maintained and documented by Mike Bayer (the author of SqlAlchemy). No body want to start a development upon a deprecated tools... so I have no more choice than selecting Alembic.

• Alembic is available here on BitBucket
• Documentation of Alembic is available on readthedocs.org

Finally, while reading the Miguel's article, I did learn that we should review the migration script because Alembic doesn't take care about information like Indexes and stuff like that.

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

Database Upgrade with SqlAlchemy Migrate

The last article did introduce SqlAlchemy as a potential tool for developing Audacious.
I'm still looking at Sql Alchemy YouTube video... and I must admit that it looks a great tool.

In the meanwhile, I was also concerned by the PostgresSql database setup and upgrade with SqlAlchemy.
A software developpement live cycle imply regular database update/upgrade in both development and production environment where schema (and data) could be upgraded to the latest version.

Home Made Software
Until know, I was using an Home Made tool that query the database to extract the current schema version number (something written by the home made Upgrade software) and then execute all the update statement over that version. Each update executed one after the other.

This is simple and efficient... you only update your database with this tools (even in developement environnement) and you can be ensured that changes would also been applied smoothly into the production database.

The update files contains a series of SQL statements that are all identified by a unique and increasing number.
Example of db-update.dat
[001.001]
type=sql
descr=first sql to create the first table
content=
...sql..statement...here...

[001.002]
type=sql
descr=add user table
content=
....other...sql...statement....

Home Made Software: the limitations
This approach rocks and is reliable. However, it is tuned for only one target database server (eg: either PostgreSql, either MsSqlServer, either ...).
It is still possible to maintain one file per kind of database (or limit ourself to only one database kind).

However, one of the Advandages of SqlAlchemy is to easlisy switch from one database to the other.

So, the best would be to consider database upgrading with SqlAlchemy himself... the response is available in the project Alchemy-migrate

Alchemy Migrate

Alchemy-migrate was initialy released on Google Code and now available on GitHub .
This tool and project is fully documented! :-)

See the sqlalchemy-migrate-doc-0.7.2.pdf document.This document properly explain how SqlAlchemy-migrate works and how to use it... the principles are similars to the Home Made approach and allows upgrade/downgrade the database with SqlAlchemy code or with SQL Statements.
That's pretty nice... maybe a bit cumbersome to many newcomer but definitively the path to follow when we need to control and tame the upgrade process of the database.

The online documentation is also available here on readthedocs.org

Alternative: Alembic
I did also read some thread about Alembic.
This manage revision of schema as revision of source file. It then store the differences between revisions to be reapply them on other database (eg: your production database).
With this approach the user modifies its database with tools... and the Alembic software tries to figure out the changes in the schema.Those changes are collected and registered to be re-applied later on the production database.
If this approach is a good idea, it don't really like it because it do the job "at best" with the changes you applied in your database.
If you accidently drop an unrelated table or rename a field (or table) during your test/development phase, this will also been applied to the production database.
That accidental operation not identified during developement may kill your production setup (because of the number of users using it).

So, I do prefer write the changes up-front. When I do this, it is clear in my mind that changes will be applied to development environnement but also in production environment.
So, even if the operation is a bit more cumbersome, you always think twice before doing it. And thinking twice is better when creating softwares that manage "money".

My preference will go to something like SqlAlchemy-migrate.

Database documentation consideration

There are many tools and professional tools to document the database.
But I would like the documentation to be as accessible and simple as possible... so avoid the usage of tool is best to offer an easy access to the information.

Regarding database documentation, here some nice recommendation collected on the Net

For my money, some good human-readable documentation (perhaps supplemented with diagrams of smaller portions of the system) will give you the most mileage. This will include, for each table:

* Descriptions of what the table means and how it's functionally used (in the UI, etc.)
* Descriptions of what each attribute means, if it isn't obvious
* Explanations of the relationships (foreign keys) from this table to others, and vice-versa
* Explanations of additional constraints and / or triggers
* Additional explanation of major views & procs that touch the table, if they're not well documented already

With all of the above, don't document for the sake of documenting - documentation that restates the obvious just gets in people's way. Instead, focus on the stuff that confused you at first, and spend a few minutes writing really clear, concise explanations. That'll help you think it through, and it'll massively help other developers who run into these tables for the first time.

Tooling or not tooling
One of the most recommended approach (after use of Tools) is to use the database feature allowing to include comment within the database schema.
If this is efficient and probably one of my favourite, this approach requires the user to know this feature to access the information.
The usage of tools also requires the user to have this tools prior to access the information.

So usage of tools is perhaps not the best approach for the audacious project which have to be open-source and KISS (keep is simple stupid) even for the technical documentation

Word processing document
Another approach than I used to document project is the usage of a word processing document.
I usually reverse engineering databases and maintain a Word documentation with database structure and all the useful information  about it.
The problem is that "Word" is a non-open-source tools! If I do use Libre-Office/Open-Office, the Windows user would have trouble to open-it.
An option would be to publish a PDF version on regular base... but that's not very efficient, and still include binary file (Word or Libre-Office) within the sources of Audacious project.

A Simple text base file
That would be definitively a good option. Not specific software needed, easily available, can be included within the source code.
As issue we have:

• No document indexes
• No images inclusion (sometime quite useful).

A Wiki?
A wiki is not my prefered approach. It requires to be stored somewhere on Internet and that someone pay for it. No money = No service = No wiki = No documentation.
If the documentation could be organized and modified more easily (and more nicely),  it would also been more difficult to consolidate the information to create a PDF/Word like document.

On the other hand, the "Audacious User Guide" should also been available... a Wiki is the best tool to build and organize such documentation/information.
So why not integrate developer (and database) documentation within the same wiki?

Any suggestion?
Any suggestion are welcome.

Database & data access consideration: Client-Server model or ORM approach

The back-end database choice is clear: that will be PostgreSql.
But a question remain... how will we access the data from the application?

Client-Server Model
One of the option is to use a database access library like PsycoPG2 to access the database.
By doing so, we are going to use a Client-Server model. PsycoPG2 does fully implement the Python DB API 2.0 specification.
The advantage is the simplicity of the approah, fews layers between the code and the database means that the code thighly control what's happening within the database. There is a better control on what's happen in the code.

Want to know a bit more about PsycoPG2:

• psycopg (initd.org, english)
• psycopg2 tutorial (postgresql.org, english)

Object Relational Mapping
On the other side, using an ORM approche allows you to concentrate efforts on the Business logic without worry (or lesser) about how to access/extract the data from the database.
Don't need to write your own in-memory data storage layer, lists, sorting, etc... its already done by the ORM.

Source: sqlalchemy.org

One very popular an robust tools in Python is SQLAlchemy which also provide ORM support.
I don't know if this would work like a charm on Windows or Raspberry-Pi.
Any options simplifying the development tasks should always be taken in consideration.

Source: sqlalchemy.org

You can find many information in the tutorial section of SqlAlchemy.org

You can also have a look to the following vidéo

Python SQLAlchemy Tutorial - on the Python Central website

Author: Xiaonuo Gantan
A series of beginner-focused SQLAlchemy tutorials covering a wide range of basic topics. While a lot of the information here is derived from the main documentation, the pace is slower and there are also details culled from other sources, including performance tips, comparison to other ORMs, and design philosophies. A very good effort by author Xiaonuo Gantan.

• Index
• Introductory Tutorial
• How to Install SQLAlchemy
• Comparison to other ORMs
• Overview of Expression Language and ORM Queries
• Commonly Asked Questions
• ORM Examples
• Association Tables