def update_contest_statistics(result):
"""
更新比赛统计数据
:param result:
:return:
"""
count = db.select_int('select count(*) from t_ranking where contest_id=? and problem_id=? and username=?',
result['contest_id'], result['problem_id'], result['user_id'])
if count == 0:
record = dict(contest_id=result['contest_id'], problem_id=result['problem_id'], username=result['user_id'])
db.insert('t_ranking', **record)
p_time = db.select_one('select AC_time from t_ranking where contest_id=? and problem_id=? and username=?',
result['contest_id'], result['problem_id'], result['user_id']).get('AC_time')
if p_time == 0: # 本题还没有AC
if result['result'] == configs.result_code.AC: # 本题初次AC
with db.connection():
submit_time = db.select_one('select submit_time from t_submission where submission_id=?',
result['solution_id']).get('submit_time') # 提交时间, datetime类型
date_time = db.select_one('select contest_date, start_time from t_contest where contest_id=?',
result['contest_id'])
c_date = date_time.get('contest_date') # 比赛开始日期, date类型
c_time = date_time.get('start_time') # 比赛开始时间, timedelta类型
# 转换为比赛开始时间, datetime类型
contest_time = datetime.strptime(c_date.strftime('%Y-%m-%d %H:%M:%S'), '%Y-%m-%d %H:%M:%S') + c_time
ac_time = (submit_time - contest_time).total_seconds() / 60 # 本题初次AC所用时间, 单位为分钟, float类型
with db.transaction():
db.update('update t_ranking set AC_time=? where contest_id=? and problem_id=? and username=?',
ac_time, result['contest_id'], result['problem_id'], result['user_id']) # AC题目所用时间
else:
db.update('update t_ranking set wrong_submit = wrong_submit + 1 where contest_id=? and problem_id=? '
'and username=?', result['contest_id'], result['problem_id'], result['user_id'])
def count_all(cls):
'''
Find by 'select count(pk) from table' and return integer.
'''
return db.select_int('select count(`%s`) from `%s`' % (cls.__primary_key__.name, cls.__table__))
@classmethod
def count_by(cls, where, *args):
'''
Find by 'select count(pk) from table where ... ' and return int.
'''
return db.select_int('select count(`%s`) from `%s` %s' % (cls.__primary_key__.name, cls.__table__, where), *args)
def update(self):
self.pre_update and self.pre_update()
L = []
args = []
for k, v in self.__mappings__.iteritems():
if v.updatable:
if hasattr(self, k):
arg = getattr(self, k)
else:
arg = v.default
setattr(self, k, arg)
L.append('`%s`=?' % k)
args.append(arg)
pk = self.__primary_key__.name
args.append(getattr(self, pk))
db.update('update `%s` set %s where %s=?' % (self.__table__, ','.join(L), pk), *args)
return self
def count_by(cls, where, *args):
"""
Find by 'select count(pk) from table where ... ' and return int.
"""
return db.select_int(
"select count(`%s`) from `%s` %s" % (cls.__primary_key__.name, cls.__table__, where), *args
)
def count_by(cls,where,*args):
'''
Find by 'select count(pk) from table where ...' and retutn int.
:param where:
:param args:
:return:
'''
return db.select_int('select count(`%s`) from `%s` %s'%(cls.__primary_key__.name,cls.__table__,where),*args)
def count_all(cls):
"""
执行 select count(pk) from table语句，返回一个数值
效率：
count(1) 和 count(主键) 效率相对高
count(*) 是扫描表的，相对慢
"""
return db.select_int('select count(`%s`) from `%s`' % (cls.__primary_key__.name, cls.__table__))
def dount_by(cls, where, *args):
'''
:param where:
:param args:
:return:
'''
return db.select_int('select count(`%s`) from `%s` %s' %(cls.__primary_key__.name, cls.__table__, where), args)
def count_all(cls, **kwargs):
'''
Find by 'select count(pk) from table' and return integer.
'''
if kwargs.has_key('sub_name'):
sub_name = kwargs['sub_name']
else:
sub_name = ""
return db.select_int('select count(`%s`) from `%s%s`' % (cls.__primary_key__.name, cls.__table__, sub_name))
def count_by(cls, where, *args, **kwargs):
'''
Find by 'select count(pk) from table where ... ' and return int.
'''
if kwargs.has_key('sub_name'):
sub_name = kwargs['sub_name']
else:
sub_name = ""
return db.select_int('select count(`%s`) from `%s%s` %s' % (cls.__primary_key__.name, cls.__table__, sub_name, where), *args)
def count_all(cls):
'''
>>> class User(Model):
... __table__ = 'user'
... id = IntegerField(primary_key=True)
... name = StringField()
>>> user7 = User(id=7, name='a')
>>> user8 = User(id=8, name='a')
>>> user7.insert()
{'name': 'a', 'id': 7}
>>> user8.insert()
{'name': 'a', 'id': 8}
>>> User.count_all()
2L
'''
return db.select_int('select count(`%s`) from `%s`' % (cls.__primary_key__, cls.__table__))
def find_logs_for_table(kwargs, start=0, size=10):
sql_count = " select count(*) from logs "
sql = " select * from `logs` "
if kwargs:
where = " where "
if kwargs.get('created'):
s = kwargs.get('created').get("start")
e = kwargs.get('created').get("end")
kwargs.pop('created')
where += "created > '%s' and created< '%s' " % (s, e)
where += ' and '.join(["`%s`=%s" % (k, v) for k, v in kwargs])
sql_count += where
sql += where
sql += ' order by created desc limit ?,?; '
count = db.select_int(sql_count)
logs = db.select(sql, start, size)
result = {
"count": count,
"data": logs
}
raise gen.Return(result)
def update_contest_ranking(result):
"""
更新比赛统计数据
:param result:
:return:
"""
rowcount = db.update("show tables like 't_?'", result['contest_id'])
if rowcount == 0:
PutTask._create_ranking_table(result)
count = db.select_int('select count(*) from t_? where username=?', result['contest_id'], result['user_id'])
if count == 0:
user = dict(username=result['user_id'])
db.insert('t_%s' % result['contest_id'], **user)
p_time = db.select_one('select ?_time from t_? where username=?',
result['problem_id'], result['contest_id'], result['user_id'])\
.get('%s_time' % result['problem_id'])
if p_time == 0: # 本题还没有AC
if result['result'] == configs.result_code.AC: # 本题初次AC
with db.connection():
submit_time = db.select_one('select submit_time from t_submission where submission_id=?',
result['solution_id']).get('submit_time') # 提交时间, datetime类型
date_time = db.select_one('select contest_date, start_time from t_contest where contest_id=?',
result['contest_id'])
c_date = date_time.get('contest_date') # 比赛开始日期, date类型
c_time = date_time.get('start_time') # 比赛开始时间, timedelta类型
# 转换为比赛开始时间, datetime类型
contest_time = datetime.strptime(c_date.strftime('%Y-%m-%d %H:%M:%S'), '%Y-%m-%d %H:%M:%S') + c_time
ac_time = (submit_time - contest_time).total_seconds() # 本题初次AC所用时间, 单位为秒, float类型
with db.transaction():
db.update('update t_? set solved = solved + 1 where username=?',
result['contest_id'], result['user_id']) # AC题数 + 1
db.update('update t_? set ?_time=? where username=?',
result['contest_id'], result['problem_id'], ac_time, result['user_id']) # AC题目所用时间
else:
db.update('update t_? set ?_wrong = ?_wrong + 1 where username=?',
result['contest_id'], result['problem_id'], result['problem_id'], result['user_id'])
def count_all(cls):
'''
@summary: 通过'select count(pk) from table'查询，并返回一个整数。
'''
return db.select_int('select count(`%s`) from `%s`' %
(cls.__primary_key__.name, cls.__table__))
def count_by(cls, where, *args):
return db.select_int(
'select count(%s) from %s %s' %
(cls.__primary_key__, cls.__table__, where), args)
def count_all(cls, *args):
return db.select_int('select count(%s) from %s' %
(cls.__primary_key__, cls.__table__))
def count_all(cls, *args):
return db.select_int('select count(%s) from %s' % (cls.__primary_key__, cls.__table__))
def count_all(cls):
"""
Find by 'select count(pk) from table' and return integer.
"""
return db.select_int("select count(`%s`) from `%s`" % (cls.__primary_key__.name, cls.__table__))
def count_by(cls, where, *args):
'''
Find by 'select count(pk) from table where ... ' and return int.
'''
return db.select_int('select count(`%s`) from `%s` %s' % (cls.__primary_key__.name, cls.__table__, where), *args)
def count_all(cls):
'''
查询记录数量
:return:
'''
return db.select_int('select count(`%s`) from %s' % (cls.__primary_key__.name, cls.__table__))
def count_by(cls, where, *args):
return db.select_int('select count(%s) from %s %s' % (cls.__primary_key__, cls.__table__, where), args)
args.append(getattr(self, pk))
db.update('update `%s` set %s where %s=?' % (self.__table__, ','.join(L), pk), *args)
return self
def delete(self):
self.pre_delete and self.pre_delete()
pk = self.__primary_key__.name
args = (getattr(self, pk), )
db.update('delete from `%s` where `%s`=?' % (self.__table__, pk), *args)
return self
def insert(self):
self.pre_insert and self.pre_insert()
params = {}
for k, v in self.__mappings__.iteritems():
if v.insertable:
if not hasattr(self, k):
setattr(self, k, v.default)
params[v.name] = getattr(self, k)
db.insert('%s' % self.__table__, **params)
return self
if __name__=='__main__':
logging.basicConfig(level=logging.DEBUG)
db.create_engine('root', 'admin', 'test')
d = db.select('select * from test1')
print d
d = db.select_one('select * from test1')
print d
d = db.select_int('select count(*) from test1')
print d
def count_by(cls, order=None, group=None, limit=None, offset=None, **kwargs):
return db.select_int(cls.__table__, what='count(%s)' % cls.__primary_key__.name,
where=kwargs, order=order, group=group, limit=limit, offset=offset)
def count_all(cls):
return db.select_int(cls.__table__, what='count(%s)' % cls.__primary_key__.name)
def count_all(cls):
"""Count by attribute"""
sql = 'select count(*) from %s' % cls.__table_name__
return db.select_int(sql)
def count_by(cls, where, *args):
return db.select_int(
'select count(`%s`) from `%s` %s' %
(cls, __primary_key__.name, cls.__table__, where), *args)
def count_all(cls):
'''
Find by 'select count(pk) from table' and return integer.
'''
return db.select_int('select count(`%s`) from `%s`' % (cls.__primary_key__.name, cls.__table__))
def count_by(cls, where, *args):
"""
通过select count(pk) from table where ...语句进行查询，返回一个数值
"""
return db.select_int('select count(`%s`) from `%s` %s' % (cls.__primary_key__.name, cls.__table__, where), *args)
def count_by(cls, where, *args):
'''
通过select count(pk) from table where ...语句进行查询， 返回一个数值
Find by 'select count(pk) from table where ... ' and return int.
'''
return db.select_int('select count(`%s`) from `%s` %s' % (cls.__primary_key__.name, cls.__table__, where), *args)
def count_all(cls):
"""
执行 select count(pk) from table
"""
return db.select_int('select count(`%s`) from %s' %(cls.__primary_key__.name,cls.__table__))
def count_by(cls, where, *args):
"""
通过select count(pk) from table where ...语句进行查询， 返回一个数值
"""
return db.select_int('select count(`%s`) from `%s` %s' % (cls.__primary_key__.name, cls.__table__, where), *args)
def count_all(cls):
'''
Find by 'select count(pk) from table' and return integer.
'''
return db.select_int('select count(`%s`) from `%s`' % (cls.__primary_key__.name, cls.__table__))
#!/usr/bin/env python
def count_all(cls):
'''select count(pk) from table 返回一个integer'''
return db.select_int('select count(`%s`) from `%s`' % (cls.__primary_key__.name, cls.__table__))
def count_all(cls):
"""
find by 'select count(pk) from table's and return integer'
"""
return db.select_int('elect count("%s") from "%s"'%(cls.__primary_key__.name,cls.__table__))
def count(cls, where, *args):
'''
Find by 'select count(*) from where ... ' and return one and only one result.
'''
return db.select_int('select count(%s) from %s %s' % (cls.__primary_key__.name, cls.__table__, where), *args) if where else \
db.select_int('select count(%s) from %s' % (cls.__primary_key__.name, cls.__table__))
def count(cls, where, *args):
'''
Find by 'select count(*) from where ... ' and return one and only one result.
'''
return db.select_int('select count(%s) from %s %s' % (cls.__primary_key__.name, cls.__table__, where), *args) if where else \
db.select_int('select count(%s) from %s' % (cls.__primary_key__.name, cls.__table__))
def count_all(cls):
return db.select_int('select count(`%s`) from `%s`' % (cls.__primary_key__.name,cls.__table__))
@classmethod
def count_all(cls):
return db.select_int('select count(%s) from `%s` %s' % (cls.__primary_key__.name, cls.__table__))
def count_all(cls):
return db.select_int('select count(`%s`) from `%s`' %
(cls.__primary_key__.name, cls.__table__))
def count_by(cls, where, *args):
return db.select_int('select count(`%s`) from `%s` %s' % (cls.__primary_key__.name, cls.__table__, where), *args)
def count_by(cls, where, *args):
' find by `select count(pk) from table where ... ` and return int '
return db.select_int('select count(`%s`) from `%s` %s' % (cls.__primary_key__.name, cls.__table__, where), *args)