async def rps(ctx, player_choice):
"""Play rock, paper, scissors against Botboy (eg. !rps r)"""
# See if player is already in database. If not, create their entry.
member = str(ctx.message.author)
query = sql.select(rps_table, condition={"DiscordName":member})
# Store all results from query in a list of tuples
results = c.execute(query).fetchall()
if len(results) == 0:
# Create entry
default_values = [member,0,0,0]
c.execute(sql.insert(rps_table, default_values))
# Result matrix - columns: player's choice / rows: bot's choice
result_matrix = [['draw','user','bot'],['bot','draw','user'],['user','bot','draw']]
choices = ['rock','paper','scissors']
alt_choices = ['r','p','s']
# Bot makes a choice
bot_choice_index = random.randint(0,2)
log.debug("rps - player input: {0}".format(player_choice))
log.debug("rps - bot's random number: {0}".format(bot_choice_index))
# Try getting index from players choice
try:
player_choice_index = choices.index(player_choice)
except:
try:
player_choice_index = alt_choices.index(player_choice)
except:
await ctx.send("ERROR: must enter 'rock' (or r), 'paper' (or p), 'scissors' (or s)")
return
# Determine result from matrix
result = result_matrix[bot_choice_index][player_choice_index]
column_update = {}
if result == "user":
winner = "You win!"
column_update = {"Wins":"Wins+1"}
elif result == "draw":
winner = "It's a draw ¯\_(ツ)_/¯"
column_update = {"Draws":"Draws+1"}
elif result == "bot":
winner = "Better luck next time!"
column_update = {"Losses":"Losses+1"}
# Update database
c.execute(sql.update(rps_table, column_update, {"DiscordName":member}))
# Create response
response = "You chose: {0} \nBotBoy chose: {1} \n{2}".format(choices[player_choice_index], choices[bot_choice_index], winner)
query = sql.select(rps_table, condition={"DiscordName":member})
results = c.execute(query).fetchone()
wins = results[1]
draws = results[2]
losses = results[3]
win_percentage = (wins/(wins+draws+losses))*100
response += "\nWins: " + str(wins) + "\nDraws: " + str(draws) + "\nLosses: " + str(losses) + "\nWin %: " + str(win_percentage) + "%"
# Say it
await ctx.send(response)
conn.commit()
def CG1():
t1 = table.Table("data/posts.xml")
t2 = select(t1, Condition("PostTypeId","==", 1), ["Id","OwnerUserId"])
t3 = select(t1, Condition("PostTypeId","==", 2), ["ParentId","OwnerUserId"])
t4 = join(t2,t3,[("Id","ParentId")])
t5 = group(t4, ["OwnerUserId1", "OwnerUserId2"], "Count", "cnt")
g = graph.Graph("directed")
g.addNodes(t1,"OwnerUserId")
g.addEdges(t5,"OwnerUserId1","OwnerUserId2",["Count"])
return g
def CG3():
t1 = table.Table("data/posts.xml") #["Id","PostTypeId","OwnerUserId",...]
t2 = table.Table("data/comments.xml") #["PostId","UserId","Text",...]
t3 = select(t1, attributes=["Id","OwnerUserId"])
t4 = select(t2, attributes=["PostId","UserId"])
t5 = join(t4,t3,[("PostId","Id")])
g = graph.Graph("multiedge")
g.addNodes(t1,"OwnerUserId")
g.addNodes(t2,"UserId")
g.addEdges(t5,"UserId","OwnerUserId")
return g
def CG9():
t1 = table.Table("data/badges.xml") #[UserId","Name"...]
t2 = table.Table("data/posts.xml") #["Id","PostTypeId","OwnerUserId",...]
t3 = join(t1,t2,["UserId"],["OwnerUserId"],["Name"],["PostTypeId","ParentId","Id"])
t4 = select(t3, Condition("PostTypeId", "==", 1)) #Questions
t5 = select(t3, Condition("PostTypeId", "==", 2)) #Answers
t6 = join(t5,t4,["ParentId"],["Id"],["Name"],["Name"],["Id","Badge1","Badge2"])
t7 = group(t6,["Badge1","Badge2"],"Count","cnt")
g = graph.Graph("directed")
g.addNodes(t1,"Name")
g.addEdges(t7,"Badge1","Badge2",["Count"])
return g
def CG2():
t1 = table.Table("data/posts.xml") #["Id","PostTypeId","OwnerUserId",...]
t2 = table.Table("data/votes.xml") #["PostId","VoteTypeId","UserId",...]
t3 = select(t1, attributes=["Id","OwnerUserId"])
t4 = select(t2, Condition("VoteTypeId", "==", 2),["PostId","UserId"])
t5 = join(t4,t3,[("PostId","Id")])
t6 = group(t5,["UserId","OwnerUserId"], "Count","cnt")
g = graph.Graph("directed")
g.addNodes(t1,"OwnerUserId")
g.addNodes(t2,"UserId")
g.addEdges(t6,"UserId","OwnerUserId",["Count"])
return g
def total_commoditys(company_name):
try:
total = list()
# 打开数据库连接
db = pymysql.connect("localhost", "root", "wujiahao.", "flaskTest", charset='utf8')
# 使用cursor()方法获取操作游标
cursor = db.cursor()
key = ["id", "name"]
condition = dict()
query = sql.select("commodity", key, condition , 0)
cursor.execute(query)
commodities = cursor.fetchall()
detail = list()
for commodity in commodities:
dp = per_commoditys(company_name, commodity[0])
result = dict()
if dp[0]:
result["productId"] = commodity[0]
result["productName"] = commodity[1]
result["detail"] = dp[2]["detail"]
result["pdf"] = dp[2]["pdf"]
total.append(result)
db.close()
return total
except:
return False, "获取商家详细信息失败", "null"
def dns(request, *args, **kwargs):
cursor = connection.cursor()
date = dt.now()
query = " SELECT p.user_id, d.ip FROM provision_ports p, provision_devices d WHERE p.device_id = d.id AND p.user_id IS NOT NULL AND p.user_id not LIKE '%%l%%' order by d.ip"
names = select(query, ('name', 'ip'))
cursor.close()
return render('dns.zone', {'names': names.objects, 'date': date, 'serial': ("%02d" % ((date.hour*date.minute+15)/15)) }, mimetype='text/plain')
def company_name2vendor_id(company_name):
try:
# 打开数据库连接
db = pymysql.connect("localhost",
"root",
"wujiahao.",
"flaskTest",
charset='utf8')
# 使用cursor()方法获取操作游标
cursor = db.cursor()
result = {}
key = ['id', 'username']
condition = dict()
condition['company_name'] = company_name
query = sql.select("vendor", key, condition, 0)
if cursor.execute(query):
info = cursor.fetchone()
result["id"] = info[0]
result["company_name"] = company_name
db.close()
return result
except:
return "无法连接数据库"
def commodity_id2commodity_name(id):
try:
# 打开数据库连接
db = pymysql.connect("localhost",
"root",
"wujiahao.",
"flaskTest",
charset='utf8')
# 使用cursor()方法获取操作游标
cursor = db.cursor()
result = dict()
key = ['id', 'name']
condition = dict()
condition['id'] = id
query = sql.select("commodity", key, condition, 0)
if cursor.execute(query):
info = cursor.fetchone()
result['id'] = info[0]
result['name'] = info[1]
db.close()
return result
except:
return "无法连接数据库"
def commodity_id2company_name(id):
try:
# 打开数据库连接
db = pymysql.connect("localhost",
"root",
"wujiahao.",
"flaskTest",
charset='utf8')
# 使用cursor()方法获取操作游标
cursor = db.cursor()
stores = []
key = ['company']
condition = dict()
condition['commodity_id'] = id
query = sql.select("provide", key, condition, 0)
if cursor.execute(query):
info = cursor.fetchall()
for row in info:
stores.append(row[0])
db.close()
stores = list(set(stores))
return stores
except:
return "无法连接数据库"
def wrp(MainWindow):
setupUi(MainWindow)
ui.book_search.textChanged.connect(filter_table)
ui.lib_table.clicked.connect(
lambda x: fill_book_form(get_selected_info()))
ui.cmd_read_book.clicked.connect(
lambda x: read_book(get_selected_info()))
ui.cmd_read_in_notepad.clicked.connect(
lambda x: read_with_notepad(get_selected_info()))
ui.cmd_add_book.clicked.connect(show_add_book_dialog)
ui.cmd_edit_book.clicked.connect(
lambda x: edit_book_info(get_book_form()))
ui.cmd_delete_book.clicked.connect(
lambda x: delete_book(get_selected_id()))
update_book_table()
for author in sql.select("authors"):
ui.cmb_author.addItem(author['name'] + ' ' + author['surname'],
author['author_id'])
ui.cmb_author.setCurrentIndex(-1)
ui.book_readers = list()
def get_spec(company_name,commodity_id):
try:
result = dict()
# 打开数据库连接
db = pymysql.connect("localhost", "root", "wujiahao.", "flaskTest", charset='utf8')
# 使用cursor()方法获取操作游标
cursor = db.cursor()
key = ["spec", "pdf_path"]
condition = dict()
condition["company"] = company_name
condition["commodity_id"] = commodity_id
query = sql.select("provide", key, condition, 0)
if cursor.execute(query):
data = cursor.fetchall()
specs = []
for row in data:
specs.append(row[0])
result['pdf_path'] = data[0][1]
result['specs'] = specs
db.close()
return True, "获取支数和色卡成功", result
else:
db.close()
return False, "商品不存在", "null"
except:
return False, "获取支数和色卡失败", "null"
def get_price(company_name,commodity_id,spec,number):
try:
result = dict()
# 打开数据库连接
db = pymysql.connect("localhost", "root", "wujiahao.", "flaskTest", charset='utf8')
# 使用cursor()方法获取操作游标
cursor = db.cursor()
key = ["price"]
condition = dict()
condition["company"] = company_name
condition["commodity_id"] = commodity_id
condition["spec"] = spec
query = sql.select("provide", key, condition, 0)
if cursor.execute(query):
data = cursor.fetchone()
result['price'] = float(data[0])*float(number)
db.close()
return True, "获取特定产品信息成功", result
else:
db.close()
return False, "商品不存在", "null"
except:
return False, "获取价格失败", "null"
async def rps_rank(ctx):
"""Rank rps players by # of wins"""
query = sql.select(rps_table, order="Wins DESC")
#response = "{0:25} {1:4} {2:4} {3:4} {4:6}".format('Name', 'Wins', 'Draws', 'Losses', 'Win%')
# Create a list of lists that is Name Wins Draws Losses Win%
stats = [[],[],[]]
for row in c.execute(query):
wins = row[1]
draws = row[2]
losses = row[3]
# Populate each sublist with the appropriate value
stats[0].append(str(row[0]))
scores = [str(wins),str(draws),str(losses)]
stats[1].append(" / ".join(scores))
win_percentage = (wins/(wins+draws+losses))*100
# Append win percentage to last entry (because it's last)
stats[-1].append(str(win_percentage))
#response += "\n{0:25} {1:4} {2:4} {3:4} {4:6}".format(name, wins, draws, losses, win_percentage)
em = discord.Embed(title="Rock Paper Scissors Leaderboard", colour=0x800020)
em.add_field(name="Name", value='\n'.join(stats[0]), inline=True)
em.add_field(name="W / D / L", value='\n'.join(stats[1]), inline=True)
em.add_field(name="Win %", value='\n'.join(stats[-1]), inline=True)
await bot.send_message(ctx.message.channel, embed=em)
def remove(o):
SQL = "SELECT * FROM deleteorder WHERE ordernumber = %s"
data = (o, )
rows = sql.select(SQL, data)
for row in rows:
orderNumber = row[0]
notice = row[1]
status = row[2]
directory = row[3]
folder = os.path.join(directory, str(orderNumber))
print('Order {order} [{notice}] has the status {status}'.format(
order=orderNumber, notice=notice, status=status))
question = 'Are you sure you want to delete this order at {d}?'.format(
d=folder)
decision = utilities.query_yes_no(question, default="yes")
if decision == 'yes':
utilities.deletefiles(folder)
utilities.deletefolder(folder)
if not os.path.exists(folder):
sql.setOrderStatus(orderNumber, 'DELETED')
elif decision == 'no':
print('Nothing will be delete.')
else:
print('ERROR: {d} not a local folder.'.format(d=folder))
print('INFO: Are you on the right computer?')
exit()
async def ow_add(ctx, battle_tag, member : discord.Member = None):
"""Add an Overwatch player to the database (e.g. !ow_add JeffKaplan#420 @JeffKaplan)"""
if member is None:
member = ctx.message.author
log.debug(type(member))
log.debug(discord.Member)
await bot.send_typing(ctx.message.channel)
# print(type(member))
# print(discord.Member)
if type(member) is not discord.Member:
await ctx.send("ERROR: @mention the user instead of just typing it")
return
# See if the battle_tag is already in the db
query = sql.select(overwatch_table, column_names=['BattleTag', 'DiscordName'], condition={'BattleTag':battle_tag})
if len((c.execute(query)).fetchall()) is not 0:
await ctx.send("Account " + battle_tag + " already in list!")
return
sr = await owh.get_sr(battle_tag)
if sr == None:
await ctx.send("Account " + battle_tag + " doesn't exist!")
return
query = sql.insert(overwatch_table, [battle_tag, sr, str(member)])
#query = "INSERT INTO " + overwatch_table + " VALUES('" + battle_tag + "', '" + str(member) + "')"
#print(query)
c.execute(query)
await ctx.send("Added " + battle_tag + " with discord member @" + str(member))
conn.commit()
def loadxml(xmlfile, orderNumber):
print('INFO: Loading XML Manifest file', str(xmlfile),
'into table images')
tree = ET.parse(xmlfile)
root = tree.getroot()
comment = root.find('comment').text
total_files = int(root.find('total_files').text)
for lineitem in root.findall('./line_item'):
try:
noaaid = lineitem.get('id')
file_name = lineitem.find('item/file_name').text
file_size = lineitem.find('item/file_size').text
creation_date = lineitem.find('item/creation_date').text
creation_date = datetime.strptime(creation_date,
"%Y-%m-%dT%H:%M:%SZ")
expiration_date = lineitem.find('item/expiration_date').text
expiration_date = datetime.strptime(expiration_date,
"%Y-%m-%dT%H:%M:%SZ")
except:
print('ERROR: Cannot read all values in Manifest',
str(xmlfile))
try:
checksum = lineitem.find('item/checksum').text
except:
print('ERROR: Manifest at', str(xmlfile),
'does not include checksum')
checksum = None
print(
'INFO: Loading data to database table images:\
', noaaid, file_name, file_size, creation_date,
expiration_date, checksum)
SQL = "INSERT INTO images (manifest,file_name,checksum,ordernumber,ordercreated,orderexpiration,status,file_size,noaaid) VALUES (%s,%s,%s,%s,TIMESTAMP %s,TIMESTAMP %s,%s,%s,%s);" # Note: no quotes
data = (os.path.basename(xmlfile), file_name, checksum,
orderNumber, creation_date, expiration_date, 'NEW',
file_size, noaaid)
try:
sql.insert(SQL, data)
print('insert')
except:
print(
'ERROR: Information for this image and order already present?'
)
SQL = "UPDATE orders set notice = %s, manifesttotal = %s WHERE ordernumber = %s"
data = (comment, total_files, orderNumber)
try:
sql.insert(SQL, data)
except:
print('ERROR: Cannot insert in database')
SQL = "SELECT COUNT(ordernumber) FROM images WHERE ordernumber = %s"
data = (orderNumber, )
count = sql.select(SQL, data)
if total_files == count[0][
0]: # get the only element that the query returns
return 1
else:
return 0
def register_process(user, identity):
# user password 加密
user.password = sha256_crypt.hash(user.password)
try:
# 打开数据库连接
db = pymysql.connect("localhost", "root", "wujiahao.", "flaskTest")
# 使用cursor()方法获取操作游标
cursor = db.cursor()
key = ["id"]
condition = {"username": user.username}
query = sql.select(identity,key,condition,0)
if cursor.execute(query):
return "该用户名已经存在", False
key = ["id"]
condition = {"number": user.number}
query = sql.select(identity, key, condition, 0)
if cursor.execute(query):
return "该手机号已经存在", False
# 生成SQL语句
query = sql.insert(identity, utility.class_2_dict(user))
try:
# 使用execute方法执行SQL语句
cursor.execute(query)
# 提交操作到db
db.commit()
except:
# 操作失败回滚
db.rollback()
db.close()
return "注册失败", False
# 关闭数据库连接
db.close()
return "注册成功", True
except:
return "Unable to connect to DB", False
def commodity_detail(id):
try:
# 打开数据库连接
db = pymysql.connect("localhost",
"root",
"wujiahao.",
"flaskTest",
charset='utf8')
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# 查询字段
key = [
'id', 'name', 'info', 'ingredient', 'feature', 'manufacture',
'average_price'
]
# 查询条件
condition = {}
condition['id'] = id
# 生成SQL语句
query = sql.select("commodity", key, condition, 0)
cursor = db.cursor()
# 使用execute方法执行SQL语句
if cursor.execute(query):
# 获取结果
data = cursor.fetchone()
dict_commo = {}
dict_commo["id"] = data[0]
dict_commo["name"] = data[1]
dict_commo["info"] = data[2]
dict_commo["ingredient"] = data[3]
dict_commo["feature"] = data[4]
dict_commo["manufacture"] = data[5]
dict_commo["average_price"] = data[6]
stores = []
store_names = commodity_id2company_name(id)
for row in store_names:
store_dict = company_name2vendor_id(row)
stores.append(store_dict)
dict_commo["stores"] = stores
# 关闭数据库连接
db.close()
return True, "获取商品信息成功", dict_commo
else:
return False, "获取商品信息失败", "null"
except:
return "无法连接数据库"
def CG7():
t1 = table.Table("data/posthistory.xml")
t2 = group(t1,["PostId","UserId"])
t3 = select(t1,attributes=["PostId","UserId"])
t3 = join(t2,t2,[("UserId","UserId")])
t4 = group(t3,["PostId1","PostId2"])
g = graph.Graph("undirected",False)
g.addNodes(t1,"PostId")
g.addEdges(t4,"PostId1","PostId2")
return g
async def ow_rank(ctx):
"""Rank Overwatch players in database by SR"""
query = sql.select(overwatch_table, order="SR DESC")
em = discord.Embed(title="Overwatch SR Leaderboard", colour=0xFF00FF)
rank = 1
for row in c.execute(query):
name = "#"+str(rank)+" "+row[0]
values = ["SR: " + str(row[1]), "@"+row[2]]
value = '\n'.join(values)
em.add_field(name=name, value=value)
rank += 1
await bot.send_message(ctx.message.channel, embed=em)
def cd(city, pagenum):
res, maxpage = sql.select(city, pagenum)
pagenum = min(max(1, int(pagenum)), maxpage + 1)
# print(maxpage)
pages = [i + 1 for i in range(maxpage + 1)]
title = res[0][-2] + '公告'
return render_template('detail.html',
infos=res,
p=pagenum - 1,
n=pagenum + 1,
title=title,
pages=pages,
current=pagenum,
city=city)
def login_process(number, password, identity):
try:
# 打开数据库连接
db = pymysql.connect("localhost", "root", "wujiahao.", "flaskTest")
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# 查询字段
key = ["password", "username"]
# 查询条件
condition = {'number': number}
# 可以用phone number也可以用name登录
# if '9' >= account[0] >= '0' or account[0] == '+':
# condition["number"] = account
# else:
# condition["username"] = account
# 生成SQL语句
query = sql.select(identity, key, condition, 0)
# 使用execute方法执行SQL语句
if cursor.execute(query):
# 获取结果
result = cursor.fetchone()
# 关闭数据库连接
db.close()
# 验证密码
password_verified = sha256_crypt.verify(password, str(result[0]))
if password_verified:
return "登录成功", True, result[1]
else:
return "密码错误", False, "null"
else:
return "用户不存在，请先注册", False, "null"
except:
return "Unable to connect to DB", False, "null"
def download():
SQL = "SELECT * FROM downloadimages" # all that are READY
data = ('', )
rows = sql.select(SQL, data)
print('INFO: {d} images to download'.format(d=len(rows)))
for row in rows:
orderNumber = row[0]
filename = row[1]
destination = row[2]
server = row[3]
checksum = row[4]
filesize = row[5]
if not utilities.filesandfolders.freespace(destination):
print('ERROR: Not enough space on server the limit is {limit}GB'.format(
limit=cfg_limit)
)
exit() # this will exit the whole program
else:
print('INFO: Still enough space, limit is {limit}GB'.format(limit=cfg_limit))
# actually downloading the file
dest = os.path.join(destination, str(orderNumber), str(filename))
url = 'ftp://ftp.class.{s}.noaa.gov/{o}/001/{f}'.format(
s=server,
o=orderNumber,
f=filename
)
ftpres = ftp.file(str(url), str(dest))
if ftpres is None:
print('INFO: Download completed')
else:
print('INFO: Finished with Error {r}'.format(r=ftpres))
sql.setImageStatus(orderNumber, filename, 'ERROR')
continue # continiue with next row
if (
# check in the database if the checksum was given, if not, it is non-verified download
(not checksum == '') and
(image.checksumcheck(dest, checksum.replace('-', ''))) and
(utilities.filesandfolders.getFileSize(dest) == filesize)
):
print('INFO: Download size and md5 verified')
sql.setImageStatus(orderNumber, filename, 'FINISHED')
else:
print('ERROR: Download of {d} has errors'.format(d=dest))
sql.setImageStatus(orderNumber, filename, 'ERROR')
sql.orderFinished(orderNumber)
def process():
SQL = ("SELECT * FROM processmanifest")
data = ('',)
rows = sql.select(SQL, data)
print('INFO: Processing Manifest for', len(rows), 'orders with the status MANIFEST')
for row in rows:
orderNumber = row[0]
path = row[1]
manifestfile = str(row[2])
if os.path.exists(os.path.join(path, str(orderNumber), manifestfile)):
if manifest.loadxml(os.path.join(path, str(orderNumber), manifestfile), orderNumber) == 1:
sql.setOrderStatus(str(orderNumber), 'READY')
else:
sql.setOrderStatus(str(orderNumber), 'ERROR')
exit()
def home_commodity():
try:
# 打开数据库连接
db = pymysql.connect("localhost",
"root",
"wujiahao.",
"flaskTest",
charset='utf8')
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# 查询字段
key = ['id', 'name', 'pic_path']
# 查询条件
condition = {}
# 生成SQL语句
query = sql.select("commodity", key, condition, 0)
# 使用execute方法执行SQL语句
if cursor.execute(query):
# 获取结果
data = cursor.fetchall()
result = []
for row in data:
dict_commo = dict()
dict_commo["id"] = row[0]
dict_commo["name"] = row[1]
# file = open(row[2], 'r')
# base = file.read()
# file.close()
# dict_commo["base64"] = base
result.append(dict_commo)
# 关闭数据库连接
db.close()
return True, "查询所有商品名称和ID成功", result
else:
return False, "查询失败", "null"
except:
return False, "无法连接数据库", "null"
async def ow_ru(ctx):
"""Update Discord users' roles according to Overwatch SR"""
await bot.send_typing(ctx.message.channel)
squery = sql.select(overwatch_table)
# Because another query occurs in the loop, you have to put the data into an array first.
data = c.execute(squery).fetchall()
# Build list of requests
print("Building tasks")
tasks = [asyncio.ensure_future(update_sr(row[0])) for row in data]
print("asyncio.gather on tasks")
await asyncio.gather(*tasks)
conn.commit()
server = ctx.message.guild
await update_roles(guild)
await ctx.send("Done updating roles!")
def acao_espec(query, colunas):
bold_underline = '\033[1m \033[4m'
end_bold_underline = '\033[0m'
df_select = select(query, colunas)
print(f'\n{bold_underline}RESULTADO: {end_bold_underline}\n')
if not df_select.empty:
print(df_select.to_string(index=False))
print('\n')
else:
print(f'{bold_underline}Essa tabela está vazia{end_bold_underline}\n')
input('\nPressione enter para continuar\n')
return 'Tabelas'
def generate(datadir, tempdir):
rows = sql.select("SELECT * FROM footprintmissing", '')
# https://stackoverflow.com/a/15143994/1623867
# ThreadPoolExecutor for I/O bound operations
# ProcessPoolExecutor for CPU bound
multicore = False
start = time.time()
if multicore is True:
executor = concurrent.futures.ProcessPoolExecutor(1)
futures = [executor.submit(
footprint.processor, row, datadir, tempdir
) for row in rows]
concurrent.futures.wait(futures)
else:
for row in rows:
footprint.processor(row, datadir, tempdir)
end = time.time()
print(end - start)
async def ow_list(ctx):
"""List players in Overwatch database"""
query = sql.select(overwatch_table, order="LOWER(BattleTag)")
#query = "SELECT * FROM Overwatch"
tags = []
for row in c.execute(query):
battle_tag = row[0]
sr = row[1]
member_name = row[2]
tags.append([battle_tag, member_name])
#tags.sort(key=lambda y: y[0].lower())
log.debug(tags)
# print(tags)
output = ''
for row in tags:
output += row[0] + " as @" + row[1] + '\n'
await ctx.send(output)
def home_store():
try:
# 打开数据库连接
db = pymysql.connect("localhost", "root", "wujiahao.", "flaskTest",charset='utf8')
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# 查询字段
key = ['id', 'company_name', 'company_address' , 'main_product']
# 查询条件
condition = {}
# 生成SQL语句
query = sql.select("vendor", key, condition, 0)
# 使用execute方法执行SQL语句
if cursor.execute(query):
# 获取结果
data = cursor.fetchall()
result = []
for row in data:
dict_commo = dict()
dict_commo["id"] = row[0]
dict_commo["name"] = row[1]
dict_commo["address"] = row[2]
dict_commo["star"] = row[3]
result.append(dict_commo)
# 关闭数据库连接
db.close()
return True, "获取入驻商家成功", result
else:
return False, "查询失败", "null"
except:
return False, "无法连接数据库" , "null"
def select_lazy(self, **kw):
q = {'_from' : sql.escape_field(self.table)}
q.update(kw)
q['_where'] = self.where(q)
return self.db.query_lazy(sql.select(**q),_factory=self.record)
def select_value(self, **kw):
q = {'_from' : sql.escape_field(self.table)}
q.update(kw)
q['_where'] = self.where(q)
return self.db.query_value(sql.select(**q))
m = hashlib.sha1()
m.update(pwd)
return(m.hexdigest())
def pwd(p):
string = "abcdefghijklmnopqrstuvwxyz0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
pw_length = p
key = ""
for i in range(pw_length):
next_index = random.randrange(len(string))
key = key + string[next_index]
return(key)
sql = sql.select("*")
print(res)
nb = int(input("\nNumber of characters : "))
file = open("accounts2.txt", "r")
tps1 = time.clock()
print("\nworking...")
for line in file:
dico = line.split(',' or '\n')
found = ""
i = 0
while dico[1] != found:
keys = pwd(nb)
found = sha1(keys+dico[2])
def bills():
bills = select("bills")
return render_template("bills.html", bills=bills)
def spending():
spending = select("spending")
return render_template("spending.html", spending=spending)
def select(self, table_name, fields, into=None):
db_cursor = self.db_conn.cursor()
for values in db_cursor.execute(sql.select(table_name, fields)):
if into is not None:
values = into(*values)
yield values
def accounts():
accounts = select("accounts")
return render_template("accounts.html", accounts=accounts)
# CG1
import table
import graph
from gbuild import makeGraph
from sql import select, project, rename, join, group
from condition import Condition
t1 = table.Table("data/posts.xml")
t2 = select(t1, Condition("PostTypeId","==", 1), ["Id","OwnerUserId"])
t3 = select(t1, Condition("PostTypeId","==", 2), ["ParentId","OwnerUserId"])
t4 = join(t2,t3,[("Id","ParentId")])
t5 = group(t4, ["OwnerUserId1", "OwnerUserId2"], "Count", "cnt")
g = graph.Graph("directed")
g.addNodes(t1,"OwnerUserId",[])
g.addEdges(t5,"UserId1","UserId2",["Count"])
# CG2
import table
import graph
from gbuild import makeGraph
from sql import select, project, rename, join, group
from condition import Condition
t1 = table.Table("data/posts.xml") #["Id","PostTypeId","OwnerUserId",...]
t2 = table.Table("data/votes.xml") #["PostId","VoteTypeId","UserId",...]
t3 = select(t2, Condition("VoteTypeId", "==", 2))
t4 = join(t3,t1,["PostId"],["Id"],["UserId"],["OwnerUserId"],["Id","UserId1","UserId2"])
t5 = group(t4,["UserId1","UserId2"], "Count","cnt")
g = graph.Graph("directed")
g.addNodes(t1,"OwnerUserId",[])
g.addNodes(t2,"UserId",[])
g.addEdges(t5,"UserId1","UserId2",["Count"])
def spending_add():
accounts = select("accounts")
return render_template("spending_add.html", accounts=accounts)
