def edit_user_info(self, user_name=None, password=None):
     """编辑用户信息"""
     if user_name:
         self.user_name = user_name
     if password:
         self.password = password
     return Dao.save_all()

 def add(d):
     del d['id']
     sql = "INSERT INTO assignments (%s) VALUES (%s);" % (
         ','.join(d.keys()), '?' + ',?' * (len(d) - 1)
     )
     vals = list(d.values())
     return Dao.execute(sql, vals)

 def db_connect(self):
     """返回一个数据库连接，跟上面的session不一样，想要裸写sql就调用这个。
     execute("把sql语句写进来就好了")，返回的结果需要注意，是ResultProxy。了解详细见下面连接
     http://docs.sqlalchemy.org/en/rel_1_0/core/connections.html?highlight=resultproxy#sqlalchemy.engine.ResultProxy。
     但是可以调用first(), all()等，差不多了一样用。只是返回的类似dict或mapping，先dict(res.items())比较好。
     """
     return Dao.db_connect()

 def login(username, password):
     sql = "SELECT * FROM users WHERE username=?;"
     vals = (username, )
     rex = Dao.execute(sql, vals)
     if not rex or len(rex) != 1:
         raise Exception('Invalid login!')
     if not User.__verify_pw(password, rex[0]['password']):
         raise Exception('Invalid login!')
     return rex[0]

 def update(d):
     asnid = d['id']
     del d['id']
     sql = ("UPDATE assignments "
            "SET %s "
            "WHERE id=?;") % (
         ','.join(f + '=?' for f in d.keys()))
     vals = list(d.values()) + [asnid]
     return Dao.execute(sql, vals)

 def update(d):
     empid = d['id']
     del d['id']
     sql = ("UPDATE employees "
            "SET %s "
            "WHERE id=?;") % (
         ','.join(f + '=?' for f in d.keys()))
     vals = list(d.values()) + [empid]
     return Dao.execute(sql, vals)

 def setUp(self):
     """初始化数据库表结构， 完成数据库session，缓存及redis连接"""
     Dao.init_db_uri()
     self.session = Dao.session()
     Dao.init_table_schema()
     self.cache = OPCache()
     self.rds = Dao.redis()
     self.session.rollback()

 def get_for_timeframe(first_month, last_month):
     sql = ("SELECT a.id AS id, "
            "a.employee_id AS employee_id, "
            "a.project_id AS project_id, "
            "a.first_month AS first_month, "
            "a.last_month AS last_month, "
            "a.effort AS effort, "
            "e.name AS employee, "
            "p.nickname AS project "
            "FROM assignments AS a "
            "JOIN employees AS e ON a.employee_id=e.id "
            "JOIN projects AS p ON a.project_id=p.id "
            "WHERE a.first_month BETWEEN ? AND ? "
            "OR a.last_month BETWEEN ? AND ?;")
     vals = (first_month, last_month, first_month, last_month)
     return Dao.execute(sql, vals)

 def get_for_project(prjid, month=None):
     sql = ("SELECT a.id AS id, "
            "a.employee_id AS employee_id, "
            "a.first_month AS first_month, "
            "a.last_month AS last_month, "
            "a.effort AS effort, "
            "a.notes AS notes, "
            "e.name AS employee "
            "FROM assignments AS a "
            "JOIN employees AS e ON a.employee_id= e.id "
            "WHERE a.project_id=? ")
     vals = [prjid]
     if month:
         sql += "AND a.last_month >= ? "
         vals += [month]
     sql += "ORDER BY e.name;"
     return Dao.execute(sql, vals)

 def get_for_employee(empid, month=None):
     sql = ("SELECT a.id AS id, "
            "a.project_id AS project_id, "
            "a.first_month AS first_month, "
            "a.last_month AS last_month, "
            "a.effort AS effort, "
            "a.notes AS notes, "
            "p.nickname AS project "
            "FROM assignments AS a "
            "JOIN projects AS p "
            "ON a.project_id= p.id "
            "WHERE a.employee_id=? ")
     vals = [empid]
     if month:
         sql += ("AND a.first_month <= ? "
                 "AND a.last_month >= ? ")
         vals += [month, month]
     sql += "ORDER BY p.nickname;"
     return Dao.execute(sql, vals)

 def set_name(self, name):
     """修改名称"""
     if not name:
         return False
     self.district_name = name
     return Dao.save_all()

 def delete(self):
     """设置为删除"""
     self.is_delete = 1
     return Dao.save_all()

 def recover(self):
     """反删除"""
     self.is_delete = 0
     return Dao.save_all()

            login_url='/login/',
        )

        tornado.web.Application.__init__(self, urls, **settings)


if __name__ == '__main__':
    """"""
    options.define(name='config', default='dev')
    options.define(name='port', default=38001)
    options.define(name='process', default=1)
    options.define(name='server', default='total')

    tornado.options.parse_command_line()
    config = Config(options.config)
    Dao.init_db_uri(options.config)
    # TODO for dev and test
    Dao.init_schema()

    app = Application()
    app.config = options.config

    log.info('Starting box api server... Listening port: {}'.format(options.port))
    try:
        server = HTTPServer(app, xheaders=True)
        server.bind(int(options.port))
        server.start(num_processes=int(options.process))
        tornado.ioloop.IOLoop.instance().start()
    except Exception as e:
        log.error('box api can not running:\n{}'.format(e), exc_info=True)
        tornado.ioloop.IOLoop.instance().stop()

 def edit_name(self, name):
     """改名"""
     if name:
         self.category_name = name
     return Dao.save_all()

 def delete(empid):
     sqls = [
         "DELETE FROM assignments WHERE employee_id=%s;" % (empid,),
         "DELETE FROM employees WHERE id=%s;" % (empid,)
     ]
     return Dao.transaction(sqls)

 def get_all():
     sql = "SELECT * FROM employees ORDER BY name;"
     return Dao.execute(sql)

 def op_session(self):
     """"""
     return Dao.session()

from scrapy.contrib.spiders import CrawlSpider, Rule
from scrapy.contrib.linkextractors.sgml import SgmlLinkExtractor as sle

# from sqlalchemy import create_engine
#
# DB_URL = 'mysql://root:[email protected]/yiqidingtest?charset=utf8'
#
# conn = create_engine(DB_URL, encoding='utf8')

from models.fun_video import FunVideo
from models.dao import Dao

# from utils.utils import get_funtv_mp4
from utils.pinyin import pinyin

Dao.init_db_uri('rory')
db_session = Dao.db_session()


class FunMovieSpider(CrawlSpider):
    """
    风行视频爬取
    """

    name = 'fun_movie'
    allows_domains = ['fun.tv', ]
    start_urls = [
        'http://www.fun.tv/retrieve/c-e794b5e5bdb1.n-e5bdb1e78987.o-pl.pg-1'
    ]

    # http://www.fun.tv/vplay/g-118135.v-635102/

 def on_finish(self):
     """关闭数据库连接"""
     Dao.close_mysql_session()