def query_join(conn, columns_list, table0, table1, join_column0, join_column1, join_type, table1_columns): with conn.cursor() as cursor: join_str = "" if join_type == "left": join_str = "left join" elif join_type == "right": join_str = "right join" elif join_type == "inner": join_str = "inner join" elif join_type == "full": join_str = "full join" query_sql = join_query_sql % (table0, join_str, table1, join_column0, join_column1) cursor.execute(query_sql) fetchall = cursor.fetchall() # 删除第一二行的ROW fetchall_list = [] table1_column_list = table1_columns.split("^") for fetchone in fetchall: size_index = len(columns_list) - len(table1_column_list) + 1 fetchone.pop(size_index) fetchone.pop(0) fetchall_list.append(fetchone) return fetchall_list
def get_phoenix_tab_exists_rows(config, tab): db_phoenix = config['db_phoenix'] cursor = db_phoenix.cursor() v_new_tab = '"{0}"."{1}"'.format(config['db_phoenix_service'], tab) cursor.execute('select count(0) from {0}'.format(v_new_tab)) rs = cursor.fetchone() return rs[0]
def write_phoenix_rows(config, tab, row_data): v_rkey = '' d_cols = {} db_phoenix = config['db_phoenix'] v_new_tab = '"{0}"."{1}"'.format(config['db_phoenix_service'], tab) pk_name = '_id' cursor = db_phoenix.cursor() for r in row_data: v_header = 'UPSERT INTO {0} ('.format(v_new_tab) v_values = '' for key in r: if r[key]['value'] is not None: v_header = v_header + '"' + key + '",' if r[key]['type'] in (1, 12): # char,varchar v_values = v_values + "'" + format_sql(str( r[key]['value'])) + "'," elif r[key]['type'] in (-5, 4, 8): # bigint,integer,double v_values = v_values + str(r[key]['value']) + "," else: v_values = v_values + "'" + format_sql(str( r[key]['value'])) + "'," v_upsert = v_header[0:-1] + ') values (' + v_values[0:-1] + ')' try: cursor.execute(v_upsert) except Exception as e: print('ERROR:', str(e)) print(v_upsert)
def checkPrivilegeMenu(token, menu): resultSecret = getApikeyApiSecretSuperAdmin(token) if resultSecret: result = {"status": 200, "message": "This is token SuperAdmin!", "super_admin": True} else: #check from db connDB = phoenixConn() cursor = connDB.cursor() cmd = "SELECT privilege_id, has_insert, has_update, has_delete, has_view FROM FHIR247.UI_PRIVILEGES p, " \ "FHIR247.UI_GROUPS g, FHIR247.UI_MENU m, FHIR247.UI_USERS u " \ "WHERE p.group_id = g.group_id AND p.menu_id = m.menu_id AND g.group_id = u.group_id AND " \ "u.token = '"+ token +"' AND m.menu_id = '"+ menu +"' " cursor.execute(cmd) dataPrivileges = cursor.fetchall() if len(dataPrivileges) > 0: for privilege in dataPrivileges: privilegeId = privilege[0] hasInsert = privilege[1] hasUpdate = privilege[2] hasDelete = privilege[3] hasView = privilege[4] result = {"status": 200, "privilegeId": privilegeId, "hasInsert": hasInsert, "hasUpdate": hasUpdate, "hasDelete": hasDelete, "hasView": hasView, "super_admin": False} else: result = {"status": 404, "message": "Permission denied, user privileges not found!"} connDB.close() return result
def clear_status_table(cls, conn): cursor = conn.cursor() cursor.execute('delete from SYSTEM.STATS \ where PHYSICAL_NAME =\'data_report_keywords\'') cursor.execute('delete from SYSTEM.STATS \ where PHYSICAL_NAME =\'data_report_product_ads\'') logger.info('clear system.status of hbase table')
def getMenubyID(id): cmd = "SELECT menu_id, menu_parent, menu_label, menu_name, menu_description, menu_active, menu_url, menu_config, menu_icon, menu_sort " \ "FROM FHIR247.UI_MENU WHERE menu_id = '" + id + "'" conn = phoenixConn() cursor = conn.cursor() cursor.execute(cmd) listMenu = cursor.fetchall() conn.close() if len(listMenu) > 0: MenuJSON = {} for data in listMenu: MenuJSON["id"] = data[0] MenuJSON["parent"] = data[1] MenuJSON["label"] = data[2] MenuJSON["name"] = data[3] MenuJSON["description"] = data[4] MenuJSON["active"] = data[5] MenuJSON["url"] = data[6] MenuJSON["config"] = data[7] MenuJSON["icon"] = data[8] MenuJSON["sort"] = data[9] result = {"status": 200, "data": MenuJSON} else: result = {"status": 404, "message": "Menu ID is not found."} return result
def _check_dict_cursor(self, cursor): self.createTable( "test", "CREATE TABLE {table} (id INTEGER PRIMARY KEY, text VARCHAR)") cursor.execute("UPSERT INTO test VALUES (?, ?)", [1, 'text 1']) cursor.execute("SELECT * FROM test ORDER BY id") self.assertEqual(cursor.fetchall(), [{'ID': 1, 'TEXT': 'text 1'}])
def getPrivilegeByID(id): cmd = "SELECT privilege_id, has_insert, has_update, has_delete, has_view, has_approval " \ "FROM FHIR247.UI_PRIVILEGES WHERE privilege_id = '" + id + "'" conn = phoenixConn() cursor = conn.cursor() cursor.execute(cmd) listPrivilege = cursor.fetchall() conn.close() if len(listPrivilege) > 0: PrivilegeJSON = {} for data in listPrivilege: PrivilegeJSON["id"] = data[0] PrivilegeJSON["hasInsert"] = data[1] PrivilegeJSON["hasUpdate"] = data[2] PrivilegeJSON["hasDelete"] = data[3] PrivilegeJSON["hasView"] = data[4] PrivilegeJSON["hasApproval"] = data[5] result = {"status": 200, "data": PrivilegeJSON} else: result = {"status": 404, "message": "Privilege ID is not found."} return result
def tokenValidate(token): result = {} resultSecret = getApikeyApiSecretSuperAdmin(token) if resultSecret: result = {"status": 200, "message": "This is token SuperAdmin!"} else: #check from db connDB = phoenixConn() cursor = connDB.cursor() cmd = "SELECT user_id, TO_CHAR(expiry_token, 'yyyy-MM-dd HH:mm:ss'), token FROM FHIR247.UI_USERS WHERE token = '"+ token +"' " cursor.execute(cmd) dataUser = cursor.fetchall() if len(dataUser) > 0: for data in dataUser: # userID = data[0] expiry = data[1] # token = data[2] if expiry > datetime.now().strftime('%Y-%m-%d %H:%M:%S'): result = {"status": 200, "message": "Token is valid!"} else: result = {"status": 403, "message": "Token is expired!"} else: result = {"status": 404, "message": "Token is not found!"} connDB.close() return result
def test_select_parameter(self): with self.conn.cursor() as cursor: self.createTable("test", "CREATE TABLE {table} (id INTEGER PRIMARY KEY, text VARCHAR)") cursor.executemany("UPSERT INTO test VALUES (?, ?)", [[i, 'text {}'.format(i)] for i in range(10)]) with self.conn.cursor() as cursor: cursor.itersize = 4 cursor.execute("SELECT * FROM test WHERE id>? ORDER BY id", [1]) self.assertEqual(cursor.fetchall(), [[i, 'text {}'.format(i)] for i in range(2, 10)])
def get_fields(table: str, cursor): query = f"""select column_name from system.catalog where table_schem is NULL and table_name = '{table}' and column_name is not null order by ordinal_position """ cursor.execute(query, parameters=()) resp = cursor.fetchall() fields = [i for sublist in resp for i in sublist] if resp is not None else list() return fields
def create_phoenix_table(create_sql): phoenix_app = create_app(get_config('develop')) phoenix_app.app_context() database_url = phoenix_app.config['DATABASE_URL'] conn = phoenixdb.connect(database_url, autocommit=True) with conn.cursor() as cursor: cursor.execute(create_sql) conn.close()
def drop_table(table_name): phoenix_app = create_app(get_config('develop')) phoenix_app.app_context() database_url = phoenix_app.config['DATABASE_URL'] conn = phoenixdb.connect(database_url, autocommit=True) with conn.cursor(cursor_factory=phoenixdb.cursor.DictCursor) as cursor: drop_sql = "drop table \"" + table_name + "\"" cursor.execute(drop_sql) conn.close()
def execute(self, sql): try: # 创建游标 cursor = self.conn.cursor(cursor_factory=phoenixdb.cursor.Cursor) # 执行sql语句 cursor.execute(sql) # 关闭游标 cursor.close() except Exception as e: raise Exception('执行异常>>> {}'.format(e))
def query_dp_data(query_sql): phoenix_app = create_app(get_config('develop')) phoenix_app.app_context() database_url = phoenix_app.config['DATABASE_URL'] conn = phoenixdb.connect(database_url, autocommit=True) with conn.cursor() as cursor: cursor.execute(query_sql) fetchall = cursor.fetchall() conn.close() return fetchall
def getCountDataOfTable(table): connDB = phoenixConn() cursor = connDB.cursor() cmd = "SELECT COUNT(1) total FROM " + table cursor.execute(cmd) total = cursor.fetchone() connDB.close() return total[0]
def insert_sqoop_meta(job_id): phoenix_app = create_app(get_config('develop')) phoenix_app.app_context() database_url = phoenix_app.config['DATABASE_URL'] conn = phoenixdb.connect(database_url, autocommit=True) with conn.cursor() as cursor: now_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') cursor.execute(insert_sqoop_meta_sql, (job_id, now_time, now_time)) conn.close()
def query(cls, sql_str, params=None): conn = phoenixdb.connect(hbase_url, autocommit=True) cursor = conn.cursor(cursor_factory=phoenixdb.cursor.DictCursor) try: time_start = time.time() cursor.execute(sql_str, params) if time.time() - time_start > 30: HBaseHelper.clear_status_table(conn) except Exception as ex: HBaseHelper.clear_status_table(conn) raise ex return cursor.fetchall()
def query(self): """ 查询函数,查询sql并将结果赋予self.data。 """ conn = phoenixdb.connect(HbaseMysqlConfig.HBASE_IP, autocommit=True) cursor = conn.cursor() cursor.execute(self.sql) result = cursor.fetchall() cursor.close() conn.close() self.data = result
def _check_dict_cursor(self, cursor): cursor.execute("DROP TABLE IF EXISTS test") cursor.execute( "CREATE TABLE test (id INTEGER PRIMARY KEY, text VARCHAR)") cursor.execute("UPSERT INTO test VALUES (?, ?)", [1, 'text 1']) cursor.execute("SELECT * FROM test ORDER BY id") self.assertEqual(cursor.fetchall(), [{'ID': 1, 'TEXT': 'text 1'}])
def check_phoenix_table(config, tname): db_phoenix = config['db_phoenix'] cursor = db_phoenix.cursor() v_new_tab = '"{0}"."{1}"'.format(config['db_mysql_service'], tname) v_cre_tab = """CREATE TABLE {0}("id" VARCHAR PRIMARY KEY)""".format( v_new_tab) v_drp_tab = """drop TABLE {0}""".format(v_new_tab) try: cursor.execute(v_cre_tab) cursor.execute(v_drp_tab) print('hbase table :{0} not exists!'.format(tname)) return False except: print('hbase table :{0} exists!'.format(tname)) return True
def insert_join_data(columns_str_meta, conn, fetchall_list, tmp_table, tmp_table_uuid): with conn.cursor() as cursor: insert_join_sql = "UPSERT INTO \"" + tmp_table_uuid + "\" VALUES (?" size = len(columns_str_meta.split("^")) for i in range(size - 1): insert_join_sql += ", ?" insert_join_sql += ")" for fetchone in fetchall_list: fetchone.insert(0, uuid1().hex) cursor.execute(insert_join_sql, fetchone) # 删除临时表及临时元数据 if tmp_table != "": drop_table(tmp_table) delete_meta_table(tmp_table)
def getUserByEmailOrUsername(username, email): cmd = "SELECT user_id, username, firstname, lastname, fullname, email," \ " TO_CHAR(last_login, 'yyyy-MM-dd HH:mm:ss'), TO_CHAR(last_activity, 'yyyy-MM-dd HH:mm:ss'), is_login, is_block, " \ " TO_CHAR(first_login, 'yyyy-MM-dd HH:mm:ss'), TO_CHAR(last_pass_change, 'yyyy-MM-dd HH:mm:ss'), is_active, " \ " TO_CHAR(created_date, 'yyyy-MM-dd HH:mm:ss'), created_by, TO_CHAR(updated_date, 'yyyy-MM-dd HH:mm:ss'), updated_by, is_deleted, " \ " apikey, apisecret, token, group_id, password " \ " FROM FHIR247.UI_USERS WHERE username = '******' OR email = '"+ email +"' ORDER BY user_id " conn = phoenixConn() cursor = conn.cursor() cursor.execute(cmd) listUser = cursor.fetchall() conn.close() if len(listUser) > 0: UserJSON = {} for data in listUser: UserJSON["id"] = data[0] UserJSON["username"] = data[1] UserJSON["firstname"] = data[2] UserJSON["lastname"] = data[3] UserJSON["fullname"] = data[4] UserJSON["email"] = data[5] UserJSON["lastLogin"] = data[6] UserJSON["lastActivity"] = data[7] UserJSON["isLogin"] = data[8] UserJSON["isBlock"] = data[9] UserJSON["firstLogin"] = data[10] UserJSON["lastPassChange"] = data[11] UserJSON["isActive"] = data[12] UserJSON["createdDate"] = data[13] UserJSON["createdBy"] = data[14] UserJSON["updatedDate"] = data[15] UserJSON["updatedBy"] = data[16] UserJSON["isDeleted"] = data[17] UserJSON["apiKey"] = data[18] UserJSON["apiSecret"] = data[19] UserJSON["token"] = data[20] UserJSON["groupId"] = data[21] UserJSON["password"] = data[22] result = {"status": 200, "data": UserJSON} else: result = {"status": 404, "message": "User is not found."} return result
def pandas_read_phoenix(cursor, sql_statement: str, batch=10): cursor.execute(sql_statement) rows = cursor.fetchall() cols = list(rows[0].keys()) df = pd.DataFrame(columns=cols) df_rows = [] for row in rows: df_rows.append(row) if len(df_rows) == batch: df = df.append(df_rows) df_rows.clear() return df
def query(self, sql, is_dict): try: # 判断是否需要返回结果为字典类型 if is_dict: cursor = self.conn.cursor(cursor_factory=phoenixdb.cursor.DictCursor) else: cursor = self.conn.cursor(cursor_factory=phoenixdb.cursor.Cursor) # 执行sql语句 cursor.execute(sql) # 查询结果 data = cursor.fetchall() # 关闭游标 cursor.close() return data except Exception as e: raise Exception('查询异常>>> {}'.format(e))
def getPersonbyID(id): cmd = "SELECT PERSON_ID FROM FHIR247.PERSON WHERE PERSON_ID = '" + id + "' ORDER BY PERSON_ID " conn = phoenixConn() cursor = conn.cursor() cursor.execute(cmd) listPerson = cursor.fetchall() conn.close() if len(listPerson) > 0: result = {"status": 200} else: result = {"status": 404, "message": "Person ID is not found."} return result
def getPatientbyID(id): cmd = "SELECT PATIENT_ID FROM FHIR247.PATIENT WHERE PATIENT_ID = '" + id + "' ORDER BY PATIENT_ID " conn = phoenixConn() cursor = conn.cursor() cursor.execute(cmd) listPatient = cursor.fetchall() conn.close() if len(listPatient) > 0: result = {"status": 200} else: result = {"status": 404, "message": "Patient ID is not found."} return result
def test_schema(self): db = phoenixdb.connect(TEST_DB_URL, autocommit=True) self.addCleanup(db.close) with db.cursor() as cursor: try: cursor.execute("CREATE SCHEMA IF NOT EXISTS test_schema") except InternalError as e: if "phoenix.schema.isNamespaceMappingEnabled" in e.message: self.skipTest(e.message) raise cursor.execute("DROP TABLE IF EXISTS test_schema.test") cursor.execute("CREATE TABLE test_schema.test (id INTEGER PRIMARY KEY, text VARCHAR)") cursor.execute("UPSERT INTO test_schema.test VALUES (?, ?)", [1, 'text 1']) cursor.execute("SELECT * FROM test_schema.test ORDER BY id") self.assertEqual(cursor.fetchall(), [[1, 'text 1']])
def easy_conn(): """ 基础操作phoenix :return: """ database_url = 'http://%s:8765/' % ZK_URL conn = phoenixdb.connect(database_url, autocommit=True) cursor = conn.cursor() cursor.execute("DROP TABLE users") cursor.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, username VARCHAR)") cursor.execute("UPSERT INTO users VALUES (?, ?)", (1, 'qjj')) cursor.execute("SELECT * FROM users") print(cursor.fetchall()) # 只能用在select之后,fetch之后清空cursor的内容,再fetch结果为空 # ----------------------------------------------- cursor = conn.cursor(cursor_factory=phoenixdb.cursor.DictCursor) # DictCursor :A cursor which returns results as a dictionary cursor.execute("SELECT * FROM users WHERE id=1") print(cursor.fetchone()['USERNAME'])
def usernameValidate(username): result = {} #check from db connDB = phoenixConn() cursor = connDB.cursor() cmd = "SELECT user_id FROM FHIR247.UI_USERS WHERE username = '******' " cursor.execute(cmd) dataUser = cursor.fetchall() if len(dataUser) > 0: result = {"status": 403, "message": "Username already exist!"} else: result = {"status": 200, "message": "Username ready to use."} connDB.close() return result
def getMenuByName(name): cmd = "SELECT menu_id, menu_parent, menu_label, menu_name, menu_description, menu_active, menu_url, menu_config, menu_icon, menu_sort " \ "FROM FHIR247.UI_MENU WHERE menu_name = '" + name + "'" conn = phoenixConn() cursor = conn.cursor() cursor.execute(cmd) listMenu = cursor.fetchall() conn.close() if len(listMenu) > 0: result = {"status": 403, "message": "Menu name already exist."} else: result = {"status": 200, "message": "Menu name is ready to use."} return result
def test_select_parameter(self): db = phoenixdb.connect(TEST_DB_URL, autocommit=True) self.addCleanup(db.close) with db.cursor() as cursor: cursor.execute("DROP TABLE IF EXISTS test") cursor.execute("CREATE TABLE test (id INTEGER PRIMARY KEY, text VARCHAR)") cursor.executemany("UPSERT INTO test VALUES (?, ?)", [[i, 'text {}'.format(i)] for i in range(10)]) with db.cursor() as cursor: cursor.itersize = 4 cursor.execute("SELECT * FROM test WHERE id>? ORDER BY id", [1]) self.assertEqual(cursor.fetchall(), [[i, 'text {}'.format(i)] for i in range(2, 10)])
# Use UPSERT import phoenixdb.cursor conn = phoenixdb.connect('http://localhost:8765', autocommit=True) cursor = conn.cursor(cursor_factory=phoenixdb.cursor.DictCursor) table = 'USERS' id_column = 'id' username_column = 'username' cursor.execute(f'DROP TABLE IF EXISTS {table}') cursor.execute(f'CREATE TABLE {table} ({id_column} INTEGER PRIMARY KEY, {username_column} VARCHAR)') cursor.execute(f"UPSERT INTO {table} ({id_column}, {username_column}) VALUES (1, 'admin')") cursor.execute(f"UPSERT INTO {table} ({id_column}, {username_column}) VALUES (2, 'guest')") cursor.execute(f'SELECT * FROM {table}', parameters=()) # "parameters" can't be None result = cursor.fetchall() assert str(result) == "[{'ID': 1, 'USERNAME': '******'}, {'ID': 2, 'USERNAME': '******'}]" cursor.close() conn.close()
def _check_dict_cursor(self, cursor): cursor.execute("DROP TABLE IF EXISTS test") cursor.execute("CREATE TABLE test (id INTEGER PRIMARY KEY, text VARCHAR)") cursor.execute("UPSERT INTO test VALUES (?, ?)", [1, 'text 1']) cursor.execute("SELECT * FROM test ORDER BY id") self.assertEqual(cursor.fetchall(), [{'ID': 1, 'TEXT': 'text 1'}])
# # http://www.apache.org/licenses/LICENSE-2.0 # # Unless required by applicable law or agreed to in writing, software # distributed under the License is distributed on an "AS IS" BASIS, # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. # See the License for the specific language governing permissions and # limitations under the License. # ############################################################################ import phoenixdb import phoenixdb.cursor import sys if __name__ == '__main__': pqs_port = sys.argv[1] database_url = 'http://localhost:' + str(pqs_port) + '/' print("CREATING PQS CONNECTION") conn = phoenixdb.connect(database_url, autocommit=True, auth="SPNEGO") cursor = conn.cursor() cursor.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, username VARCHAR)") cursor.execute("UPSERT INTO users VALUES (?, ?)", (1, 'admin')) cursor.execute("UPSERT INTO users VALUES (?, ?)", (2, 'user')) cursor.execute("SELECT * FROM users") print("RESULTS") print(cursor.fetchall())
# Create schema # (!) NOT WORK import phoenixdb.cursor conn = phoenixdb.connect('http://localhost:8765/', autocommit=True) cursor = conn.cursor(cursor_factory=phoenixdb.cursor.DictCursor) cursor.execute("CREATE SCHEMA ABC") # cursor.execute("CREATE SCHEMA IF NOT EXISTS ABC") cursor.close() conn.close()