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)