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()
