python与数据库sqlalchemy框架简述

最近正式转入以Python为主的开发环境，我有罪，罪在浪费时间。折腾了三年的C++开发经历，折腾了一年的APP开发。撇开APP的经验（或多或少还是有些意义），C++的三年开发经历实在有些惋惜，因为C++已经非常非常接近没落的场景了。而我还在抱残守缺。于企业而言，于团队而言，效率就是生命线。一个产品如何能够快速实现并推向市场进行验证才是王道。诚如所有C++开发者所面临的，无数次造轮子，无数次写无用代码。

回到正题，通常的业务可以分http（restful）、web、socket这几种类型（也可能是几种的综合体）。

我这里试图构建一套小规模的业务数据模型。诸如Python的django或PHP的一些web框架，均带orm框架。如果Python初学者不喜欢或者是非web类型项目，可以考虑sqlalchemy。

sqlalchemy的github地址猛击

"""basic_association.py
illustrate a many-to-many relationship between an
"Order" and a collection of "Item" objects, associating a purchase price
with each via an association object called "OrderItem"
The association object pattern is a form of many-to-many which
associates additional data with each association between parent/child.
The example illustrates an "order", referencing a collection
of "items", with a particular price paid associated with each "item".
"""

from datetime import datetime

from sqlalchemy import (create_engine, MetaData, Table, Column, Integer,
    String, DateTime, Float, ForeignKey, and_)
from sqlalchemy.orm import mapper, relationship, Session
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Order(Base):
    __tablename__ = 'order'

    order_id = Column(Integer, primary_key=True)
    customer_name = Column(String(30), nullable=False)
    order_date = Column(DateTime, nullable=False, default=datetime.now())
    order_items = relationship("OrderItem", cascade="all, delete-orphan",
                            backref='order')

    def __init__(self, customer_name):
        self.customer_name = customer_name

class Item(Base):
    __tablename__ = 'item'
    item_id = Column(Integer, primary_key=True)
    description = Column(String(30), nullable=False)
    price = Column(Float, nullable=False)

    def __init__(self, description, price):
        self.description = description
        self.price = price

    def __repr__(self):
        return 'Item(%r, %r)' % (
                    self.description, self.price
                )

class OrderItem(Base):
    __tablename__ = 'orderitem'
    order_id = Column(Integer, ForeignKey('order.order_id'), primary_key=True)
    item_id = Column(Integer, ForeignKey('item.item_id'), primary_key=True)
    price = Column(Float, nullable=False)

    def __init__(self, item, price=None):
        self.item = item
        self.price = price or item.price
    item = relationship(Item, lazy='joined')

if __name__ == '__main__':
    engine = create_engine('sqlite://')
    Base.metadata.create_all(engine)

    session = Session(engine)

    # create catalog
    tshirt, mug, hat, crowbar = (
        Item('SA T-Shirt', 10.99),
        Item('SA Mug', 6.50),
        Item('SA Hat', 8.99),
        Item('MySQL Crowbar', 16.99)
    )
    session.add_all([tshirt, mug, hat, crowbar])
    session.commit()

    # create an order
    order = Order('john smith')

    # add three OrderItem associations to the Order and save
    order.order_items.append(OrderItem(mug))
    order.order_items.append(OrderItem(crowbar, 10.99))
    order.order_items.append(OrderItem(hat))
    session.add(order)
    session.commit()

    # query the order, print items
    order = session.query(Order).filter_by(customer_name='john smith').one()
    print([(order_item.item.description, order_item.price)
           for order_item in order.order_items])

    # print customers who bought 'MySQL Crowbar' on sale
    q = session.query(Order).join('order_items', 'item')
    q = q.filter(and_(Item.description == 'MySQL Crowbar',
                      Item.price > OrderItem.price))

    print([order.customer_name for order in q])

待续