def test_transaction(self):
self.reopen(autocommit=False)
with self.conn.cursor() as cursor:
self.createTable(
"test",
"CREATE TABLE {table} (id INTEGER PRIMARY KEY, text VARCHAR)")
cursor.execute("UPSERT INTO test VALUES (?, ?)", [1, 'one'])
cursor.execute("SELECT * FROM test ORDER BY id")
self.assertEqual(cursor.fetchall(), [])
self.conn.commit()
cursor.execute("SELECT * FROM test ORDER BY id")
self.assertEqual(cursor.fetchall(), [[1, 'one']])
self.assertEqual(self.conn.autocommit, False)
cursor.execute("UPSERT INTO test VALUES (?, ?)", [2, 'two'])
self.conn.rollback()
cursor.execute("SELECT * FROM test ORDER BY id")
self.assertEqual(cursor.fetchall(), [[1, 'one']])
self.assertEqual(self.conn.autocommit, False)
cursor.execute("UPSERT INTO test VALUES (?, ?)", [2, 'two'])
# Since we expose the JDBC semantics, this is an implicit commit
self.conn.autocommit = True
cursor.execute("SELECT * FROM test ORDER BY id")
self.assertEqual(cursor.fetchall(), [[1, 'one'], [2, 'two']])
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 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 _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 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 _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 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 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 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 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 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 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)])
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 test_schema(self):
with self.conn.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
self.createTable("test_schema.test", "CREATE TABLE {table} (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 generateNewToken(apikey, apisecret):
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 apikey = ? AND apisecret= ? "
cursor.execute(cmd, (apikey, apisecret),)
dataUser = cursor.fetchall()
result = {}
if len(dataUser) > 0:
#check expiry token existing
for data in dataUser:
userID = data[0]
expiry = data[1]
token = data[2]
if token != None:
if expiry > datetime.now().strftime('%Y-%m-%d %H:%M:%S'):
print("Token masih aktif")
newToken = token
expiryToken = expiry
else:
# set token 1 hours
print("Token expired 1")
newToken = generateUniqeID('T0K247')
expiryTokenOneHours = datetime.now() + timedelta(hours=1)
expiryToken = expiryTokenOneHours.strftime('%Y-%m-%d %H:%M:%S')
# upsert
cmd = "UPSERT INTO FHIR247.UI_USERS(user_id, token, expiry_token) SELECT user_id, '" + newToken + "', TO_DATE('" + expiryToken + "', 'yyyy-MM-dd HH:mm:ss') FROM FHIR247.UI_USERS WHERE user_id = '" + userID + "'"
cursor.execute(cmd)
else:
#set token 1 hours
print("Token expired 2")
newToken = generateUniqeID('T0K247')
expiryTokenOneHours = datetime.now() + timedelta(hours=1)
expiryToken = expiryTokenOneHours.strftime('%Y-%m-%d %H:%M:%S')
#upsert
cmd = "UPSERT INTO FHIR247.UI_USERS(user_id, token, expiry_token) SELECT user_id, '"+ newToken +"', TO_DATE('"+ expiryToken +"', 'yyyy-MM-dd HH:mm:ss') FROM FHIR247.UI_USERS WHERE user_id = '"+ userID + "'"
cursor.execute(cmd)
result = {"status": 200, "token": newToken, "expiry": expiryToken}
else:
result = {"status": 200, "message": "Apikey and apisecret is not exist."}
connDB.close()
return result
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 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 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 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 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 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)])
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 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 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 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 test_param_number_mismatch(self):
self.createTable("phoenixdb_test_param_number", "CREATE TABLE {table} (id INTEGER PRIMARY KEY, username VARCHAR, name VARCHAR)")
with self.conn.cursor() as cursor:
cursor.execute("UPSERT INTO phoenixdb_test_param_number VALUES (?, ?, ?)", (123, 'John Doe', 'Doe'))
cursor.execute("SELECT * FROM phoenixdb_test_param_number")
self.assertEqual(cursor.fetchall(), [
[123, 'John Doe', 'Doe']
])
with self.assertRaises(ProgrammingError) as cm:
cursor.execute("UPSERT INTO phoenixdb_test_param_number VALUES (?, ?)", (123, 'John Doe', 'admin'))
self.assertEqual("Number of placeholders (?) must match number of parameters."
" Number of placeholders: 2. Number of parameters: 3", cm.exception.message)
with self.assertRaises(ProgrammingError) as cm:
cursor.execute("UPSERT INTO phoenixdb_test_param_number VALUES (?, ?, ?)", (123, 'John Doe', 'admin', 'asd'))
self.assertEqual("Number of placeholders (?) must match number of parameters."
" Number of placeholders: 3. Number of parameters: 4", cm.exception.message)
with self.assertRaises(ProgrammingError) as cm:
cursor.execute("UPSERT INTO phoenixdb_test_param_number VALUES (?, ?, ?)", (123, 'John Doe'))
self.assertEqual("Number of placeholders (?) must match number of parameters."
" Number of placeholders: 3. Number of parameters: 2", cm.exception.message)
def getApikeyApiSecretSuperAdmin(token):
# check from db
connDB = phoenixConn()
cursor = connDB.cursor()
cmd = "SELECT user_id, apikey, apisecret FROM FHIR247.UI_USERS WHERE token = '" + token + "' "
cursor.execute(cmd)
dataUser = cursor.fetchall()
connDB.close()
if len(dataUser) > 0:
for data in dataUser:
# userID = data[0]
apiKey = data[1]
apiSecret = data[2]
if apiKey == '64cb156db93458ac469e8a3562b7892fa0d08597' and apiSecret == 'e5101c5b7dac13db925b99a53d174bbdc648b0eb':
return True
else:
return False
else:
return False
def isdataready(date, source):
"""
判断某个时间的数据是否全部到达。
:param date: 判断的日期。（可为小时或天）
:param source: 数据来源
:return: 数据是否已经到达
"""
date = str(date) + '23' if len(str(date)) == 8 else str(date)
conn = phoenixdb.connect(HbaseMysqlConfig.HBASE_IP, autocommit=True)
cursor = conn.cursor()
if source == 'cctv5':
table = HbaseMysqlConfig.CCTV5_HBASE_TABLE
threshold = 20000
elif source == 'finance':
table = HbaseMysqlConfig.FINANCE_HBASE_TABLE
threshold = 300
elif source == 'children':
table = HbaseMysqlConfig.CHILDREN_HBASE_TABLE
threshold = 50
elif source == 'music':
table = HbaseMysqlConfig.MUSIC_HBASE_TABLE
threshold = 100
else:
table = ''
threshold = 100
sql = "select count(*) from {table} where ymdh={ymdh}".format(table=table,
ymdh=date)
cursor.execute(sql)
result = cursor.fetchall()
cursor.close()
conn.close()
if int(result[0][0]) > threshold:
return True
else:
return False
def getGroupbyID(id):
cmd = "SELECT group_id, name, description FROM FHIR247.UI_GROUPS WHERE group_id = '" + id + "' ORDER BY group_id "
conn = phoenixConn()
cursor = conn.cursor()
cursor.execute(cmd)
listGroup = cursor.fetchall()
conn.close()
if len(listGroup) > 0:
GroupJSON = {}
for data in listGroup:
GroupJSON["id"] = data[0]
GroupJSON["name"] = data[1]
GroupJSON["description"] = data[2]
result = {"status": 200, "data": GroupJSON}
else:
result = {"status": 404, "message": "Group ID is not found."}
return result
def isdataready(date):
"""
判断某个时间的数据是否全部到达。
:param date: 判断的日期。（可为小时或天）
:return: 数据是否已经到达
"""
date = str(date) + '23' if len(str(date)) == 8 else str(date)
conn = phoenixdb.connect(HbaseMysqlConfig.HBASE_IP, autocommit=True)
cursor = conn.cursor()
sql = "select count(*) from {table} where ymdh={ymdh}".format(
table=HbaseMysqlConfig.HBASE_TABLE, ymdh=date)
cursor.execute(sql)
result = cursor.fetchall()
cursor.close()
conn.close()
if int(result[0][0]) > 500:
return True
else:
return False
# 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())
