def test_case_sensitivity(self):
with self.conn.cursor() as cursor:
try:
cursor.execute('drop table if exists AAA')
cursor.execute('drop table if exists "aaa"')
cursor.execute('drop table if exists "Aaa"')
cursor.execute('create table AAA (ID integer primary key, YYY integer)')
cursor.execute('create table "aaa" ("ID_x" integer primary key, YYY integer, "Yyy" integer, "yyy" integer)')
cursor.execute('create table "Aaa" (ID_X integer primary key, ZZZ integer, "Zzz" integer, "zzz" integer)')
cursor.execute('upsert into AAA values (1, 2)')
cursor.execute('upsert into "aaa" values (11, 12, 13, 14)')
cursor.execute('upsert into "Aaa" values (21, 22, 23, 24)')
cursor.execute('select YYY from AAA')
self.assertEqual(cursor.fetchone(), [2])
cursor.execute('select YYY from "aaa"')
self.assertEqual(cursor.fetchone(), [12])
cursor.execute('select "YYY" from "aaa"')
self.assertEqual(cursor.fetchone(), [12])
cursor.execute('select "Yyy" from "aaa"')
self.assertEqual(cursor.fetchone(), [13])
meta = self.conn.meta()
self.assertEquals(len(meta.get_tables(schemaPattern='')), 3)
print(meta.get_columns(schemaPattern='',
tableNamePattern='"aaa"'))
self.assertEquals(len(meta.get_tables(schemaPattern='',
tableNamePattern='AAA')), 1)
self.assertEquals(len(meta.get_tables(schemaPattern='',
tableNamePattern='"aaa"')), 1)
self.assertEquals(meta.get_columns(tableNamePattern='AAA',
columnNamePattern='YYY'), 1)
self.assertEquals(meta.get_columns(tableNamePattern='AAA',
columnNamePattern='yyy'), 1)
self.assertEquals(meta.get_columns(tableNamePattern='AAA',
columnNamePattern='"yyy"'), 0)
finally:
cursor.execute('drop table if exists AAA')
cursor.execute('drop table if exists "aaa"')
cursor.execute('drop table if exists "Aaa"')
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 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 query_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:
cursor.execute(query_sqoop_meta_sql, job_id)
fetchone = cursor.fetchone()
conn.close()
return fetchone
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 getUserIDbyToken(token):
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 user_id FROM FHIR247.UI_USERS WHERE token = '"+ token +"' "
cursor.execute(cmd)
dataUser = cursor.fetchone()
if len(dataUser) > 0:
userID = dataUser[0]
result = {"status": 200, "userId": userID, "super_admin": False}
else:
result = {"status": 404, "message": "Token is not found!"}
connDB.close()
return result
"filter_LIKE_JLDW": "",
"filter_LIKE_SJDW": "",
"currentPage": currentPage,
"pageSize": 15,
"OrderByField": "",
"OrderByDesc": ""
}
database_url = 'http://node1:8765/'
phoenix_conn = phoenixdb.connect(database_url, autocommit=True)
cursor = phoenix_conn.cursor(cursor_factory=phoenixdb.cursor.DictCursor)
try:
cursor.execute(
"SELECT * FROM THIRDWORK_PERMIT ORDER BY case when PERMITDATE is null then 1 else 0 end asc ,PERMITDATE desc limit 1")
date = cursor.fetchone()['PERMITDATE'].strftime('%Y-%m-%d')
cursor.execute("SELECT * FROM THIRDWORK_PERMIT ORDER BY id desc limit 1")
id = cursor.fetchone()['ID']
flag = True
session = getSession(url, headers, params)
urlList = []
while flag:
time.sleep(3)
r = getRequest(url, headers, params, session)
print(r.status_code)
bsObj = BeautifulSoup(r.text, 'html.parser')
# print(bsObj)
aTags = bsObj.find_all("a", text="查看")
for a in aTags:
td = a.parent
import phoenixdb.cursor
'''
使用python3
远程连接phoneix query server服务
'''
database_url = 'http://129.28.191.99:8765/'
conn = phoenixdb.connect(database_url, autocommit=True)
cursor = conn.cursor()
# ddl
cursor.execute("DROP TABLE users ")
cursor.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, username VARCHAR)")
# dml 如果对应PRIMARY KEY有数据就更新，没就插入一条
cursor.execute("UPSERT INTO users VALUES (?, ?)", (1, 'admin'))
cursor.execute("SELECT * FROM users")
print(cursor.fetchall())
cursor = conn.cursor(cursor_factory=phoenixdb.cursor.DictCursor)
cursor.execute("SELECT * FROM users WHERE id=1")
print(cursor.fetchone()['USERNAME'])
"filter_LIKE_BABH": "",
"currentPage": currentPage,
"pageSize": 15,
"OrderByField": "",
"OrderByDesc": ""
}
database_url = 'http://node1:8765/'
phoenix_conn = phoenixdb.connect(database_url, autocommit=True)
cursor = phoenix_conn.cursor(cursor_factory=phoenixdb.cursor.DictCursor)
try:
cursor.execute(
"SELECT * FROM THIRDWORK_FINISH ORDER BY case when FINISHDATE is null then 1 else 0 end asc ,FINISHDATE desc limit 1"
)
date = cursor.fetchone()['FINISHDATE'].strftime('%Y-%m-%d')
cursor.execute("SELECT * FROM THIRDWORK_FINISH ORDER BY id desc limit 1")
id = cursor.fetchone()['ID']
flag = True
session = getSession(url, headers, params)
while flag:
time.sleep(3)
r = getRequest(url, headers, params, session)
print(r.status_code)
bsObj = BeautifulSoup(r.text, 'html.parser')
table = bsObj.find('table', {"class": "gridview"})
if table is None:
print("连接不上")
continue
trs = table.find_all("tr")
import phoenixdb.cursor
# 使用python需在环境中安装phoenix-python驱动，参见https://python-phoenixdb.readthedocs.io/en/latest/
if __name__ == "__main__":
# url修改为运行环境的QueryServer连接地址
database_url = 'http://localhost:8765'
# python客户端仅支持自动提交方式
conn = phoenixdb.connect(database_url, autocommit=True)
cursor = conn.cursor()
cursor.execute("DROP TABLE IF EXISTS users")
# 创建测试表
cursor.execute(
"CREATE TABLE users (id INTEGER PRIMARY KEY, username VARCHAR)")
# 创建索引
cursor.execute("CREATE INDEX users_index ON users (username)")
# 插入一条数据
cursor.execute("UPSERT INTO users VALUES (?, ?)", (11, 'admin'))
# 批量插入数据
param = ((1, "tom"), (2, "alice"), (3, "bob"))
cursor.executemany("UPSERT INTO users VALUES (?, ?)", param)
# 查询数据
cursor.execute("SELECT * FROM users")
print cursor.fetchall()
cursor = conn.cursor(cursor_factory=phoenixdb.cursor.DictCursor)
# query data use index
cursor.execute("SELECT * FROM users WHERE username='******'")
print cursor.fetchone()['ID']
phoenix_conn = phoenixdb.connect(database_url, autocommit=True)
cursor = phoenix_conn.cursor(cursor_factory=phoenixdb.cursor.DictCursor)
chrome_options = Options()
chrome_options.add_argument('--headless')
chrome_options.add_argument('--disable-gpu')
driver = webdriver.Chrome(
executable_path=r'D:\chromedriver_win32\chromedriver.exe',
chrome_options=chrome_options)
# driver = webdriver.PhantomJS(executable_path=r'D:\phantomjs-2.1.1-windows\bin\phantomjs')
# driver = webdriver.Chrome(executable_path=r'D:\chromedriver_win32\chromedriver.exe')
try:
returnNumber = 0
cursor.execute(
"SELECT * FROM THIRDWORK_VOCATION ORDER BY date_start desc limit 1")
date = cursor.fetchone()['DATE_START'].strftime('%Y')
cursor.execute("SELECT * FROM THIRDWORK_VOCATION ORDER BY id desc limit 1")
id = cursor.fetchone()['ID']
print(date, id)
# driver.get("http://pythonscraping.com/pages/javascript/ajaxDemo.html")
# time.sleep(3)
# print(driver.find_element_by_id('content').text)
# print(driver.page_source)
# driver.close()
# flag = True
nextYear = str((int(date) + 1))
keyword = r"^国务院办公厅.*" + nextYear + ".*节假日.*"
number = r"^[一、|二、|三、|四、|五、|六、|七、|八、|九、|十、].*"
print(keyword)
# driver.find_element_by_css_selector('.cate-box-policy cate-group-infos')
import phoenixdb
import phoenixdb.cursor
import pandas as pd
database_url = 'http://18.188.70.4:8765/'
conn = phoenixdb.connect(database_url, autocommit=True)
cursor = conn.cursor()
############################
# ###### Easy Queries ######
############################
cursor.execute("select count(*) from \"server_logs_test\"")
Total_rows = int(cursor.fetchone()[0])
## QUERY 1 ##
cursor.execute(
"select \"type\", count(*) as counts from \"server_logs_test\" group by \"type\""
)
Type_counts = pd.DataFrame(cursor.fetchall())
Type_counts.columns = ["Type", "Counts"]
# computing percentage of GET,POST and HEAD requests
Type_counts['Percent'] = Type_counts.Counts / Total_rows * 100
# Printing to console
print("Query 1: Get Percentage of GET, POST and HEAD requests")
print(Type_counts)
