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 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_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 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 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()
"""""" 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()