def saveDB():
'''
存储到数据库中
:return:
'''
for username, info in RESULT.items():
sql = "SELECT {} FROM 51reboot_users WHERE name = '{}';".format(
','.join(FIELDS), username)
print(sql)
resp, ok = select(sql) # select需要一个单独的判断是否为空的情况
if not ok:
fields_string = ','.join(FIELDS)
values_string = "'{}', {}, '{}', '{}'".format(
info['name'], info['age'], info['tel'], info['email'])
sql = '''INSERT INTO 51reboot_users({}) VALUES({})'''.format(
fields_string, values_string)
print(sql)
resp, ok = insert(sql)
if ok:
print('username: {} save succ.'.format(username))
else:
print('username: {} save fail.'.format(username))
else:
print('username: {} already exists.'.format(username))
def get_pageinfo(userinfo):
'''
display page 2 pagesize 5
:param args: page 2 pagesize 5 ;default pagesize = 5
page 1 -> 0-4
切片
slice
'''
if len(userinfo) == 2:
if userinfo[0] != 'page':
return "syntax error."
pagesize = 5
elif len(userinfo) == 4:
if userinfo[0] != 'page' or userinfo[-2] != 'pagesize':
return "syntax error."
pagesize = int(userinfo[-1])
else:
return "syntax error."
sql = '''select * from users;'''
result, ok = select(sql)
if not ok:
print(result)
else:
page_value = int(userinfo[1]) - 1 # 1
# 总数据长度
data_length = len(result)
# 计算出最大可以输入的页码
max_page_number = data_length // pagesize + 1 if data_length % pagesize else data_length // pagesize
start = page_value * pagesize
end = start + pagesize
# 0:5
# 5:10
# 输入的页码过大时打印提示，输出最后一页内容
if page_value < max_page_number:
xtb = PrettyTable()
xtb.field_names = FIELDS
for index in result[start:end]:
xtb.add_row(index)
return (xtb)
else:
return "\033[5;31mQuery data does not exist.\n"
# def csv_export():
# userid_sort = sorted(RESULT['userid'].items(), key=lambda x: x[1])
#
# with open('export.csv', 'w', newline="") as csvfile:
# fieldnames = FIELDS
# writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
# writer.writeheader()
#
# for i in userid_sort:
# get_id = str(i[1])
# RESULT['userinfo'][get_id].update({'id': str(i[1])})
# writer.writerow(RESULT['userinfo'].get(get_id))
# return "\033[5;32mExport Succeed.\033[0m\n"
def load(self):
# sql_select = '''select username,age,tel,email from users;'''
sql_select = "select {} from users".format(','.join(FIELDS))
rows, ok = dbutils.select(sql_select)
if ok:
# global RESULT
RESULT = { x[0] : {FIELDS[0] : x[0] ,FIELDS[1] :x[1] ,FIELDS[2] : x[2],FIELDS[3] : x[3] }for x in rows}
return RESULT
def select(self):
sql_sel = '''select username,age,tel,email from users;'''
rows = dbutils.select(sql_sel)
for i in rows:
b = dict(zip(FIELDS, i))
RESULT[i[0]] = b
# print(RESULT)
return RESULT
def checkAuth(request, trancode):
userid = request.session[u'ID_USER']
user = models.UserInfo.objects.get(userid = userid)
group = user.group
grouplist = u'(' + u','.join(["'" + group[2 * i: 2 * i + 2] + "'" for i in xrange(len(group) / 2)]) + u')'
result = False
sql = 'select trancode from mysite_GroupAuth where groupid in %s and trancode = %s ' % (grouplist, "'%s'" % trancode)
queryset = dbutils.select(sql)
if queryset.rownum > 0:
result = True
return result
def get_list():
sql = '''select * from users;'''
result, ok = select(sql)
if not ok:
print(result)
else:
ltab = PrettyTable()
ltab.field_names = FIELDS
for i in result:
ltab.add_row(i)
print(ltab)
def load():
fields = ['username', 'age', 'tel', 'email']
sql = ''' select * from users'''
result, ok = dbutils.select(sql)
if not ok:
msg = 'result:%s' % result
else:
data_dic = {}
# print(result, type(result))
for i in result:
data_dic[i[1]] = dict(zip(fields, i[1:]))
return data_dic
def loadDB():
'''
从数据库中加载到内存中
:return:
'''
sql = "SELECT {} FROM 51reboot_users;".format(','.join(FIELDS))
print(sql)
resp, ok = select(sql)
if ok:
global RESULT
RESULT = [dict(zip(FIELDS, x)) for x in resp]
else:
print('Load from db fail.')
def clean_message(self, ):
group = self.cleaned_data.get('group', u'')
# raise Exception(str(self.cleaned_data))
if group is None or group == u'' or len(group) % 2 != 0:
raise FormException(u'the length of group must be "\d{2}"')
validgroup = dbutils.select(
u'select distinct groupid from mysite_groupauth')
for i in xrange(len(group) / 2):
g = group[2 * i:2 * i + 2]
if g not in validgroup:
raise FormException(u"no group %s" % g)
return
return True
def listsongs(bot, update, dbfile):
bot.send_chat_action(chat_id=update.message.chat_id, action=ChatAction.TYPING)
try:
songs = dbutils.select("SELECT * FROM songs", dbfile)
nsongs = len(songs)
bot.sendMessage(chat_id=update.message.chat_id, text="There's {} songs saved, here's the list:".format(nsongs))
bot.send_chat_action(chat_id=update.message.chat_id, action=ChatAction.TYPING)
for mim in songs:
mylist = mim[0] + mim[1] + "\n"
bot.sendMessage(chat_id=update.message.chat_id, text="".format(mylist))
except Error as e:
print(e)
bot.sendMessage(chat_id=update.message.chat_id, text="".format(e))
return False
def gettango(bot, update, dbfile):
# Expecting string in the form: "Tango curl 1337"
mail = update.message.text[11:]
try:
if mail.isdigit():
bot.sendMessage(chat_id=update.message.chat_id, text="Alan! Not implemented yet! Only full list for now :)")
else:
query = "SELECT * FROM songs;"
tangolist = dbutils.select(query, dbfile)
with open("./wubbalubbadubdub.csv", "wb") as tangofile:
for row in tangolist:
wrow = " ".join(row)
tangofile.write(wrow)
bot.send_document(chat_id=update.message.chat_id, document=open('./wubbalubbadubdub.csv', 'rb'))
except Error as e:
print(e)
bot.sendMessage(chat_id=update.message.chat_id, text="Action requested is Alan \n{}".format(e))
def find_info(userinfo):
while len(userinfo) == 1:
sql = '''select * from users where username='******';'''.format(
userinfo[0])
findMsg, ok = select(sql)
if not ok:
return (findMsg)
else:
ltab = PrettyTable()
ltab.field_names = FIELDS
for i in findMsg:
ltab.add_row(i)
return (ltab)
# else:
# return ("\033[1;31;43m{} does not exist!\033[0m\n".format(info_list[1]))
else:
return (
"\033[1;31mInput Error！\033[0m\n\033[5;33;42mUsage: find [ {} ]\033[0m\n"
.format(FIELDS[1]))