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(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 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 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 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
# Read fields of a table
import phoenixdb.cursor
conn = phoenixdb.connect('http://localhost:8765', autocommit=True)
cursor = conn.cursor()
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
table = 'FIELD_NAME_TEST'
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)')
fields = get_fields(table, cursor)
cursor.close()
conn.close()
assert str(fields) == "['ID', 'USERNAME']"
# 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()