def post(self):
username = self.get_argument('username', '')
passwd = self.get_argument('passwd', '')
print '!!!!!!!!!!!!!!!!!!'
print username
print username.encode('cp1251')
print '!!!!!!!!!!!!!!!!!!!'
conn = yield tornado_mysql.connect(host='127.0.0.1', port=3306, user='******', passwd='root', db='drunk_adventures')
cur = conn.cursor()
yield cur.execute("SELECT password, session FROM avatar WHERE login = '******' ;" % username)
cur.close()
del conn
creds = []
for row in cur:
creds.append(row)
if creds and creds[0][0] == passwd:
session_id = startSession(username)
h.update(session_id)
self.write({'auth_status': 'Authorization successful', 'se': h.hexdigest(), 'u': username})
conn = yield tornado_mysql.connect(host='127.0.0.1', port=3306, user='******', passwd='root', db='drunk_adventures')
cur = conn.cursor()
yield cur.execute("UPDATE avatar SET session = %s WHERE login = %s; COMMIT;" , (session_id, username))
cur.close()
del conn
else:
self.write({'auth_status': 'Go f**k yourself asshole', 'se':''})
def post(self):
username = self.get_argument('reg-username', '')
passwd = self.get_argument('reg-passwd', '')
email = self.get_argument('reg-email','')
conn = yield tornado_mysql.connect(host='127.0.0.1', port=3306, user='******', passwd='root', db='drunk_adventures')
cur = conn.cursor()
yield cur.execute("SELECT login FROM avatar WHERE login = '******' ;" % (username,))
cur.close()
del conn
names = []
for row in cur:
names.append(row)
if names:
self.write({'reg_status': 'Go f**k yourself asshole', 'se':''})
else:
session_id = startSession(username)
h.update(session_id)
conn = yield tornado_mysql.connect(host='127.0.0.1', port=3306, user='******', passwd='root', db='drunk_adventures')
cur = conn.cursor()
yield cur.execute("INSERT INTO avatar (login, password, email, session) VALUES (%s,%s,%s, %s) ; COMMIT;" , (username, passwd, email, session_id))
cur.close()
del conn
self.write({'reg_status': 'Registration successful', 'se': h.hexdigest(), 'u': username})
def post(self):
event = self.get_argument('event', '')
if not event:
response = {
'error': True,
'msg': 'Пожалуйста, введите id события.'
}
else:
try:
conn = yield tornado_mysql.connect(host='127.0.0.1', port=3306, user='******', passwd='', db='miemgames', charset='utf8')
cur = conn.cursor()
yield cur.execute("SELECT pl.name, pl.num_group FROM participants pr, players pl WHERE pr.player = pl.id AND pr.event = %s",
(event))
response = []
for i, row in enumerate(cur):
response.append({'name': str(row[0]), 'num_group': str(row[1])})
response = {"events": response}
cur.close()
conn.close()
except:
response = {
'error' : True,
'msg' : 'Данного события не существует.'
}
self.write(response)
def __connect_to_ctl(self):
self._ctl_connection_settings = dict(self.__connection_settings)
self._ctl_connection_settings["db"] = "information_schema"
self._ctl_connection_settings["cursorclass"] = DictCursor
self._ctl_connection = yield tornado_mysql.connect(**self._ctl_connection_settings)
self._ctl_connection._get_table_information = self.__get_table_information
self.__connected_ctl = True
def get(self,prob_id):
prob_id=int(prob_id)
if prob_id < 1 :
self.redirect_msg('/problems','题目编号错误')
return
prob_id=norm_page(prob_id)
msg = self.get_argument('msg',None)
conn = yield tornado_mysql.connect(host=conf.DBHOST,\
port=conf.DBPORT,user=conf.DBUSER,passwd=conf.DBPW,db=conf.DBNAME,charset='utf8')
cur = conn.cursor()
sql = "SELECT id,tp,name,author,visible FROM problems WHERE id = %s LIMIT 1"
yield cur.execute(sql,(prob_id,))
problem = cur.fetchone()
cur.close()
conn.close()
if problem == None :
self.redirect_msg('/problems','题目编号错误')
return
user = self.current_user
auth = self.auth()
if problem[3].encode('utf-8') != self.current_user and auth < problem[4]:
self.redirect_msg('/status','权限不足')
return
self.render('submit.html',msg=msg,problem=problem,page_type='problem',\
page_title='提交#'+str(problem[0])+'. '+problem[2]+' -XOJ')
def get(self,judge_id):
judge_id=int(judge_id)
if judge_id < 1 :
self.redirect_msg('/status','评测记录未找到')
return
judge_id = norm_page(judge_id)
msg = self.get_argument('msg',None)
conn = yield tornado_mysql.connect(host=conf.DBHOST,\
port=conf.DBPORT,user=conf.DBUSER,passwd=conf.DBPW,db=conf.DBNAME,charset='utf8')
cur = conn.cursor()
sql = "SELECT id,author,code,problem_id,problem_name,status,mem_use,tim_use,\
lang,code_len,submit_date,result,visible FROM judge WHERE id = %s"
yield cur.execute(sql,(judge_id,))
info = cur.fetchone()
cur.close()
conn.close()
if info == None :
self.redirect_msg('/status','评测记录未找到')
return
user = self.current_user
auth = self.auth()
if info[1].encode('utf-8') != self.current_user and auth < info[12]:
self.redirect_msg('/status','权限不足')
return
#if self.current_user != info[1].encode('utf-8') and self.current_user != 'zrt'.encode('utf-8') and self.current_user != 'sys'.encode('utf-8'):
# self.redirect_msg('/status','权限不足')
# return
self.render('status_info.html',msg=msg,info=info,page_type='status',\
page_title='评测记录 -XOJ')
def get(self):
msg = self.get_argument('msg',None)
page_now = int(self.get_argument('page','1'))
page_now=norm_page(page_now)
user = self.get_argument('user',None)
problem = self.get_argument('problem',None)
conn = yield tornado_mysql.connect(host=conf.DBHOST,\
port=conf.DBPORT,user=conf.DBUSER,passwd=conf.DBPW,db=conf.DBNAME,charset='utf8')
cur = conn.cursor()
#visible
sql = "SELECT id,problem_id,problem_name,author,status,\
tim_use,mem_use,submit_date FROM judge WHERE id >0 "
param = [] #visible
if user:
sql+=" AND author=%s "
param.append(user)
if problem:
sql+=" AND problem_id = %s "
param.append(problem)
sql+="ORDER BY id DESC LIMIT %s,%s"
param.append((page_now-1)*conf.STATUS_PER_PAGE)
param.append(conf.STATUS_PER_PAGE)
yield cur.execute(sql,tuple(param))
records = [row for row in cur]
cur.close()
conn.close()
url=urljoin('/status','?'+urlencode({'user':user or '','problem':problem or ''}))
pages=gen_pages(url,page_now)
self.render('status.html',msg=msg,records=records,pages=pages,\
page_type='status',page_title='状态 -XOJ')
def upd(self,user,prob_id):
#user submit_num+1
#user tongji[0]+1
#problems submit_num+1
#problems tongji[0]+1
conn = yield tornado_mysql.connect(host=conf.DBHOST,\
port=conf.DBPORT,user=conf.DBUSER,passwd=conf.DBPW,db=conf.DBNAME,charset='utf8')
cur = conn.cursor()
sql = "SELECT submit_num,tongji FROM user WHERE user = %s LIMIT 1"
yield cur.execute(sql,(user,))
info = cur.fetchone()
tongji = json.loads(info[1])
tongji[0]=tongji[0]+1
sql = "UPDATE user SET submit_num = %s,tongji=%s WHERE user = %s"
yield cur.execute(sql,(info[0]+1,json.dumps(tongji),user))
yield conn.commit()
sql = "SELECT submit_num,tongji FROM problems WHERE id = %s LIMIT 1"
yield cur.execute(sql,(prob_id,))
info = cur.fetchone()
tongji = json.loads(info[1])
tongji[0]=tongji[0]+1
sql = "UPDATE problems SET submit_num = %s,tongji=%s WHERE id = %s"
yield cur.execute(sql,(info[0]+1,json.dumps(tongji),prob_id))
yield conn.commit()
cur.close()
conn.close()
def main():
conn = yield tornado_mysql.connect(host=os.getenv("DB_HOST", "localhost"),
port=int(os.getenv("DB_PORT", "3306")),
user=os.getenv("DB_USER", "root"),
passwd=os.getenv("DB_PASS", "toor"),
db=os.getenv("DB_NAME", "eleme"))
cur = conn.cursor()
# 创建数据表SQL语句
sql = """CREATE TABLE IF NOT EXISTS `order` (
`order_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned,
`total` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;"""
yield cur.execute(sql)
# 创建数据表SQL语句
sql = """CREATE TABLE IF NOT EXISTS `order_item` (
`order_item_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`order_id` int(10) unsigned,
`food_id` int(10) unsigned NOT NULL DEFAULT '0',
`count` int(10) NOT NULL DEFAULT '0',
PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;"""
yield cur.execute(sql)
cur.close()
conn.close()
def select_mysql(host, port, db, query, login='******', password='******', connect_timeout=60, request_timeout=120):
import tornado_mysql
conn = yield tornado_mysql.connect(host=host, port=port, user=login, passwd=password, db=db)
cur = conn.cursor()
yield cur.execute('set names utf8;')
cur.close()
cur = conn.cursor()
yield cur.execute(query)
points = []
for row in cur:
toa = []
points.append(toa)
for val in row:
if isinstance(val, datetime):
val = val.timestamp()
elif isinstance(val, Number):
val = float(val)
toa.append(val)
tor = {
'name': query,
'columns': list(map(lambda c: c[0], cur.description)),
'points': points
}
cur.close()
conn.close()
raise tornado.gen.Return(tor)
def _get_conn(self): # -> Future[connection]
now = self.io_loop.time()
# Try to reuse in free pool
while self._free_conn:
conn = self._free_conn.popleft()
if now - conn.connected_time > self.max_recycle_sec:
self._close_async(conn)
continue
log.debug("Reusing connection from pool: %s", self.stat())
fut = Future()
fut.set_result(conn)
return fut
# Open new connection
if self.max_open == 0 or self._opened_conns < self.max_open:
self._opened_conns += 1
log.debug("Creating new connection: %s", self.stat())
fut = connect(**self.connect_kwargs)
fut.add_done_callback(self._on_connect) # self._opened_conns -=1 on exception
return fut
# Wait to other connection is released.
fut = Future()
self._waitings.append(fut)
return fut
def post(self):
# 获取参数
print(self.json_args)
username = self.json_args.get("username", "")
useraddress = self.json_args.get("useraddress", "")
email = self.json_args.get("email", "")
messagetext = self.json_args.get("messagetext", "")
conn = yield tornado_mysql.connect(host='127.0.0.1',
port=3306,
user='******',
passwd='',
db='messageboard')
cur = conn.cursor()
sql = "INSERT INTO message (username, useraddress, email, messagetext) VALUES ('%s', '%s', '%s', '%s');" % (
username, useraddress, email, messagetext)
try:
yield cur.execute(sql)
yield cur.execute("SELECT * FROM message")
for row in cur:
print(row)
yield conn.commit()
raise gen.Return(dict(errcode=200, errmsg="留言成功"))
except Exception as e:
logging.error(e)
raise gen.Return(dict(errcode=500, errmsg="留言失败"))
finally:
cur.close()
conn.close()
def post(self):
p=[self.get_argument(s) for s in ['name','content','invitecode'] ]
auth = self.auth()
if auth < 10:
self.redirect_msg('/posts','权限不足')
return
if p[2] != 'addpost':
self.redirect_msg('/post/new','邀请码错误')
return
conn = yield tornado_mysql.connect(host=conf.DBHOST,\
port=conf.DBPORT,user=conf.DBUSER,passwd=conf.DBPW,db=conf.DBNAME,charset='utf8')
cur = conn.cursor()
sql = "INSERT INTO posts (name,author,content,gen_date,modify_date) VALUES (%s,%s,%s,%s,%s)"
try:
yield cur.execute(sql,(p[0],self.current_user,p[1],datetime.now(),datetime.now()))
yield conn.commit()
except BaseException as e:
self.redirect_msg('/post/new','数据库错误')
raise
else:
sql = "SELECT id FROM posts ORDER BY id DESC LIMIT 1"
yield cur.execute(sql)
p=cur.fetchone()
self.redirect_msg('/post/%d'%p[0],'发布文章成功')
finally:
cur.close()
conn.close()
def get(self,contest_id):
contest_id=int(contest_id)
if contest_id < 1 :
self.redirect_msg('/contests','比赛编号错误')
return
contest_id=norm_page(contest_id)
msg = self.get_argument('msg',None)
conn = yield tornado_mysql.connect(host=conf.DBHOST,\
port=conf.DBPORT,user=conf.DBUSER,passwd=conf.DBPW,db=conf.DBNAME,charset='utf8')
cur = conn.cursor()
sql = "SELECT id,name,author,begin_date,end_date,status,content,problems,info,visible FROM contests WHERE id = %s"
yield cur.execute(sql,(contest_id,))
contest=cur.fetchone()
cur.close()
conn.close()
if contest == None:
self.redirect_msg('/contests','比赛编号错误')
return
user=self.current_user
auth = self.auth()
if contest[2].encode('utf-8')!=user and auth < 250:
self.redirect_msg('/contests','权限不足')
return
self.render('contest_edit.html',msg=msg,contest=contest,\
page_type='contest',page_title='编辑比赛#'+str(contest[0])+'. '+contest[1]+' -XOJ')
def get(self,post_id):
post_id=int(post_id)
if post_id < 1 :
self.redirect_msg('/posts','文章编号错误')
return
post_id=norm_page(post_id)
msg = self.get_argument('msg',None)
conn = yield tornado_mysql.connect(host=conf.DBHOST,\
port=conf.DBPORT,user=conf.DBUSER,passwd=conf.DBPW,db=conf.DBNAME,charset='utf8')
cur = conn.cursor()
#
sql = "SELECT name,content,author FROM posts WHERE id = %s LIMIT 1"
yield cur.execute(sql,(post_id,))
post = cur.fetchone()
cur.close()
conn.close()
if post == None :
self.redirect_msg('/posts','题目编号错误')
return
user = self.current_user
auth = self.auth()
if auth < 200 and post[2].encode('utf-8')!= user:
self.redirect_msg('/posts','权限不足')
return
self.render('edit_post.html',msg=msg,post=post,page_type='post',\
page_title='修改文章:'+post[0]+' -XOJ')
def post(self):
p=[self.get_argument(s) for s in ['name','info','content','problems','begin_date','end_date','author','visible','invitecode'] ]
if p[-1] != 'addcontest':
self.redirect_msg('/contest/new','邀请码错误')
return
conn = yield tornado_mysql.connect(host=conf.DBHOST,\
port=conf.DBPORT,user=conf.DBUSER,passwd=conf.DBPW,db=conf.DBNAME,charset='utf8')
cur = conn.cursor()
auth = self.auth()
if auth < 25:
self.redirect_msg('/contests','权限不足')
return
sql = "INSERT INTO contests (name,info,content,problems,begin_date,end_date,author,visible,gen_date,status,result) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
try:
yield cur.execute(sql,(p[0],p[1],p[2],p[3],p[4],p[5],p[6],p[7],datetime.now(),1,''))
yield conn.commit()
except BaseException as e:
self.redirect_msg('/contest/new','数据库错误')
else:
sql = "SELECT id FROM contests ORDER BY id DESC LIMIT 1"
yield cur.execute(sql)
p=cur.fetchone()
self.redirect_msg('/contest/%d/edit'%p[0],'添加比赛成功')
finally:
cur.close()
conn.close()
def test_issue_17(self):
""" could not connect mysql use passwod """
conn = self.connections[0]
host = self.databases[0]["host"]
db = self.databases[0]["db"]
c = conn.cursor()
# grant access to a table to a user with a password
with warnings.catch_warnings():
warnings.simplefilter("ignore")
yield c.execute("drop table if exists issue17")
yield c.execute("create table issue17 (x varchar(32) primary key)")
try:
yield c.execute("insert into issue17 (x) values ('hello, world!')")
yield c.execute(
"grant all privileges on %s.issue17 to 'issue17user'@'%%' identified by '1234'"
% db)
yield conn.commit()
conn2 = yield tornado_mysql.connect(host=host,
user="******",
passwd="1234",
db=db)
c2 = conn2.cursor()
yield c2.execute("select x from issue17")
self.assertEqual("hello, world!", c2.fetchone()[0])
finally:
yield c.execute("drop table issue17")
def post(self):
player = self.get_argument('player', '')
if not player:
response = {
'error': True,
'msg': 'Пожалуйста, введите login игрока.'
}
else:
try:
conn = yield tornado_mysql.connect(host='127.0.0.1', port=3306, user='******', passwd='', db='miemgames', charset='utf8')
cur = conn.cursor()
yield cur.execute("SELECT e.id, g.name, e.time, e.place FROM players p, participants pa, events e, games g WHERE pa.player = p.login AND g.id = e.game AND e.id = pa.event AND p.login = %s",
(player))
response = []
for i, row in enumerate(cur):
response.append({'id': str(row[0]), 'name': str(row[1]), 'time': str(row[2]), 'place': str(row[3])})
response = {"payers": response}
cur.close()
conn.close()
except:
response = {
'error' : True,
'msg' : 'Данный игрок не учавствует ни в каких играх.'
}
self.write(response)
def connect(self):
engine = self.param['engine']
if engine == 'mysql':
import tornado_mysql
self.conn = yield tornado_mysql.connect(host = self.param['host'],
port = self.param['port'],
user = self.param['user'],
passwd = self.param['passwd'],
db = self.param['db'],
charset = self.param['charset'],
connect_timeout = self.param.get('timeout', 10),
)
yield self.conn.autocommit(1)
cur = self.conn.cursor()
yield cur.execute("show variables like 'server_id'")
row = cur.fetchone()
self.server_id = int(row[1])
#if self.param.get('autocommit',None):
# log.note('set autocommit')
# self.conn.autocommit(1)
#initsqls = self.param.get('init_command')
#if initsqls:
# log.note('init sqls:', initsqls)
# cur = self.conn.cursor()
# cur.execute(initsqls)
# cur.close()
else:
raise ValueError, 'engine error:' + engine
def _get_conn(self):
now = self.io_loop.time()
# Try to reuse in free pool
while self._free_conn:
conn = self._free_conn.popleft()
if now - conn.connected_time > self.max_recycle_sec:
self._close_async(conn)
continue
log.debug("Reusing connection from pool: %s", self.stat())
fut = Future()
fut.set_result(conn)
return fut
# Open new connection
if self.max_open == 0 or self._opened_conns < self.max_open:
self._opened_conns += 1
log.debug("Creating new connection: %s", self.stat())
fut = connect(**self.connect_kwargs)
fut.add_done_callback(
self._on_connect) # self._opened_conns -=1 on exception
return fut
# Wait to other connection is released.
fut = Future()
self._waitings.append(fut)
return fut
def post(self):
player = self.get_argument('player', '')
event = self.get_argument('event', '')
if not player:
response = {
'error': True,
'msg': 'Пожалуйста, введите login игрока.'
}
elif not event:
response = {
'error': True,
'msg': 'Пожалуйста, введите id события.'
}
else:
try:
conn = yield tornado_mysql.connect(host='127.0.0.1', port=3306, user='******', passwd='', db='miemgames', charset='utf8')
cur = conn.cursor()
yield cur.execute("INSERT INTO participants (event, player) VALUES (%s, %s)",
(event, player))
conn.commit()
cur.close()
conn.close()
response = {
'error': False
}
except:
response = {
'error' : True,
'msg' : 'double value'
}
self.write(response)
def post(self):
login = self.get_argument('login', '')
password = self.get_argument('password', '')
response = {}
try:
conn = yield tornado_mysql.connect(host='127.0.0.1',
port=3306,
user='******',
passwd='123',
db='raspberry',
charset='utf8')
cur = conn.cursor()
yield cur.execute(
"SELECT count(*) FROM users WHERE login = %s AND password = %s",
(login, password))
if cur.fetchone()[0]:
response = {
'error': False,
'page': open("home.html", "r").read()
}
else:
response = {
'error': True,
}
cur.close()
conn.close()
finally:
self.write(response)
def post(self):
event = self.get_argument('event', '')
if not event:
response = {
'error': True,
'msg': 'Пожалуйста, введите id события.'
}
else:
try:
conn = yield tornado_mysql.connect(host='127.0.0.1', port=3306, user='******', passwd='', db='miemgames', charset='utf8')
cur = conn.cursor()
yield cur.execute("SELECT g.name, g.description, g.picture, e.place, e.time FROM events e, games g WHERE e.game = g.id AND e.id = %s",
(event))
response = []
for i, row in enumerate(cur):
response.append({'name': str(row[0]), 'description': str(row[1]), 'picture': str(row[2]), 'place': str(row[3]), 'time': str(row[4])})
response = {"events": response}
cur.close()
conn.close()
except:
response = {
'error' : True,
'msg' : 'Данного события не существует.'
}
self.write(response)
def on_connection_close(self):
t.cancel()
print 'Finish ', session_id
conn = yield tornado_mysql.connect(host='127.0.0.1', port=3306, user='******', passwd='root', db='drunk_adventures')
cur = conn.cursor()
yield cur.execute("UPDATE avatar SET session = NULL WHERE login = %s; COMMIT;" , (player,) )
cur.close()
del conn
def test_issue_34(self):
try:
yield tornado_mysql.connect(host="localhost", port=1237, user="******")
self.fail()
except tornado_mysql.OperationalError as e:
self.assertEqual(2003, e.args[0])
except Exception:
self.fail()
def db_adj(root, passwd):
conn = yield connect(host=db_config['host'],
charset=db_config['charset'],
user=root,
passwd=passwd)
cur = conn.cursor()
yield cur.execute('DROP USER IF EXISTS %s@%s',
(db_config['user'], db_config['host']))
yield cur.execute(
'CREATE USER %s@%s IDENTIFIED BY %s',
(db_config['user'], db_config['host'], db_config['passwd']))
yield cur.execute('GRANT ALL PRIVILEGES ON * . * TO %s@%s',
(db_config['user'], db_config['host']))
yield cur.execute('FLUSH PRIVILEGES')
cur.close()
conn.commit()
conn.close()
print('USER CREATED!')
conn = yield connect(host=db_config['host'],
charset=db_config['charset'],
user=db_config['user'],
passwd=db_config['passwd'])
cur = conn.cursor()
yield cur.execute('DROP DATABASE IF EXISTS {}'.format(db_config['db']))
yield cur.execute('CREATE DATABASE {}'.format(db_config['db']))
cur.close()
conn.commit()
conn.close()
print('DATABASE CREATED!')
conn = yield connect(**db_config)
cur = conn.cursor()
yield cur.execute(
'CREATE TABLE news (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(30), body VARCHAR(700), d CHAR(8))'
)
yield cur.execute(
'CREATE TABLE users(login VARCHAR(20) PRIMARY KEY, password VARCHAR(65))'
)
cur.close()
conn.commit()
conn.close()
print('TABLES CREATED!')
print('Done!')
print('Now you need to create administrator using `python3 new_admin.py`')
def add_admin(login, password):
hash_pass = hashpw(password, gensalt())
conn = yield connect(**db_config)
cur = conn.cursor()
yield cur.execute(SQL, (login, hash_pass))
cur.close()
conn.commit()
conn.close()
def test_issue_6(self):
""" exception: TypeError: ord() expected a character, but string of length 0 found """
# ToDo: this test requires access to db 'mysql'.
kwargs = self.databases[0].copy()
kwargs['db'] = "mysql"
conn = yield tornado_mysql.connect(**kwargs)
c = conn.cursor()
yield c.execute("select * from user")
yield conn.close_async()
def main():
conn = yield tornado_mysql.connect(host='127.0.0.1', port=3306, user='******', passwd='111111', db='autoops')
cur = conn.cursor()
sql = "SELECT * FROM users"
yield cur.execute(sql)
for row in cur:
print(row)
cur.close()
conn.close()
def get(self,contest_id):
contest_id=int(contest_id)
if contest_id < 1 :
self.redirect_msg('/contests','比赛编号错误')
return
contest_id=norm_page(contest_id)
msg = self.get_argument('msg',None)
conn = yield tornado_mysql.connect(host=conf.DBHOST,\
port=conf.DBPORT,user=conf.DBUSER,passwd=conf.DBPW,db=conf.DBNAME,charset='utf8')
cur = conn.cursor()
sql = "SELECT problems,result,author,visible FROM contests WHERE id = %s LIMIT 1"
yield cur.execute(sql,(contest_id,))
contest=cur.fetchone()
if contest == None:
cur.close()
conn.close()
self.redirect_msg('/contests','比赛编号错误')
return
user=self.current_user
auth = self.auth()
if contest[2].encode('utf-8')!=user and auth < contest[3]:
self.redirect_msg('/contests','权限不足')
return
problems=json.loads(contest[0])
result=json.loads(contest[1])
# 用户名 过题数 罚时 题目[是否通过，提交次数，罚时]
p=[]
sql="SELECT id,name FROM problems WHERE id = %s"
for i in range(len(problems)):
yield cur.execute(sql,(problems[i]))
name=cur.fetchone()[1]
p.append(name)
cur.close()
conn.close()
OI=self.get_argument('OI',None)
if OI:
for i in range(len(result)):
result[i][1]=0
for j in range(len(result[i][3])):
result[i][1]+=result[i][3][j][2]
result=sorted(result,key = lambda x: -x[1]*1000000+x[2])
if OI:
s='_oi'
else:
s=''
self.render('contest_rank%s.html'%s,msg=msg,problems=p,result=result,contest_id=contest_id,\
page_type='contest',page_title='排名版#'+str(contest_id)+' -XOJ')
def test_issue_6(self):
""" exception: TypeError: ord() expected a character, but string of length 0 found """
# ToDo: this test requires access to db 'mysql'.
kwargs = self.databases[0].copy()
kwargs['db'] = "mysql"
conn = yield tornado_mysql.connect(**kwargs)
c = conn.cursor()
yield c.execute("select * from user")
yield conn.close_async()
def main():
conn = yield tornado_mysql.connect(host='127.0.0.1', port=3306, user='******', passwd='', db='mysql')
cur = conn.cursor()
yield cur.execute("SELECT Host,User FROM user")
print(cur.description)
for row in cur:
print(row)
cur.close()
conn.close()
def post(self):
session_id = self.get_argument('session_id','')
player = self.get_argument('u','')
print session_id
conn = yield tornado_mysql.connect(host='127.0.0.1', port=3306, user='******', passwd='root', db='drunk_adventures')
cur = conn.cursor()
yield cur.execute("UPDATE avatar SET session = NULL WHERE login = %s; COMMIT;" , (player,))
cur.close()
del conn
def get(self,contest_id,prob_id):
contest_id=int(contest_id)
prob_id=int(prob_id)
if contest_id < 1 :
self.redirect_msg('/contests','比赛编号错误')
return
contest_id=norm_page(contest_id)
msg = self.get_argument('msg',None)
conn = yield tornado_mysql.connect(host=conf.DBHOST,\
port=conf.DBPORT,user=conf.DBUSER,passwd=conf.DBPW,db=conf.DBNAME,charset='utf8')
cur = conn.cursor()
sql = "SELECT id,problems,end_date,begin_date,author,visible FROM contests WHERE id = %s LIMIT 1"
yield cur.execute(sql,(contest_id,))
contest=cur.fetchone()
if contest == None:
cur.close()
conn.close()
self.redirect_msg('/contests','比赛编号错误')
return
user = self.current_user
auth = self.auth()
if contest[4].encode('utf-8') != user and auth < contest[5]:
self.redirect_msg('/contests','权限不足')
return
if datetime.now() < contest[3]:
self.redirect_msg('/contests','权限不足')
return
if datetime.now() > contest[2]:
self.redirect_msg('/contest/%d'%contest_id,'提交时间已过')
return
problems=json.loads(contest[1])
if prob_id < 0 or prob_id >= len(problems):
self.redirect_msg('/contest/%d'%contest_id,'题目编号配置错误')
return
contest_prob_id=prob_id
prob_id=problems[prob_id]
sql = "SELECT id,tp,name FROM problems WHERE id = %s LIMIT 1"
yield cur.execute(sql,(prob_id,))
problem = cur.fetchone()
cur.close()
conn.close()
if problem == None :
self.redirect_msg('/contest/%d'%contest_id,'题目编号错误')
return
self.render('contest_submit.html',msg=msg,problem=problem,page_type='contest',\
page_title='比赛提交#'+problem[2]+' -XOJ')
def get(self):
conn = yield tornado_mysql.connect(host='127.0.0.1', port=3306, user='******', passwd='', db='miemgames', charset='utf8')
cur = conn.cursor()
yield cur.execute("SELECT e.id, g.name, e.time, g.picture, g.id FROM events e, games g WHERE g.id = e.game")
result = []
for row in cur:
result.append({'id': str(row[0]), 'game': str(row[1]), 'time': str(row[2]), 'picture': str(row[3])})
self.write({"events": result})
cur.close()
conn.close()
def submit_callback(self,response):
if response.error:
conn = yield tornado_mysql.connect(host=conf.DBHOST,\
port=conf.DBPORT,user=conf.DBUSER,passwd=conf.DBPW,db=conf.DBNAME,charset='utf8')
cur = conn.cursor()
sql = "UPDATE judge SET status=%s,result=%s WHERE id = %s LIMIT 1"
yield cur.execute(sql,(9,'####访问评测机时错误\n'+str(response.error),self.j_id,))
yield conn.commit()
cur.close()
conn.close()
def test_issue_34(self):
try:
yield tornado_mysql.connect(host="localhost",
port=1237,
user="******")
self.fail()
except tornado_mysql.OperationalError as e:
self.assertEqual(2003, e.args[0])
except Exception:
self.fail()
def get(self):
conn = yield tornado_mysql.connect(host='127.0.0.1', port=3306, user='******', passwd='', db='miemgames', charset='utf8')
cur = conn.cursor()
yield cur.execute("SELECT * FROM games")
result = []
for row in cur:
result.append({'name': str(row[2]), 'min_players': str(row[3]), 'max_players': str(row[4]), 'description': str(row[1])})
self.write({"games": result})
cur.close()
conn.close()
def test():
conn = yield tornado_mysql.connect(**{'host': 'localhost', 'port': 3306, 'user': '******', 'password': '******'})
with timing('ms', 10) as ti:
for _ in range(30):
cur = conn.cursor(tornado_mysql.cursors.DictCursor)
with timing('ms', 10) as t:
yield cur.execute('SELECT * FROM mysql.user;')
print(t)
print('total:')
print(ti)
def main():
conn = yield tornado_mysql.connect(host="127.0.0.1",
user="******",
passwd="root",
db="bangzai",
port=3306)
cur = conn.cursor()
yield cur.execute("update food set sales=sales+10")
conn.commit()
cur.close()
conn.close()
def setUp(self):
super(TestCTLConnectionSettings, self).setUp()
self.stream.close()
ctl_db = copy.copy(self.database)
ctl_db["db"] = None
ctl_db["port"] = 3307
self.ctl_conn_control = tornado_mysql.connect(**ctl_db)
self.ctl_conn_control.cursor().execute(
"DROP DATABASE IF EXISTS tornado_mysqlreplication_test")
self.ctl_conn_control.cursor().execute(
"CREATE DATABASE tornado_mysqlreplication_test")
self.ctl_conn_control.close()
ctl_db["db"] = "tornado_mysqlreplication_test"
self.ctl_conn_control = tornado_mysql.connect(**ctl_db)
self.stream = BinLogStreamReader(
self.database,
ctl_connection_settings=ctl_db,
server_id=1024,
only_events=(WriteRowsEvent, ),
fail_on_table_metadata_unavailable=True)
def post(self,contest_id):
contest_id=int(contest_id)
if contest_id < 1 :
self.redirect_msg('/contests','比赛编号错误')
return
contest_id=norm_page(contest_id)
conn = yield tornado_mysql.connect(host=conf.DBHOST,\
port=conf.DBPORT,user=conf.DBUSER,passwd=conf.DBPW,db=conf.DBNAME,charset='utf8')
cur = conn.cursor()
user=self.current_user
auth = self.auth()
sql = "SELECT id,author FROM contests WHERE id = %s LIMIT 1"
yield cur.execute(sql,(contest_id,))
contest=cur.fetchone()
if contest == None:
cur.close()
conn.close()
self.redirect_msg('/contests','比赛编号错误')
return
if contest[1].encode('utf-8')!=user and auth < 250:
self.redirect_msg('/contests','权限不足')
return
p=[self.get_argument(s) for s in ['name','info','content','problems','begin_date','end_date','author','visible','status'] ]
conn = yield tornado_mysql.connect(host=conf.DBHOST,\
port=conf.DBPORT,user=conf.DBUSER,passwd=conf.DBPW,db=conf.DBNAME,charset='utf8')
cur = conn.cursor()
sql = "UPDATE contests SET name=%s,info=%s,content=%s,problems=%s,begin_date=%s,end_date=%s,author=%s,visible=%s,status=%s WHERE id = %s"
try:
yield cur.execute(sql,(p[0],p[1],p[2],p[3],p[4],p[5],p[6],p[7],p[8],contest_id))
yield conn.commit()
except BaseException as e:
self.redirect_msg('/contest/%d/edit'%contest_id,'数据库错误')
else:
self.redirect_msg('/contest/%d/edit'%contest_id,'编辑比赛成功')
finally:
cur.close()
conn.close()
def main():
conn = yield tornado_mysql.connect(host='127.0.0.1',
port=3306,
user='******',
password='******',
db='mysql')
cur = conn.cursor()
yield cur.execute("SELECT Host,User FROM user")
for row in cur:
print(row)
cur.close()
conn.close()
def db_adj():
#############################################################
# Если этот блок не сработал, то нужно создать пользователя #
# вручную и, закоментировав блок, запустить скрипт ещё раз #
#############################################################
# conn = yield connect(host=db_config['host'], charset=db_config['charset'], user=root, passwd=passwd)
# cur = conn.cursor()
# yield cur.execute('DROP USER IF EXISTS %s@%s', (db_config['user'], host))
# yield cur.execute('CREATE USER %s@%s IDENTIFIED BY %s', (db_config['user'], host, db_config['passwd']))
# yield cur.execute('GRANT ALL PRIVILEGES ON * . * TO %s@%s', (db_config['user'], host))
# yield cur.execute('FLUSH PRIVILEGES')
# cur.close()
# conn.commit()
# conn.close()
# print('USER CREATED!')
#############################################################
conn = yield connect(host=db_config['host'],
charset=db_config['charset'],
user=db_config['user'],
passwd=db_config['passwd'])
cur = conn.cursor()
yield cur.execute('DROP DATABASE IF EXISTS {}'.format(db_config['db']))
yield cur.execute('CREATE DATABASE {}'.format(db_config['db']))
cur.close()
conn.commit()
conn.close()
print('DATABASE CREATED!')
conn = yield connect(**db_config)
cur = conn.cursor()
yield cur.execute(
'CREATE TABLE users(login VARCHAR(20) PRIMARY KEY, password VARCHAR(65), balance INT, task BIGINT)'
)
cur.close()
conn.commit()
conn.close()
print('TABLES CREATED!')
print('Done!')
def mysql(sql, params=""):
conn = yield tornado_mysql.connect(host=options.host,
port=options.port,
user=options.user,
passwd=options.passwd,
db=options.db,
charset=options.charset)
cur = conn.cursor(cursors.DictCursor)
yield cur.execute(sql % params)
conn.commit()
rows = cur
cur.close()
conn.close()
return rows
def get_user(user):
conn = yield tornado_mysql.connect(host='127.0.0.1',
port=3306,
user='******',
passwd='123',
db='cmdb',
charset='utf8')
cur = conn.cursor()
# yield cur.execute("SELECT name,email FROM web_models_userprofile where name=%s", (user,))
yield cur.execute("select sleep(10)")
row = cur.fetchone()
cur.close()
conn.close()
raise gen.Return(row)
def main():
conn = yield tornado_mysql.connect(host='192.168.1.155',
port=3306,
charset='utf8',
user='******',
passwd='1qazxsw2!@',
db='wpnbmdb')
cur = conn.cursor()
yield cur.execute("SELECT * from zs_yjsf")
print(cur.description)
for row in cur:
print(row)
cur.close()
conn.close()
def gen_notice_statistics():
"""
查询历史notice，并且统计命中tag，存入notice_stat数据库表，生成风险名单报表
"""
logger.info('开始统计风险名单')
start_time = int(settings.Working_TS) * 1000
end_time = start_time + 60 * 60 * 1000
strategies_weigh = utils.Strategies_Weigh
try:
# 初始化数据库连接
conn = yield tornado_mysql.connect(**config)
cursor = conn.cursor()
query_params = ','.join(QUERY_NOTICE_PARAMS)
insert_values = []
yield cursor.execute(
NOTICE_QUERY_STRING %
(query_params, start_time, end_time, query_params))
for _ in cursor:
# 将查询结果解析为notice dict，并且根据命中策略名查询命中tag
notice = parser_notice(*_)
notice['timestamp'] = start_time
notice['last_modified'] = millis_now()
if notice['strategy_name'] in strategies_weigh:
tags = strategies_weigh.get(notice['strategy_name'],
{}).get('tags', [])
# 将每一个命中tag和统计后的notice组合存入数据库
for tag in tags:
notice['tag'] = utf8(tag)
insert_values.append(
[notice[p] for p in INSERT_NOTICE_PARAMS])
# 避免重复插入数据，需要先删除该时段数据，重新插入
yield cursor.execute(NOTICE_DELETE_STRING % start_time)
yield cursor.executemany(NOTICE_INSERT_STRING, insert_values)
# 提交，不然无法保存新建数据
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
logger.info('风险名单统计完成')
except Exception as e:
logger.error(e)
logger.error('风险名单统计失败')
def test_issue_114(self):
""" autocommit is not set after reconnecting with ping() """
conn = yield tornado_mysql.connect(charset="utf8", **self.databases[0])
yield conn.autocommit(False)
c = conn.cursor()
yield c.execute("""select @@autocommit;""")
self.assertFalse(c.fetchone()[0])
yield conn.close_async()
yield conn.ping()
yield c.execute("""select @@autocommit;""")
self.assertFalse(c.fetchone()[0])
yield conn.close_async()
# Ensure autocommit() is still working
conn = yield tornado_mysql.connect(charset="utf8", **self.databases[0])
c = conn.cursor()
yield c.execute("""select @@autocommit;""")
self.assertFalse(c.fetchone()[0])
yield conn.close_async()
yield conn.ping()
yield conn.autocommit(True)
yield c.execute("""select @@autocommit;""")
self.assertTrue(c.fetchone()[0])
yield conn.close_async()
def test_issue_33(self):
conn = yield tornado_mysql.connect(charset="utf8", **self.databases[0])
c = conn.cursor()
with warnings.catch_warnings():
warnings.simplefilter("ignore")
yield c.execute(
b"drop table if exists hei\xc3\x9fe".decode("utf8"))
try:
yield c.execute(
b"create table hei\xc3\x9fe (name varchar(32))".decode("utf8"))
yield c.execute(
b"insert into hei\xc3\x9fe (name) values ('Pi\xc3\xb1ata')".
decode("utf8"))
yield c.execute(b"select name from hei\xc3\x9fe".decode("utf8"))
self.assertEqual(b"Pi\xc3\xb1ata".decode("utf8"), c.fetchone()[0])
finally:
yield c.execute(b"drop table hei\xc3\x9fe".decode("utf8"))
def get(self):
conn = yield tornado_mysql.connect(host='127.0.0.1',
port=3306,
user='******',
passwd='',
db='word_tornado')
cur = conn.cursor()
# yield cur.execute("SELECT Host,User FROM user")
# print(cur.description)
# for row in cur:
# print(row)
cur.close()
conn.close()
self.write('<html><body><form action="/" method="POST">'
'<input type="text" name="message">'
'<input type="submit" value="Submit">'
'</form></body></html>')
def _get_connection(self):
setting = _db_settings.get(self.target)
conn = None
if not USE_POOL:
conn = yield tornado_mysql.connect(host=setting.get('host'),
port=setting.get('port'),
user=setting.get('user'),
passwd=setting.get('password'),
db=setting.get('db'),
charset=setting.get('charset'))
else:
conn = pools.Pool(dict(host=setting.get('host'),
port=setting.get('port'),
user=setting.get('user'),
passwd=setting.get('password'),
db=setting.get('db'),
charset=setting.get('charset')),
max_idle_connections=1,
max_recycle_sec=3)
return conn
def main():
conn = yield tornado_mysql.connect(host="127.0.0.1",
user="******",
passwd="root",
db="bangzai",
port=3306)
cur = conn.cursor()
curtime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
yield cur.execute(
"update user_order set ostatus=4,remark_time=%s where ostatus=3",
(curtime, ))
yield cur.execute(
"update user_order set ostatus=3,rec_time=%s where ostatus=2",
(curtime, ))
yield cur.execute(
"update user_order set ostatus=2,jd_time=%s where ostatus=1",
(curtime, ))
conn.commit()
cur.close()
conn.close()
def test_example(self):
conn = tornado_mysql.connect(host='127.0.0.1', port=3306, user='******', passwd='', db='mysql')
cur = conn.cursor()
cur.execute("SELECT Host,User FROM user")
# print cur.description
# r = cur.fetchall()
# print r
# ...or...
u = False
for r in cur.fetchall():
u = u or conn.user in r
self.assertTrue(u)
cur.close()
conn.close()
def main():
conn =yield tornado_mysql.connect(host="127.0.0.1", user="******", passwd="root", db="bangzai", port=3306)
cur = conn.cursor()
#curtime=time.strftime("%Y-%m-%d %X",time.localtime())
#curtime=time.localtime()
yield cur.execute("select start_time,order_id from user_order where ostatus=1")
orders=cur.fetchall()
for order in orders:
seconds=(datetime.now()-order[0]).seconds
if seconds>1800:
#print order[0]
yield cur.execute("select food_id,amount from food_amount where order_id=%s",
(
order[1]
)
)
foods = cur.fetchall()
for food in foods:
food_id = food[0]
amount = food[1]
yield cur.execute("UPDATE food SET surplus=surplus+%s WHERE food_id=%s",
(
amount,
food_id,
)
)
yield cur.execute("DELETE FROM food_amount WHERE order_id=%s",
(
order[1],
)
)
yield cur.execute("delete from user_order where order_id=%s",
(
order[1]
)
)
conn.commit()
cur.close()
conn.close()
def main():
conn = yield tornado_mysql.connect(host="127.0.0.1",
user="******",
passwd="root",
db="bangzai",
port=3306)
cur = conn.cursor()
#curtime=time.strftime("%Y-%m-%d %X",time.localtime())
#curtime=time.localtime()
yield cur.execute(
"select start_time,order_id from user_order where ostatus=2")
orders = cur.fetchall()
for order in orders:
days = (datetime.now() - order[0]).days
print days
if days > 1:
yield cur.execute(
"update user_order set ostatus=3,rec_time=%s where order_id=%s",
(datetime.now(), order[1]))
conn.commit()
cur.close()
conn.close()
def Execute(target, sql, params):
setting = _db_settings.get(target)
conn = None
if not USE_POOL:
conn = yield tornado_mysql.connect(host=setting.get('host'),
port=setting.get('port'),
user=setting.get('user'),
passwd=setting.get('password'),
db=setting.get('db'),
charset=setting.get('charset'))
else:
conn = pools.Pool(dict(host=setting.get('host'),
port=setting.get('port'),
user=setting.get('user'),
passwd=setting.get('password'),
db=setting.get('db'),
charset=setting.get('charset')),
max_idle_connections=1,
max_recycle_sec=3)
result = 0
if USE_POOL:
cur = yield conn.execute(sql, params)
result = cur.rowcount
else:
cur = conn.cursor()
try:
result = yield cur.execute(sql, params)
yield conn.commit()
except:
raise
finally:
cur.close()
conn.close()
return result
def Select(target, sql, params):
setting = _db_settings.get(target)
conn = None
if not USE_POOL:
conn = yield tornado_mysql.connect(host=setting.get('host'),
port=setting.get('port'),
user=setting.get('user'),
passwd=setting.get('password'),
db=setting.get('db'),
charset=setting.get('charset'))
else:
conn = pools.Pool(dict(host=setting.get('host'),
port=setting.get('port'),
user=setting.get('user'),
passwd=setting.get('password'),
db=setting.get('db'),
charset=setting.get('charset')),
max_idle_connections=1,
max_recycle_sec=3)
if USE_POOL:
cur = yield conn.execute(sql, params)
else:
cur = conn.cursor()
try:
yield cur.execute(sql, params)
except:
raise
finally:
cur.close()
conn.close()
rows = cur.fetchall()
column_names = [d[0] for d in cur.description]
result = [Row(zip(column_names, row)) for row in rows]
return result
def get(self):
key_word = self.get_argument('key', '').encode('utf-8')
pn = self._check_argument_pn(self.get_argument('pn', 1))
ex = self.get_argument('ex', '')
fid = self.__check_fid(self.get_argument('fid', 'all').encode('utf-8'))
if len(key_word) < 2:
self.write('关键字长度必须大于两个字符!!!')
return
# sphinx client
cl = AstostSphinxClient()
cl.set_filter_fid(ALL_MUSIC)
if ex != 'on':
cl.open_ex(False)
if fid != 'all':
cl.set_filter_fid(fid_param_to_str.get(fid, 0x00))
res = yield cl.search(key_word, (pn - 1) * 10)
if not res:
self.write('Something maybe wrong, Please wait some seconds!')
return
# result need
result = {
'key_word': key_word,
'pn': pn,
'fid': fid,
'count': res['total_found'],
'time': res['time']
}
result_items = None
if 'matches' in res.keys() and res['total'] > 0:
tids = [str(match['id']) for match in res['matches']
] if 'matches' in res.keys() else None
result_items = []
if len(tids) > 0:
tid_sql_str = ','.join(tids)
conn = yield tornado_mysql.connect(host=SQL_HOST,
port=SQL_PORT,
user=SQL_USER,
passwd=SQL_PWD,
db=DB,
charset='utf8')
cur = conn.cursor()
yield cur.execute(sql_cmd % (tid_sql_str, tid_sql_str))
for row in cur:
row = [
row_item.encode('utf-8') if isinstance(
row_item, unicode) else row_item
for row_item in row
]
row_excerpts = [row[1], row[6]]
row_excerpts[1] = self.__re_html.sub('', row_excerpts[1])
row_excerpts = cl.build_excerpts(row_excerpts, 'astost',
key_word, excerpt_opts)
result_item = {
'tid': row[0],
'title': row_excerpts[0] + '_' + row[2],
'time': row[5],
'abstract': row_excerpts[1],
'fid_str': row[2],
'fid_num': fid_str_nu.get(row[2], ''),
'uid': row[3],
'user': row[4]
}
result_items.append(result_item)
cur.close()
conn.close()
self.render('result.html', result=result, result_items=result_items)
def cursor(self):
if not self.__conn:
self.__conn = yield connect(host='127.0.0.1', port=3306, user='******', passwd='root', db='web_graph',
use_unicode=True, autocommit=True, charset='utf8')
raise tornado.gen.Return(self.__conn.cursor())