对象关系映射

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Date, Float, Integer, String

Base = declarative_base()

class Order(Base):
    __tablename__ = 'orders'

    order_id = Column(String, primary_key=True)
    date = Column(Date)
    symbol = Column(String)
    quantity = Column(Integer)
    price = Column(Float)

    def get_cost(self):
        return self.quantity*self.price

import datetime
order = Order(order_id='A0004', date=datetime.date.today(), symbol='MSFT', quantity=-1000, price=187.54)

order.get_cost()

-187540.0

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine("sqlite:///my_database.sqlite")   # 相当于 connection
Session = sessionmaker(bind=engine) # 相当于 cursor
session = Session()

session.add(order)
session.commit()

for row in engine.execute("SELECT * FROM orders"):
    print row

(u'A0001', u'2013-12-01', u'AAPL', 1000, 203.4)
(u'A0002', u'2013-12-01', u'MSFT', 1500, 167.5)
(u'A0003', u'2013-12-02', u'GOOG', 1500, 167.5)
(u'A0004', u'2015-09-10', u'MSFT', -1000, 187.54)

for order in session.query(Order).filter(Order.symbol=="AAPL"):
    print order.order_id, order.date, order.get_cost()

A0001 2013-12-01 203400.0

order_2 = session.query(Order).filter(Order.order_id=='A0002').first()

order_2.symbol

u'MSFT'