====== Getting Started ======

===== Connection =====
<code python>
In [1]: from sqlalchemy import *
In [2]: engine = create_engine('postgres://database=k4ml&host=127.0.0.1&user=kamal&password=any')
</code>

===== Database Metadata =====
<code python>
In [3]: nodes = Table('node', engine, autoload=True)
In [4]: [c.name for c in nodes.columns]
Out[4]:
['nid',
 'type',
 'title',
 'uid',
 'status',
 'created',
 'changed',
 'comment',
 'promote',
 'moderate',
 'teaser',
 'body',
 'revisions',
 'sticky',
 'format']
</code>

===== Database Manipulation =====
<code python>
In [5]: nodes.select()
Out[5]: <sqlalchemy.sql.Select object at 0x406c876c>

In [6]: str(nodes.select())
Out[6]: 'SELECT node.nid, node.type, node.title, node.uid, node.status, node.created, node.changed, node.comment, node.promote, node.moderate, node.teaser, node.body, node.revisions, node.sticky, node.format \nFROM node'

In [7]: n1 = nodes.select().execute()

In [8]: n1.fetchone()

In [9]: row = n1.fetchone()

In [10]: row[0]
Out[10]: 20

In [11]: row[1]
Out[11]: 'story'

In [12]: row['title']
Out[12]: 'Sun Mad Hatter ..'

n [17]: n1 = nodes.select(nodes.c.nid == 123)

In [18]: str(n1)
Out[18]: 'SELECT node.nid, node.type, node.title, node.uid, node.status, node.created, node.changed, node.comment, node.promote, node.moderate, node.teaser, node.body, node.revisions, node.sticky, node.format \nFROM node \nWHERE node.nid = %(node_nid)s'

In [19]: n1 = nodes.select(nodes.c.nid == 123).execute()
</code>

===== Code - Database=====
<code python>
from sqlalchemy import *

engine = create_engine('postgres://database=clinic-models&host=127.0.0.1&user=kamal&password=any', echo=True, echo_uow=True)

customers = Table('customers', engine, autoload=True)
orders = Table('orders', engine, autoload=True)
items = Table('items', engine, autoload=True)
order_items = Table('order_items', engine, autoload=True)
</code>

===== Code - Models =====
<code python>
import database

class Customers(object):
    pass
database.assign_mapper(Customers, database.customers)

class Orders(object):
    pass
database.assign_mapper(Orders, database.orders)

class Items(object):
    pass
database.assign_mapper(Items, database.items)

class OrderItems(object):
    def _set_item_id(self, item_id):
        self._item_id = item_id
        self.calc_total_price()
    item_id = property(fset=_set_item_id)

    def _set_qty(self, qty):
        self._qty = qty
        self.calc_total_price()
    def _get_qty(self):
        return self._qty
    qty = property(_get_qty, _set_qty)

    def _get_total_price(self):
        if hasattr(self, '_total_price'):
            return self._total_price
        return None
    total_price = property(_get_total_price)

    def calc_total_price(self):
        if hasattr(self, '_qty') and isinstance(self.qty, int):
            if hasattr(self, '_item_id'):
                item_price = database.select(
                    [database.items.c.price], database.items.c.id == self._item_id
                ).execute().fetchone()[0]
                self._total_price = self._qty * item_price

database.assign_mapper(OrderItems, database.order_items, properties = {
        '_item_id': database.order_items.c.item_id,
        '_qty': database.order_items.c.qty,
        '_total_price': database.order_items.c.total_price,
    }
)
</code>