idds/tests/run_sql.py

0001 #!/usr/bin/env python
0002 #
0003 # Licensed under the Apache License, Version 2.0 (the "License");
0004 # You may not use this file except in compliance with the License.
0005 # You may obtain a copy of the License at
0006 # http://www.apache.org/licenses/LICENSE-2.0OA
0007 #
0008 # Authors:
0009 # - Wen Guan, <wen.guan@cern.ch>, 2019
0010
0011
0012 """
0013 performance test to insert contents.
0014 """
0015 import json
0016 import cx_Oracle
0017
0018
0019 from idds.common.config import config_get
0020 # from idds.core.contents import add_content
0021
0022
0023 def get_subfinished_requests(db_pool):
0024     connection = db_pool.acquire()
0025
0026     req_ids = []
0027     # sql = """select request_id from atlas_IDDS.requests where status in (4,5) and scope!='hpo'"""
0028     sql = """select request_id from atlas_IDDS.requests where scope!='hpo' and ( status in (4,5) or request_id in (select request_id from atlas_idds.transforms where status in (4, 5) and transform_type=2)) order by request_id"""
0029     sql = """select request_id from atlas_idds.collections where status=4 and total_files > processed_files order by request_id asc"""
0030     sql = """select request_metadata, processing_metadata from atlas_idds.requests where request_id in (283511)"""
0031
0032     cursor = connection.cursor()
0033     cursor.execute(sql)
0034     rows = cursor.fetchall()
0035     for row in rows:
0036         # print(row)
0037         # print(row[0])
0038         data = json.loads(row[0].read())
0039         print(json.dumps(data, sort_keys=True, indent=4))
0040         req_ids.append(row[0])
0041     cursor.close()
0042
0043     connection.commit()
0044     db_pool.release(connection)
0045     print(len(req_ids))
0046
0047     print(req_ids)
0048
0049
0050 def get_session_pool():
0051     sql_connection = config_get('database', 'default')
0052     sql_connection = sql_connection.replace("oracle://", "")
0053     user_pass, tns = sql_connection.split('@')
0054     user, passwd = user_pass.split(':')
0055     db_pool = cx_Oracle.SessionPool(user, passwd, tns, min=12, max=20, increment=1)
0056     return db_pool
0057
0058
0059 def test():
0060     pool = get_session_pool()
0061     get_subfinished_requests(pool)
0062
0063
0064 if __name__ == '__main__':
0065     test()