def get_all(limit=None, offset=None, **params): query = SQL_GET_ALL multi_param = False if params: query += """ WHERE """ if "block_id" in params: query += """ block_id = """ + str(params["block_id"]) multi_param = True if "transaction_type" in params: if multi_param: query += """ AND """ query += """ transaction_type = '""" + str(params["transaction_type"]) + """'""" multi_param = True if "owner" in params: if multi_param: query += """ AND """ query += """ owner = '""" + str(params["owner"]) + """'""" multi_param = True if "actor" in params: if multi_param: query += """ AND """ query += """ actor = '""" + str(params["actor"]) + """'""" multi_param = True if "entity" in params: if multi_param: query += """ AND """ query += """ entity = '""" + str(params["entity"]) + """'""" multi_param = True # not used but left in place to handle future params query += """ ORDER BY transaction_ts DESC """ if not limit: limit = 10 if limit: query += """ LIMIT """ + str(limit) if offset: query += """ OFFSET """ + str(offset) conn = get_connection_pool().getconn() try: cur = conn.cursor(get_cursor_name(), cursor_factory=psycopg2.extras.DictCursor) cur.execute(query) 'An iterator that uses fetchmany to keep memory usage down' while True: results = cur.fetchmany(DEFAULT_PAGE_SIZE) if not results: break for result in results: yield format_transaction(result) cur.close() finally: get_connection_pool().putconn(conn)
def get(txid): conn = get_connection_pool().getconn() try: cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) cur.execute(SQL_GET_BY_ID, (txid,)) result = cur.fetchone() cur.close() if result: result = format_transaction(result) return result finally: get_connection_pool().putconn(conn)
def get(txid): conn = get_connection_pool().getconn() try: cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) cur.execute(SQL_GET_BY_ID, (txid,)) result = cur.fetchone() cur.close() if result: result = format_transaction(result) return result finally: get_connection_pool().putconn(conn)
def get_subscription_txns(criteria, block_id=None): """ retrieve transactions that meet given criteria and have a block_id >= minimum_block_id """ query = SQL_GET_ALL query += """ WHERE """ separator_needed = False if "transaction_type" in criteria: query += """ transaction_type = %(transaction_type)s""" separator_needed = True if "actor" in criteria: if separator_needed: query += """ AND """ query += """ actor = %(actor)s""" separator_needed = True if "entity" in criteria: if separator_needed: query += """ AND """ query += """ entity = %(entity)s""" separator_needed = True if "owner" in criteria: if separator_needed: query += """ AND """ query += """ owner = %(owner)s""" separator_needed = True if block_id: if separator_needed: query += """ AND """ query += """ block_id = %(block_id)s""" criteria['block_id'] = block_id # adding for query execution vars conn = get_connection_pool().getconn() try: cur = conn.cursor(get_cursor_name(), cursor_factory=psycopg2.extras.DictCursor) cur.execute(query, criteria) 'An iterator that uses fetchmany to keep memory usage down' while True: results = cur.fetchmany(DEFAULT_PAGE_SIZE) if not results: break for result in results: yield format_transaction(result) cur.close() finally: get_connection_pool().putconn(conn)
def get_subscription_txns(criteria, block_id=None): """ retrieve transactions that meet given criteria and have a block_id >= minimum_block_id """ query = SQL_GET_ALL query += """ WHERE """ separator_needed = False if "transaction_type" in criteria: query += """ transaction_type = %(transaction_type)s""" separator_needed = True if "actor" in criteria: if separator_needed: query += """ AND """ query += """ actor = %(actor)s""" separator_needed = True if "entity" in criteria: if separator_needed: query += """ AND """ query += """ entity = %(entity)s""" separator_needed = True if "owner" in criteria: if separator_needed: query += """ AND """ query += """ owner = %(owner)s""" separator_needed = True if block_id: if separator_needed: query += """ AND """ query += """ block_id = %(block_id)s""" criteria['block_id'] = block_id # adding for query execution vars conn = get_connection_pool().getconn() try: cur = conn.cursor(get_cursor_name(), cursor_factory=psycopg2.extras.DictCursor) cur.execute(query, criteria) 'An iterator that uses fetchmany to keep memory usage down' while True: results = cur.fetchmany(DEFAULT_PAGE_SIZE) if not results: break for result in results: yield format_transaction(result) cur.close() finally: get_connection_pool().putconn(conn)
def get_all(limit=None, offset=None, **params): query = SQL_GET_ALL multi_param = False if params: query += """ WHERE """ if "block_id" in params: query += """ block_id = """ + str(params["block_id"]) multi_param = True if "transaction_type" in params: if multi_param: query += """ AND """ query += """ transaction_type = '""" + str(params["transaction_type"]) + """'""" multi_param = True if "business_unit" in params: if multi_param: query += """ AND """ query += """ business_unit = '""" + str(params["business_unit"]) + """'""" multi_param = True if "family_of_business" in params: if multi_param: query += """ AND """ query+= """ family_of_business = '""" + str(params["family_of_business"]) + """'""" if "line_of_business" in params: if multi_param: query += """ AND """ query += """ line_of_business = '""" + str(params["line_of_business"]) + """'""" multi_param = True if "signature" in params: if multi_param: query += """ AND """ query += """ signature = '""" + str(params["signature"]) + """'""" multi_param = True if "status" in params: if multi_param: query += """ AND """ query += """ status = '""" + str(params["status"]) + """'""" multi_param = True if "owner" in params: if multi_param: query += """ AND """ query += """ owner = '""" + str(params["owner"]) + """'""" multi_param = True if "actor" in params: if multi_param: query += """ AND """ query += """ actor = '""" + str(params["actor"]) + """'""" multi_param = True if "entity" in params: if multi_param: query += """ AND """ query += """ entity = '""" + str(params["entity"]) + """'""" multi_param = True if "create_ts" in params: if multi_param: query += """ AND """ if '-' in params["create_ts"]: # if it is timestamp >= UNIX-epoch timecode if params["create_ts"].index('-') == 0: start_time = time.strftime('%Y-%m-%d %H:%M:%S',time.gmtime(float(params["create_ts"][1:]))) query += """ create_ts >= '""" + start_time + """'""" elif params["create_ts"].endswith('-'): end_time = time.strftime('%Y-%m-%d %H:%M:%S',time.gmtime(float(params["create_ts"][:len(params["create_ts"])-1]))) query += """ create_ts <= '""" + end_time + """'""" else: start_time = time.strftime('%Y-%m-%d %H:%M:%S',time.gmtime(float(params["create_ts"][:params["create_ts"].index('-')]))) end_time = time.strftime('%Y-%m-%d %H:%M:%S', time.gmtime(float(params["create_ts"][params["create_ts"].index('-')+1:]))) query += """ create_ts >= '""" + start_time + """' AND create_ts <= '""" + end_time + """'""" else: cur_time = time.strftime('%Y-%m-%d %H:%M:%S', time.gmtime(float(params["create_ts"]))) query += """ create_ts = '""" + cur_time + """'""" multi_param = True if "transaction_ts" in params: print("transaction_ts") if multi_param: query += """ AND """ if '-' in params["transaction_ts"]: # if it is timestamp >= UNIX-epoch timecode if params["transaction_ts"].index('-') == 0: start_time = time.strftime('%Y-%m-%d %H:%M:%S', time.gmtime(float(params["transaction_ts"][1:]))) query += """ transaction_ts >= '""" + start_time + """'""" elif params["transaction_ts"].endswith('-'): end_time = time.strftime('%Y-%m-%d %H:%M:%S', time.gmtime(float(params["create_ts"][:len(params["transaction_ts"]) - 1]))) query += """ transaction_ts <= '""" + end_time + """'""" else: start_time = time.strftime('%Y-%m-%d %H:%M:%S', time.gmtime(float(params["create_ts"][:params["transaction_ts"].index('-')]))) end_time = time.strftime('%Y-%m-%d %H:%M:%S', time.gmtime( float(params["transaction_ts"][params["create_ts"].index('-') + 1:]))) query += """ transaction_ts >= '""" + start_time + """' AND transaction_ts <= '""" + end_time + """'""" else: cur_time = time.strftime('%Y-%m-%d %H:%M:%S', time.gmtime(float(params["transaction_ts"]))) query += """ transaction_ts = '""" + cur_time + """'""" multi_param = True # not used but left in place to handle future params query += """ ORDER BY transaction_ts DESC """ if not limit: limit = 10 if limit: query += """ LIMIT """ + str(limit) if offset: query += """ OFFSET """ + str(offset) conn = get_connection_pool().getconn() try: cur = conn.cursor(get_cursor_name(), cursor_factory=psycopg2.extras.DictCursor) cur.execute(query) 'An iterator that uses fetchmany to keep memory usage down' while True: results = cur.fetchmany(DEFAULT_PAGE_SIZE) if not results: break for result in results: yield format_transaction(result) cur.close() finally: get_connection_pool().putconn(conn)
def get_all(limit=None, offset=None, **params): query = SQL_GET_ALL separator_needed = False if params: query += """ WHERE""" if "block_id" in params and params["block_id"]: query += """ block_id = %(block_id)s""" separator_needed = True if "transaction_type" in params and params["transaction_type"]: if separator_needed: query += """ AND """ query += """ transaction_type = %(transaction_type)s""" separator_needed = True if "business_unit" in params and params["business_unit"]: if separator_needed: query += """ AND """ query += """ business_unit = %(business_unit)s""" separator_needed = True if "family_of_business" in params and params["family_of_business"]: if separator_needed: query += """ AND """ query+= """ family_of_business = %(family_of_business)s""" if "line_of_business" in params and params["line_of_business"]: if separator_needed: query += """ AND """ query += """ line_of_business = %(line_of_business)s""" separator_needed = True if "signature" in params and params["signature"]: if separator_needed: query += """ AND """ query += """ signature = %(signature)s""" separator_needed = True if "status" in params and params["status"]: if separator_needed: query += """ AND """ query += """ status = %(status)s""" separator_needed = True if "owner" in params and params["owner"]: if separator_needed: query += """ AND """ query += """ owner = %(owner)s""" separator_needed = True if "actor" in params and params["actor"]: if separator_needed: query += """ AND """ query += """ actor = %(actor)s""" separator_needed = True if "entity" in params and params["entity"]: if separator_needed: query += """ AND""" query += """ entity = %(entity)s""" separator_needed = True if "create_ts" in params: if separator_needed: query += """ AND""" if '-' in params["create_ts"]: # if it is timestamp >= UNIX-epoch timecode if params["create_ts"].index('-') == 0: start_time = time.strftime('%Y-%m-%d %H:%M:%S',time.gmtime(float(params["create_ts"][1:]))) params["start_time"] = start_time query += """ create_ts >= %(start_time)s""" elif params["create_ts"].endswith('-'): end_time = time.strftime('%Y-%m-%d %H:%M:%S',time.gmtime(float(params["create_ts"][:len(params["create_ts"])-1]))) params["end_time"] = end_time query += """ create_ts <= %(end_time)s""" else: start_time = time.strftime('%Y-%m-%d %H:%M:%S',time.gmtime(float(params["create_ts"][:params["create_ts"].index('-')]))) end_time = time.strftime('%Y-%m-%d %H:%M:%S', time.gmtime(float(params["create_ts"][params["create_ts"].index('-')+1:]))) params["start_time"] = start_time params["end_time"] = end_time query += """ create_ts >= %(start_time)s AND create_ts <= %(end_time)s""" else: cur_time = time.strftime('%Y-%m-%d %H:%M:%S', time.gmtime(float(params["create_ts"]))) params["cur_time"] = cur_time query += """ create_ts = %(cur_time)s""" separator_needed = True if "transaction_ts" in params: print("transaction_ts") if separator_needed: query += """ AND""" if '-' in params["transaction_ts"]: # if it is timestamp >= UNIX-epoch timecode if params["transaction_ts"].index('-') == 0: start_time = time.strftime('%Y-%m-%d %H:%M:%S', time.gmtime(float(params["transaction_ts"][1:]))) params["start_time"] = start_time query += """ transaction_ts >= %(start_time)s""" elif params["transaction_ts"].endswith('-'): end_time = time.strftime('%Y-%m-%d %H:%M:%S', time.gmtime(float(params["create_ts"][:len(params["transaction_ts"]) - 1]))) params["end_time"] = end_time query += """ transaction_ts <= %(end_time)s""" else: start_time = time.strftime('%Y-%m-%d %H:%M:%S',time.gmtime(float(params["create_ts"][:params["transaction_ts"].index('-')]))) end_time = time.strftime('%Y-%m-%d %H:%M:%S', time.gmtime(float(params["transaction_ts"][params["create_ts"].index('-') + 1:]))) params["start_time"] = start_time params["end_time"] = end_time query += """ transaction_ts >= %(start_time)s AND transaction_ts <= %(end_time)s""" else: cur_time = time.strftime('%Y-%m-%d %H:%M:%S', time.gmtime(float(params["transaction_ts"]))) params["cur_time"] = cur_time query += """ transaction_ts = %(cur_time)s""" separator_needed = True # not used but left in place to handle future params query += """ ORDER BY transaction_ts DESC """ if not limit: limit = 10 if limit: params["limit"] = limit query += """ LIMIT %(limit)s""" if offset: params["offset"] = offset query += """ OFFSET $(offset)s""" conn = get_connection_pool().getconn() try: cur = conn.cursor(get_cursor_name(), cursor_factory=psycopg2.extras.DictCursor) cur.execute(query, params) 'An iterator that uses fetchmany to keep memory usage down' while True: results = cur.fetchmany(DEFAULT_PAGE_SIZE) if not results: break for result in results: yield format_transaction(result) cur.close() finally: get_connection_pool().putconn(conn)