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 update_user(d):
sql = ("UPDATE users "
"SET username=?, password=?, role_id=? "
"WHERE id=?;")
vals = (d['username'], User.__hash_pw(d['password']), d['role_id'],
d['id'])
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 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 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 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 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 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_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_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 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 delete_role(role_id):
sql = "DELETE FROM roles WHERE id=?;"
vals = (role_id, )
return Dao.execute(sql, vals)
def add_role(d):
sql = ("INSERT INTO roles " "(name, description) " "VALUES (?,?);")
vals = (d['name'], d['description'])
return Dao.execute(sql, vals)
def change_password(user_id, new_password):
sql = ("UPDATE users " "SET password=? " "WHERE id=?")
vals = (User.__hash_pw(new_password), user_id)
return Dao.execute(sql, vals)
def get_all():
sql = "SELECT * FROM employees ORDER BY name;"
return Dao.execute(sql)
def get_users():
sql = "SELECT * FROM users;"
return Dao.execute(sql)
def recover(self):
"""反删除"""
self.is_delete = 0
return Dao.save_all()
def on_finish(self):
"""关闭数据库连接"""
Dao.close_mysql_session()
)
# self.db_session = scoped_session(sessionmaker(bind=Dao.engine()))
tornado.web.Application.__init__(self, urls, **settings)
if __name__ == '__main__':
""""""
options.define(name='config', default='TT')
options.define(name='port', default=8002)
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)
# for dev and test
# if options.config != __release__:
# Dao.init_schema()
app = Application()
app.config = options.config
app.rds = Dao.rds()
log.info('Starting {} server... Listening port: {}'.format(options.server, options.port))
try:
server = HTTPServer(app, xheaders=True)
server.bind(int(options.port))
server.start(num_processes=int(options.process))
from scrapy.selector import Selector
try:
from scrapy.spider import Spider
except ImportError, e:
from scrapy.spider import BaseSpider as Spider
from scrapy.contrib.spiders import CrawlSpider, Rule
from scrapy.contrib.linkextractors.sgml import SgmlLinkExtractor as sle
from models.fun_video import FunVideo, SubFunViedo
from models.dao import Dao
from utils.pinyin import pinyin
Dao.__echo = False
Dao.init_db_uri('rory')
Dao.init_schema()
db_session = Dao.db_session()
class FunTVSpider(CrawlSpider):
"""
风行电视爬取
"""
name = 'fun_cartoon'
allows_domains = ['fun.tv', ]
start_urls = [
'http://www.fun.tv/retrieve/c-e58aa8e6bcab.n-e5bdb1e78987.o-pl.pg-1'
]
from scrapy.selector import Selector
try:
from scrapy.spider import Spider
except ImportError, e:
from scrapy.spider import BaseSpider as Spider
from scrapy.contrib.spiders import CrawlSpider, Rule
from scrapy.contrib.linkextractors.sgml import SgmlLinkExtractor as sle
from models.fun_video import FunVideo, SubFunViedo
from models.dao import Dao
from utils.pinyin import pinyin
Dao.__echo = False
Dao.init_db_uri('rory')
Dao.init_schema()
db_session = Dao.db_session()
class FunTVSpider(CrawlSpider):
"""
风行综艺爬取
"""
name = 'fun_variety'
allows_domains = [
'fun.tv',
]
start_urls = [
'http://www.fun.tv/retrieve/c-e7bbbce889ba.n-e5bdb1e78987.o-mf.pg-1'
""""""
urls = [
# (Controller.url, controller),
(DYW.url, DYW),
(AuthController.url, AuthController),
]
settings = dict(
xsrf_cookies=False,
template_path='template',
static_path='static',
cookie_domain='.duanyong.wang',
cookie_secret='de635135f4d0a75bc8368d5760d0b953',
)
tornado.web.Application.__init__(self, urls, **settings)
if __name__ == '__main__':
""""""
options.define(name='config', default='dev')
options.define(name='port', default=9999)
options.define(name='process', default=1)
tornado.options.parse_command_line()
Dao.init_db_uri(options.config)
app = Application()
server = HTTPServer(app, xheaders=True)
server.bind(int(options.port))
server.start(num_processes=int(options.process))
tornado.ioloop.IOLoop.instance().start()
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://*****:*****@127.0.0.1/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 edit_name(self, name):
"""改名"""
if name:
self.venue_name = name
return Dao.save_all()
def add(d):
del d['id']
sql = "INSERT INTO employees (%s) VALUES (%s);" % (','.join(
d.keys()), '?' + ',?' * (len(d) - 1))
vals = list(d.values())
return Dao.execute(sql, vals)
def delete(asnid):
sql = "DELETE FROM assignments WHERE id=?;"
vals = [asnid]
return Dao.execute(sql, vals)
def delete(self):
"""屏蔽一个用户"""
self.is_delete = 1
return Dao.save_all()
def delete(self):
"""设置为删除"""
self.is_delete = 1
return Dao.save_all()
def edit_name(self, name):
"""改名"""
if name:
self.section_name = name
return Dao.save_all()
def set_name(self, name):
"""修改名称"""
if not name:
return False
self.district_name = name
return Dao.save_all()
def get_all():
sql = "SELECT * FROM projects ORDER BY nickname;"
return Dao.execute(sql)
def add_user(d):
sql = ("INSERT INTO users "
"(username, password, role_id) "
"VALUES (?,?,?);")
vals = (d['username'], User.__hash_pw(d['password']), d['role_id'])
return Dao.execute(sql, vals)
from scrapy.contrib.spiders import CrawlSpider, Rule
from scrapy.contrib.linkextractors.sgml import SgmlLinkExtractor as sle
# from sqlalchemy import create_engine
#
# DB_URL = 'mysql://*****:*****@127.0.0.1/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'
]
def delete_user(user_id):
sql = "DELETE FROM users WHERE id=?;"
vals = (user_id, )
return Dao.execute(sql, vals)
)
# self.db_session = scoped_session(sessionmaker(bind=Dao.engine()))
tornado.web.Application.__init__(self, urls, **settings)
if __name__ == '__main__':
""""""
options.define(name='config', default='TT')
options.define(name='port', default=8002)
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)
# for dev and test
# if options.config != __release__:
# Dao.init_schema()
app = Application()
app.config = options.config
app.rds = Dao.rds()
log.info('Starting {} server... Listening port: {}'.format(
options.server, options.port))
try:
server = HTTPServer(app, xheaders=True)
server.bind(int(options.port))
def get_roles():
sql = 'SELECT id, name AS value, description FROM roles;'
return Dao.execute(sql)
def delete(empid):
sqls = [
"DELETE FROM assignments WHERE employee_id=%s;" % (empid,),
"DELETE FROM employees WHERE id=%s;" % (empid,)
]
return Dao.transaction(sqls)
def update_role(d):
sql = ("UPDATE roles " "SET name=?, description=? " "WHERE id=?;")
vals = (d['name'], d['description'], d['id'])
return Dao.execute(sql, vals)
def get_all():
sql = "SELECT * FROM employees ORDER BY name;"
return Dao.execute(sql)
def delete(empid):
sqls = [
"DELETE FROM assignments WHERE employee_id=%s;" % (empid, ),
"DELETE FROM employees WHERE id=%s;" % (empid, )
]
return Dao.transaction(sqls)
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()
class OPCache(object):
"""歌曲缓存：
缓存歌曲信息
"""
rds = Dao.redis()
music_key = 'music:{}'
ani_key = 'ani:{}'
def get_ani(self, vid=None, *args):
""""""
vid_list = []
if vid is not None:
vid_list.append(aid)
vid_list = vid_list + list(args)
rds_key_list = list(map(self.ani_key.format, vid_list))
try:
res = self.rds.mget(rds_key_list)
except RedisError:
logging.error(msg='RedisError: MGET {}'.format(rds_key_list),
exc_info=True)
return {}
return dict([
(i.get('video_id'), i)
for i in map(json.loads, map(self.byte_decode, filter(None, res)))
])
# ---------- 歌曲信息缓存操作 ----------
def cache_music(self, data):
"""缓存一首歌的信息到redis"""
try:
self.rds.set(self.music_key.format(data.get('mid')),
json.dumps(data))
return True
except RedisError:
logging.error(msg='RedisError: SET {} {}'.format(
self.music_key.format(data.get('mid')), json.dumps(data)),
exc_info=True)
return False
def cache_multi_music(self, music_info_list):
"""批量缓存歌曲信息"""
cache_data = dict([(self.music_key.format(m.get('mid')), json.dumps(m))
for m in music_info_list])
try:
self.rds.mset(**cache_data)
return True
except RedisError:
logging.error(msg='RedisError: MSET {}'.format(str(cache_data)),
exc_info=True)
return False
def get_multi_music(self, mid_list):
"""批量获取歌曲信息"""
rds_key_list = list(map(self.music_key.format, mid_list))
try:
res = self.rds.mget(rds_key_list)
except RedisError:
logging.error(msg='RedisError: MGET {}'.format(rds_key_list),
exc_info=True)
return {}
# return dict([(i.get('mid'), i) for i in map(json.loads, filter(None, res))])
return dict([
(i.get('mid'), i)
for i in map(json.loads, map(self.byte_decode, filter(None, res)))
])
def clear_music(self, mid=None, mid_list=None):
"""清除歌的缓存"""
del_key = []
if mid is not None:
del_key += [self.music_key.format(mid)]
if mid_list is not None:
del_key += list(map(self.music_key.format, mid_list))
if not del_key:
return True
try:
self.rds.delete(*del_key)
return True
except RedisError:
logging.error(msg='RedisError: DEL {}'.format(del_key),
exc_info=True)
return False
# ---------- 歌曲信息缓存操作 ----------
def byte_decode(self, res):
"""# TODO 在windows下会是bytes，需要处理一下"""
return bytes.decode(res)
def edit_name(self, name):
"""改名"""
if name:
self.category_name = name
return Dao.save_all()
),
force_persistence=True,
)
settings.update(session=session_settings)
super(Application, self).__init__(handlers, **settings)
if __name__ == "__main__":
tornado.options.define(name="config", default="dev")
tornado.options.define(name="port", default=81)
tornado.options.define(name="process", default=1)
tornado.options.define(name="name", default="default")
tornado.options.parse_command_line()
FFConfig.init_config(options.config)
Dao.do_init()
io_loop = tornado.ioloop.IOLoop.instance()
app = Application()
server = HTTPServer(app, xheaders=True)
server.bind(int(options.port))
server.start(num_processes=options.process)
# 平滑关闭脚本，输入kill -2 pid 或者 kill -15 pid 平滑关闭
graceful_shutdown(io_loop, server)
logging.info("listening on port: {} with {} process on {} environment".format(
options.port, options.process, options.config))
io_loop.start()
def initialize(self):
"""初始化数据库连接"""
self.__angular_body_argument = None
Dao.create_mysql_session()
def delete(asnid):
sql = "DELETE FROM assignments WHERE id=?;"
vals = [asnid]
return Dao.execute(sql, vals)
""""""
urls = [
(ReadCaricature.url, ReadCaricature),
(WatchAnimation.url, WatchAnimation),
(ListenMusic.url, ListenMusic),
(CreateMusic.url, CreateMusic),
(CreateCaricature.url, CreateCaricature),
(CreateAnimation.url, CreateAnimation),
]
settings = dict(xsrf_cookies=False)
tornado.web.Application.__init__(self, urls, **settings)
if __name__ == '__main__':
""""""
options.define(name='config', default='dev')
options.define(name='port', default=21002)
options.define(name='process', default=1)
tornado.options.parse_command_line()
Dao.init_db_uri(options.config)
if options.config == 'unittest':
Dao.init_table_schema()
app = Application()
server = HTTPServer(app, xheaders=True)
server.bind(int(options.port))
server.start(num_processes=int(options.process))
tornado.ioloop.IOLoop.instance().start()
