sqlChemy相关知识点总结（增删改查）

增：

    def insert(self, data):
        result = {'errorcode': -1}
        try:
            //如果传递过来的参数中没有createTime这个key值，但是调用insert方法的类（表对象）有createTime这个字段，那么为这个字段设置值
            if data.has_key("createTime") is False and hasattr(self, "createTime"):
                setattr(self, "createTime", str(datetime.datetime.utcnow())[:19])
                setattr(self, "updateTime", str(datetime.datetime.utcnow())[:19])
            //遍历传递过来的数据
            for key, value in data.items():
                //如果表对象中有这个key相同的属性
                if hasattr(self, key):
                    //设置值
                    setattr(self, key, value)
            self.session.add(self)
            self.session.flush()
            result = self.to_dict(self.__dict__)
            self.session.commit()
            result["errorcode"] = 0

        except:
            self.session.rollback()
            logging.error(traceback.format_exc())

        finally:
            self.session.close()
            return result

更新和新增：

    try:
        if values["action"] == "sync":
            # 删除该字段中的值
            values.pop("action")

            params = {"contentId": values["contentId"]}
            query_result = Series().all(**params)

            if query_result:
                # 有则更新
                result = update(values)
            else:
                # 无则新增
                result = insert(values)
        elif values["action"] == "delete":
            # TODO 根据contentId删除剧集，同时删除剧集的关联关系
            pass

查询：

//查询的条件
params = {"authCode": values["authCode"]}
query_result = AuthInfo().all(**params)


 def all(self, **kwargs):
        result = []
        try:
            instance = self.session.query(self.model)
            for row in self._apply_kwargs(instance, **kwargs).all():
                result.append(self.to_dict(row.__dict__))

        except:
            logging.error(traceback.format_exc())

        finally:
            self.session.close()
            return result

联合唯一索引的语法：

from sqlalchemy import Index
from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import  UniqueConstraint
from dao import agent_base
from dao.base import BaseWrapper


class Poster(agent_base, BaseWrapper):
    """产品信息表，记录产品信息。"""
    __tablename__ = 't_series_poster'


    id = Column(Integer, primary_key=True)      # 主键，自增长
    seriesId = Column("series_id", Integer)              # 剧集id
    posterId = Column("poster_id", Integer) # 海报id

    #剧集id和海报id联合唯一索引
    Index("idx_series_id_poster_id",seriesId,posterId,unique=True)
    UniqueConstraint(seriesId,posterId)
    def __init__(self):
        BaseWrapper.__init__(self, self.__class__)