def get_hit_count():
retries = 5
while True:
try:
phoenixdb.connect("http://pqs:8765", autocommit=True)
except phoenixdb.errors.InterfaceError as exc:
raise exc
html = "<h3>Hello from {name}!</h3>" \
"Hostname: <b>{hostname}</b><br/>"
return html.format(name=os.getenv("NAME", "Standalone Flask App"),
hostname=socket.gethostname())
def create_jobtable():
conn = phoenixdb.connect(database_url, autocommit=True)
cursor = conn.cursor()
cursor.execute('''
CREATE TABLE analysis_job
(
jobId Integer PRIMARY KEY,
jobName VARCHAR(200) ,
jobPlace VARCHAR(200) ,
jobSalary VARCHAR(100) ,
jobAdvantage VARCHAR(300) ,
releaseTime VARCHAR(300) ,
jobNeed VARCHAR(300) ,
educationRequire VARCHAR(300) ,
experienceRequire VARCHAR(300) ,
skillRequire VARCHAR(300) ,
jobLink VARCHAR(500) ,
jobInfo VARCHAR ,
jobNature VARCHAR(200) ,
jobLabels VARCHAR(300) ,
company_id Integer NULL,
clicktimes Integer DEFAULT 0 ,
keyword VARCHAR(100) NULL
)
''')
def connect_db():
print("Making new database connection")
REQUIRED_OPTS = ['Username', 'Password', 'Url']
config = configparser.ConfigParser()
config.read('config.ini')
if not 'COD' in config:
raise Exception("Could not find section for COD in config.ini")
cod_config = config['COD']
opts = {}
# Validate the configuration
for required_opt in REQUIRED_OPTS:
if not required_opt in cod_config:
raise Exception("Did not find %s in configuration" %
(required_opt))
# Provide non-required options
if 'Truststore' in cod_config:
opts['verify'] = cod_config['Truststore']
if 'Authentication' in cod_config:
opts['authentication'] = cod_config['Authentication']
else:
opts['authentication'] = 'BASIC'
# Read required options
opts['avatica_user'] = cod_config['Username']
opts['avatica_password'] = cod_config['Password']
db = phoenixdb.connect(cod_config['Url'], autocommit=True, **opts)
return db
def run_query(self, query, user):
logger.error("Lindorm running query: %s", query)
connection = phoenixdb.connect(url=self.configuration.get("url", "") +
":" +
str(self.configuration.get("port", "")),
autocommit=True)
cursor = connection.cursor()
try:
cursor.execute(self.del_comments(query))
column_tuples = [(i[0], TYPES_MAPPING.get(i[1], None))
for i in cursor.description]
columns = self.fetch_columns(column_tuples)
rows = [
dict(zip(([column["name"] for column in columns]), r))
for i, r in enumerate(cursor.fetchall())
]
data = {"columns": columns, "rows": rows}
json_data = json_dumps(data)
error = None
cursor.close()
except Error as e:
json_data = None
error = "code: {}, sql state:{}, message: {}, Query:{}".format(
e.code, e.sqlstate, str(e), query)
finally:
if connection:
connection.close()
return json_data, error
def count_djob():
conn = phoenixdb.connect(database_url, autocommit=True)
cursor = conn.cursor()
cursor.execute("select count(*) from analysis_digitizedjob")
return cursor.fetchone()[0]
def getjob_byjobid(jobid):
conn = phoenixdb.connect(database_url, autocommit=True)
cursor = conn.cursor()
cursor.execute("select * from analysis_job where jobid = " + str(jobid))
return cursor.fetchone()
def connect_to_database():
log.info("Database Connection")
REQUIRED_OPTS = [
'Username', 'Password', 'Authentication', 'Url', 'Serialization'
]
config = configparser.ConfigParser()
config.read('config.ini')
if not 'COD' in config:
raise Exception("Could not find section for COD in config.ini")
cod_config = config['COD']
opts = {}
# Validate the configuration
for required_opt in REQUIRED_OPTS:
if not required_opt in cod_config:
raise Exception("Did not find %s in configuration" %
(required_opt))
# Read required options
opts['avatica_user'] = cod_config['Username']
opts['avatica_password'] = cod_config['Password']
opts['authentication'] = cod_config['Authentication']
opts['serialization'] = cod_config['serialization']
try:
db = phoenixdb.connect(cod_config['Url'], autocommit=True, **opts)
except Exception as e:
print(f"\tDATABASE CONNECTION ERROR: {str(e)}")
log.error(e)
raise
return db
def get_connection(self, db_name=None):
if self.conn:
return self.conn
database_url = f'http://{self.host}:{self.port}/'
self.conn = phoenixdb.connect(database_url, autocommit=True)
return self.conn
def insert_company(com=None):
"""
:param com: 一个包含所有company字段的元组
:return:
"""
if com is None:
com = [
32836, '达疆网络科技（上海）有限公司', '500-2000人', 'O2O', '['
'年底双薪'
', '
'绩效奖金'
', '
'岗位晋升'
', '
'定期体检'
']', 'https://www.lagou.com/gongsi/32836.html', '['
'达达-京东到家，是中国优质物流信息服务平台和生鲜商超O2O平台，同城速递信息服务平台“达达”目前已经覆盖全国360多个重要城市，拥有300多万众包配送员用户，80多万商家用户和3000万个人用户并为其提供平台信息服务，日单量峰值超过400万单。其生鲜商超O2O平台“京东到家”，包含超市便利、新鲜果蔬、零食小吃、鲜花烘焙、医药健康等业务，覆盖北京、上海、广州等22个城市，注册用户超过3000万。公司成立于2014年初，先后获得了红杉、DST、京东、沃尔玛等顶级基金和战略合作伙伴的投资，累计融资金额近7亿美金。'
']', 'nan', 0, 0, '['
'东外滩'
', '
'八佰伴'
', '
'梅园'
']', 'http://www.imdada.cn',
'i/image/M00/40/E3/CgpFT1lbMmCAJyQhAAA1-lNtzeQ674.png', 'D轮及以上'
]
conn = phoenixdb.connect(database_url, autocommit=True)
cursor = conn.cursor()
cursor.execute(
'''
upsert INTO analysis_company
(companyId, compName, compSize, compIndustry, companyLabels, compLink, compIntroduce, contactInfo, longitude, latitude, businessZones, compHome, companyLogo, financeStage)
VALUES(?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?)
''', com)
def run_query(self, query, user):
connection = phoenixdb.connect(
url=self.configuration.get('url', ''),
autocommit=True)
cursor = connection.cursor()
try:
cursor.execute(query)
column_tuples = [(i[0], TYPES_MAPPING.get(i[1], None)) for i in cursor.description]
columns = self.fetch_columns(column_tuples)
rows = [dict(zip(([c['name'] for c in columns]), r)) for i, r in enumerate(cursor.fetchall())]
data = {'columns': columns, 'rows': rows}
json_data = json_dumps(data)
error = None
cursor.close()
except Error as e:
json_data = None
error = 'code: {}, sql state:{}, message: {}'.format(e.code, e.sqlstate, e.message)
except (KeyboardInterrupt, InterruptException) as e:
error = "Query cancelled by user."
json_data = None
except Exception as ex:
json_data = None
error = unicode(ex)
finally:
if connection:
connection.close()
return json_data, error
def test_dict_cursor_default_attribute(self):
db = phoenixdb.connect(TEST_DB_URL, autocommit=True)
db.cursor_factory = phoenixdb.cursor.DictCursor
self.addCleanup(db.close)
with db.cursor() as cursor:
self._check_dict_cursor(cursor)
def test_dict_cursor_default_attribute(self):
db = phoenixdb.connect(TEST_DB_URL, autocommit=True)
db.cursor_factory = phoenixdb.cursor.DictCursor
self.addCleanup(db.close)
with db.cursor() as cursor:
self._check_dict_cursor(cursor)
def select_all_hbase():
database_url = 'http://localhost:8765/'
conn = phoenixdb.connect(database_url, autocommit=True)
cursor = conn.cursor()
a = cursor.execute("SELECT * FROM ROUTES2")
routes_list = []
data = cursor.fetchall()
data_numrows = len(data)
print "[log] jumlah baris: ", len(data)
for row in data :
route_temp = {
'id_route': row[0],
'airline': row[1],
'id_airline': row[2],
'src_airport': row[3],
'id_src_airport': row[4],
'dst_airport': row[5],
'id_dst_airport': row[6],
'codeshare': row[7],
'stop_val': row[8],
'equipment': row[9],
'log_date': row[10]
}
routes_list.append(route_temp)
result = {
"Flight_Rows" : data_numrows,
"Flight_Routes" : routes_list
}
return jsonify(result)
def run_query(self, query, user):
connection = phoenixdb.connect(url=self.configuration.get('url', ''),
autocommit=True)
cursor = connection.cursor()
try:
cursor.execute(query)
column_tuples = [(i[0], TYPES_MAPPING.get(i[1], None))
for i in cursor.description]
columns = self.fetch_columns(column_tuples)
rows = [
dict(zip(([c['name'] for c in columns]), r))
for i, r in enumerate(cursor.fetchall())
]
data = {'columns': columns, 'rows': rows}
json_data = json_dumps(data)
error = None
cursor.close()
except Error as e:
json_data = None
error = 'code: {}, sql state:{}, message: {}'.format(
e.code, e.sqlstate, e.message)
except (KeyboardInterrupt, InterruptException) as e:
error = "Query cancelled by user."
json_data = None
except Exception as ex:
json_data = None
error = unicode(ex)
finally:
if connection:
connection.close()
return json_data, error
def __init__(self, address):
url = 'http://{}/'.format(address)
logging.debug('Connect HBase: {}'.format(url))
try:
self.conn = phoenixdb.connect(url, autocommit=True)
except Exception as e:
raise Exception('连接异常>>> {}'.format(e))
def setUp(self):
self.conn = phoenixdb.connect(TEST_DB_URL, autocommit=True, **httpArgs)
def closeDb():
self.conn.close()
self.addCleanup(closeDb)
def get_cityjobsalary(city, **kwargs):
conn = phoenixdb.connect(database_url, autocommit=True)
cursor = conn.cursor()
start = kwargs.get('start')
end = kwargs.get('end')
if (end is None):
start = str(start)
cursor.execute("""
SELECT COUNT(JOBID) AS s
FROM ANALYSIS_JOB AS j
JOIN ANALYSIS_DIGITIZEDJOB AS d ON j.JOBID=d.job_id
WHERE d.salary > """ + start + """ AND j.JOBPLACE LIKE '""" +
city + """%'
""")
result = cursor.fetchone()[0]
else:
start = str(start)
end = str(end)
cursor.execute("""
SELECT COUNT(JOBID) AS s
FROM ANALYSIS_JOB AS j
JOIN ANALYSIS_DIGITIZEDJOB AS d ON j.JOBID=d.job_id
WHERE d.salary <= """ + start + """ AND d.SALARY>""" +
end + """ AND j.JOBPLACE LIKE '""" + city + """%'
""")
result = cursor.fetchone()[0]
print(city, start, end, result)
return result
def getConn(self, DATABASE_URL):
"""
:param DATABASE_URL:phoenix 服务的地址 ，记得启动phoenix的querysever.py 服务
:return:
"""
conn = phoenixdb.connect(DATABASE_URL, autocommit=True)
cursor = conn.cursor()
return conn, cursor
def Init(self):
try:
database_url = 'http://{}:{}/'.format(self.host, self.port)
conn = phoenixdb.connect(database_url, autocommit=True)
return conn
except:
Logging(msg=traceback.format_exc(), level='error')
return None
def ntest_phoenix():
conn = phoenixdb.connect(database_url, autocommit=True)
cursor = conn.cursor()
cursor.execute("SELECT * FROM analysis_company ")
all = cursor.fetchall()
for item in all:
print(item)
def getcompsize_bycompid(compid):
conn = phoenixdb.connect(database_url, autocommit=True)
cursor = conn.cursor()
cursor.execute("select compsize from analysis_company where COMPANYID = " +
str(compid))
return cursor.fetchone()[0]
def getdjob_byjobid(jobid):
conn = phoenixdb.connect(database_url, autocommit=True)
cursor = conn.cursor()
cursor.execute("select * from ANALYSIS_DIGITIZEDJOB where job_id = " +
str(jobid))
return cursor.fetchone()
def mysql_to_phoenixdb(user_name, repo_name):
connection = phoenixdb.connect('10.28.32.114:8765', autocommit=True)
print(connection)
cursor = connection.cursor()
Record.prepare(user_name, repo_name)
query = session.query(Record).filter(Record.record_date == datetime.date(2017, 8, 6)) \
.order_by(Record.record_date, Record.record_time, Record.version_id, Record.line_no)
def __init__(self):
try:
self.client = docker.from_env()
self.dbconn = phoenixdb.connect(self.dburl, autocommit=True)
except Exception as inst:
print(type(inst))
print(inst.args)
print(inst)
sys.exit(1)
def __init__(self, **kwargs):
# http://localhost:8765/
self.dburl = kwargs['dburl']
# autocommit = True
self.autocommit = kwargs['autocommit']
self.conn = phoenixdb.connect(self.dburl, autocommit=self.autocommit)
pass
def __init__(self):
opts = {}
opts['authentication'] = 'BASIC'
opts['avatica_user'] = config.WORKLOAD_USER
opts['avatica_password'] = config.WORKLOAD_PASSWORD
database_url = config.OPDB_ENDPOINT
self.TABLENAME = "users"
self.conn = phoenixdb.connect(database_url, autocommit=True, **opts)
self.curs = self.conn.cursor()
def __init__(self, url=default_phoenix_url):
self.phoenix_url = url
try:
self.connection = phoenixdb.connect(self.phoenix_url,
autocommit=True)
except Exception as e:
self.connection = None
logInfo('phoenix connection')
logInfo(e)
def __init__(self):
opts = {}
opts['authentication'] = 'BASIC'
opts['avatica_user'] = os.environ["WORKLOAD_USER"]
opts['avatica_password'] = os.environ["WORKLOAD_PASSWORD"]
database_url = os.environ["OPDB_ENDPOINT"]
self.TABLENAME = "stocks_feed"
self.conn = phoenixdb.connect(database_url, autocommit=True,**opts)
self.curs = self.conn.cursor()
def getjob_bykeyword(keyword):
conn = phoenixdb.connect(database_url, autocommit=True)
cursor = conn.cursor()
cursor.execute("select * from analysis_job where keyword = '" + keyword +
"'")
jobs = cursor.fetchall()
for job in jobs:
print(job)
def __init__(self):
opts = {}
opts['authentication'] = 'BASIC'
opts['avatica_user'] = '******'
opts['avatica_password'] = '******'
database_url = "<cod thin jdbc url>"
self.TABLENAME = "users"
self.conn = phoenixdb.connect(database_url, autocommit=True,**opts)
self.curs = self.conn.cursor()
def _connect(self, conf):
conf = copy.deepcopy(conf)
self.max_op_fail_retry = conf.pop('max_op_fail_retry', 0)
self.default_timeout = conf.pop('timeout', DEFAULT_TIMEOUT)
conn = phoenixdb.connect(random.choice(conf['urls']), autocommit=True)
cursor = conn.cursor(cursor_factory=phoenixdb.cursor.DictCursor)
return conn, cursor
def connect_to_phoenix():
try:
db = phoenixdb.connect(url=config.PHOENIX_QUERY_SERVER_URI,
max_retries=2,
autocommit=True)
except Exception as e:
print("PhoenixDB Failure Exception:\n %s" % str(e))
else:
return db
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 setUp(self):
self.conn = phoenixdb.connect(TEST_DB_URL, autocommit=True)
self.cleanup_tables = []
def __init__(self, database_url):
self.database_url = database_url
self.conn = phoenixdb.connect(database_url, autocommit=True)
#!/usr/bin/env python
import phoenixdb
with phoenixdb.connect('http://localhost:8765/', autocommit=True) as connection:
with connection.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 (?, ?)", [[1, 'hello'], [2, 'world']])
cursor.execute("SELECT * FROM test ORDER BY id")
for row in cursor:
print row
#
# 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())
