def show_hide_summary(bot: Bot, cmd: str, parent: int, reaction_post_id: int,
chat_id: int) -> None:
reaction_msg_id = make_msg_id(reaction_post_id, chat_id)
with get_conn() as conn:
is_expanded = conn.execute("select expanded from message where id=?;",
(reaction_msg_id, )).fetchone()[0]
if (cmd == "show_reactions"
and is_expanded) or (cmd == "hide_reactions"
and not is_expanded):
# cant show/hide already shown/hidden
# race condition may produce multiple show/hide commands in a row
return
if cmd == "show_reactions":
new_text = get_text_for_expanded(parent, chat_id)
with get_conn() as conn:
conn.execute(
"UPDATE message SET expanded=TRUE where id=?;",
(reaction_msg_id, ),
)
else:
new_text = EMPTY_MSG
with get_conn() as conn:
conn.execute(
"UPDATE message SET expanded=FALSE where id=?;",
(reaction_msg_id, ),
)
bot.edit_message_text(chat_id=chat_id,
message_id=reaction_post_id,
text=new_text,
parse_mode="HTML")
update_message_markup(bot, chat_id, reaction_post_id, parent)
def main(amount, year, month):
value = {}
date_range = []
sum, avg, std = 0, 0, 0
conn = db.get_conn()
cursor = db.get_conn()
# Gather all dates in month
cursor.execute(
"select distinct d.date from asset_value av, dates d where av.date_id = d.date_id and d.year = \'"
+ year + "\' and d.month_num = " + month +
" and av.asset = 'AAPL' and av.volume != 0 order by av.date_id")
rows = cursor.fetchall()
for row in rows:
date_range.append(row)
for process_date in date_range:
print process_date[0]
print amount, str(process_date[0]), "4", "30", ".0025", "2", "1", "20"
value[process_date[0]] = run_all.main(amount, str(process_date[0]),
"4", "30", ".0025", "2", "1",
"20")
print value[process_date[0]]
try:
sum, avg, std = meanstdv_dict(value)
except:
print "fail"
print value
print "Date = " + year + "-" + month + ", Average Return = " + str(
avg) + ", Std dev = " + str(std) + ", Return = " + str(sum)
return (sum)
cursor.close()
conn.commit()
def updateDevice(userid, deviceid, sensorid, state=0, value=0):
if (len(findSensor(userid, deviceid, sensorid)) > 0):
sql_update = ''' UPDATE devices SET state = ?, value=?
WHERE (userID=? and deviceID=? and sensorID=?) '''
db.update(db.get_conn(DBPath), sql_update,
[(state, value, userid, deviceid, sensorid)])
else:
sql_insert = ''' insert into devices (userID, deviceID, sensorID, state, value)
VALUES (?,?,?,?,?);
'''
db.save(db.get_conn(DBPath), sql_insert,
[(userid, deviceid, sensorid, state, value)])
def place_order(sym, action, shares, price, con):
# Generate a new Order ID. I do this becuase you can't call query the API for NextOrderId in rapid succession
DBconn = db.get_conn()
cursor = DBconn.cursor()
cursor.execute("insert into orderID_gen (request) values (1)")
cursor.execute("select max(orderID) from orderID_gen")
result = cursor.fetchone()
orderID = result[0]
print "Placing orderID: "
print "Symbol: " + str(sym)
print "Action: " + str(action)
print "Shares: " + str(shares)
print "Price: " + str(price)
printl("Create contract")
stkContract = makeStkContract(sym)
printl("Create order")
stkOrder = makeStkOrder(shares, action, price)
printl("place order")
con.placeOrder(orderID, stkContract, stkOrder)
sleep(2)
print "Order placed"
def get(self):
# get db = all entries
plans = {
'ok' : False,
'entry_count': 0,
'entries': list()
}
entry_count = 0
conn = db.get_conn()
c = conn.cursor() #cursor to execute commands
c.execute("SELECT Task.pillar, COUNT(LearningEntry.task) as num FROM Task LEFT OUTER JOIN LearningEntry ON Task.id = LearningEntry.task GROUP BY Task.pillar ORDER BY COUNT(LearningEntry.task) DESC;") #quotes is SQL command/query. question mark defines placeholder, second part - give tuple
results = c.fetchall() # actually gets result from query
# fetch all is a list of lists
if (results == []): # no result from database
api.abort(404, "No learning plans found",ok=False)
for entry in results:
course = {
'pillar': entry[0],
'count_users': entry[1],
}
entry_count = entry_count + 1
plans['entries'].append(course)
plans['ok'] = True
plans['entry_count'] = entry_count
conn.close() # make sure to close database
return plans
def get(self, task_id):
plans = { #dictionary
'ok': False,
'number_interested' : 0, #label : information
'entries': list()
}
entry_count = 0
conn = db.get_conn()
c = conn.cursor() #cursor to execute commands
c.execute("SELECT EXISTS(SELECT name FROM Task WHERE id = ?)", (task_id,))
user_check = c.fetchone()[0] # returns 1 if exists, otherwise 0
if (user_check == 0): # user doesn't exist
api.abort(404, "Task '{}' doesn't exist".format(task_id),ok=False)
c.execute("SELECT User.name, User.email FROM Task LEFT OUTER JOIN (LearningEntry LEFT OUTER JOIN User ON LearningEntry.user = User.email) ON Task.id = LearningEntry.task WHERE Task.id=? AND User.name IS NOT null AND (LearningEntry.start_date < (SELECT strftime('%Y-%m-%d', 'now'))) AND LearningEntry.completed = False", (task_id,)) #quotes is SQL command/query. question mark defines placeholder, second part - give tuple
results = c.fetchall() # actually gets result from query
# fetch all is a list of lists
conn.close() # make sure to close database
if (results == []): # no result from database
plans['ok'] = True
plans['number_interested'] = 0
else:
for entry in results:
course = {
'name': entry[0],
'email': entry[1],
}
entry_count = entry_count + 1
plans['entries'].append(course)
plans['ok'] = True
plans['number_interested'] = entry_count
return plans
def get(self):
conn = db.get_conn()
c = conn.cursor()
c.execute("SELECT id, name FROM Skill")
results = c.fetchall()
if (results == []):
api.abort(400, "No skills found", ok=False)
entries = []
entry_count = 0
for r in results:
entry = {
'id': r[0],
'name': r[1],
}
entries.append(entry)
entry_count += 1
return_val = {
'ok': True,
'entry_count': entry_count,
'entries': entries
}
return return_val
def create_table():
conn = db.get_conn()
cur = conn.cursor()
try:
cur.execute("""
CREATE TABLE IF NOT EXISTS resource_types (
channel TEXT NOT NULL,
type TEXT NOT NULL,
PRIMARY KEY(channel, type)
)
""")
cur.execute("""
CREATE TABLE IF NOT EXISTS resources (
channel TEXT NOT NULL,
type TEXT NOT NULL,
name TEXT NOT NULL,
user TEXT,
updated DATETIME NOT NULL,
PRIMARY KEY(channel, type, name)
)
""")
ok = True
except Exception:
conn.rollback()
ok = False
finally:
cur.close()
conn.commit()
conn.close()
return ok
def webhook(request):
content = yield from request.read()
string = content.decode('utf-8')
update = json.loads(string)
mes = {
'chat_id': update['message']['chat']['id'],
'text': 'hello '+update['message']['from']['first_name']
}
command = update['message']['text'].partition(' ')
user_id = update['message']['from']['id']
conn = db.get_conn()
if command[0] == '/addsite':
site = command[2]
r.table('sites').insert({'site': site, 'user_id': user_id}).run(conn)
mes['text'] = 'Site '+site+' was added successfully'
if command[0] == '/list':
sites = r.table('sites').filter({'user_id': user_id}).run(conn)
mes['text'] = 'Your sites:\n'
for site in sites:
mes['text'] += site['site']+'\n'
print(api.sendMessage(mes))
# cursor = r.table('sites').run(conn)
# for row in cursor:
# print(row)
conn.close()
return web.Response(body='ok'.encode('utf-8'))
def get(self, number):
learning_plan = { #dictionary
'entry_count': 0, #label : information
'entries': list()
}
today = str(date.today())
entry_count = 0
conn = db.get_conn()
c = conn.cursor() #cursor to execute commands
c.execute(
"SELECT e.id, e.user, e.start_date, e.end_date, e.course, c.pillar FROM LearningEntry e, Course c WHERE e.course = c.name AND user = ?",
(user_id, )
) #quotes is SQL command/query. question mark defines placeholder, second part - give tuple
results = c.fetchall() # actually gets result from query
# fetch all is a list of lists
conn.close() # make sure to close database
for entry in results:
learning_entry = {
'id': entry[0],
'user': entry[1],
'start_date': entry[2],
'end_date': entry[3],
'course': entry[4],
'pillar': entry[5]
}
# TODO: Add logic of whether today is between start_date and end_date
entry_count = entry_count + 1
learning_plan['entries'].append(learning_entry)
learning_plan['entry_count'] = entry_count
return learning_plan
def file2db(file, zhinengleibie, province):
conn = get_conn()
filename = path.split(file)[-1]
job_id = filename.replace(".html", "")
exists = conn.execute(
f"select count(1) from jobs where job_id='{job_id}' and year_month={year_month}"
).fetchall()[0][0]
if exists:
try_rename(file)
return
#print(file)
job = file2job(file, zhinengleibie, province)
if not job:
try_rename(file)
return
data = pd.DataFrame(columns=get_featurenames(job))
l = object2list(job)
data.loc[job.job_id] = l
data.to_sql("jobs", conn, if_exists="append", index=False)
conn.close()
try_rename(file)
def top_list(clan):
(executor, caller, enactor) = pennmush.call_info()
global conn
if not conn:
conn = db.get_conn()
cursor = conn.cursor()
pennmush.notify([enactor],"Not implemented at present.")
def get(self, email):
conn = db.get_conn()
c = conn.cursor()
# get link records for user
c.execute("SELECT link, tag FROM Candidate_Links WHERE email = ?", (email,))
linkResult = c.fetchall()
conn.close()
links = []
for r in linkResult:
entry = {
'link': r[0],
'tag': r[1]
}
links.append(entry)
return_val = {
'ok': True,
'email': email,
'links': links
}
return return_val
def get(self):
# get db = all entries
plans = {'ok': False, 'entry_count': 0, 'entries': list()}
entry_count = 0
conn = db.get_conn()
c = conn.cursor() #cursor to execute commands
c.execute(
"SELECT e.id, e.user, e.start_date, e.end_date, c.name, c.pillar, e.task FROM LearningEntry e INNER JOIN Task c ON e.task = c.id ORDER BY e.start_date"
) #quotes is SQL command/query. question mark defines placeholder, second part - give tuple
results = c.fetchall() # actually gets result from query
# fetch all is a list of lists
conn.close() # make sure to close database
if (results == []): # no result from database
api.abort(404, "No learning plans found", ok=False)
for entry in results:
learning_entry = {
'id': entry[0],
'user': entry[1],
'start_date': entry[2],
'end_date': entry[3],
'course': entry[4],
'pillar': entry[5],
'task_id': entry[6]
}
entry_count = entry_count + 1
plans['entries'].append(learning_entry)
plans['ok'] = True
plans['entry_count'] = entry_count
return plans
def file2db(file, city):
conn = get_conn()
try:
filename = path.split(file)[-1]
job_id = filename.replace(".html", "")
exists = conn.execute(
"select count(1) from _51jobs where job_id='{0}'".format(
job_id)).fetchall()[0][0]
if exists:
os.rename(file, file.replace("51jobs", "51jobs_back"))
return
print(file)
job = file2job(file, city)
if not job:
return
# if not job.check_all():
# return
data = pd.DataFrame(columns=get_featurenames(job))
l = object2list(job)
data.loc[job.job_id] = l
data.to_sql("_51jobs", conn, if_exists="append", index=False)
except Exception:
pass
finally:
conn.close()
def get(self):
tasks = {'ok': False, 'entry_count': 0, 'entries': list()}
entry_count = 0
conn = db.get_conn()
c = conn.cursor() #cursor to execute commands
c.execute(
"SELECT * FROM Task;"
) #quotes is SQL command/query. question mark defines placeholder, second part - give tuple
results = c.fetchall() # actually gets result from query
# fetch all is a list of lists
if (results == []): # no result from database
api.abort(404, "No tasks found", ok=False)
for entry in results:
task = {
'id': entry[0],
'name': entry[1],
'isCertificate': entry[2],
'pillar': entry[3],
'specialisation': entry[4]
}
entry_count = entry_count + 1
tasks['entries'].append(task)
tasks['ok'] = True
tasks['entry_count'] = entry_count
conn.close() # make sure to close database
return tasks
def process(id):
conn = get_conn()
cur = conn.cursor()
cur.execute('select recipient, body, date from drop where id = %s', (id, ))
row = cur.fetchone()
rcpt = row[0]
msg = message_from_string(row[1])
date = row[2]
cur.execute("select m.id as member_id, a.id as alias_id from alias a inner join member m on (a.member_id = m.id) inner join domain d on (a.domain_id = d.id) where (a.name || '@' || d.name) = %s", (rcpt, ))
row = cur.fetchone()
member_id = row[0]
alias_id = row[1]
subject = msg['Subject']
body = str(msg.get_payload(0))
# print member_id, date, alias_id, subject, body
cur.execute('insert into message ( member_id, date, alias_id, subject, body ) values ( %s, %s, %s, %s, %s ) returning ( id )', (member_id, date, alias_id, subject, body))
row = cur.fetchone()
message_id = row[0]
cur.execute('update drop set processed = true where id = %s', (id, ))
conn.commit()
cur.close()
conn.close()
return (id, message_id)
def remove_resource_type(msg, rtype):
ch = msg.channel._body['name']
conn = db.get_conn()
try:
if not is_exist_resouce_type(conn, ch, rtype):
msg.reply("Oops! {} is not existed in resource type".format(rtype))
return
cur = conn.cursor()
cur.execute(
"""
DELETE FROM resource_types
WHERE channel = ?
AND type = ?
""", (ch, rtype))
cur.close()
cur = conn.cursor()
cur.execute(
"""
DELETE FROM resources
WHERE channel = ?
AND type = ?
""", (ch, rtype))
cur.close()
except Exception:
conn.rollback()
msg.reply("Error: {} is not removed from resource type".format(rtype))
return
finally:
conn.commit()
conn.close()
msg.reply("{} is removed from resource type".format(rtype))
def post(self):
req = request.get_json(force=True)
conn = db.get_conn()
c = conn.cursor() #cursor to execute commands
new_id = generate_ID()
c.execute(
'INSERT INTO Task values(?,?,?,?,?)',
(
new_id,
req['name'],
req['isCertificate'],
req['pillar'],
req['specialisation'],
),
)
conn.commit()
conn.close()
entry = {
'id': new_id,
'name': req['name'],
'isCertificate': req['isCertificate'],
'pillar': req['pillar'],
'specialisation': req['specialisation']
}
return_val = {'ok': True, 'entry': entry}
return return_val
def add_resource_type(msg, rtype):
ch = msg.channel._body['name']
conn = db.get_conn()
try:
if is_exist_resouce_type(conn, ch, rtype):
msg.reply(
"Oops! {} was already existed in resource type".format(rtype))
return
cur = conn.cursor()
cur.execute(
"""
INSERT INTO resource_types (
channel, type
) VALUES (
?, ?
)
""", (ch, rtype))
cur.close()
except Exception:
conn.rollback()
msg.reply("Error: {} is not added to resource type".format(rtype))
return
finally:
conn.commit()
conn.close()
msg.reply("{} is added to resource type".format(rtype))
def list_resource_type(msg):
ch = msg.channel._body['name']
conn = db.get_conn()
try:
cur = conn.cursor()
cur.execute(
"""
SELECT type
FROM resource_types
WHERE channel = ?
""", (ch, ))
rtlist = cur.fetchall()
cur.close()
except Exception:
conn.rollback()
msg.reply(
"Error: Sorry but I can't show you resource type list in this channel"
)
return
finally:
conn.commit()
conn.close()
if len(rtlist) <= 0:
msg.reply("Oops! There is no resource type in this channel")
else:
ret_msg = "\n```\n{}\n```".format("\n".join([rt[0] for rt in rtlist]))
msg.reply(ret_msg)
def get(self, record_id):
conn = db.get_conn()
c = conn.cursor()
c.execute(
"SELECT id, candidate_email, description, startDate, endDate, employer, job_title FROM Employment WHERE id = ?",
(record_id, ))
result = c.fetchone()
if (result == None):
api.abort(400,
"Employment record '{}' doesn't exist".format(record_id),
ok=False)
entry = {
'id': result[0],
'job_title': result[5],
'start_date': result[3],
'end_date': result[4],
'description': result[2],
'employer': result[5]
}
user = result[1]
return_val = {'ok': True, 'user': user, 'employment': entry}
return return_val
def update_word(self, li):
try:
# print(li)
self.conn, self.cursor = db.get_conn()
sql = "insert into word(" \
"update_time,name,confirm_add,confirm,heal,dead" \
") " \
"values(%s,%s,%s,%s,%s,%s)"
sql_query = 'select %s=(' \
'select update_time ' \
'from word ' \
'order by id ' \
'desc limit 1' \
')'
# 对比当前最大时间戳
self.cursor.execute(sql_query, li[0]["update_time"])
if not self.cursor.fetchone()[0]:
print(f"{time.asctime()}开始更新数据")
for item in li:
# k, v = item.items()
print(item)
# print(v)
v = []
v = list(item.values())
# print(list(v))
self.cursor.execute(sql, v)
self.conn.commit()
print(f"{time.asctime()}更新到最新数据")
else:
print(f"{time.asctime()}已是最新数据！")
except:
traceback.print_exc()
finally:
db.close_conn(self.conn, self.cursor)
def get(self, email):
conn = db.get_conn()
c = conn.cursor()
c.execute(
'''SELECT id, description, startDate, endDate, employer, job_title FROM Employment WHERE candidate_email = ?''',
(email, ))
results = c.fetchall()
if (results == []):
api.abort(400,
"No employment records found for '{}'".format(email),
ok=False)
entries = []
entry_count = 0
for r in results:
print(r)
entry = {
'id': r[0],
'job_title': r[5],
'description': r[1],
'start_date': r[2],
'end_date': r[3],
'employer': r[4]
}
entries.append(entry)
entry_count += 1
return_val = {
'ok': True,
'entry_count': entry_count,
'entries': entries
}
return return_val
def before_first_request():
"""Hook to ensure our database is initialized the first time we are connected to
"""
conn = db.get_conn()
conn.cursor().ensure_initialized()
conn.commit()
conn.close()
def get_images():
"""
Get all images from /images endpoint
and add it to db.
"""
conn = get_conn()
cursor = get_cursor(conn)
token = get_token()
if token.get("status") == "error":
return {"status": "error", "message": token.get("message")}
page = 1
first_page_response = get_page_of_images(page, token["data"])
if first_page_response.get("status") == "error":
return {
"status": "error",
"message": first_page_response.get("message")
}
results = []
results.extend(first_page_response["pictures"])
total_pages = first_page_response["pageCount"]
while page < total_pages:
response = get_page_of_images(page + 1, token["data"])
if response.get("status") == "error":
return {"status": "error", "message": response.get("message")}
results.extend(response["pictures"])
page += 1
for i, image in enumerate(results, start=1):
if i % 10 == 0:
logger.info(f"Getting details for image {i}/{len(results)}")
item = get_image_info(image["id"], token["data"])
add_to_db(item, conn, cursor)
conn.close()
logger.info("Received all images")
return {"status": "success"}
def get(self, entry_id):
conn = db.get_conn()
c = conn.cursor()
c.execute(
"SELECT e.id, e.user, e.task, t.name, e.start_date, e.end_date, e.completed FROM LearningEntry e, Task t WHERE e.id = ? AND e.task = t.id",
(entry_id, ))
entry_res = c.fetchall()
if (entry_res == []): # no result from database
api.abort(404, "No learning plan found", ok=False)
entry = entry_res[0]
learning_entry = {
'id': entry[0],
'user': entry[1],
'start_date': entry[4],
'end_date': entry[5],
'task': entry[2],
'task_name': entry[3],
'completed': True if entry[6] == 1 else False
}
ret = {"ok": False, "entry": learning_entry}
ret['ok'] = True
return ret
def main(db_user, db_pass, api_user, api_pass, time_interval):
db_info = {
'HOST': 'yen-wang.clcafikcugph.ap-northeast-1.rds.amazonaws.com',
'PORT': 3306,
'USER': db_user,
'PASSWD': db_pass,
'DB': 'pinnacle_db',
}
conn = get_conn(db_info)
cur = init_cur(conn)
last = 0
while type(last) == int:
try:
result, last, ctime = getFixtures(api_user, api_pass, 29, leagueid,
last)
values = ', '.join(map(str, result))
sql = "REPLACE INTO fixtures VALUES {}".format(values)
cur.execute(sql)
conn.commit()
# print log
print(ctime, ': successfully fetch {} obs'.format(len(result)))
sleep(int(time_interval))
except:
ctime = strftime("%Y-%m-%d %H:%M:%S", gmtime())
print(ctime, ': nothing to fetch at this moment')
sleep(int(time_interval))
def show():
conn = db.get_conn(db.DBNAME)
db.creat(conn, db.table_name)
open_data()
db.insert_info(conn)
datas = db.select_info(conn, db.table_name)
return render_template('show.html', list = datas, list1 = keys)
def insert_game(season, date, competition, ground, team1, team2, toss_winner,
toss_decision, player_match, winner):
# TODO: Add match_result column
conn = db.get_conn()
cursor = conn.cursor()
if winner == '' and player_match == '':
sql = "INSERT INTO game(season, date, competition, ground, team1, team2, toss_winner, toss_decision) VALUES(%s,%s,%s,%s,%s,%s,%s,%s)"
cursor.execute(sql,
(season, date, competition, int(ground), int(team1),
int(team2), int(toss_winner), toss_decision))
elif winner == '':
sql = "INSERT INTO game(season, date, competition, ground, team1, team2, toss_winner, toss_decision, player_match) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
cursor.execute(
sql,
(season, date, competition, int(ground), int(team1), int(team2),
int(toss_winner), toss_decision, int(player_match)))
else:
sql = "INSERT INTO game(season, date, competition, ground, team1, team2, toss_winner, toss_decision, player_match, winner) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
cursor.execute(
sql,
(season, date, competition, int(ground), int(team1), int(team2),
int(toss_winner), toss_decision, int(player_match), int(winner)))
log.debug(sql)
row_id = cursor.lastrowid
conn.commit()
return row_id
def delete(self):
req = request.json
conn = db.get_conn()
c = conn.cursor()
# check if course exists
check_query = 'SELECT * FROM Course WHERE code = ? and university = ?'
code_course = (req['code'], req['university'])
try:
c.execute(check_query, code_course)
except db.Sqlite3.Error as e:
api.abort(400, 'invalid query {}'.format(e), ok = False)
res = c.fetchone()
if res == None:
conn.close()
api.abort(400, 'Course {} at {} does not exist.'.format(req['code'], req['university']), ok = False)
else:
# this delete query should cascade to delete all relevant rows in relationship tables
# ie deletes associated relationships with grad/learning outcomes.
delete_query = 'DELETE FROM Course WHERE code = ? and university = ?'
try:
c.execute(delete_query, code_course)
except db.Sqlite3.Error as e:
api.abort(400, 'invalid query {}'.format(e), ok = False)
conn.commit()
conn.close()
returnVal = {
'ok' : True
}
return returnVal
def generate_ID():
conn = db.get_conn()
c = conn.cursor() #cursor to execute commands
c.execute('SELECT COUNT(*) FROM LearningEntry')
sum = c.fetchone()[0]
print(sum)
return sum + 1
def insert_history(self):
try:
dic = self.get_tencent_data()[0] # 0代表历史数据字典
print(f"{time.asctime()}开始插入历史数据")
self.conn, self.cursor = db.get_conn()
sql = "insert into history values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
for k, v in dic.items():
self.cursor.execute(sql, [
k,
v.get("confirm"),
v.get("confirm_add"),
v.get("suspect"),
v.get("suspect_add"),
v.get("heal"),
v.get("heal_add"),
v.get("dead"),
v.get("dead_add"),
v.get("importedCase"),
v.get("importedCase_add")
])
self.conn.commit()
print(f"{time.asctime()}插入历史数据完毕")
except:
traceback.print_exc()
finally:
db.close_conn(self.conn, self.cursor)
def update_details(self):
try:
li = self.get_tencent_data()[1] # 1代表最新数据
self.conn, self.cursor = db.get_conn()
sql = "insert into details(" \
"update_time,province,city,confirm,confirm_add,heal,dead" \
") " \
"values(%s,%s,%s,%s,%s,%s,%s)"
sql_query = 'select %s=(' \
'select update_time ' \
'from details ' \
'order by id ' \
'desc limit 1' \
')'
# 对比当前最大时间戳
self.cursor.execute(sql_query, li[0][0])
if not self.cursor.fetchone()[0]:
print(f"{time.asctime()}开始更新数据")
for item in li:
self.cursor.execute(sql, item)
self.conn.commit()
print(f"{time.asctime()}更新到最新数据")
else:
print(f"{time.asctime()}已是最新数据！")
except:
traceback.print_exc()
finally:
db.close_conn(self.conn, self.cursor)
def before_request():
"""Set up the database connection so we have it available in the request
"""
# First, make sure we have the table we need
conn = db.get_conn()
cur = conn.cursor()
return cur
def post(resource, data, user_id=None):
data["owner_id"] = user_id
if "id" in data:
del data["id"]
conn = db.get_conn()
result = conn.execute(resource.table.insert(), data)
return get(resource, user_id=user_id, id_=result.inserted_primary_key[0], id_dict=True)
def _execute_sql(sql, values, mode='execute'):
""" 连接到数据库执行sql语句, mode: execute/get/query
"""
if mode not in ['execute', 'get', 'query']:
return None
if configs['debug']:
logging.info('[HqOrm Gen-SQL]:' + sql % tuple(values))
dbc = db.get_conn(_DATABASE_NAME)
return getattr(dbc, mode)(sql, *values)
def index():
uid = request.cookies.get('uid')
user = None
if uid:
conn = db.get_conn()
user_key = 'user:{uid}'.format(uid=uid)
user = conn.hgetall(user_key)
return render_template('index.html', conf=config, user=user)
def put(resource, data, id_, user_id=None):
data["owner_id"] = user_id
data["id"] = id_
conn = db.get_conn()
table = resource.table
conn.execute(table.update()
.where(table.c.id == id_)
.where(table.c.owner_id == user_id)
.values(data))
return get(resource, user_id=user_id, id_=id_, id_dict=True)
def pts(victim):
global conn
if not conn:
conn = db.get_conn()
victim = int(victim.lstrip("#"))
cursor = conn.cursor()
cursor.execute("SELECT balance FROM honor_log WHERE victim = " + str(victim) + " ORDER BY dt desc LIMIT 1")
if cursor.rowcount == 0: # no honor recorded as of yet - should rarely happen since new players will be assigned some
return 0
balance = cursor.fetchone()
return balance[0]
def async_get_schema(get_schema, refresh_seconds):
global SCHEMA
global FUNCTIONS
global PKS
while True:
conn = None
try:
conn = db.get_conn()
SCHEMA, FUNCTIONS, PKS = get_schema(conn)
finally:
if conn:
db.release_conn(conn)
time.sleep(refresh_seconds)
def log_view(victim):
(executor, caller, enactor) = pennmush.call_info()
victim = int(victim.lstrip("#"))
global conn
if not conn:
conn = db.get_conn()
cursor = conn.cursor()
cursor.execute("SELECT delta, balance, description, dt FROM honor_log WHERE victim = " + str(victim) + " ORDER BY dt desc LIMIT 20")
pennmush.notify([enactor], util.titlebar("Honor Log: " + pennmush.api.name("#" + str(victim))))
pennmush.notify([enactor], " Date/Time Change Balance Description")
for (delta, balance, description, dt) in cursor.fetchall():
pennmush.notify([enactor], " " + dt.strftime("%d/%m/%y %H:%m") + " " + str(delta).rjust(6) + " " + str(balance).rjust(7) + " " + str(description)[:44])
pennmush.notify([enactor], util.footerbar())
def before_request():
"""A hook to set up a database connection before
we do anything, and make it globally available.
"""
app.logger.debug('setup request')
conn = db.get_conn()
g.tx = conn.cursor()
g.tx.log = g.log = app.logger
year = session.get('year', None)
g.log.debug('session[year] = %s' % (year,))
if year is not None:
try:
g.tx.years.use(year)
except Exception as e:
# TODO: this may not be the right way to handle this
g.log.debug('failed to use year %s: %s' % (year, str(e)))
del session['year']
def login():
source = request.form['source']
access_token = request.form['access_token']
expires_in = request.form['expires_in']
platform_data = json.loads(request.form['platform_data'])
if platform_data is None:
return ''
platform_user = platform_data['data']
if platform_user is None:
return ''
if 'id' not in platform_user or 'screen_name' not in platform_user:
return ''
pid = '{source}_{platform_uid}'.format(source=source, platform_uid=platform_user['id'])
pid_uid_key = 'platform_uid:to:uid'
conn = db.get_conn()
uid = conn.hget(pid_uid_key, pid)
if uid == None:
uid = str(uuid.uuid1()).upper()
conn.hset(pid_uid_key, pid, uid)
user_key = 'user:{uid}'.format(uid=uid)
pipe = conn.pipeline()
# Save user data.
pipe.hset(user_key, 'source', source)
pipe.hset(user_key, 'platform_uid', platform_user['id'])
pipe.hset(user_key, 'access_token', access_token)
pipe.hset(user_key, 'name', platform_user['screen_name'])
# Save token expires time.
if expires_in.isdigit():
token_score = int(time.time()) + int(expires_in)
pipe.zadd('token_expires', token_score, user_key)
pipe.execute()
# Set cookies.
resp = make_response()
resp.set_cookie('uid', uid, max_age=864000)
return resp
def get(resource, user_id=None, id_=None, id_dict=False):
conn = db.get_conn()
stmt = select([resource.table]).select_from(resource.table)
if resource.has_user_id:
stmt = stmt.where(resource.table.c.owner_id == user_id)
if id_ is not None:
stmt = stmt.where(resource.table.c.id == id_)
query = conn.execute(stmt)
results = query.fetchall()
if id_dict:
return make_id_dict(results)
else:
return (dict(row) for row in results)
def before_request():
conn = db.get_conn()
g.db = conn.cursor()
g.db.execute("""SELECT * FROM netconfigs""")
res = g.db.fetchall()
g.nc_map = {}
for ncid, ncname in res:
g.nc_map[ncname] = ncid
g.ncid_map = dict((v, k) for k, v in g.nc_map.items())
g.db.execute("""SELECT * FROM operating_systems""")
res = g.db.fetchall()
g.os_map = {}
for osid, osname in res:
g.os_map[osname] = osid
g.osid_map = dict((v, k) for k, v in g.os_map.items())
def delete(resource, id_, user_id=None):
conn = db.get_conn()
table = resource.table
conn.execute(table.delete()
.where(table.c.id == id_)
.where(table.c.owner_id == user_id))
#!/usr/bin/python2.7
import sys, psycopg2
from tasks import process
from db import get_conn
# gather the information
recipient = sys.argv[1]
data = sys.stdin.read()
conn = get_conn()
cur = conn.cursor()
# insert it into the database
cur.execute("insert into drop ( date, processed, recipient, body ) values ( current_timestamp, 'N', %s, %s ) returning ( id )", (recipient, data))
ret = cur.fetchone()
# get the database id
id = ret[0]
conn.commit()
cur.close()
conn.close()
# send to the queue for processing
process.delay(id)
print 'delivered (%d).' % (id)
import json
import hashlib
import bson
import bson.json_util
from flask import Flask, jsonify, url_for, request, make_response, g, redirect
from mgoquery import Parser, ParseException
from db import get_conn
from config import config
mgoparser = Parser()
db = get_conn()
app = Flask(__name__)
def to_bson(obj):
return json.dumps(obj, default=bson.json_util.default)
def bsonify(obj):
resp = make_response(to_bson(obj))
resp.headers['Content-Type'] = 'application/json'
return resp
def abs_url_for(*args, **kw):
tail = url_for(*args, **kw)
return '%s%s' % (app.config['base_url'], tail)
def __init__(self, name):
self.database = db.get_conn()[name]
def process_item(self, item, spider):
conn = get_conn(item.mongoname)
conn.save(dict(item))
def forget_project(name):
conn = db.get_conn()
return _forget_project(name, conn)
